【PostgreSQL 02】PostgreSQL数据类型革命：JSON、数组与地理信息让你的应用飞起来

article/2025/6/21 7:30:57

PostgreSQL数据类型革命：JSON、数组与地理信息让你的应用飞起来

关键词
PostgreSQL高级数据类型, JSONB, 数组类型, PostGIS, 地理信息系统, NoSQL, 文档数据库, 空间数据, 数据库设计, PostgreSQL扩展

摘要
PostgreSQL的高级数据类型是其区别于传统关系数据库的核心优势。本文将通过实际案例深入解析JSON/JSONB、数组、地理信息等高级数据类型的使用方法和最佳实践。从电商用户画像到外卖配送系统，从社交网络到智慧城市，我们将看到这些数据类型如何让复杂的业务需求变得简单优雅。无论你是想要存储灵活的文档数据，还是需要处理复杂的地理位置信息，这篇文章都将为你提供完整的解决方案。

引言：当传统数据库遇到现代需求

想象一下，你正在开发一个现代化的电商平台。产品经理走过来说：

“我们需要存储用户的个性化偏好，每个用户的偏好结构都不一样，有些用户关心价格，有些关心品牌，有些关心配送速度…”

“我们还要做基于位置的推荐，用户在哪里，就推荐附近的商家…”

“用户的标签系统要灵活，可能有几个标签，也可能有几十个…”

如果你用的是传统的关系数据库思维，你可能会这样设计：

-- 传统方案：需要多个表
CREATE TABLE users (id, name, email);
CREATE TABLE user_preferences (user_id, preference_key, preference_value);
CREATE TABLE user_tags (user_id, tag_name);
CREATE TABLE user_locations (user_id, latitude, longitude);

但这样的设计有什么问题呢？

  • 查询复杂，需要多次JOIN
  • 性能不佳，特别是当数据量大时
  • 扩展困难，新增偏好类型需要修改表结构
  • 地理位置计算复杂，需要复杂的数学公式

PostgreSQL的高级数据类型就是为了解决这些问题而生的。让我们看看用PostgreSQL如何优雅地解决这些需求。

第一部分：JSON/JSONB - 让数据库拥抱NoSQL的灵活性

JSON vs JSONB：选择的智慧

PostgreSQL提供了两种JSON数据类型：JSON和JSONB。这就像选择存储文档的方式：

JSON类型：就像把文档原样保存在文件柜里

  • 保留原始格式（包括空格、键的顺序）
  • 存储为文本格式
  • 查询时需要重新解析

JSONB类型：就像把文档整理后放入高效的档案系统

  • 以二进制格式存储
  • 自动去除空格，重新排序键
  • 支持索引，查询速度快

在这里插入图片描述

实战案例：电商用户画像系统

让我们用一个真实的例子来看看JSONB的威力：

-- 创建用户表，使用JSONB存储复杂的偏好数据
CREATE TABLE users (id SERIAL PRIMARY KEY,name VARCHAR(100),email VARCHAR(255),preferences JSONB,created_at TIMESTAMP DEFAULT NOW()
);-- 插入用户数据
INSERT INTO users (name, email, preferences) VALUES 
('张三', 'zhangsan@example.com', '{"shopping": {"categories": ["电子产品", "图书", "服装"],"price_range": {"min": 100, "max": 5000},"brands": ["苹果", "华为", "小米"],"delivery": {"preferred_time": "晚上","accept_weekend": true}},"notifications": {"email": true,"sms": false,"push": true},"privacy": {"share_location": true,"show_online_status": false}
}'),
('李四', 'lisi@example.com', '{"shopping": {"categories": ["美食", "旅游"],"price_range": {"min": 50, "max": 2000},"delivery": {"preferred_time": "中午","accept_weekend": false}},"notifications": {"email": false,"sms": true,"push": true}
}');

现在看看JSONB的强大查询能力：

-- 1. 查找偏好电子产品的用户
SELECT name, email 
FROM users 
WHERE preferences->'shopping'->'categories' ? '电子产品';-- 2. 查找价格范围在1000-3000的用户
SELECT name, preferences->'shopping'->'price_range'->>'min' as min_price,preferences->'shopping'->'price_range'->>'max' as max_price
FROM users 
WHERE (preferences->'shopping'->'price_range'->>'min')::int <= 1000AND (preferences->'shopping'->'price_range'->>'max')::int >= 3000;-- 3. 查找接受邮件通知的用户
SELECT name, email
FROM users 
WHERE preferences->'notifications'->>'email' = 'true';-- 4. 更新用户偏好（只更新特定字段）
UPDATE users 
SET preferences = preferences || '{"shopping": {"new_feature": true}}'
WHERE id = 1;-- 5. 添加新的偏好类别
UPDATE users 
SET preferences = jsonb_set(preferences, '{shopping,categories}', preferences->'shopping'->'categories' || '["运动用品"]'
)
WHERE name = '张三';

JSONB的高级技巧

1. 创建索引提升查询性能

-- 为JSONB字段创建GIN索引
CREATE INDEX idx_user_preferences ON users USING GIN (preferences);-- 为特定路径创建索引
CREATE INDEX idx_user_shopping_categories 
ON users USING GIN ((preferences->'shopping'->'categories'));-- 查询性能测试
EXPLAIN ANALYZE 
SELECT * FROM users 
WHERE preferences->'shopping'->'categories' ? '电子产品';

2. 复杂的聚合查询

-- 统计各个类别的用户数量
SELECT category,COUNT(*) as user_count
FROM users,jsonb_array_elements_text(preferences->'shopping'->'categories') as category
GROUP BY category
ORDER BY user_count DESC;-- 计算平均价格范围
SELECT AVG((preferences->'shopping'->'price_range'->>'min')::int) as avg_min_price,AVG((preferences->'shopping'->'price_range'->>'max')::int) as avg_max_price
FROM users 
WHERE preferences->'shopping'->'price_range' IS NOT NULL;

3. 动态查询构建

-- 根据多个条件动态查询
WITH user_filters AS (SELECT id, name, preferences,CASE WHEN preferences->'shopping'->'categories' ? '电子产品' THEN 1 ELSE 0 END as likes_electronics,CASE WHEN (preferences->'shopping'->'price_range'->>'max')::int > 3000 THEN 1 ELSE 0 END as high_budget,CASE WHEN preferences->'notifications'->>'email' = 'true' THEN 1 ELSE 0 END as email_enabledFROM users
)
SELECT name, (likes_electronics + high_budget + email_enabled) as match_score
FROM user_filters
WHERE (likes_electronics + high_budget + email_enabled) >= 2
ORDER BY match_score DESC;

第二部分：数组类型 - 一对多关系的优雅解决方案

为什么需要数组类型？

传统的关系数据库处理一对多关系时，通常需要创建关联表：

-- 传统方案
CREATE TABLE users (id, name);
CREATE TABLE tags (id, name);
CREATE TABLE user_tags (user_id, tag_id);

但如果标签系统比较简单，这种设计就显得过于复杂。PostgreSQL的数组类型提供了更直接的解决方案：

-- PostgreSQL数组方案
CREATE TABLE users (id SERIAL PRIMARY KEY,name VARCHAR(100),tags TEXT[],  -- 文本数组scores INTEGER[],  -- 整数数组metadata JSONB[]  -- 甚至可以是JSONB数组
);

实战案例：社交媒体标签系统

-- 创建用户表，使用数组存储标签和技能
CREATE TABLE social_users (id SERIAL PRIMARY KEY,username VARCHAR(50),tags TEXT[],skills TEXT[],skill_levels INTEGER[],interests JSONB[],created_at TIMESTAMP DEFAULT NOW()
);-- 插入数据
INSERT INTO social_users (username, tags, skills, skill_levels, interests) VALUES 
('tech_guru', ARRAY['程序员', '技术博主', 'AI爱好者', '开源贡献者'],ARRAY['Python', 'PostgreSQL', 'Docker', 'Kubernetes'],ARRAY[9, 8, 7, 6],ARRAY['{"category": "技术", "topic": "机器学习", "level": "高级"}'::jsonb,'{"category": "生活", "topic": "摄影", "level": "中级"}'::jsonb]
),
('design_master',ARRAY['设计师', 'UI/UX', '创意工作者'],ARRAY['Photoshop', 'Figma', 'Sketch', 'After Effects'],ARRAY[9, 8, 7, 6],ARRAY['{"category": "设计", "topic": "平面设计", "level": "专家"}'::jsonb,'{"category": "艺术", "topic": "插画", "level": "高级"}'::jsonb]
);

数组的强大操作

1. 基础查询操作

-- 查找包含特定标签的用户
SELECT username, tags 
FROM social_users 
WHERE '程序员' = ANY(tags);-- 查找包含多个标签的用户
SELECT username, tags 
FROM social_users 
WHERE tags @> ARRAY['程序员', 'AI爱好者'];-- 查找标签数量超过3个的用户
SELECT username, array_length(tags, 1) as tag_count
FROM social_users 
WHERE array_length(tags, 1) > 3;-- 查找Python技能等级高于7的用户
SELECT username, skills, skill_levels
FROM social_users 
WHERE skills @> ARRAY['Python'] AND skill_levels[array_position(skills, 'Python')] > 7;

2. 数组操作和更新

-- 添加新标签
UPDATE social_users 
SET tags = array_append(tags, '新标签')
WHERE username = 'tech_guru';-- 删除特定标签
UPDATE social_users 
SET tags = array_remove(tags, 'AI爱好者')
WHERE username = 'tech_guru';-- 合并数组
UPDATE social_users 
SET tags = tags || ARRAY['机器学习专家', '数据科学家']
WHERE username = 'tech_guru';-- 去重数组
UPDATE social_users 
SET tags = (SELECT ARRAY(SELECT DISTINCT unnest(tags) ORDER BY 1)
);

3. 高级数组查询

-- 统计最受欢迎的标签
SELECT tag, COUNT(*) as user_count
FROM social_users,unnest(tags) as tag
GROUP BY tag
ORDER BY user_count DESC
LIMIT 10;-- 查找技能匹配度高的用户
WITH skill_matches AS (SELECT u1.username as user1,u2.username as user2,array_length(u1.skills & u2.skills, 1) as common_skillsFROM social_users u1CROSS JOIN social_users u2WHERE u1.id < u2.id
)
SELECT user1, user2, common_skills
FROM skill_matches
WHERE common_skills >= 2
ORDER BY common_skills DESC;-- 创建技能推荐系统
SELECT username,skills,ARRAY(SELECT DISTINCT skill FROM social_users s2,unnest(s2.skills) as skillWHERE s2.tags && s1.tags  -- 有共同标签AND NOT skill = ANY(s1.skills)  -- 但用户还没有这个技能GROUP BY skillORDER BY COUNT(*) DESCLIMIT 3) as recommended_skills
FROM social_users s1;

4. 数组索引优化

-- 为数组字段创建GIN索引
CREATE INDEX idx_social_users_tags ON social_users USING GIN (tags);
CREATE INDEX idx_social_users_skills ON social_users USING GIN (skills);-- 查询性能对比
EXPLAIN ANALYZE 
SELECT * FROM social_users WHERE tags @> ARRAY['程序员'];

第三部分：地理信息类型 - 构建位置智能应用

PostGIS：让PostgreSQL拥有空间超能力

PostGIS是PostgreSQL的地理信息扩展，它让数据库能够存储和查询地理空间数据。这就像给数据库装上了GPS导航系统。

-- 启用PostGIS扩展
CREATE EXTENSION postgis;-- 查看PostGIS版本
SELECT PostGIS_Version();

在这里插入图片描述

实战案例：外卖配送系统

让我们构建一个完整的外卖配送系统，看看地理信息类型的威力：

-- 创建餐厅表
CREATE TABLE restaurants (id SERIAL PRIMARY KEY,name VARCHAR(100),address TEXT,location GEOMETRY(POINT, 4326),  -- 使用WGS84坐标系delivery_radius INTEGER DEFAULT 3000,  -- 配送半径（米）rating DECIMAL(3,2),cuisine_type TEXT[],created_at TIMESTAMP DEFAULT NOW()
);-- 创建用户表
CREATE TABLE delivery_users (id SERIAL PRIMARY KEY,name VARCHAR(100),phone VARCHAR(20),current_location GEOMETRY(POINT, 4326),home_address GEOMETRY(POINT, 4326),work_address GEOMETRY(POINT, 4326),created_at TIMESTAMP DEFAULT NOW()
);-- 创建配送员表
CREATE TABLE delivery_drivers (id SERIAL PRIMARY KEY,name VARCHAR(100),phone VARCHAR(20),current_location GEOMETRY(POINT, 4326),is_available BOOLEAN DEFAULT true,vehicle_type VARCHAR(20),created_at TIMESTAMP DEFAULT NOW()
);-- 创建订单表
CREATE TABLE orders (id SERIAL PRIMARY KEY,user_id INTEGER REFERENCES delivery_users(id),restaurant_id INTEGER REFERENCES restaurants(id),driver_id INTEGER REFERENCES delivery_drivers(id),delivery_address GEOMETRY(POINT, 4326),status VARCHAR(20) DEFAULT 'pending',created_at TIMESTAMP DEFAULT NOW()
);

插入地理数据

-- 插入餐厅数据（北京地区）
INSERT INTO restaurants (name, address, location, delivery_radius, rating, cuisine_type) VALUES 
('老北京炸酱面', '北京市朝阳区三里屯', ST_GeomFromText('POINT(116.4551 39.9380)', 4326), 2000, 4.5, ARRAY['中餐', '面食']),
('麦当劳', '北京市朝阳区国贸', ST_GeomFromText('POINT(116.4579 39.9081)', 4326), 3000, 4.2, ARRAY['快餐', '西餐']),
('海底捞', '北京市海淀区中关村', ST_GeomFromText('POINT(116.3105 39.9830)', 4326), 5000, 4.8, ARRAY['火锅', '中餐']),
('星巴克', '北京市西城区西单', ST_GeomFromText('POINT(116.3770 39.9065)', 4326), 1500, 4.3, ARRAY['咖啡', '轻食']);-- 插入用户数据
INSERT INTO delivery_users (name, phone, current_location, home_address, work_address) VALUES 
('张三', '13800138001', ST_GeomFromText('POINT(116.4520 39.9350)', 4326),  -- 当前位置：三里屯附近ST_GeomFromText('POINT(116.4200 39.9100)', 4326),  -- 家庭地址ST_GeomFromText('POINT(116.4600 39.9200)', 4326)   -- 工作地址
),
('李四', '13800138002',ST_GeomFromText('POINT(116.3100 39.9800)', 4326),  -- 当前位置：中关村附近ST_GeomFromText('POINT(116.3000 39.9750)', 4326),ST_GeomFromText('POINT(116.3200 39.9850)', 4326)
);-- 插入配送员数据
INSERT INTO delivery_drivers (name, phone, current_location, vehicle_type) VALUES 
('王师傅', '13900139001', ST_GeomFromText('POINT(116.4500 39.9300)', 4326), '电动车'),
('赵师傅', '13900139002', ST_GeomFromText('POINT(116.3150 39.9820)', 4326), '摩托车'),
('刘师傅', '13900139003', ST_GeomFromText('POINT(116.4000 39.9150)', 4326), '电动车');

地理空间查询的魔法

1. 基础距离查询

-- 查找用户附近2公里内的餐厅
SELECT r.name,r.cuisine_type,r.rating,ST_Distance(r.location, u.current_location) as distance_meters
FROM restaurants r,delivery_users u
WHERE u.name = '张三'AND ST_DWithin(r.location, u.current_location, 2000)
ORDER BY distance_meters;-- 查找餐厅配送范围内的用户
SELECT u.name,u.phone,ST_Distance(r.location, u.current_location) as distance_meters
FROM restaurants r,delivery_users u
WHERE r.name = '海底捞'AND ST_DWithin(r.location, u.current_location, r.delivery_radius);

2. 智能配送员分配

-- 为订单分配最近的可用配送员
WITH order_location AS (SELECT ST_GeomFromText('POINT(116.4550 39.9370)', 4326) as location
),
available_drivers AS (SELECT d.*,ST_Distance(d.current_location, ol.location) as distanceFROM delivery_drivers d,order_location olWHERE d.is_available = true
)
SELECT name,phone,vehicle_type,distance
FROM available_drivers
ORDER BY distance
LIMIT 1;

3. 配送路径优化

-- 计算从餐厅到用户的配送路径
SELECT r.name as restaurant,u.name as customer,ST_Distance(r.location, u.current_location) as direct_distance,-- 如果有路网数据，可以计算实际路径距离ST_Length(ST_MakeLine(r.location, u.current_location)) as route_length
FROM restaurants r,delivery_users u
WHERE r.name = '老北京炸酱面'AND u.name = '张三';

4. 热力图分析

-- 分析订单密度热点
WITH order_grid AS (SELECT ST_SnapToGrid(delivery_address, 0.01) as grid_point,  -- 创建网格COUNT(*) as order_countFROM ordersWHERE created_at >= NOW() - INTERVAL '30 days'GROUP BY ST_SnapToGrid(delivery_address, 0.01)
)
SELECT ST_X(grid_point) as longitude,ST_Y(grid_point) as latitude,order_count
FROM order_grid
WHERE order_count > 10
ORDER BY order_count DESC;

5. 地理围栏功能

-- 创建配送区域多边形
CREATE TABLE delivery_zones (id SERIAL PRIMARY KEY,name VARCHAR(100),zone_polygon GEOMETRY(POLYGON, 4326),delivery_fee DECIMAL(10,2)
);-- 插入配送区域
INSERT INTO delivery_zones (name, zone_polygon, delivery_fee) VALUES 
('市中心区', ST_GeomFromText('POLYGON((116.35 39.85, 116.50 39.85, 116.50 39.95, 116.35 39.95, 116.35 39.85))', 4326), 5.00),
('郊区', ST_GeomFromText('POLYGON((116.25 39.80, 116.60 39.80, 116.60 40.00, 116.25 40.00, 116.25 39.80))', 4326), 8.00);-- 判断用户位置属于哪个配送区域
SELECT u.name,dz.name as zone_name,dz.delivery_fee
FROM delivery_users u
JOIN delivery_zones dz ON ST_Within(u.current_location, dz.zone_polygon);

地理空间索引优化

-- 为地理字段创建空间索引
CREATE INDEX idx_restaurants_location ON restaurants USING GIST (location);
CREATE INDEX idx_users_current_location ON delivery_users USING GIST (current_location);
CREATE INDEX idx_drivers_location ON delivery_drivers USING GIST (current_location);-- 查看索引使用情况
EXPLAIN ANALYZE 
SELECT * FROM restaurants 
WHERE ST_DWithin(location, ST_GeomFromText('POINT(116.4520 39.9350)', 4326), 2000);

第四部分：其他高级数据类型

UUID：全局唯一标识符

-- 启用UUID扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";-- 使用UUID作为主键
CREATE TABLE products (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),name VARCHAR(100),price DECIMAL(10,2),created_at TIMESTAMP DEFAULT NOW()
);-- 插入数据
INSERT INTO products (name, price) VALUES 
('iPhone 15', 7999.00),
('MacBook Pro', 15999.00);-- 查询
SELECT * FROM products;

枚举类型：类型安全的选择

-- 创建枚举类型
CREATE TYPE order_status AS ENUM ('pending', 'confirmed', 'preparing', 'delivering', 'completed', 'cancelled');
CREATE TYPE priority_level AS ENUM ('low', 'medium', 'high', 'urgent');-- 使用枚举类型
CREATE TABLE tasks (id SERIAL PRIMARY KEY,title VARCHAR(200),status order_status DEFAULT 'pending',priority priority_level DEFAULT 'medium',created_at TIMESTAMP DEFAULT NOW()
);-- 插入数据
INSERT INTO tasks (title, status, priority) VALUES 
('完成数据库设计', 'preparing', 'high'),
('编写API文档', 'pending', 'medium');-- 枚举类型的优势：类型安全
-- 这会报错：INSERT INTO tasks (status) VALUES ('invalid_status');

范围类型：处理区间数据

-- 使用范围类型处理时间段和数值区间
CREATE TABLE events (id SERIAL PRIMARY KEY,name VARCHAR(100),time_range TSRANGE,  -- 时间范围price_range NUMRANGE,  -- 价格范围age_range INT4RANGE   -- 年龄范围
);-- 插入数据
INSERT INTO events (name, time_range, price_range, age_range) VALUES 
('音乐节', '[2024-06-01 10:00, 2024-06-01 22:00)', '[100, 500)', '[18, 65)'),
('儿童剧', '[2024-06-02 14:00, 2024-06-02 16:00)', '[50, 200)', '[3, 12)');-- 范围查询
-- 查找特定时间有哪些活动
SELECT name FROM events 
WHERE time_range @> '2024-06-01 15:00'::timestamp;-- 查找价格区间重叠的活动
SELECT name FROM events 
WHERE price_range && '[150, 300)'::numrange;-- 查找适合25岁用户的活动
SELECT name FROM events 
WHERE age_range @> 25;

复合类型：自定义数据结构

-- 创建复合类型
CREATE TYPE address_type AS (street VARCHAR(100),city VARCHAR(50),state VARCHAR(50),zip_code VARCHAR(10),country VARCHAR(50)
);CREATE TYPE contact_info AS (email VARCHAR(100),phone VARCHAR(20),address address_type
);-- 使用复合类型
CREATE TABLE companies (id SERIAL PRIMARY KEY,name VARCHAR(100),contact contact_info
);-- 插入数据
INSERT INTO companies (name, contact) VALUES 
('科技公司', ROW('tech@company.com', '010-12345678', ROW('中关村大街1号', '北京', '北京市', '100000', '中国')));-- 查询复合类型
SELECT name,(contact).email,((contact).address).city
FROM companies;

第五部分：性能优化与最佳实践

索引策略

-- 1. JSONB索引策略
-- GIN索引：适合包含查询
CREATE INDEX idx_user_preferences_gin ON users USING GIN (preferences);-- 表达式索引：适合特定路径查询
CREATE INDEX idx_user_email_notifications 
ON users ((preferences->'notifications'->>'email'));-- 2. 数组索引策略
-- GIN索引：适合数组包含查询
CREATE INDEX idx_user_tags_gin ON social_users USING GIN (tags);-- 3. 地理空间索引策略
-- GIST索引：适合空间查询
CREATE INDEX idx_restaurants_location_gist ON restaurants USING GIST (location);

查询优化技巧

-- 1. 使用EXPLAIN ANALYZE分析查询性能
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM users 
WHERE preferences->'shopping'->'categories' ? '电子产品';-- 2. 避免不必要的数据转换
-- 好的做法
SELECT * FROM users WHERE preferences->>'email_verified' = 'true';
-- 不好的做法
SELECT * FROM users WHERE (preferences->>'email_verified')::boolean = true;-- 3. 使用合适的操作符
-- 包含查询使用 @>
SELECT * FROM social_users WHERE tags @> ARRAY['程序员'];
-- 相交查询使用 &&
SELECT * FROM social_users WHERE tags && ARRAY['程序员', '设计师'];

数据建模最佳实践

1. 何时使用JSONB

  • ✅ 数据结构灵活，经常变化
  • ✅ 嵌套层级不深（建议不超过3层）
  • ✅ 查询模式相对固定
  • ❌ 需要强类型约束
  • ❌ 需要复杂的关联查询

2. 何时使用数组

  • ✅ 一对多关系，且"多"的一方结构简单
  • ✅ 不需要复杂的关联查询
  • ✅ 数组元素数量相对固定（建议不超过100个）
  • ❌ 需要频繁的增删改操作
  • ❌ 需要复杂的统计分析

3. 何时使用地理类型

  • ✅ 需要距离计算
  • ✅ 需要空间关系判断
  • ✅ 需要地理围栏功能
  • ❌ 只是简单的经纬度存储

第六部分：实战项目：构建智能推荐系统

让我们把所有学到的知识整合起来，构建一个完整的智能推荐系统：

-- 创建综合用户表
CREATE TABLE smart_users (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),username VARCHAR(50) UNIQUE,email VARCHAR(100),profile JSONB,interests TEXT[],location GEOMETRY(POINT, 4326),activity_zones GEOMETRY(POLYGON, 4326)[],created_at TIMESTAMP DEFAULT NOW(),updated_at TIMESTAMP DEFAULT NOW()
);-- 创建内容表
CREATE TABLE content_items (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),title VARCHAR(200),content_type VARCHAR(50),tags TEXT[],metadata JSONB,location GEOMETRY(POINT, 4326),target_audience JSONB,created_at TIMESTAMP DEFAULT NOW()
);-- 创建用户行为表
CREATE TABLE user_behaviors (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),user_id UUID REFERENCES smart_users(id),content_id UUID REFERENCES content_items(id),behavior_type VARCHAR(20), -- view, like, share, commentbehavior_data JSONB,location GEOMETRY(POINT, 4326),created_at TIMESTAMP DEFAULT NOW()
);-- 插入示例数据
INSERT INTO smart_users (username, email, profile, interests, location) VALUES 
('tech_lover', 'tech@example.com', '{"age": 28, "occupation": "软件工程师", "experience_years": 5, "skills": ["Python", "PostgreSQL", "React"]}',ARRAY['编程', '人工智能', '开源项目', '技术博客'],ST_GeomFromText('POINT(116.4074 39.9042)', 4326)
);-- 智能推荐查询
WITH user_profile AS (SELECT * FROM smart_users WHERE username = 'tech_lover'
),
content_scores AS (SELECT c.*,-- 兴趣匹配分数(SELECT COUNT(*) FROM unnest(c.tags) tag WHERE tag = ANY(up.interests)) * 10 as interest_score,-- 位置分数（距离越近分数越高）CASE WHEN c.location IS NOT NULL AND up.location IS NOT NULL THENGREATEST(0, 100 - ST_Distance(c.location, up.location) / 100)ELSE 0END as location_score,-- 用户画像匹配分数CASE WHEN c.target_audience->'age_range' IS NOT NULL THENCASE WHEN (up.profile->>'age')::int BETWEEN (c.target_audience->'age_range'->>'min')::int AND (c.target_audience->'age_range'->>'max')::int THEN 20 ELSE 0ENDELSE 10END as demographic_scoreFROM content_items c, user_profile up
)
SELECT title,content_type,tags,interest_score + location_score + demographic_score as total_score
FROM content_scores
WHERE interest_score > 0 OR location_score > 50
ORDER BY total_score DESC
LIMIT 10;

总结：拥抱PostgreSQL的数据类型革命

PostgreSQL的高级数据类型不仅仅是技术特性，它们代表了一种新的数据建模思维：

核心优势回顾

  1. JSONB：让关系数据库拥有NoSQL的灵活性

    • 高性能的文档存储
    • 强大的查询能力
    • 完整的索引支持

  2. 数组类型：简化一对多关系的处理

    • 减少表连接
    • 提升查询性能
    • 保持数据完整性

  3. 地理信息类型：构建位置智能应用

    • 精确的距离计算
    • 高效的空间查询
    • 丰富的地理函数

  4. 其他高级类型：满足特殊业务需求

    • UUID保证全局唯一性
    • 枚举类型提供类型安全
    • 范围类型处理区间数据

选择指南

数据特征推荐类型使用场景
结构灵活、层级嵌套JSONB用户偏好、配置信息、产品属性
一对多、结构简单数组标签系统、技能列表、分类
地理位置相关PostGISLBS应用、配送系统、地图服务
需要全局唯一UUID分布式系统、微服务架构
有限选择集合枚举状态字段、优先级、类别

最佳实践总结

  1. 合理建模：根据查询模式选择合适的数据类型
  2. 索引优化：为高级数据类型创建合适的索引
  3. 性能监控：使用EXPLAIN ANALYZE分析查询性能
  4. 渐进迁移：从简单场景开始，逐步应用高级特性

PostgreSQL的高级数据类型让我们能够用更自然的方式表达复杂的业务逻辑，减少不必要的表连接，提升应用性能。在数据驱动的时代，掌握这些特性将让你的应用更加强大和灵活。

下一篇预告：《PostGIS空间数据深度实战：从地图服务到智慧城市》

我们将深入探讨PostGIS的高级功能，学习如何构建复杂的地理信息系统，从简单的地图服务到智慧城市的空间分析应用。

如果这篇文章对你有帮助，欢迎点赞、收藏和分享。有任何问题或建议，欢迎在评论区讨论！


http://www.hkcw.cn/article/WGGoApRVIW.shtml

相关文章

[Windows] 剪映 视频编辑处理

[Windows] 剪映 视频编辑处理

附链接&#xff1a;夸克网盘分享&#xff08;点击蓝色字体自行保存下载&#xff09;
阅读更多...
NW994NX734美光固态闪存NX737NX740

NW994NX734美光固态闪存NX737NX740

NW994NX734美光固态闪存NX737NX740 在数字化浪潮汹涌澎湃的今天&#xff0c;数据存储技术如同一座坚实的基石&#xff0c;支撑着科技世界的大厦。美光固态闪存以其卓越的性能和创新的技术&#xff0c;在存储领域占据着重要的地位。本文将深入剖析NW994、NX734、NX737以及NX740…
阅读更多...
C# 类和继承(使用基类的引用)

C# 类和继承(使用基类的引用)

使用基类的引用 派生类的实例由基类的实例和派生类新增的成员组成。派生类的引用指向整个类对象&#xff0c;包括 基类部分。 如果有一个派生类对象的引用&#xff0c;就可以获取该对象基类部分的引用&#xff08;使用类型转换运算符把 该引用转换为基类类型&#xff09;。类…
阅读更多...
VMvare 创建虚拟机 安装CentOS7，配置静态IP地址

VMvare 创建虚拟机 安装CentOS7，配置静态IP地址

创建虚拟机 安装CentOS7 设置网络模式 设置静态ip vim /etc/sysconfig/network-scripts/ifcfg-ens33 systemctl restart network
阅读更多...
python：PyMOL 能处理 *.pdb 文件吗？

python：PyMOL 能处理 *.pdb 文件吗？

PyMOL 完全可以打开并处理 PDB&#xff08;Protein Data Bank&#xff09;文件&#xff0c;这是 PyMOL 最主要的功能之一。PDB 格式是结构生物学领域的标准文件格式&#xff0c;专门用于存储生物大分子&#xff08;如蛋白质、核酸&#xff09;的三维结构数据。 在 PyMOL 中打开…
阅读更多...
【数据治理】要点整理-信息技术数据质量评价指标-GB/T36344-2018

【数据治理】要点整理-信息技术数据质量评价指标-GB/T36344-2018

导读&#xff1a;指标为数据质量评估提供了一套系统化、标准化的框架&#xff0c;涵盖规范性、完整性、准确性、一致性、时效性、可访问性六大核心指标&#xff0c;助力组织提升数据处理效率、支持决策制定及业务流程优化&#xff0c;确保数据在数据生存周期各阶段的质量可控。…
阅读更多...
【Redis】hash 类型

【Redis】hash 类型

hash 一. hash 类型介绍二. hash 命令hset、hgethexists、hdelhkeys、hvals、hgetallhmset、hmgethlen、hstrlen、hsetnxhincrby、hincrbyfloat 三. hash 命令小结四. hash 内部编码方式五. hash 的应用场景缓存功能缓存方式对比 一. hash 类型介绍 哈希表在日常开发中&#x…
阅读更多...
ubuntu/windows系统下如何让.desktop/.exe文件 在开机的时候自动运行

ubuntu/windows系统下如何让.desktop/.exe文件 在开机的时候自动运行

目录 1&#xff0c;​​让 .desktop 文件在 Ubuntu 开机时自动启动​ 1.1 创建 autostart 目录&#xff08;如果不存在&#xff09;​ ​ 1.2 将 .desktop 文件复制到 autostart 目录​ ​ 1.3 确保 .desktop 文件有可执行权限​ 2,windows 2.1 打开「启动」文件夹​…
阅读更多...
1-Wire 一线式总线：从原理到实战，玩转 DS18B20 温度采集

1-Wire 一线式总线：从原理到实战，玩转 DS18B20 温度采集

引言 在嵌入式系统中&#xff0c;通信总线是连接 CPU 与外设的桥梁。从 I2C、SPI 到 UART&#xff0c;每种总线都有其独特的应用场景。而本文要介绍的1-Wire 一线式总线&#xff0c;以其极简的硬件设计和独特的通信协议&#xff0c;在温度采集、身份识别等领域大放异彩。本文将…
阅读更多...
有机黑鸡蛋与普通鸡蛋：差异剖析与选购指南

有机黑鸡蛋与普通鸡蛋：差异剖析与选购指南

在我们的日常饮食结构里&#xff0c;鸡蛋始终占据着不可或缺的位置&#xff0c;是人们获取营养的重要来源。如今&#xff0c;市场上鸡蛋种类丰富&#xff0c;除了常见的普通鸡蛋&#xff0c;有机黑鸡蛋也逐渐崭露头角&#xff0c;其价格通常略高于普通鸡蛋。这两者究竟存在哪些…
阅读更多...
Fastapi 学习使用

Fastapi 学习使用

Fastapi 学习使用 Fastapi 可以用来快速搭建 Web 应用来进行接口的搭建。 参考文章&#xff1a;https://blog.csdn.net/liudadaxuexi/article/details/141062582 参考文章&#xff1a;https://blog.csdn.net/jcgeneral/article/details/146505880 参考文章&#xff1a;http…
阅读更多...
数字化转型进阶：精读41页华为数字化转型实践【附全文阅读】

数字化转型进阶：精读41页华为数字化转型实践【附全文阅读】

该文档聚焦华为数字化转型实践&#xff0c;核心内容如下&#xff1a; 转型本质与目标&#xff1a;数字化转型是通过数字技术穿透业务&#xff0c;实现物理世界与数字世界的融合&#xff0c;目标是支撑主业成功、提升体验与效率、探索模式创新。华为以 “平台 服务” 为核心&am…
阅读更多...
共享内存-systemV

共享内存-systemV

01. 共享内存简述 共享内存是一个允许多个进程直接访问同一块物理内存区域的进程通信工具&#xff0c;因其本身不涉及用户态与核心态之间转换&#xff0c;故效率最佳。为了使用一个共享内存段&#xff0c;一般需要以下几个步骤&#xff1a; 调用shmget()创建一个新共享内存段…
阅读更多...
大语言模型值ollama使用（1）

大语言模型值ollama使用（1）

ollama为本地调用大语言模型提供了便捷的方式。下面列举如何在windows系统中快捷调用ollama。 winR打开运行框&#xff0c;输入cmd 1、输入ollama list 显示已下载模型 2、输入ollama pull llama3 下载llama3模型 3、 输入 ollama run llama3 运行模型 4、其他 ollama li…
阅读更多...
【基础算法】高精度（加、减、乘、除）

【基础算法】高精度（加、减、乘、除）

文章目录 什么是高精度1. 高精度加法解题思路代码实现 2. 高精度减法解题思路代码实现 3. 高精度乘法解题思路代码实现 4. 高精度除法 (高精度 / 低精度)解题思路代码实现 什么是高精度 我们平时使用加减乘除的时候都是直接使用 - * / 这些符号&#xff0c;前提是进行运算的数…
阅读更多...
uni-data-picker级联选择器、fastadmin后端api

uni-data-picker级联选择器、fastadmin后端api

记录一个部门及部门人员选择的功能&#xff0c;效果如下&#xff1a; 组件用到了uni-ui的级联选择uni-data-picker 开发文档&#xff1a;uni-app官网 组件要求的数据格式如下&#xff1a; 后端使用的是fastadmin&#xff0c;需要用到fastadmin自带的tree类生成部门树 &#x…
阅读更多...
MonitorSDK_性能监控（从Web Vital性能指标、PerformanceObserver API和具体代码实现）

MonitorSDK_性能监控（从Web Vital性能指标、PerformanceObserver API和具体代码实现）

性能监控 性能指标 在实现性能监控前&#xff0c;先了解Web Vitals涉及的常见的性能指标 Web Vitals 是由 Google 推出的网页用户体验衡量指标体系&#xff0c;旨在帮助开发者量化和优化网页在实际用户终端上的性能体验。Web Vitals 强调“以用户为中心”的度量&#xff0c;而…
阅读更多...
Kubernetes架构与核心概念深度解析：Pod、Service与RBAC的奥秘

Kubernetes架构与核心概念深度解析：Pod、Service与RBAC的奥秘

&#x1f525;「炎码工坊」技术弹药已装填&#xff01; 点击关注 → 解锁工业级干货【工具实测|项目避坑|源码燃烧指南】 引言&#xff1a;云原生时代的操作系统 在云原生技术浪潮中&#xff0c;Kubernetes&#xff08;简称K8s&#xff09;已成为容器编排领域的"分布式操…
阅读更多...
enumiax：IAX 协议用户名枚举器！全参数详细教程！Kali Linux教程！

enumiax：IAX 协议用户名枚举器！全参数详细教程！Kali Linux教程！

简介 enumIAX 是一个 Inter Asterisk Exchange 协议用户名暴力枚举器。enumIAX 可以以两种不同的模式运行&#xff1b;顺序用户名猜测或字典攻击。 enumIAX 可以以两种不同的模式运行&#xff1a;顺序用户名猜测或字典攻击。 顺序用户名猜测 在顺序用户名猜测模式下&#xf…
阅读更多...
《深入解析SPI协议及其FPGA高效实现》-- 第一篇：SPI协议基础与工作机制

《深入解析SPI协议及其FPGA高效实现》-- 第一篇：SPI协议基础与工作机制

第一篇&#xff1a;SPI协议基础与工作机制 1. 串行外设接口导论 1.1 SPI的核心定位 协议本质 &#xff1a; 全双工同步串行协议&#xff08;对比UART异步、IC半双工&#xff09;核心优势 &#xff1a; 无寻址开销&#xff08;通过片选直连&#xff09;时钟速率可达100MHz&…
阅读更多...
推荐文章

Copyright @ 2022~2023