10 表达式

表达式就是 PostgreSQL 能够求值为单个值的任何内容：字面量、列引用、算术运算、函数调用、CASE，甚至另一个返回单个值的 SELECT。表达式几乎无处不在：选择列表、WHERE、ORDER BY、GROUP BY 和索引定义中都能使用。掌握表达式，也就掌握了 SQL。

本章要点

• 算术、字符串和布尔表达式
• 在表达式中调用内置函数
• 在 SELECT、WHERE 和 ORDER BY 中使用表达式
• 用 :: 和 CAST(... AS ...) 做类型转换
• 用 AS 为表达式结果命名

示例

-- Arithmetic and concatenation
SELECT 10 + 2 AS sum, 10 * 2 AS product, 10 / 4 AS int_div, 10 / 4.0 AS real_div;
SELECT 'Hello ' || 'world' AS greeting;
SELECT 'fruit_' || 1 AS labeled;  -- numbers are coerced to text for ||

-- Function calls in expressions
SELECT upper(name), length(name), name || ' (#' || id || ')' AS label
FROM fruits ORDER BY id LIMIT 3;

-- Expression in WHERE
SELECT * FROM fruits WHERE id * 2 > 4 ORDER BY id LIMIT 3;

-- Expression in ORDER BY
SELECT id, name FROM fruits ORDER BY length(name) DESC LIMIT 3;

-- Casting
SELECT '42'::integer AS to_int, 42::text AS to_text, '2024-01-15'::date AS to_date;
SELECT CAST(3.7 AS integer) AS rounded_value;  -- SQL-standard cast syntax

|| 操作符用于拼接字符串。在 PostgreSQL 中，如果任一操作数不是 text 类型，就会将其转换为 text（所以 'id_' || 5 可以运行）。+、-、*、/、% 是常见的算术操作符；记住，整数除法会向零截断。

PostgreSQL 的类型转换有两种写法。value::type 是 PostgreSQL 特有的简写，也是常用写法。CAST(value AS type) 属于 SQL 标准，表达式复杂时读起来更清楚。两种写法最终表达的是同一种类型转换。

WHERE 和 ORDER BY 中的表达式会对每个候选行求值，因此在这里调用函数可能会妨碍使用索引。WHERE lower(email) = 'a@b.com' 无法使用 email 上的普通索引，但如果定义了 CREATE INDEX ON users (lower(email)) 这样的表达式索引，就可以使用它。

运行示例

运行方式：

$ psql -f source/expressions.sql postgres
 sum | product | int_div | real_div
-----+---------+---------+----------
  12 |      20 |       2 |      2.5
(1 row)

   greeting
-------------
 Hello world
(1 row)
 ...

常见陷阱

• 整数除法返回 0：1 / 2 的结果是 0。可以改用非整数类型：1.0 / 2、1::numeric / 2，或使用 numeric 类型的列。
• 对无法解析的字符串做类型转换，例如 'abc'::integer，会报错。与 MySQL 相比，PostgreSQL 对类型的要求更严格。
• 如果表达式在 CASE 的不同分支中返回不同类型，就会引发类型不匹配错误；应转换为共同类型。（译注：不同类型并不一定报错；若能按类型解析规则隐式转换为共同类型，表达式仍可成立。）
• 操作符具有优先级：1 + 2 * 3 的结果是 7。拿不准时就加括号。

下载

SQL 文件： expressions.sql · 全部示例文件 · 准备工作