PostgreSQL / 系统课时

使用常用标量函数

使用常用标量函数

本课解决查询时需要清洗、计算和格式化单个字段值的问题。你将使用字符串、数值、日期和类型转换函数,在不修改原始数据的情况下生成新的查询结果。

学习目标

  • 使用常用字符串和数值函数处理字段
  • 使用日期函数提取或格式化时间
  • 使用 CAST 和 PostgreSQL 简写语法转换类型

标量函数的作用

标量函数对每一行输入分别计算,并为每一行返回一个结果。它与聚合函数不同:UPPER(name) 会处理每条记录,而 COUNT(*) 会把多条记录汇总成一个统计值。

下面通过固定值演示常用函数,示例可直接在 PostgreSQL 中运行:

sql
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;

预期结果

text
 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 表:

sql
CREATE INDEX IF NOT EXISTS idx_users_lower_email
ON users (LOWER(email));

预期结果

text
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 无法得到整数。导入外部数据时,应先校验格式,或通过条件判断过滤非法值,再执行转换。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

使用常用标量函数

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通