PostgreSQL - 运算符

PostgreSQL - 现代运算符指南

PostgreSQL 中的运算符是什么？

运算符是一个特殊的字符或关键字，用于对一个或多个值（操作数）执行操作。运算符是 SQL 的基础，广泛用于 SELECT 列表中计算新值，并在 WHERE 子句中根据特定条件过滤数据。

PostgreSQL 提供了一组丰富的运算符，可按以下类别划分：

• 算术运算符
• 比较运算符
• 逻辑运算符
• 位运算符与位字符串运算符
• 模式匹配运算符
• 用于 JSON 和数组的现代运算符

PostgreSQL 算术运算符

算术运算符执行数学计算。以下所有示例都可以在 psql 或 DBeaver 等 SQL 客户端中直接运行。

运算符	描述	示例
+	加法	SELECT 10 + 5; — 返回 15
-	减法	SELECT 10 - 5; — 返回 5
*	乘法	SELECT 10 * 5; — 返回 50
/	除法。注意：整数除法会截断结果。	SELECT 10 / 4; — 返回 2
::numeric	要获得精确的除法结果，请将其中一个操作数转换为数值类型。	SELECT 10::numeric / 4; — 返回 2.50
%	取模（余数）	SELECT 10 % 4; — 返回 2
^	幂运算	SELECT 2 ^ 3; — 返回 8
|/	平方根	SELECT |/ 25.0; — 返回 5
||/	立方根	SELECT ||/ 27.0; — 返回 3
!	阶乘（后缀）	SELECT 5!; — 返回 120

PostgreSQL 比较运算符

比较运算符用于 WHERE 子句中过滤行。我们来使用一个示例 employees 表：

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary INT
);

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Engineering', 80000),
('Bob', 'Engineering', 95000),
('Charlie', 'HR', 60000);

运算符	描述	示例
=	等于	SELECT name FROM employees WHERE department = ‘HR’; — 返回 Charlie
<> or !=	不等于。<> 运算符是标准 SQL。	SELECT name FROM employees WHERE department <> ‘HR’; — 返回 Alice, Bob
>	大于	SELECT name FROM employees WHERE salary > 80000; — 返回 Bob
<	小于	SELECT name FROM employees WHERE salary < 80000; — 返回 Charlie
>=	大于或等于	SELECT name FROM employees WHERE salary >= 80000; — 返回 Alice, Bob
<=	小于或等于	SELECT name FROM employees WHERE salary <= 80000; — 返回 Alice, Charlie
IS NULL	检查值是否为 NULL。	SELECT name FROM employees WHERE salary IS NULL;
IS NOT NULL	检查值是否不为 NULL。	SELECT name FROM employees WHERE salary IS NOT NULL;

PostgreSQL 逻辑运算符

逻辑运算符用于组合多个布尔条件。运算符优先级为 NOT，然后是 AND，最后是 OR。使用括号 () 来控制计算顺序。

运算符	描述	示例（使用 employees 表）
AND	如果两个条件都为 true，则返回 true。	SELECT name FROM employees WHERE department = ‘Engineering’ AND salary > 90000; — 返回 Bob
OR	如果任一条件为 true，则返回 true。	SELECT name FROM employees WHERE department = ‘HR’ OR salary > 90000; — 返回 Bob, Charlie
NOT	反转逻辑表达式的结果。	SELECT name FROM employees WHERE NOT department = ‘Engineering’; — 返回 Charlie

位运算符和位字符串运算符

这些运算符对整数类型或 bit / bit varying 字符串类型执行位级操作。

整数上的位运算符

Let A = 60 (binary 00111100) and B = 13 (binary 00001101)

运算符	描述	示例
&	按位与	SELECT 60 & 13; — 返回 12 (二进制 00001100)
|	按位或	SELECT 60 | 13; — 返回 61 (二进制 00111101)
#	按位异或	SELECT 60 # 13; — 返回 49 (二进制 00110001)
~	按位非（反转位）	SELECT ~60; — 返回 -61
<<	左移	SELECT 60 << 2; — 返回 240 (二进制 11110000)
>>	右移	SELECT 60 >> 2; — 返回 15 (二进制 00001111)

位字符串运算符

SELECT B'10101' | B'01101'; -- 返回 11101
SELECT B'10101' & B'01101'; -- 返回 00101
SELECT 'hello' || ' world'; -- 字符串拼接，不是按位或

现代与特殊用途运算符

PostgreSQL 的功能通过对 JSONB 和数组等现代数据类型的运算符得到了扩展。

• 模式匹配：LIKE（区分大小写）、ILIKE（不区分大小写）用于简单的通配符匹配。~ 用于 POSIX 正则表达式匹配。
• 数组运算符：@>（包含）、<@（被包含）、&&（重叠）。示例：SELECT '{1, 4, 3}'::int[] @> '{3, 1}'::int[]; -- 返回 true
• JSONB 运算符：->（获取对象字段）、->>（以文本形式获取对象字段）、?（键是否存在）、@>（包含）。这些对于处理无模式数据至关重要。

常见错误与最佳实践

• 整数除法：请记住 5 / 2 的结果是 2。使用 5::numeric / 2 这样的类型转换来获得精确结果。
• NULL 传播：与 NULL 的比较（例如，col = NULL）结果是 NULL，而不是 true 或 false。请始终使用 IS NULL 或 IS NOT NULL。
• 运算符优先级：当混合使用 AND 和 OR 时，请务必使用括号 () 来确保执行您预期的逻辑。示例：WHERE (department = 'HR' OR department = 'Sales') AND status = 'active';