CREATE TABLE demo (
id SERIAL PRIMARY KEY,
name TEXT,
age INT,
salary NUMERIC(10,2),
tags TEXT[],
config JSONB,
score INT,
active BOOLEAN,
ip INET,
period TSRANGE,
bits INT
);
INSERT INTO demo (name, age, salary, tags, config, score, active, ip, period, bits)
VALUES
('Alice', 25, 70000.00, '{"dev","lead"}', '{"level": 5, "theme": "dark"}', 95, true, '192.168.1.10', '[2025-01-01, 2025-12-31)', 5),
('Bob', 30, 50000.50, '{"hr"}', '{"level": 3}', 80, true, '192.168.1.20', '[2025-06-01, 2025-12-31)', 3),
('Carol', NULL, 80000.00, '{"dev","ai"}', '{"level": 6}', NULL, false, NULL, NULL, 7);
三、算术运算符
运算符
说明
示例
结果
+
加
5 + 3
8
-
减
10 - 4
6
*
乘
6 * 7
42
/
除（整数 → 整数）
10 / 3
3
/
除（浮点）
10.0 / 3
3.333...
%
取模
10 % 3
1
^
幂
2 ^ 3
8
SELECT salary, salary * 1.1 AS bonus_10 FROM demo;
四、比较运算符
运算符
说明
示例
=
等于
age = 25
<> / !=
不等于
name <> 'Alice'
> / <
大于 / 小于
salary > 60000
>= / <=
≥ / ≤
score >= 90
注意：NULL 比较返回 UNKNOWN，不会是 TRUE
-- 错误：不会匹配 NULL
SELECT * FROM demo WHERE score != 90;
-- 正确：用 IS NULL
SELECT * FROM demo WHERE score IS NULL;
五、逻辑运算符
运算符
说明
示例
AND
且
active AND age > 25
OR
或
age < 30 OR salary > 70000
NOT
非
NOT active
SELECT * FROM demo
WHERE (age > 25 OR salary > 70000) AND active;
六、字符串运算符
运算符
说明
示例
结果
||
拼接
'Hello' || ' ' || 'World'
'Hello World'
LIKE
模式匹配（大小写敏感）
name LIKE 'A%'
Alice
ILIKE
忽略大小写
name ILIKE 'a%'
Alice
~
正则匹配（大小写敏感）
name ~ '^A'
Alice
~*
正则忽略大小写
name ~* '^a'
Alice
!~ / !~*
正则不匹配
name !~ 'bob'
true
SELECT name || ' earns $' || salary AS info FROM demo;
SELECT * FROM demo WHERE name ILIKE '%a%';
七、数组运算符（Array）
运算符
说明
示例
结果
@>
包含
tags @> '{"dev"}'
Alice, Carol
<@
被包含
'{"dev"}' <@ tags
Alice, Carol
=
相等
tags = '{"dev","lead"}'
Alice
&&
重叠（有交集）
tags && '{"ai"}'
Carol
||
拼接数组
tags || '{"new"}'
{"dev","lead","new"}
-- 包含 dev 和 lead
SELECT * FROM demo WHERE tags @> '{"dev","lead"}';
-- 添加标签
UPDATE demo SET tags = tags || '{"beta"}' WHERE name = 'Alice';
性能：数组列建议建 GIN 索引
CREATE INDEX idx_demo_tags ON demo USING GIN (tags);
八、JSON / JSONB 运算符（PostgreSQL 杀手级）
运算符
说明
示例
结果
->
按键取对象
config -> 'level'
{"level": 5}
->>
按键取文本
config ->> 'level'
'5'
#>
按路径取对象
config #> '{settings,theme}'
"dark"
#>>
按路径取文本
config #>> '{settings,theme}'
'dark'
@>
包含
config @> '{"level": 5}'
Alice
<@
被包含
'{"level": 5}' <@ config
Alice
?
键存在
config ? 'level'
true
?|, ?&
任一/所有键存在
config ?| '{level,theme}'
true
-- 查询 level >= 5
SELECT name, (config ->> 'level')::INT AS level
FROM demo
WHERE (config ->> 'level')::INT >= 5;
-- 更新 JSON 字段
UPDATE demo SET config = jsonb_set(config, '{theme}', '"light"') WHERE name = 'Alice';
性能：JSONB 列建 GIN 索引
CREATE INDEX idx_demo_config ON demo USING GIN (config);
九、范围类型运算符（Range）
-- 查询包含 2025-06-01 的记录
SELECT * FROM demo WHERE period @> '2025-06-01'::date;
-- 两个时间段有交集
SELECT * FROM demo WHERE period && '[2025-07-01, 2025-08-01)'::tsrange;
十、位运算符（Bitwise）
运算符
说明
示例
结果
&
按位与
5 & 3
1 (101 & 011 = 001)
|
按位或
5 | 3
7 (101
#
按位异或
5 # 3
6 (101 # 011 = 110)
~
按位取反
~5
-6
<<
左移
1 << 2
4
>>
右移
8 >> 1
4
-- 权限系统：bit 1=读, 2=写, 4=删
SELECT * FROM demo WHERE (bits & 1) = 1; -- 有读权限
十一、NULL 安全运算符
运算符
说明
示例
结果
IS NULL
是 NULL
email IS NULL
Carol
IS NOT NULL
非 NULL
ip IS NOT NULL
Alice, Bob
IS DISTINCT FROM
NULL 安全 ≠
score IS DISTINCT FROM 90
所有行
IS NOT DISTINCT FROM
NULL 安全 =
score IS NOT DISTINCT FROM NULL
Carol
-- 传统写法（错误）
SELECT * FROM demo WHERE score != 90; -- 跳过 NULL
-- 正确写法
SELECT * FROM demo WHERE score IS DISTINCT FROM 90;
十二、IN / NOT IN / ANY / ALL
-- IN
SELECT * FROM demo WHERE name IN ('Alice', 'Bob');
-- ANY (数组)
SELECT * FROM demo WHERE 'dev' = ANY(tags);
-- ALL (数组)
SELECT * FROM demo WHERE 'dev' = ALL(tags); -- 所有元素都是 dev
-- 子查询
SELECT * FROM demo WHERE age > ANY (SELECT age FROM demo WHERE active);
十三、CASE 条件表达式（逻辑运算符）
SELECT name,
CASE
WHEN salary > 70000 THEN 'High'
WHEN salary > 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_level
FROM demo;
十四、运算符优先级（从高到低）
1. :: [] . -> #>
2. - (负号) ^
3. * / %
4. + -
5. IS, ISNULL, NOTNULL
6. (其他比较) BETWEEN IN LIKE ILIKE
7. NOT
8. AND
9. OR
用 () 明确优先级！
十五、自定义运算符（高级）
-- 创建加法运算符 !!
CREATE OR REPLACE FUNCTION add_int(a int, b int) RETURNS int AS $$
BEGIN RETURN a + b; END;
$$ LANGUAGE plpgsql;
CREATE OPERATOR !! (
LEFTARG = int, RIGHTARG = int,
PROCEDURE = add_int
);
-- 使用
SELECT 5 !! 3; -- 结果 8
十六、最佳实践 Checklist
项目
建议
NULL 安全
用 IS NULL / IS DISTINCT FROM
数组/JSON
用 @> + GIN 索引
字符串
用 ILIKE 忽略大小写
性能
避免 LIKE '%abc'（无法用索引）
可读性
复杂条件用 ()
类型转换
显式 ::int 避免隐式错误
十七、快速实战：5 分钟掌握核心运算符
-- 1. 字符串拼接 + 算术
SELECT name || ' earns $' || (salary * 1.1) AS bonus FROM demo;
-- 2. 数组包含 + JSON 提取
SELECT name, tags, config ->> 'level' AS level
FROM demo
WHERE tags @> '{"dev"}' AND (config ->> 'level')::INT > 4;
-- 3. NULL 安全 + 位运算
SELECT * FROM demo
WHERE score IS DISTINCT FROM 90
AND (bits & 2) = 2; -- 有写权限
-- 4. 范围包含
SELECT * FROM demo WHERE period @> CURRENT_DATE;