PostgreSQL教程FG045-PG复合类型与数组类型：结构化数据存储详解
本文档风哥主要介绍PostgreSQL教程045相关内容。风哥教程参考PostgreSQL官方文档Server Administration, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
1. 复合类型概述
复合类型（Composite Type）允许将多个字段组合成一个自定义类型，类似于其他语言中的结构体或对象。
2. 创建复合类型
使用CREATE TYPE语句创建复合类型：
CREATE TYPE fgedu_address AS (
province VARCHAR(50),
city VARCHAR(50),
district VARCHAR(50),
street VARCHAR(100),
postal_code VARCHAR(10)
);
— 创建坐标复合类型
CREATE TYPE fgedu_coordinate AS (
latitude NUMERIC(10, 6),
longitude NUMERIC(10, 6)
);
— 创建联系人复合类型
CREATE TYPE fgedu_contact AS (
name VARCHAR(50),
phone VARCHAR(20),
email VARCHAR(100)
);
— 查看复合类型定义
SELECT
t.typname AS type_name,
a.attname AS field_name,
a.attnum AS field_order,
format_type(a.atttypid, a.atttypmod) AS field_type
FROM pg_type t
JOIN pg_attribute a ON t.oid = a.attrelid
WHERE t.typname = ‘fgedu_address’ AND a.attnum > 0
ORDER BY a.attnum;
执行结果：
—————+————-+————-+———————
fgedu_address | province | 1 | character varying(50)
fgedu_address | city | 2 | character varying(50)
fgedu_address | district | 3 | character varying(50)
fgedu_address | street | 4 | character varying(100)
fgedu_address | postal_code | 5 | character varying(10)
(5 rows)
3. 复合类型操作
创建使用复合类型的表并进行操作：
CREATE TABLE fgedu_customers (
customer_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100),
home_address fgedu_address,
work_address fgedu_address,
location fgedu_coordinate,
emergency_contact fgedu_contact
);
— 插入数据（使用ROW构造器）
INSERT INTO fgedu_customers (customer_name, home_address, work_address, location, emergency_contact) VALUES
(‘风哥1号’,
ROW(‘北京市’, ‘北京市’, ‘海淀区’, ‘中关村大街1号’, ‘100080’),
ROW(‘北京市’, ‘北京市’, ‘朝阳区’, ‘建国门外大街10号’, ‘100022’),
ROW(39.9847, 116.3054),
ROW(‘风哥2号’, ‘13800138000’, ‘lisi@fgedu.net’)),
(‘王五’,
ROW(‘上海市’, ‘上海市’, ‘浦东新区’, ‘陆家嘴环路1000号’, ‘200120’),
ROW(‘上海市’, ‘上海市’, ‘黄浦区’, ‘南京东路100号’, ‘200001’),
ROW(31.2397, 121.4998),
ROW(‘赵六’, ‘13900139000’, ‘zhaoliu@fgedu.net’));
— 查询数据
SELECT customer_id, customer_name, home_address FROM fgedu_customers;
执行结果：
from oracle:www.itpux.com
————-+—————+——————————————————
1 | 风哥1号 | (北京市,北京市,海淀区,中关村大街1号,100080)
2 | 王五 | (上海市,上海市,浦东新区,陆家嘴环路1000号,200120)
(2 rows)
访问复合类型字段：
SELECT
customer_name,
(home_address).province AS province,
(home_address).city AS city,
(home_address).street AS street,
(location).latitude AS lat,
(location).longitude AS lng,
(emergency_contact).name AS contact_name,
(emergency_contact).phone AS contact_phone
FROM fgedu_customers;
执行结果：
—————+———-+———-+————————-+———-+———–+————–+—————
风哥1号 | 北京市 | 北京市 | 中关村大街1号 | 39.984700 | 116.305400 | 风哥2号 | 13800138000
王五 | 上海市 | 上海市 | 陆家嘴环路1000号 | 31.239700 | 121.499800 | 赵六 | 13900139000
(2 rows)
更新复合类型字段：
UPDATE fgedu_customers
SET home_address = ROW(‘北京市’, ‘北京市’, ‘西城区’, ‘金融街1号’, ‘100032’)
WHERE customer_id = 1;
— 更新复合类型的单个字段
UPDATE fgedu_customers
SET home_address.street = ‘金融街2号’
WHERE customer_id = 1;
— 查询更新后的数据
SELECT customer_name, home_address FROM fgedu_customers WHERE customer_id = 1;
执行结果：
—————+——————————————————
风哥1号 | (北京市,北京市,西城区,金融街2号,100032)
(1 row)
复合类型比较：
更多学习教程公众号风哥教程itpux_com
SELECT
customer_name,
home_address = work_address AS same_address,
(home_address).province = (work_address).province AS same_province
FROM fgedu_customers;
执行结果：
—————+————–+—————
风哥1号 | f | t
王五 | f | t
(2 rows)
4. 数组类型概述
PostgreSQL支持数组类型，可以在单个字段中存储多个值。
CREATE TABLE fgedu_array_test (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
phone_numbers VARCHAR(20)[],
scores INTEGER[],
tags TEXT[],
matrix INTEGER[][]
);
— 插入数组数据
INSERT INTO fgedu_array_test (name, phone_numbers, scores, tags, matrix) VALUES
(‘风哥1号’, ARRAY[‘13800138000’, ‘13900139000’], ARRAY[85, 90, 78, 92], ARRAY[‘技术’, ‘开发’, ‘PostgreSQL’],
ARRAY[[1,2,3], [4,5,6]]),
(‘风哥2号’, ARRAY[‘13700137000’], ARRAY[70, 85, 90], ARRAY[‘市场’, ‘销售’], ARRAY[[7,8,9], [10,11,12]]),
(‘王五’, ‘{15000138000,15100139000,15200139000}’, ‘{95,88,92,87,90}’, ‘{管理,项目}’, ‘{{1,2},{3,4}}’);
— 查询数据
SELECT * FROM fgedu_array_test;
执行结果：
—-+——+——————————–+——————-+————————–+———————
1 | 风哥1号 | {13800138000,13900139000} | {85,90,78,92} | {技术,开发,PostgreSQL} | {{1,2,3},{4,5,6}}
2 | 风哥2号 | {13700137000} | {70,85,90} | {市场,销售} | {{7,8,9},{10,11,12}}
3 | 王五 | {15000138000,15100139000,15200139000} | {95,88,92,87,90} | {管理,项目} | {{1,2},{3,4}}
(3 rows)
5. 数组操作详解
访问数组元素：
SELECT
name,
phone_numbers[1] AS first_phone,
phone_numbers[2] AS second_phone,
scores[1] AS first_score,
scores[2:3] AS scores_2_3,
matrix[1][2] AS matrix_1_2
FROM fgedu_array_test;
执行结果：
——+—————+————–+————-+————+————
风哥1号 | 13800138000 | 13900139000 | 85 | {90,78} | 2
风哥2号 | 13700137000 | | 70 | {85,90} | 8
王五 | 15000138000 | 15100139000 | 95 | {88,92} | 2
(3 rows)
修改数组：
UPDATE fgedu_array_test
SET phone_numbers[1] = ‘18800138000’
WHERE name = ‘风哥1号’;
— 添加数组元素
UPDATE fgedu_array_test
SET phone_numbers = array_fgappend(phone_numbers, ‘18900139000’)
WHERE name = ‘风哥1号’;
— 删除数组元素
UPDATE fgedu_array_test
SET phone_numbers = array_remove(phone_numbers, ‘13900139000’)
WHERE name = ‘风哥1号’;
— 查询修改后的数据
SELECT name, phone_numbers FROM fgedu_array_test WHERE name = ‘风哥1号’;
执行结果：
——+——————————–
风哥1号 | {18800138000,18900139000}
(1 row)
数组切片：
学习交流加群风哥QQ113257174
SELECT
name,
scores,
scores[1:2] AS first_two,
scores[2:4] AS middle_three,
scores[3:] AS from_third
FROM fgedu_array_test;
执行结果：
——+——————-+———–+————–+—————
风哥1号 | {85,90,78,92} | {85,90} | {90,78,92} | {78,92}
风哥2号 | {70,85,90} | {70,85} | {85,90} | {90}
王五 | {95,88,92,87,90} | {95,88} | {88,92,87} | {92,87,90}
(3 rows)
6. 数组函数与运算符
数组聚合函数：
SELECT
name,
scores,
array_length(scores, 1) AS length,
cardinality(scores) AS cardinality,
array_dims(scores) AS dims
FROM fgedu_array_test;
执行结果：
——+——————-+——–+————-+———
风哥1号 | {85,90,78,92} | 4 | 4 | [1:4]
风哥2号 | {70,85,90} | 3 | 3 | [1:3]
王五 | {95,88,92,87,90} | 5 | 5 | [1:5]
(3 rows)
数组搜索：
SELECT
name,
scores,
85 = ANY(scores) AS has_85,
100 = ANY(scores) AS has_100,
90 = ALL(scores) AS all_90,
ARRAY[85, 90] && scores AS has_both
FROM fgedu_array_test;
执行结果：
——+——————-+——–+———+——–+———-
风哥1号 | {85,90,78,92} | t | f | f | t
风哥2号 | {70,85,90} | t | f | f | t
王五 | {95,88,92,87,90} | f | f | f | t
(3 rows)
数组操作函数：
风哥提示：
SELECT
name,
scores,
array_fgappend(scores, 100) AS fgappend_100,
array_prepend(60, scores) AS prepend_60,
array_cat(scores, ARRAY[80, 85]) AS concat,
array_position(scores, 90) AS pos_90,
array_positions(scores, 90) AS all_pos_90
FROM fgedu_array_test;
执行结果：
——+——————-+———————+———————+————————-+——–+————
风哥1号 | {85,90,78,92} | {85,90,78,92,100} | {60,85,90,78,92} | {85,90,78,92,80,85} | 2 | {2}
风哥2号 | {70,85,90} | {70,85,90,100} | {60,70,85,90} | {70,85,90,80,85} | 3 | {3}
王五 | {95,88,92,87,90} | {95,88,92,87,90,100}| {60,95,88,92,87,90} | {95,88,92,87,90,80,85} | 5 | {5}
(3 rows)
数组展开与聚合：
SELECT
name,
unnest(scores) AS score
FROM fgedu_array_test
WHERE name = ‘风哥1号’;
执行结果：
——+——-
风哥1号 | 85
风哥1号 | 90
风哥1号 | 78
风哥1号 | 92
(4 rows)
SELECT
name,
array_agg(score ORDER BY score DESC) AS sorted_scores
FROM (
SELECT name, unnest(scores) AS score
FROM fgedu_array_test
) sub
GROUP BY name;
执行结果：
——+—————–
风哥1号 | {92,90,85,78}
风哥2号 | {90,85,70}
王五 | {95,92,90,88,87}
(3 rows)
7. 复合类型与数组组合
复合类型数组：
CREATE TYPE fgedu_order_item AS (
product_id INTEGER,
product_name VARCHAR(100),
quantity INTEGER,
unit_price NUMERIC(10, 2)
);
— 创建订单表（使用复合类型数组）
CREATE TABLE fgedu_orders (
order_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100),
items fgedu_order_item[],
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
total_amount NUMERIC(12, 2)
);
— 插入订单数据
INSERT INTO fgedu_orders (customer_name, items, total_amount) VALUES
(‘风哥1号’, ARRAY[
ROW(1, ‘笔记本电脑’, 1, 5999.00)::fgedu_order_item,
ROW(2, ‘无线鼠标’, 2, 99.00)::fgedu_order_item,
ROW(3, ‘机械键盘’, 1, 399.00)::fgedu_order_item
], 6596.00),
(‘风哥2号’, ARRAY[
ROW(4, ‘显示器’, 2, 1599.00)::fgedu_order_item,
ROW(5, ‘HDMI线’, 2, 29.00)::fgedu_order_item
], 3256.00);
— 查询订单
SELECT order_id, customer_name, order_date, total_amount FROM fgedu_orders;
执行结果：
———-+—————+—————————-+————–
1 | 风哥1号 | 2026-04-04 14:30:25.123 | 6596.00
2 | 风哥2号 | 2026-04-04 14:30:25.123 | 3256.00
(2 rows)
查询复合类型数组元素：
SELECT
o.order_id,
o.customer_name,
(item).product_id,
(item).product_name,
(item).quantity,
(item).unit_price,
(item).quantity * (item).unit_price AS subtotal
FROM fgedu_orders o
CROSS JOIN LATERAL unnest(o.items) AS item
ORDER BY o.order_id, (item).product_id;
执行结果：
———-+—————+————+————–+———-+————+———-
1 | 风哥1号 | 1 | 笔记本电脑 | 1 | 5999.00 | 5999.00
1 | 风哥1号 | 2 | 无线鼠标 | 2 | 99.00 | 198.00
1 | 风哥1号 | 3 | 机械键盘 | 1 | 399.00 | 399.00
2 | 风哥2号 | 4 | 显示器 | 2 | 1599.00 | 3198.00
2 | 风哥2号 | 5 | HDMI线 | 2 | 29.00 | 58.00
(5 rows)
8. 实战案例：订单系统
创建完整的订单管理系统：
学习交流加群风哥微信: itpux-com
CREATE TABLE fgedu_products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50),
price NUMERIC(10, 2) NOT NULL,
stock INTEGER DEFAULT 0,
tags TEXT[]
);
— 创建订单表
CREATE TABLE fgedu_order_master (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER,
customer_name VARCHAR(100),
shipping_address fgedu_address,
order_status VARCHAR(20) DEFAULT ‘pending’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
— 创建订单明细表
CREATE TABLE fgedu_order_details (
detail_id SERIAL PRIMARY KEY,
order_id INTEGER,
product_id INTEGER,
product_name VARCHAR(100),
quantity INTEGER,
unit_price NUMERIC(10, 2),
subtotal NUMERIC(12, 2)
);
— 插入产品数据
INSERT INTO fgedu_products (product_name, category, price, stock, tags) VALUES
(‘笔记本电脑’, ‘电子产品’, 5999.00, 50, ARRAY[‘电脑’, ‘办公’, ‘游戏’]),
(‘无线鼠标’, ‘电子产品’, 99.00, 200, ARRAY[‘外设’, ‘办公’]),
(‘机械键盘’, ‘电子产品’, 399.00, 100, ARRAY[‘外设’, ‘游戏’]),
(‘显示器’, ‘电子产品’, 1599.00, 80, ARRAY[‘显示’, ‘办公’]),
(‘HDMI线’, ‘配件’, 29.00, 500, ARRAY[‘线缆’, ‘连接’]);
执行结果：
创建订单：
INSERT INTO fgedu_order_master (customer_id, customer_name, shipping_address) VALUES
(1, ‘风哥1号’, ROW(‘北京市’, ‘北京市’, ‘海淀区’, ‘中关村大街1号’, ‘100080’));
— 添加订单明细
INSERT INTO fgedu_order_details (order_id, product_id, product_name, quantity, unit_price, subtotal)
SELECT
1,
product_id,
product_name,
1,
price,
price
FROM fgedu_products
WHERE product_name = ‘笔记本电脑’;
INSERT INTO fgedu_order_details (order_id, product_id, product_name, quantity, unit_price, subtotal)
SELECT
1,
product_id,
product_name,
2,
price,
price * 2
FROM fgedu_products
WHERE product_name = ‘无线鼠标’;
— 查询订单汇总
SELECT
om.order_id,
om.customer_name,
(om.shipping_address).city AS city,
(om.shipping_address).street AS street,
SUM(od.subtotal) AS total_amount,
om.order_status
FROM fgedu_order_master om
JOIN fgedu_order_details od ON om.order_id = od.order_id
GROUP BY om.order_id, om.customer_name, (om.shipping_address).city, (om.shipping_address).street,
om.order_status;
执行结果：
———-+—————+———-+——————-+————–+————–
1 | 风哥1号 | 北京市 | 中关村大街1号 | 6197.00 | pending
(1 row)
产品标签搜索：
SELECT
product_id,
product_name,
category,
price,
tags,
‘办公’ = ANY(tags) AS has_office_tag,
tags @> ARRAY[‘办公’, ‘电脑’] AS has_both_tags
FROM fgedu_products;
执行结果：
————+————–+————+——–+———————–+—————-+—————
1 | 笔记本电脑 | 电子产品 | 5999.00 | {电脑,办公,游戏} | t | t
2 | 无线鼠标 | 电子产品 | 99.00 | {外设,办公} | t | f
3 | 机械键盘 | 电子产品 | 399.00 | {外设,游戏} | f | f
4 | 显示器 | 电子产品 | 1599.00 | {显示,办公} | t | f
5 | HDMI线 | 配件 | 29.00 | {线缆,连接} | f | f
(5 rows)
9. 清理环境
清理测试表和类型：
更多视频教程www.fgedu.net.cn
DROP TABLE IF EXISTS fgedu_customers;
DROP TABLE IF EXISTS fgedu_array_test;
DROP TABLE IF EXISTS fgedu_orders;
DROP TABLE IF EXISTS fgedu_products;
DROP TABLE IF EXISTS fgedu_order_master;
DROP TABLE IF EXISTS fgedu_order_details;
— 删除复合类型
DROP TYPE IF EXISTS fgedu_order_item;
DROP TYPE IF EXISTS fgedu_address;
DROP TYPE IF EXISTS fgedu_coordinate;
DROP TYPE IF EXISTS fgedu_contact;
执行结果：
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TYPE
DROP TYPE
DROP TYPE
DROP TYPE
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
