— 创建地址复合类型
CREATE TYPE fgedu_address AS (
street VARCHAR(100),
city VARCHAR(50),
state VARCHAR(50),
zip_code VARCHAR(20),
country VARCHAR(50)
);

— 创建客户表
CREATE TABLE fgedu_customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
billing_address fgedu_address,
shipping_address fgedu_address
);

— 插入数据
INSERT INTO fgedu_customers (name, email, billing_address, shipping_address) VALUES (
‘John Doe’,
‘john.doe@fgedu.net.cn’,
ROW(‘123 Main St’, ‘New York’, ‘NY’, ‘10001’, ‘USA’),
ROW(‘456 Oak Ave’, ‘Boston’, ‘MA’, ‘02101’, ‘USA’)
);

— 查询数据
SELECT
id,
name,
email,
(billing_address).street AS billing_street,
(billing_address).city AS billing_city,
(shipping_address).street AS shipping_street,
(shipping_address).city AS shipping_city
FROM fgedu_customers;

— 更新地址
UPDATE fgedu_customers SET
shipping_address = ROW(‘789 Pine Rd’, ‘Chicago’, ‘IL’, ‘60601’, ‘USA’)
WHERE id = 1;

— 创建订单状态枚举类型
CREATE TYPE fgedu_order_status AS ENUM (
‘pending’,
‘processing’,
‘shipped’,
‘delivered’,
‘cancelled’
);

— 创建订单表
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
order_id VARCHAR(20) UNIQUE NOT NULL,
customer_id INTEGER NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
status fgedu_order_status DEFAULT ‘pending’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 插入数据
INSERT INTO fgedu_orders (order_id, customer_id, total_amount, status) VALUES
(‘ORD-20260407-001’, 1, 100.00, ‘pending’),
(‘ORD-20260407-002’, 2, 200.00, ‘processing’),
(‘ORD-20260407-003’, 3, 300.00, ‘shipped’);

— 查询不同状态的订单
SELECT status, COUNT(*) AS order_count FROM fgedu_orders GROUP BY status;

— 更新订单状态
UPDATE fgedu_orders SET status = ‘delivered’, updated_at = CURRENT_TIMESTAMP WHERE id = 3;

— 查询已完成的订单
SELECT * FROM fgedu_orders WHERE status IN (‘delivered’, ‘cancelled’);

— 创建域类型
CREATE DOMAIN fgedu_email AS VARCHAR(100) CHECK (
VALUE ~ ‘^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$’
);

CREATE DOMAIN fgedu_phone_number AS VARCHAR(20) CHECK (
VALUE ~ ‘^[0-9]{10,15}$’
);

CREATE DOMAIN fgedu_positive_decimal AS DECIMAL(10,2) CHECK (
VALUE > 0
);

— 创建产品表
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
price fgedu_positive_decimal NOT NULL,
stock INTEGER CHECK (stock >= 0)
);

— 创建供应商表
CREATE TABLE fgedu_suppliers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email fgedu_email UNIQUE NOT NULL,
phone fgedu_phone_number
);

— 插入有效数据
INSERT INTO fgedu_products (name, description, price, stock) VALUES
(‘iPhone 15’, ‘Apple iPhone 15’, 5999.99, 100),
(‘MacBook Pro’, ‘Apple MacBook Pro’, 12999.99, 50);

INSERT INTO fgedu_suppliers (name, email, phone) VALUES
(‘Apple Inc.’, ‘supplier@apple.com’, ‘1234567890’),
(‘Microsoft’, ‘supplier@microsoft.com’, ‘0987654321’);

— 尝试插入无效数据（会失败）
— INSERT INTO fgedu_products (name, description, price, stock) VALUES
— (‘Invalid Product’, ‘Invalid’, -100.00, -5);

— INSERT INTO fgedu_suppliers (name, email, phone) VALUES
— (‘Invalid Supplier’, ‘invalid-email’, ‘123’);

— 创建房间表
CREATE TABLE fgedu_rooms (
id SERIAL PRIMARY KEY,
room_number VARCHAR(10) UNIQUE NOT NULL,
capacity INTEGER NOT NULL,
room_type VARCHAR(50) NOT NULL
);

— 创建预订表
CREATE TABLE fgedu_reservations (
id SERIAL PRIMARY KEY,
room_id INTEGER REFERENCES fgedu_rooms(id),
guest_name VARCHAR(100) NOT NULL,
reservation_period DATERANGE NOT NULL,
CHECK (lower(reservation_period) < upper(reservation_period)) ); -- 插入房间数据 INSERT INTO fgedu_rooms (room_number, capacity, room_type) VALUES ('101', 2, 'Standard'), ('102', 4, 'Deluxe'), ('103', 1, 'Single'); -- 插入预订数据 INSERT INTO fgedu_reservations (room_id, guest_name, reservation_period) VALUES (1, 'John Doe', DATERANGE('2026-04-07', '2026-04-10')), (2, 'Jane Smith', DATERANGE('2026-04-08', '2026-04-12')), (3, 'Bob Johnson', DATERANGE('2026-04-09', '2026-04-11')); -- 查询特定日期的预订 SELECT r.*, rm.room_number, rm.room_type FROM fgedu_reservations r JOIN fgedu_rooms rm ON r.room_id = rm.id WHERE reservation_period && DATERANGE('2026-04-09', '2026-04-09'); -- 查询可用房间（在特定日期范围内） SELECT * FROM fgedu_rooms rm WHERE NOT EXISTS ( SELECT 1 FROM fgedu_reservations r WHERE r.room_id = rm.id AND r.reservation_period && DATERANGE('2026-04-13', '2026-04-15') ); -- 检查预订冲突 SELECT * FROM fgedu_reservations r1 WHERE EXISTS ( SELECT 1 FROM fgedu_reservations r2 WHERE r1.room_id = r2.room_id AND r1.id != r2.id AND r1.reservation_period && r2.reservation_period );

— 1. 复合类型问题
— 问题：无法直接更新复合类型的单个字段
— 解决：
— – 读取整个复合类型，修改后再写回
— – 使用PL/pgSQL函数更新复合类型

— 2. 枚举类型问题
— 问题：无法轻松添加或删除枚举值
— 解决：
— – 使用ALTER TYPE语句修改枚举类型
— – 考虑使用查找表作为替代方案

— 3. 域类型问题
— 问题：域类型约束与表级约束冲突
— 解决：
— – 确保约束的一致性
— – 避免重复定义约束

— 4. 范围类型问题
— 问题：范围查询性能差
— 解决：
— – 为范围类型创建索引
— – 使用合适的范围操作符
— – 避免过度使用复杂的范围表达式

— 1. 复合类型优化
— – 避免在频繁查询的列中使用复合类型
— – 考虑将复合类型拆分为单独的列，提高查询性能
— – 为复合类型的字段创建索引

— 2. 枚举类型优化
— – 枚举类型存储效率高，不需要特殊优化
— – 为枚举类型列创建索引，提高查询性能

— 3. 域类型优化
— – 域类型几乎没有性能开销，不需要特殊优化
— – 确保约束的有效性，避免过度复杂的约束

— 4. 范围类型优化
— – 为范围类型创建GiST索引，提高范围查询性能
— – 合理使用范围操作符，避免复杂的范围表达式
— – 考虑使用范围类型的边界条件，提高查询效率

— 5. 通用优化
— – 只在必要时创建自定义类型
— – 评估自定义类型的性能影响
— – 定期分析表，更新统计信息
— – 考虑使用生成列优化复合类型的查询