PostgreSQL / 系统课时
使用常用标量函数
使用常用标量函数
本课解决查询时需要清洗、计算和格式化单个字段值的问题。你将使用字符串、数值、日期和类型转换函数,在不修改原始数据的情况下生成新的查询结果。
学习目标
- 使用常用字符串和数值函数处理字段
- 使用日期函数提取或格式化时间
- 使用
CAST和 PostgreSQL 简写语法转换类型
标量函数的作用
标量函数对每一行输入分别计算,并为每一行返回一个结果。它与聚合函数不同:UPPER(name) 会处理每条记录,而 COUNT(*) 会把多条记录汇总成一个统计值。
下面通过固定值演示常用函数,示例可直接在 PostgreSQL 中运行:
SELECT
UPPER(TRIM(' postgresql ')) AS normalized_text,
LENGTH('PostgreSQL') AS text_length,
ROUND(123.456::numeric, 2) AS rounded_value,
ABS(-18) AS absolute_value,
EXTRACT(YEAR FROM DATE '2026-07-29') AS year_value,
TO_CHAR(DATE '2026-07-29', 'YYYY-MM-DD') AS formatted_date,
CAST('42' AS integer) + 8 AS converted_sum;预期结果
normalized_text | text_length | rounded_value | absolute_value | year_value | formatted_date | converted_sum
-----------------+-------------+---------------+----------------+------------+----------------+--------------
POSTGRESQL | 10 | 123.46 | 18 | 2026 | 2026-07-29 | 50字符串函数
TRIM 去除首尾空白,UPPER 和 LOWER 转换大小写,LENGTH 返回字符数,CONCAT 或 || 用于拼接字符串。
处理用户输入时,常先用 TRIM 清理空格,再进行比较。需要注意,函数作用在索引列上可能让普通索引无法直接用于查找,例如 WHERE LOWER(email) = 'a@example.com'。这种高频查询可以建立表达式索引。
以下语句是片段示例,依赖已存在且包含 email 字段的 users 表:
CREATE INDEX IF NOT EXISTS idx_users_lower_email
ON users (LOWER(email));预期结果
CREATE INDEX索引创建后,可通过 EXPLAIN 检查相关查询是否使用该表达式索引。
数值函数
ROUND 用于四舍五入,ABS 返回绝对值,CEIL 向上取整,FLOOR 向下取整。金额计算建议使用 numeric,不要依赖浮点类型完成精确财务运算。
ROUND(value, scale) 的两参数形式适用于 numeric。如果原字段是 double precision,可先转换为 numeric,再指定保留的小数位数。
日期与时间函数
CURRENT_DATE 返回当前日期,CURRENT_TIMESTAMP 返回当前时间戳,EXTRACT 从日期时间中提取年、月、日等部分,DATE_TRUNC 可按小时、天或月截断时间。
动态时间值会随运行时刻变化,因此测试时可使用 DATE '2026-07-29' 或 TIMESTAMP '2026-07-29 10:30:00' 构造固定输入,便于验证结果。
类型转换
标准写法是 CAST(value AS type),PostgreSQL 还支持简写 value::type。例如 '42'::integer 会把文本转换为整数。
转换失败会直接报错,例如 'abc'::integer 无法得到整数。导入外部数据时,应先校验格式,或通过条件判断过滤非法值,再执行转换。