PostgreSQL运算符全解析：从算术运算到JSON与全文检索的完整知识体系

🎬 Clf丶忆笙：个人主页

🔥 个人专栏：《PostgreSQL专栏 》

⛺️ 努力不一定成功，但不努力一定不成功！

文章目录

• • 一、PostgreSQL运算符概述
  • • 1.1 什么是运算符
    • 1.2 运算符的分类
    • 1.3 运算符的优先级
  • 二、算术运算符详解
  • • 2.1 基础算术运算符
    • • 2.1.1 加法运算符 (+)
      • 2.1.2 减法运算符 (-)
      • 2.1.3 乘法运算符 (*)
      • 2.1.4 除法运算符 (/)
    • 2.2 高级算术运算符
    • • 2.2.1 取模运算符 (%)
      • 2.2.2 幂运算符 (^)
    • 2.3 算术运算符的实际应用案例
    • • 2.3.1 电商订单金额计算
      • 2.3.2 库存周转率计算
  • 三、比较运算符深度解析
  • • 3.1 基础比较运算符
    • • 3.1.1 等于运算符 (=)
      • 3.1.2 不等于运算符 (<> 或 !=)
      • 3.1.3 大于运算符 (>) 和大于等于运算符 (>=)
      • 3.1.4 小于运算符 (<) 和小于等于运算符 (<=)
    • 3.2 高级比较运算符
    • • 3.2.1 BETWEEN运算符
      • 3.2.2 IN运算符
      • 3.2.3 LIKE运算符
      • 3.2.4 ILIKE运算符
    • 3.3 比较运算符的高级应用
    • • 3.3.1 多条件组合查询
      • 3.3.2 子查询中的比较运算符
  • 四、逻辑运算符完全指南
  • • 4.1 AND运算符
    • 4.2 OR运算符
    • 4.3 NOT运算符
    • 4.4 逻辑运算符的组合使用
    • • 4.4.1 复杂逻辑表达式
      • 4.4.2 逻辑运算符与CASE表达式
    • 4.5 逻辑运算符的性能优化
    • • 4.5.1 条件顺序优化
      • 4.5.2 使用索引友好的条件
  • 五、字符串运算符与函数
  • • 5.1 字符串连接运算符 (||)
    • 5.2 字符串比较运算符
    • 5.3 字符串函数与运算符结合
    • 5.4 字符串运算符的实际应用
    • • 5.4.1 生成格式化输出
      • 5.4.2 动态SQL构建
  • 六、模式匹配运算符
  • • 6.1 LIKE运算符深入
    • 6.2 ILIKE运算符
    • 6.3 SIMILAR TO运算符
    • 6.4 正则表达式运算符 (~)
    • 6.5 模式匹配性能考虑
  • 七、数组运算符
  • • 7.1 数组创建与访问
    • 7.2 数组包含运算符 (@> 和 <@)
    • 7.3 数组修改运算符
    • 7.4 数组函数与运算符结合
  • 八、范围运算符
  • • 8.1 范围类型基础
    • 8.2 范围包含运算符 (@> 和 <@)
    • 8.3 范围重叠运算符 (&&)
    • 8.4 范围运算符的实际应用
    • • 8.4.1 价格区间查询
      • 8.4.2 会议室预订系统
  • 九、JSON运算符
  • • 9.1 JSON数据类型基础
    • 9.2 JSON访问运算符 (-> 和 ->>)
    • 9.3 JSON包含运算符 (@> 和 <@)
    • 9.4 JSON修改运算符
    • 9.5 JSON运算符的高级应用
    • • 9.5.1 复杂JSON查询
      • 9.5.2 JSON与数组结合
  • 十、全文检索运算符
  • • 10.1 全文检索基础
    • 10.2 全文检索运算符 (@@)
    • 10.3 全文检索高级功能
    • 10.4 全文检索实际应用
    • • 10.4.1 产品搜索系统
  • 十一、几何运算符
  • • 11.1 几何类型基础
    • 11.2 几何距离运算符
    • 11.3 几何包含运算符
    • 11.4 几何运算符应用示例
    • • 11.4.1 地理位置查询
      • 11.4.2 区域分析
  • 十二、运算符综合应用案例
  • • 12.1 电商数据分析系统
    • 12.2 复杂查询示例
    • • 12.2.1 多维度产品分析
      • 12.2.2 客户行为分析
    • 12.3 性能优化技巧
    • • 12.3.1 运算符与索引优化
      • 12.3.2 查询重写技巧
  • 十三、运算符最佳实践
  • • 13.1 选择正确的运算符
    • 13.2 处理NULL值
    • 13.3 类型转换注意事项
    • 13.4 运算符与数据一致性
  • 十四、总结
  • • 14.1 关键要点回顾
    • 14.2 实践建议

一、PostgreSQL运算符概述

1.1 什么是运算符

在PostgreSQL中，运算符就像是数据库世界里的"动词"，它们告诉数据库如何处理和比较数据。想象一下，你有一个装满数据的工具箱，运算符就是那些让你能够组合、比较和转换这些数据的工具。无论是简单的数学计算，还是复杂的数据筛选，都离不开这些小小的"魔法符号"。

PostgreSQL提供了丰富的运算符集合，涵盖了算术运算、比较运算、逻辑运算、字符串操作、模式匹配、数组处理等多个领域。这些运算符不仅功能强大，而且遵循SQL标准，同时又有PostgreSQL特有的扩展。

1.2 运算符的分类

PostgreSQL的运算符可以按照功能分为几大类：

1.3 运算符的优先级

就像数学运算有"先乘除后加减"的规则一样，PostgreSQL中的运算符也有明确的优先级顺序。理解这个优先级对于编写正确的查询至关重要。

— 运算符优先级示例
SELECT 5 + 3 * 2; — 结果是11，不是16，因为*优先级高于+
SELECT (5 + 3) * 2; — 结果是16，使用括号改变优先级

以下是PostgreSQL运算符的优先级从高到低排列：

二、算术运算符详解

2.1 基础算术运算符

PostgreSQL支持所有标准的基础算术运算，这些运算符与我们在数学课上学到的完全一致。

2.1.1 加法运算符 (+)

加法运算符用于将两个数值相加，是最基本的数学运算之一。

— 基本加法运算
SELECT 10 + 5; — 结果: 15

— 在表查询中使用加法
SELECT
product_name,
unit_price,
quantity,
unit_price + quantity AS price_plus_quantity
FROM products;

— 处理NULL值
SELECT 10 + NULL; — 结果: NULL
SELECT 10 + COALESCE(NULL, 0); — 结果: 10

应用场景：

• 计算订单总金额：商品单价 × 数量 + 运费
• 统计数据累加：月度销售额 = 1月销售额 + 2月销售额 + …
• 时间计算：当前时间 + 间隔天数

2.1.2 减法运算符 (-)

减法运算符用于计算两个数值的差值。

— 基本减法运算
SELECT 10 – 5; — 结果: 5

— 计算库存变化
SELECT
product_name,
initial_stock,
current_stock,
initial_stock – current_stock AS stock_change
FROM inventory;

— 日期减法
SELECT CURRENT_DATE – '2023-01-01'::date AS days_since_new_year;

应用场景：

• 库存管理：期初库存 – 期末库存 = 消耗量
• 财务计算：收入 – 支出 = 利润
• 时间差计算：结束时间 – 开始时间 = 持续时间

2.1.3 乘法运算符 (*)

乘法运算符用于计算两个数值的乘积。

— 基本乘法运算
SELECT 10 * 5; — 结果: 50

— 计算订单总额
SELECT
order_id,
unit_price,
quantity,
unit_price * quantity AS total_price
FROM order_items;

— 百分比计算
SELECT salary * 0.1 AS bonus_amount
FROM employees;

应用场景：

• 订单金额计算：单价 × 数量
• 税费计算：金额 × 税率
• 面积计算：长 × 宽

2.1.4 除法运算符 (/)

除法运算符用于计算两个数值的商，需要注意除数为零的情况。

— 基本除法运算
SELECT 10 / 5; — 结果: 2
SELECT 10 / 3; — 结果: 3 (整数除法)
SELECT 10.0 / 3; — 结果: 3.333… (浮点除法)

— 计算平均值
SELECT
department,
SUM(salary) / COUNT(*) AS avg_salary
FROM employees
GROUP BY department;

— 避免除零错误
SELECT
total_sales,
num_orders,
CASE
WHEN num_orders = 0 THEN 0
ELSE total_sales / num_orders
END AS avg_order_value
FROM sales_summary;

应用场景：

• 平均值计算：总和 / 数量
• 单价计算：总金额 / 数量
• 比率计算：部分 / 整体

2.2 高级算术运算符

2.2.1 取模运算符 (%)

取模运算符返回除法的余数，在周期性计算中非常有用。

— 基本取模运算
SELECT 10 % 3; — 结果: 1
SELECT 10 % 5; — 结果: 0

— 判断奇偶数
SELECT
number,
CASE
WHEN number % 2 = 0 THEN '偶数'
ELSE '奇数'
END AS parity
FROM numbers;

— 周期性任务调度
SELECT
task_id,
execution_day,
execution_day % 7 AS day_of_week
FROM task_schedule;

应用场景：

• 奇偶数判断
• 周期性数据分析
• 数据分片处理

2.2.2 幂运算符 (^)

幂运算符用于计算一个数的幂次方。

— 基本幂运算
SELECT 2 ^ 3; — 结果: 8
SELECT 5 ^ 2; — 结果: 25

— 复利计算
SELECT
principal,
interest_rate,
years,
principal * (1 + interest_rate) ^ years AS final_amount
FROM investments;

— 平方和立方计算
SELECT
x,
x ^ 2 AS square,
x ^ 3 AS cube
FROM numbers;

应用场景：

• 复利计算
• 面积和体积计算
• 指数增长模型

2.3 算术运算符的实际应用案例

2.3.1 电商订单金额计算

— 创建订单表
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER,
order_date DATE,
subtotal NUMERIC(10,2),
tax_rate NUMERIC(5,4),
shipping_fee NUMERIC(10,2),
discount_amount NUMERIC(10,2)
);

— 插入示例数据
INSERT INTO orders (customer_id, order_date, subtotal, tax_rate, shipping_fee, discount_amount)
VALUES
(1, '2023-01-15', 100.00, 0.08, 5.00, 10.00),
(2, '2023-01-16', 200.50, 0.08, 7.50, 0.00),
(3, '2023-01-17', 75.25, 0.07, 5.00, 5.00);

— 计算订单总金额
SELECT
order_id,
customer_id,
subtotal,
subtotal * tax_rate AS tax_amount,
shipping_fee,
discount_amount,
(subtotal + (subtotal * tax_rate) + shipping_fee – discount_amount) AS total_amount
FROM orders;

2.3.2 库存周转率计算

— 创建库存表
CREATE TABLE inventory (
product_id INTEGER PRIMARY KEY,
product_name VARCHAR(100),
beginning_stock INTEGER,
ending_stock INTEGER,
cost_of_goods_sold NUMERIC(12,2)
);

— 插入示例数据
INSERT INTO inventory VALUES
(101, '笔记本电脑', 50, 30, 150000.00),
(102, '无线鼠标', 200, 150, 8000.00),
(103, 'USB键盘', 100, 80, 12000.00);

— 计算库存周转率
SELECT
product_name,
beginning_stock,
ending_stock,
cost_of_goods_sold,
(beginning_stock + ending_stock) / 2.0 AS avg_inventory,
cost_of_goods_sold / ((beginning_stock + ending_stock) / 2.0) AS turnover_ratio
FROM inventory;

三、比较运算符深度解析

3.1 基础比较运算符

比较运算符是SQL查询中最常用的工具，它们帮助我们筛选出符合特定条件的数据。

3.1.1 等于运算符 (=)

等于运算符用于检查两个值是否相等，是最基本的比较操作。

— 基本等于比较
SELECT * FROM employees WHERE salary = 5000;

— 多条件等于查询
SELECT * FROM products
WHERE category = 'Electronics' AND status = 'Active';

— 处理NULL值
SELECT * FROM customers
WHERE email IS NULL; — 正确的NULL检查方式
— 注意：WHERE email = NULL 不会返回任何结果

注意事项：

• NULL值不能使用=进行比较，必须使用IS NULL
• 字符串比较区分大小写（除非使用ILIKE）
• 日期时间比较需要确保格式一致

3.1.2 不等于运算符 (<> 或 !=)

不等于运算符有两种形式：SQL标准的<>和更常见的!=，在PostgreSQL中两者完全等价。

— 使用<>运算符
SELECT * FROM products WHERE price <> 0;

— 使用!=运算符
SELECT * FROM employees WHERE department != 'IT';

— 组合使用
SELECT * FROM orders
WHERE status != 'Cancelled' AND status != 'Returned';

应用场景：

• 排除特定值
• 查找异常数据
• 数据清洗

3.1.3 大于运算符 (>) 和大于等于运算符 (>=)

这些运算符用于数值或日期的范围比较。

— 数值比较
SELECT * FROM products WHERE price > 100;

— 日期比较
SELECT * FROM orders WHERE order_date >= '2023-01-01';

— 组合条件
SELECT * FROM employees
WHERE salary >= 5000 AND years_of_service > 2;

3.1.4 小于运算符 (<) 和小于等于运算符 (<=)

与大于运算符对应，用于查找小于特定值的记录。

— 查找低价商品
SELECT * FROM products WHERE price < 50;

— 查找早期订单
SELECT * FROM orders WHERE order_date <= '2022-12-31';

— 范围查询
SELECT * FROM students
WHERE age >= 18 AND age <= 25;

3.2 高级比较运算符

3.2.1 BETWEEN运算符

BETWEEN运算符提供了一种更优雅的范围查询方式，包含边界值。

— 基本BETWEEN用法
SELECT * FROM products
WHERE price BETWEEN 100 AND 500;

— 等价于
SELECT * FROM products
WHERE price >= 100 AND price <= 500;

— 日期范围查询
SELECT * FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

— NOT BETWEEN用法
SELECT * FROM employees
WHERE salary NOT BETWEEN 3000 AND 7000;

优势：

• 语法更简洁
• 可读性更好
• 性能通常优于AND组合

3.2.2 IN运算符

IN运算符用于匹配列表中的任意值，是多个OR条件的简洁替代。

— 基本IN用法
SELECT * FROM products
WHERE category IN ('Electronics', 'Books', 'Clothing');

— 等价于
SELECT * FROM products
WHERE category = 'Electronics'
OR category = 'Books'
OR category = 'Clothing';

— 子查询中的IN
SELECT * FROM employees
WHERE department_id IN (
SELECT department_id FROM departments
WHERE location = 'New York'
);

— NOT IN用法
SELECT * FROM customers
WHERE country NOT IN ('USA', 'Canada', 'Mexico');

注意事项：

• IN列表中的值类型必须一致
• 包含NULL的IN列表可能产生意外结果
• 大量值时考虑使用JOIN替代

3.2.3 LIKE运算符

LIKE运算符用于模式匹配，支持通配符%和_。

— 基本LIKE用法
SELECT * FROM products
WHERE product_name LIKE 'Laptop%'; — 以Laptop开头

SELECT * FROM customers
WHERE email LIKE '%@gmail.com'; — 以@gmail.com结尾

SELECT * FROM employees
WHERE last_name LIKE 'Sm_th'; — Sm+任意字符+th

— 转义特殊字符
SELECT * FROM products
WHERE description LIKE '%50\\% off%' ESCAPE '\\';

通配符说明：

• %：匹配任意长度字符（包括零长度）
• _：匹配单个字符
• ESCAPE：定义转义字符

3.2.4 ILIKE运算符

ILIKE是PostgreSQL特有的不区分大小写的LIKE运算符。

— ILIKE不区分大小写
SELECT * FROM products
WHERE product_name ILIKE 'laptop%'; — 匹配Laptop、laptop、LAPTOP等

— 组合使用
SELECT * FROM customers
WHERE first_name ILIKE 'john%'
AND email ILIKE '%@gmail.com';

3.3 比较运算符的高级应用

3.3.1 多条件组合查询

— 复杂的多条件查询
SELECT
product_name,
category,
price,
stock_quantity
FROM products
WHERE
(category = 'Electronics' AND price > 500) OR
(category = 'Books' AND price BETWEEN 10 AND 50) OR
(stock_quantity < 10 AND price < 100)
ORDER BY
category,
price DESC;

3.3.2 子查询中的比较运算符

— 使用比较运算符的子查询
SELECT
employee_name,
salary,
department
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department = e.department
);

— 使用ALL和ANY
SELECT * FROM products
WHERE price > ALL (
SELECT price FROM products
WHERE category = 'Books'
);

SELECT * FROM employees
WHERE salary = ANY (
SELECT MAX(salary)
FROM employees
GROUP BY department
);

四、逻辑运算符完全指南

4.1 AND运算符

AND运算符用于组合多个条件，所有条件都必须为真时结果才为真。

— 基本AND用法
SELECT * FROM employees
WHERE department = 'IT' AND salary > 5000;

— 多个AND条件
SELECT * FROM orders
WHERE order_date >= '2023-01-01'
AND order_date <= '2023-01-31'
AND total_amount > 100
AND status = 'Completed';

— AND与括号结合
SELECT * FROM products
WHERE (category = 'Electronics' AND price > 500)
OR (category = 'Books' AND price < 50);

短路求值： PostgreSQL在处理AND运算时采用短路求值，如果第一个条件为假，就不会检查后续条件。

4.2 OR运算符

OR运算符用于组合多个条件，只要有一个条件为真结果就为真。

— 基本OR用法
SELECT * FROM products
WHERE category = 'Electronics' OR category = 'Books';

— 多个OR条件
SELECT * FROM customers
WHERE country = 'USA'
OR country = 'Canada'
OR country = 'Mexico';

— OR与AND结合
SELECT * FROM employees
WHERE (department = 'IT' AND salary > 6000)
OR (department = 'Sales' AND years_of_service > 3);

性能提示： 将最可能为真的条件放在OR表达式的最前面，可以提高查询性能。

4.3 NOT运算符

NOT运算符用于反转条件的真假值。

— 基本NOT用法
SELECT * FROM products
WHERE NOT (price > 100); — 等价于 price <= 100

— NOT与BETWEEN结合
SELECT * FROM orders
WHERE order_date NOT BETWEEN '2023-01-01' AND '2023-01-31';

— NOT与IN结合
SELECT * FROM employees
WHERE department NOT IN ('IT', 'HR', 'Finance');

— NOT与LIKE结合
SELECT * FROM customers
WHERE email NOT LIKE '%@gmail.com';

— NOT与NULL
SELECT * FROM products
WHERE description IS NOT NULL;

4.4 逻辑运算符的组合使用

4.4.1 复杂逻辑表达式

— 复杂的AND、OR、NOT组合
SELECT
order_id,
customer_id,
order_date,
total_amount,
status
FROM orders
WHERE
(order_date >= '2023-01-01' AND order_date <= '2023-03-31')
AND (
(total_amount > 1000 AND status = 'Completed')
OR (total_amount <= 1000 AND status IN ('Processing', 'Shipped'))
)
AND NOT (customer_id IN (1, 2, 3))
ORDER BY
order_date DESC,
total_amount DESC;

4.4.2 逻辑运算符与CASE表达式

— 在CASE表达式中使用逻辑运算符
SELECT
product_name,
price,
stock_quantity,
CASE
WHEN price > 500 AND stock_quantity > 0 THEN '高价有货'
WHEN price > 500 AND stock_quantity = 0 THEN '高价缺货'
WHEN price <= 500 AND stock_quantity > 10 THEN '平价充足'
WHEN price <= 500 AND stock_quantity > 0 AND stock_quantity <= 10 THEN '平价紧张'
ELSE '缺货'
END AS inventory_status
FROM products;

4.5 逻辑运算符的性能优化

4.5.1 条件顺序优化

— 优化前：可能检查大量记录
SELECT * FROM large_table
WHERE expensive_function(column1) = 'value'
AND column2 = 'simple_value';

— 优化后：先过滤简单条件
SELECT * FROM large_table
WHERE column2 = 'simple_value'
AND expensive_function(column1) = 'value';

4.5.2 使用索引友好的条件

— 索引友好的条件放在前面
SELECT * FROM orders
WHERE customer_id = 123 — 假设有索引
AND total_amount > 1000; — 计算条件

— 避免在索引列上使用函数
— 不推荐
SELECT * FROM orders
WHERE EXTRACT(MONTH FROM order_date) = 1;

— 推荐
SELECT * FROM orders
WHERE order_date >= '2023-01-01'
AND order_date < '2023-02-01';

五、字符串运算符与函数

5.1 字符串连接运算符 (||)

PostgreSQL使用||运算符来连接字符串，这是SQL标准的方式。

— 基本字符串连接
SELECT 'Hello' || ' ' || 'World' AS greeting; — 结果: Hello World

— 连接表中的列
SELECT
first_name || ' ' || last_name AS full_name
FROM employees;

— 处理NULL值
SELECT
first_name || ' ' || COALESCE(middle_name, '') || ' ' || last_name AS full_name
FROM employees;

— 连接不同类型的数据
SELECT
'Product: ' || product_name || ', Price: $' || price AS product_info
FROM products;

5.2 字符串比较运算符

字符串比较遵循字典序规则，区分大小写。

— 字符串等于比较
SELECT * FROM customers
WHERE city = 'New York';

— 字符串大小比较
SELECT * FROM products
WHERE product_name > 'Laptop';

— 不区分大小写比较
SELECT * FROM customers
WHERE LOWER(city) = 'new york';

5.3 字符串函数与运算符结合

— 字符串函数与运算符结合使用
SELECT
product_name,
UPPER(LEFT(product_name, 3)) || '…' AS short_code,
LENGTH(product_name) AS name_length,
POSITION('Pro' IN product_name) AS pro_position
FROM products
WHERE product_name LIKE '%Pro%';

5.4 字符串运算符的实际应用

5.4.1 生成格式化输出

— 生成格式化的地址信息
SELECT
first_name || ' ' || last_name AS name,
address || ', ' || city || ', ' || state || ' ' || zip_code AS full_address,
'Phone: ' || phone_number || ', Email: ' || email AS contact_info
FROM customers
WHERE country = 'USA';

5.4.2 动态SQL构建

— 构建动态查询（示例）
SELECT
'SELECT * FROM ' || table_name ||
' WHERE created_at >= ''' ||
TO_CHAR(CURRENT_DATE – INTERVAL '30 days', 'YYYY-MM-DD') || '''' AS query_text
FROM information_schema.tables
WHERE table_schema = 'public';

六、模式匹配运算符

6.1 LIKE运算符深入

LIKE运算符是SQL标准的模式匹配工具，使用%和_作为通配符。

— 基本模式匹配
SELECT * FROM products
WHERE product_name LIKE 'Laptop%'; — 以Laptop开头

SELECT * FROM customers
WHERE email LIKE '%@company.com'; — 以@company.com结尾

SELECT * FROM employees
WHERE last_name LIKE '_mith'; — 任意单个字符 + mith

— 复杂模式
SELECT * FROM products
WHERE description LIKE '%high quality% durable%';

— 转义特殊字符
SELECT * FROM products
WHERE model LIKE 'Model\\#123%' ESCAPE '\\';

6.2 ILIKE运算符

ILIKE提供不区分大小写的模式匹配。

— 不区分大小写匹配
SELECT * FROM products
WHERE product_name ILIKE 'laptop%'; — 匹配Laptop、laptop、LAPTOP等

— 组合使用
SELECT * FROM customers
WHERE first_name ILIKE 'john%'
AND email ILIKE '%@gmail.com';

6.3 SIMILAR TO运算符

SIMILAR TO提供更强大的正则表达式功能，介于LIKE和完整正则之间。

— 基本SIMILAR TO用法
SELECT * FROM products
WHERE product_code SIMILAR TO 'LPT-[0-9]{3}';

— 复杂模式
SELECT * FROM customers
WHERE phone_number SIMILAR TO '[0-9]{3}-[0-9]{3}-[0-9]{4}';

— 使用分组
SELECT * FROM employees
WHERE email SIMILAR TO '%@(gmail|yahoo|hotmail)\\.com';

6.4 正则表达式运算符 (~)

PostgreSQL提供完整的正则表达式支持，使用~系列运算符。

— 基本正则匹配
SELECT * FROM customers
WHERE email ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$';

— 不区分大小写
SELECT * FROM products
WHERE product_name ~* 'laptop';

— 不匹配
SELECT * FROM employees
WHERE phone_number !~ '^\\d{3}-\\d{3}-\\d{4}$';

— 复杂正则示例
SELECT * FROM orders
WHERE order_id ~ '^ORD-[0-9]{8}-[A-Z]{2}$';

6.5 模式匹配性能考虑

— 使用索引友好的模式
— 好的做法：前缀匹配
SELECT * FROM products
WHERE product_name LIKE 'Laptop%';

— 避免：前导通配符
— 不推荐（无法使用索引）
SELECT * FROM products
WHERE product_name LIKE '%Laptop';

— 使用函数索引解决前导通配符问题
CREATE INDEX idx_products_name_reverse ON products (REVERSE(product_name));

SELECT * FROM products
WHERE REVERSE(product_name) LIKE REVERSE('%Laptop');

七、数组运算符

7.1 数组创建与访问

PostgreSQL支持原生数组类型，提供专门的运算符。

— 创建数组
SELECT ARRAY[1, 2, 3, 4, 5] AS numbers;

— 从表创建数组
SELECT ARRAY_AGG(salary) AS salary_array
FROM employees
WHERE department = 'IT';

— 访问数组元素（从1开始）
SELECT
skills,
skills[1] AS primary_skill,
skills[2] AS secondary_skill
FROM employees
WHERE skills IS NOT NULL;

7.2 数组包含运算符 (@> 和 <@)

— 数组包含关系
SELECT * FROM products
WHERE tags @> ARRAY['electronics', 'new']; — 包含所有指定元素

SELECT * FROM products
WHERE tags <@ ARRAY['electronics', 'books', 'clothing']; — 被指定数组包含

— 数组重叠
SELECT * FROM products
WHERE tags && ARRAY['sale', 'discount', 'offer']; — 有共同元素

7.3 数组修改运算符

— 数组连接
SELECT ARRAY[1, 2] || ARRAY[3, 4]; — 结果: {1,2,3,4}
SELECT ARRAY[1, 2] || 3; — 结果: {1,2,3}

— 添加元素
SELECT array_append(ARRAY[1, 2], 3); — 结果: {1,2,3}
SELECT array_prepend(0, ARRAY[1, 2]); — 结果: {0,1,2}

— 删除元素
SELECT array_remove(ARRAY[1, 2, 2, 3], 2); — 结果: {1,3}

— 替换元素
SELECT array_replace(ARRAY[1, 2, 3], 2, 20); — 结果: {1,20,3}

7.4 数组函数与运算符结合

— 复杂数组操作
SELECT
product_id,
tags,
array_length(tags, 1) AS tag_count,
unnest(tags) AS individual_tag
FROM products
WHERE cardinality(tags) > 2;

— 数组聚合
SELECT
category,
array_agg(DISTINCT product_name) AS products_in_category
FROM products
GROUP BY category
HAVING array_length(array_agg(DISTINCT product_name), 1) > 5;

八、范围运算符

8.1 范围类型基础

PostgreSQL支持多种范围类型，用于表示连续的值区间。

— 创建范围类型
SELECT '[1, 10]'::int4range AS number_range;
SELECT '[2023-01-01, 2023-12-31]'::daterange AS date_range;

— 包含与排除边界
SELECT '[1, 10]'::int4range; — 包含1和10
SELECT '(1, 10)'::int4range; — 不包含1和10
SELECT '[1, 10)'::int4range; — 包含1，不包含10

8.2 范围包含运算符 (@> 和 <@)

— 范围包含检查
SELECT '[1, 10]'::int4range @> 5; — true，5在范围内
SELECT '[1, 10]'::int4range @> 11; — false，11不在范围内

— 范围被包含
SELECT 5 <@ '[1, 10]'::int4range; — true
SELECT 11 <@ '[1, 10]'::int4range; — false

— 范围包含范围
SELECT '[1, 20]'::int4range @> '[5, 15]'::int4range; — true
SELECT '[1, 10]'::int4range @> '[5, 15]'::int4range; — false

8.3 范围重叠运算符 (&&)

— 检查范围是否重叠
SELECT '[1, 10]'::int4range && '[5, 15]'::int4range; — true
SELECT '[1, 5]'::int4range && '[6, 10]'::int4range; — false

— 实际应用：检查时间重叠
SELECT * FROM bookings
WHERE booking_range && '[2023-06-01, 2023-06-07]'::daterange;

8.4 范围运算符的实际应用

8.4.1 价格区间查询

— 创建产品价格范围表
CREATE TABLE price_ranges (
range_name VARCHAR(50),
price_range numrange
);

INSERT INTO price_ranges VALUES
('Budget', '[0, 50)'),
('Mid-range', '[50, 200)'),
('Premium', '[200, 500)'),
('Luxury', '[500, 999999]');

— 查询产品所属价格区间
SELECT
p.product_name,
p.price,
r.range_name
FROM products p
JOIN price_ranges r ON p.price <@ r.price_range;

8.4.2 会议室预订系统

— 创建预订表
CREATE TABLE room_bookings (
booking_id SERIAL PRIMARY KEY,
room_id INTEGER,
booked_by VARCHAR(100),
booking_time tsrange,
EXCLUDE USING gist (room_id WITH =, booking_time WITH &&)
);

— 插入预订
INSERT INTO room_bookings (room_id, booked_by, booking_time)
VALUES
(1, 'Alice', '[2023-06-01 09:00, 2023-06-01 11:00)'),
(2, 'Bob', '[2023-06-01 14:00, 2023-06-01 16:00)');

— 检查时间冲突
SELECT * FROM room_bookings
WHERE room_id = 1
AND booking_time && '[2023-06-01 10:00, 2023-06-01 12:00)'::tsrange;

九、JSON运算符

9.1 JSON数据类型基础

PostgreSQL提供两种JSON数据类型：JSON和JSONB，后者是二进制格式，更高效。

— 创建JSON数据
SELECT '{"name": "John", "age": 30}'::json;

— 创建JSONB数据
SELECT '{"name": "John", "age": 30}'::jsonb;

— 表中的JSON列
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
attributes jsonb
);

INSERT INTO products (name, attributes)
VALUES
('Laptop', '{"color": "silver", "weight": 1.5, "specs": {"cpu": "i7", "ram": "16GB"}}'),
('Mouse', '{"color": "black", "wireless": true, "battery": "AA"}');

9.2 JSON访问运算符 (-> 和 ->>)

— -> 返回JSON对象或数组
SELECT attributes–>'color' AS color_json
FROM products
WHERE name = 'Laptop';

— ->> 返回文本
SELECT attributes–>>'color' AS color_text
FROM products
WHERE name = 'Laptop';

— 嵌套访问
SELECT attributes–>'specs'–>>'cpu' AS cpu_type
FROM products
WHERE name = 'Laptop';

— 数组访问
SELECT '[1, 2, 3]'::json–>>2 AS third_element; — 结果: "3"

9.3 JSON包含运算符 (@> 和 <@)

— JSON包含检查
SELECT * FROM products
WHERE attributes @> '{"color": "silver"}';

— 被包含检查
SELECT * FROM products
WHERE attributes <@ '{"color": "silver", "weight": 1.5, "specs": {"cpu": "i7"}}';

— 检查键存在
SELECT * FROM products
WHERE attributes ? 'wireless';

— 检查任意键存在
SELECT * FROM products
WHERE attributes ?| array['color', 'weight'];

— 检查所有键存在
SELECT * FROM products
WHERE attributes ?& array['color', 'specs'];

9.4 JSON修改运算符

— 更新JSON字段
UPDATE products
SET attributes = jsonb_set(attributes, '{color}', '"gold"')
WHERE name = 'Laptop';

— 添加新字段
UPDATE products
SET attributes = attributes || '{"warranty": "2 years"}'
WHERE name = 'Mouse';

— 删除字段
UPDATE products
SET attributes = attributes – 'weight'
WHERE name = 'Laptop';

— 批量更新
UPDATE products
SET attributes = jsonb_set(
attributes,
'{last_updated}',
to_jsonb(now())
);

9.5 JSON运算符的高级应用

9.5.1 复杂JSON查询

— 查询嵌套JSON数据
SELECT
name,
attributes–>'specs'–>>'cpu' AS cpu,
(attributes–>'specs'–>>'ram')::integer AS ram_gb
FROM products
WHERE attributes–>>'color' = 'silver'
AND (attributes–>'specs'–>>'ram')::integer >= 8;

— JSON聚合
SELECT
attributes–>>'color' AS color,
COUNT(*) AS product_count,
jsonb_agg(name) AS products
FROM products
GROUP BY attributes–>>'color';

9.5.2 JSON与数组结合

— JSON数组操作
SELECT
name,
jsonb_array_length(attributes–>'tags') AS tag_count,
jsonb_array_elements_text(attributes–>'tags') AS individual_tag
FROM products
WHERE jsonb_typeof(attributes–>'tags') = 'array';

— 展开JSON数组到行
SELECT
p.name,
tag.value AS tag
FROM products p,
jsonb_array_elements_text(p.attributes–>'tags') AS tag
WHERE p.name = 'Laptop';

十、全文检索运算符

10.1 全文检索基础

PostgreSQL提供强大的全文检索功能，支持文本搜索和排名。

— 创建全文检索文档
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');

— 创建查询
SELECT to_tsquery('english', 'quick & fox');

— 基本匹配
SELECT to_tsvector('english', 'The quick brown fox') @@
to_tsquery('english', 'quick & fox');

10.2 全文检索运算符 (@@)

@@运算符用于执行全文检索匹配。

— 基本全文检索
SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || content) @@
to_tsquery('english', 'database & performance');

— 使用索引的全文检索
CREATE INDEX idx_articles_search ON articles
USING gin(to_tsvector('english', title || ' ' || content));

SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & optimization');

10.3 全文检索高级功能

— 前缀搜索
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'comput:*');

— 短语搜索
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'high performance');

— 排名结果
SELECT
title,
ts_rank(search_vector, to_tsquery('english', 'database & performance')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'database & performance')
ORDER BY rank DESC;

— 高亮显示
SELECT
title,
ts_headline('english', content, to_tsquery('english', 'database')) AS highlighted
FROM articles
WHERE search_vector @@ to_tsquery('english', 'database');

10.4 全文检索实际应用

10.4.1 产品搜索系统

— 创建产品搜索表
CREATE TABLE product_search (
id SERIAL PRIMARY KEY,
product_name VARCHAR(200),
description TEXT,
search_vector tsvector
);

— 创建触发器自动更新搜索向量
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS trigger AS $$
BEGIN
NEW.search_vector :=
setweight(to_tsvector('english', COALESCE(NEW.product_name, '')), 'A') ||
setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'B');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_search_vector
BEFORE INSERT OR UPDATE ON product_search
FOR EACH ROW EXECUTE FUNCTION update_search_vector();

— 执行搜索
SELECT
product_name,
ts_rank(search_vector, query) AS relevance,
ts_headline('english', description, query) AS highlighted_desc
FROM product_search,
to_tsquery('english', 'laptop & (gaming | professional)') query
WHERE search_vector @@ query
ORDER BY relevance DESC;

十一、几何运算符

11.1 几何类型基础

PostgreSQL支持多种几何数据类型，包括点、线、多边形等。

— 点类型
SELECT point '(1, 2)' AS p;

— 线段类型
SELECT line ' {0, 1, 1}' AS l; — ax + by + c = 0

— 路径类型
SELECT path '((1,1), (2,2), (3,1))' AS p;

— 多边形类型
SELECT polygon '((0,0), (1,0), (1,1), (0,1))' AS poly;

— 圆类型
SELECT circle '((0,0), 5)' AS c; — 中心(0,0)，半径5

11.2 几何距离运算符

— 点之间的距离
SELECT point '(1,1)' <–> point '(4,5)' AS distance; — 结果: 5

— 点到线的距离
SELECT point '(1,1)' <–> line '{0,1,0}' AS distance; — 到直线y=0的距离

— 点到矩形的距离
SELECT point '(5,5)' <–> box '((0,0),(2,2))' AS distance;

11.3 几何包含运算符

— 点在多边形内
SELECT point '(1,1)' <@ polygon '((0,0),(2,0),(2,2),(0,2))'; — true

— 多边形包含点
SELECT polygon '((0,0),(2,0),(2,2),(0,2))' @> point '(1,1)'; — true

— 重叠检查
SELECT box '((0,0),(2,2))' && box '((1,1),(3,3))'; — true

11.4 几何运算符应用示例

11.4.1 地理位置查询

— 创建位置表
CREATE TABLE locations (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
coordinates point
);

INSERT INTO locations (name, coordinates) VALUES
('Store A', point '(10, 20)'),
('Store B', point '(15, 25)'),
('Store C', point '(30, 40)');

— 查找指定范围内的位置
SELECT
name,
coordinates,
coordinates <–> point '(12, 22)' AS distance
FROM locations
WHERE coordinates <–> point '(12, 22)' < 10
ORDER BY distance;

11.4.2 区域分析

— 创建区域表
CREATE TABLE service_areas (
area_name VARCHAR(100),
boundary polygon
);

INSERT INTO service_areas VALUES
('Downtown', polygon '((0,0),(10,0),(10,10),(0,10))'),
('Suburbs', polygon '((10,10),(20,10),(20,20),(10,20))');

— 查找点所在区域
SELECT
l.name AS location_name,
a.area_name
FROM locations l
LEFT JOIN service_areas a ON l.coordinates <@ a.boundary;

十二、运算符综合应用案例

12.1 电商数据分析系统

— 创建综合示例表结构
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(200) NOT NULL,
category VARCHAR(50),
price NUMERIC(10,2),
cost NUMERIC(10,2),
stock_quantity INTEGER,
tags TEXT[],
attributes JSONB,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER,
order_date DATE,
ship_date DATE,
status VARCHAR(20),
total_amount NUMERIC(12,2),
items JSONB, — 存储订单项的JSON数组
shipping_address JSONB
);

CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20),
address JSONB,
registration_date DATE,
last_login TIMESTAMP,
preferences JSONB
);

— 插入示例数据
INSERT INTO products (product_name, category, price, cost, stock_quantity, tags, attributes)
VALUES
('Gaming Laptop Pro', 'Electronics', 1299.99, 900.00, 25,
ARRAY['gaming', 'laptop', 'high-performance'],
'{"brand": "TechCorp", "model": "X1", "specs": {"cpu": "i7", "ram": "16GB", "gpu": "RTX 3070"}, "warranty": "2 years"}'),
('Wireless Mouse', 'Electronics', 29.99, 15.00, 150,
ARRAY['wireless', 'mouse', 'ergonomic'],
'{"brand": "MouseInc", "battery_type": "AA", "dpi": 1600, "color": "black"}'),
('Programming Guide', 'Books', 45.00, 20.00, 100,
ARRAY['programming', 'education', 'bestseller'],
'{"author": "John Doe", "pages": 450, "language": "English", "isbn": "123-4567890123"}');

INSERT INTO customers (first_name, last_name, email, phone, address, registration_date, preferences)
VALUES
('Alice', 'Smith', 'alice@example.com', '555-0101',
'{"street": "123 Main St", "city": "New York", "state": "NY", "zip": "10001"}',
'2022-01-15',
'{"newsletter": true, "preferred_categories": ["Electronics", "Books"], "price_alert": true}'),
('Bob', 'Johnson', 'bob@example.com', '555-0102',
'{"street": "456 Oak Ave", "city": "Los Angeles", "state": "CA", "zip": "90001"}',
'2022-03-20',
'{"newsletter": false, "preferred_categories": ["Books"], "price_alert": false}');

INSERT INTO orders (customer_id, order_date, ship_date, status, total_amount, items, shipping_address)
VALUES
(1, '2023-01-10', '2023-01-12', 'Completed', 1329.98,
'[{"product_id": 1, "quantity": 1, "price": 1299.99}, {"product_id": 2, "quantity": 1, "price": 29.99}]',
'{"street": "123 Main St", "city": "New York", "state": "NY", "zip": "10001"}'),
(2, '2023-01-15', '2023-01-17', 'Shipped', 45.00,
'[{"product_id": 3, "quantity": 1, "price": 45.00}]',
'{"street": "456 Oak Ave", "city": "Los Angeles", "state": "CA", "zip": "90001"}');

12.2 复杂查询示例

12.2.1 多维度产品分析

— 综合产品分析查询
WITH product_metrics AS (
SELECT
p.product_id,
p.product_name,
p.category,
p.price,
p.cost,
p.stock_quantity,
p.price – p.cost AS profit_margin,
p.price * p.stock_quantity AS inventory_value,
array_length(p.tags, 1) AS tag_count,
(p.attributes–>>'brand') AS brand,
CASE
WHEN p.stock_quantity > 100 THEN 'High'
WHEN p.stock_quantity > 20 THEN 'Medium'
ELSE 'Low'
END AS stock_level,
CASE
WHEN p.price – p.cost > p.cost * 0.5 THEN 'High'
WHEN p.price – p.cost > p.cost * 0.2 THEN 'Medium'
ELSE 'Low'
END AS profitability
FROM products p
),
sales_summary AS (
SELECT
(item–>>'product_id')::integer AS product_id,
SUM((item–>>'quantity')::integer * (item–>>'price')::numeric) AS total_sales,
SUM((item–>>'quantity')::integer) AS total_quantity_sold
FROM orders o,
jsonb_array_elements(o.items) AS item
WHERE o.status = 'Completed'
GROUP BY (item–>>'product_id')::integer
)
SELECT
pm.product_name,
pm.category,
pm.price,
pm.profit_margin,
pm.inventory_value,
pm.stock_level,
pm.profitability,
pm.brand,
COALESCE(ss.total_sales, 0) AS total_sales,
COALESCE(ss.total_quantity_sold, 0) AS total_quantity_sold,
CASE
WHEN ss.total_sales IS NULL THEN 'No Sales'
WHEN ss.total_sales > 1000 THEN 'High Sales'
WHEN ss.total_sales > 100 THEN 'Medium Sales'
ELSE 'Low Sales'
END AS sales_performance,
pm.tags
FROM product_metrics pm
LEFT JOIN sales_summary ss ON pm.product_id = ss.product_id
WHERE pm.price > 50
AND (pm.stock_quantity > 20 OR ss.total_sales > 0)
ORDER BY
CASE pm.category
WHEN 'Electronics' THEN 1
WHEN 'Books' THEN 2
ELSE 3
END,
pm.price DESC;

12.2.2 客户行为分析

— 客户购买行为分析
SELECT
c.customer_id,
c.first_name || ' ' || c.last_name AS customer_name,
c.email,
c.registration_date,
COUNT(o.order_id) AS total_orders,
COALESCE(SUM(o.total_amount), 0) AS total_spent,
COALESCE(AVG(o.total_amount), 0) AS avg_order_value,
MAX(o.order_date) AS last_order_date,
CURRENT_DATE – MAX(o.order_date) AS days_since_last_order,
c.preferences–>>'newsletter' AS subscribed_newsletter,
c.preferences–>'preferred_categories' AS preferred_categories,
CASE
WHEN COUNT(o.order_id) = 0 THEN 'New'
WHEN COUNT(o.order_id) < 5 THEN 'Occasional'
WHEN COUNT(o.order_id) < 20 THEN 'Regular'
ELSE 'VIP'
END AS customer_tier,
CASE
WHEN CURRENT_DATE – MAX(o.order_date) > 180 THEN 'Inactive'
WHEN CURRENT_DATE – MAX(o.order_date) > 90 THEN 'At Risk'
ELSE 'Active'
END AS activity_status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY
c.customer_id,
c.first_name,
c.last_name,
c.email,
c.registration_date,
c.preferences
HAVING COUNT(o.order_id) > 0 OR c.registration_date > CURRENT_DATE – INTERVAL '30 days'
ORDER BY
total_spent DESC,
last_order_date DESC;

12.3 性能优化技巧

12.3.1 运算符与索引优化

— 创建适当的索引
CREATE INDEX idx_products_category_price ON products(category, price);
CREATE INDEX idx_products_tags ON products USING gin(tags);
CREATE INDEX idx_products_attributes ON products USING gin(attributes);
CREATE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
CREATE INDEX idx_orders_status ON orders(status);

— 使用索引友好的查询
— 好的做法
SELECT * FROM products
WHERE category = 'Electronics' AND price > 500;

— 避免：在索引列上使用函数
— 不推荐
SELECT * FROM products
WHERE LOWER(category) = 'electronics';

— 推荐：使用函数索引或存储规范化数据
CREATE INDEX idx_products_category_lower ON products(LOWER(category));

12.3.2 查询重写技巧

— 原始查询
SELECT * FROM products
WHERE price > 100 OR stock_quantity < 10;

— 优化后：使用UNION ALL
SELECT * FROM products WHERE price > 100
UNION ALL
SELECT * FROM products WHERE stock_quantity < 10 AND price <= 100;

— 原始查询
SELECT * FROM orders
WHERE EXTRACT(MONTH FROM order_date) = 1;

— 优化后：范围查询
SELECT * FROM orders
WHERE order_date >= '2023-01-01' AND order_date < '2023-02-01';

十三、运算符最佳实践

13.1 选择正确的运算符

— 使用BETWEEN代替AND组合
— 好
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

— 不好
SELECT * FROM products WHERE price >= 100 AND price <= 500;

— 使用IN代替多个OR
— 好
SELECT * FROM products WHERE category IN ('Electronics', 'Books', 'Clothing');

— 不好
SELECT * FROM products
WHERE category = 'Electronics'
OR category = 'Books'
OR category = 'Clothing';

13.2 处理NULL值

— 正确处理NULL值
SELECT
product_name,
COALESCE(price, 0) AS price,
COALESCE(stock_quantity, 0) AS stock
FROM products;

— 使用NULL安全运算符
SELECT * FROM products
WHERE price IS NOT NULL AND stock_quantity IS NOT NULL;

— 使用NULLIF避免除零
SELECT
product_name,
total_sales / NULLIF(num_orders, 0) AS avg_order_value
FROM sales_summary;

13.3 类型转换注意事项

— 显式类型转换
SELECT * FROM orders
WHERE order_date = '2023-01-01'::date;

— 避免隐式转换
— 不好：可能导致索引失效
SELECT * FROM orders
WHERE order_date = '2023-01-01';

— 好：明确类型
SELECT * FROM orders
WHERE order_date = DATE '2023-01-01';

13.4 运算符与数据一致性

— 使用CHECK约束确保数据一致性
ALTER TABLE products
ADD CONSTRAINT chk_price_positive CHECK (price > 0);

ALTER TABLE products
ADD CONSTRAINT chk_stock_non_negative CHECK (stock_quantity >= 0);

— 使用域类型增强类型安全
CREATE DOMAIN positive_numeric AS NUMERIC
CHECK (VALUE > 0);

CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
price positive_numeric,
cost positive_numeric
);

十四、总结

通过这篇全面的PostgreSQL运算符指南，我们深入探讨了从基础的算术运算到高级的JSON操作、从简单的比较到复杂的模式匹配等各种运算符的使用方法和最佳实践。

14.1 关键要点回顾

14.2 实践建议

PostgreSQL的运算符系统是其强大功能的重要组成部分，掌握这些运算符将帮助你更高效地处理数据，编写更优雅、更高效的SQL查询。希望这篇指南能够成为你PostgreSQL学习之路上的有力助手。