PostgreSQL 内置函数

Begindev 编辑部 March 31, 2026

PostgreSQL 中的内置函数是预先定义好的函数，你可以直接使用它们对数据执行各种操作。本章将重点介绍字符串处理、数值运算和日期时间函数，这些功能对于数据清洗、数据转换和数据分析至关重要。

熟练理解并运用这些内置函数，将大幅提升你在 PostgreSQL 中处理数据的能力。

1. 字符串处理函数

字符串处理函数允许你修改和提取文本数据中的信息。PostgreSQL 为此提供了极其丰富的函数库。

1.1 `character_length()` 或 `char_length()`

此函数返回字符串中的字符数量。

SELECT character_length('PostgreSQL'); -- 输出: 10
SELECT char_length('Hello'); -- 输出: 5

示例： 假设你有一个包含 first_name（名字）列的 employees（员工）表。你可以查找每个员工名字的长度：

SELECT first_name, character_length(first_name) AS name_length
FROM employees;

1.2 `octet_length()`

此函数返回字符串占据的字节数。在处理多字节字符（例如 UTF-8 编码的中文字符）时，character_length 和 octet_length 之间的区别非常重要。

SELECT octet_length('PostgreSQL'); -- 输出: 10 (假设使用 ASCII 或 UTF-8 编码)
SELECT octet_length('你好'); -- 输出: 6 (假设使用 UTF-8 编码，通常每个中文字符占 3 个字节)

示例： 如果你需要检查特定编码下字符串的存储大小：

SELECT product_name, octet_length(product_name) AS name_size
FROM products;

1.3 `upper()` 和 `lower()`

这两个函数分别将字符串转换为大写或小写。

SELECT upper('postgresql'); -- 输出: POSTGRESQL
SELECT lower('POSTGRESQL'); -- 输出: postgresql

示例： 在比较数据时统一大小写标准：

SELECT email FROM users WHERE lower(email) = 'test@example.com';

1.4 `substring()`

此函数用于从字符串中提取子串。它接收三个参数：原字符串、起始位置和子串的长度。你也可以使用更加语义化的 SUBSTRING(string FROM start FOR length) 语法。

SELECT substring('PostgreSQL', 1, 4); -- 输出: Post
SELECT substring('PostgreSQL' FROM 6 FOR 4); -- 输出: gres

示例： 从日期字符串中提取年份：

SELECT order_date, substring(order_date::text FROM 1 FOR 4) AS order_year
FROM orders;

1.5 `trim()`

此函数主要用于移除字符串开头和结尾的空格。它也可以用来移除指定的其他字符。

SELECT trim('   PostgreSQL   '); -- 输出: PostgreSQL
SELECT trim(both 'x' from 'xxxPostgreSQLxxx'); -- 输出: PostgreSQL
SELECT trim(leading 'x' from 'xxxPostgreSQLxxx'); -- 输出: PostgreSQLxxx (仅移除开头)
SELECT trim(trailing 'x' from 'xxxPostgreSQLxxx'); -- 输出: xxxPostgreSQL (仅移除结尾)

示例： 清理用户输入时多余的空格：

UPDATE products SET description = trim(description);

1.6 `replace()`

此函数将字符串中所有出现的指定子串替换为另一个新的子串。

SELECT replace('PostgreSQL is great', 'great', 'awesome'); -- 输出: PostgreSQL is awesome

示例： 纠正拼写错误的单词：

UPDATE products SET product_name = replace(product_name, 'cofee', 'coffee');

1.7 `concat()`

此函数将两个或多个字符串拼接在一起。在 PostgreSQL 中，你也可以使用 || 运算符进行拼接。

SELECT concat('Post', 'greSQL'); -- 输出: PostgreSQL
SELECT 'Post' || 'greSQL'; -- 输出: PostgreSQL
SELECT concat('First Name: ', first_name, ', Last Name: ', last_name) FROM employees;

示例： 将名和姓拼接成全名：

SELECT concat(first_name, ' ', last_name) AS full_name FROM employees;

1.8 `split_part()`

此函数根据指定的分隔符将字符串分割成多个部分，并返回第 n 个部分。

SELECT split_part('apple,banana,orange', ',', 2); -- 输出: banana

示例： 从电子邮件地址中提取域名：

SELECT email, split_part(email, '@', 2) AS domain FROM users;

2. 数值运算函数

数值函数允许你对数值型数据执行数学运算和计算。

2.1 `abs()`

此函数返回一个数字的绝对值。

SELECT abs(-10); -- 输出: 10
SELECT abs(5); -- 输出: 5

示例： 计算两个值之间的差异，忽略谁大谁小：

SELECT abs(actual_value - expected_value) AS difference FROM measurements;

2.2 `ceil()`

此函数将数字向上取整到最接近的整数。

SELECT ceil(4.2); -- 输出: 5
SELECT ceil(-4.2); -- 输出: -4

示例： 计算运送一定数量的物品需要多少个箱子（每个箱子容量有限，哪怕只多出 1 个物品也需要多加一个箱子）：

SELECT ceil(total_items / items_per_box) AS number_of_boxes FROM orders;

2.3 `floor()`

此函数将数字向下取整到最接近的整数。

SELECT floor(4.8); -- 输出: 4
SELECT floor(-4.8); -- 输出: -5

示例： 计算员工的完整服务年限（向下取整）：

SELECT floor(months_of_service / 12) AS years_of_service FROM employees;

2.4 `round()`

此函数将数字四舍五入到指定的小数位数。如果不指定小数位数，则四舍五入到最接近的整数。

SELECT round(4.56); -- 输出: 5
SELECT round(4.56, 1); -- 输出: 4.6
SELECT round(4.56, 0); -- 输出: 5.0

示例： 将价格四舍五入到小数点后两位用于展示：

SELECT product_name, round(price, 2) AS rounded_price FROM products;

2.5 `random()`

此函数返回一个介于 0.0 到 1.0 之间的伪随机数。

SELECT random(); -- 输出: 一个随机数, 例如 0.56789

示例： 从表中随机抽取 10 行数据作为样本：

SELECT * FROM customers ORDER BY random() LIMIT 10;

2.6 `sqrt()`

此函数返回数字的平方根。

SELECT sqrt(25); -- 输出: 5
SELECT sqrt(2); -- 输出: 1.4142135623730951

示例： 使用勾股定理计算两点之间的直线距离：

SELECT sqrt(power(x1 - x2, 2) + power(y1 - y2, 2)) AS distance FROM coordinates;

2.7 `power()`

此函数计算数字的指定次幂。

SELECT power(2, 3); -- 输出: 8 (即 2 的 3 次方)
SELECT power(5, 2); -- 输出: 25 (即 5 的 2 次方)

示例： 计算复利：

SELECT principal * power(1 + interest_rate, years) AS future_value FROM investments;

2.8 `trunc()`

此函数将数字截断到指定的小数位数。与 round() 的四舍五入不同，它只是简单地砍掉多余的小数位。

SELECT trunc(4.56); -- 输出: 4
SELECT trunc(4.56, 1); -- 输出: 4.5

示例： 提取除法运算的整数部分：

SELECT trunc(total_sales / number_of_customers) AS average_sales_per_customer FROM sales_data;

3. 日期与时间函数

日期函数允许你操作和提取日期及时间数据中的具体信息。

3.1 `now()` 或 `current_timestamp`

这两个函数均返回当前的日期和时间。

SELECT now(); -- 输出: 当前日期和时间, 例如 2023-10-27 10:00:00+00
SELECT current_timestamp; -- 输出: 与 now() 相同

示例： 在记录创建时保存时间戳：

INSERT INTO orders (order_date) VALUES (now());

3.2 `current_date`

此函数仅返回当前的日期（不包含时间）。

SELECT current_date; -- 输出: 当前日期, 例如 2023-10-27

示例： 查询今天下的所有订单：

SELECT * FROM orders WHERE order_date::date = current_date;

3.3 `current_time`

此函数仅返回当前的时间（包含时区信息，不含日期）。

SELECT current_time; -- 输出: 当前时间, 例如 10:00:00+00

示例： 选择计划在当前精确时间举行的所有事件（这种用法较少见，因为时间匹配非常严格）：

SELECT * FROM events WHERE event_time = current_time;

3.4 `date()`

此函数用于从完整的时间戳中提取日期部分。

SELECT date(now()); -- 输出: 当前日期, 例如 2023-10-27

示例： 按日期对订单进行分组：

SELECT date(order_date), COUNT(*) FROM orders GROUP BY date(order_date);

3.5 `extract()`

此函数用于从日期或时间戳中提取特定部分，例如年（year）、月（month）、日（day）、小时（hour）、分钟（minute）或秒（second）。

SELECT extract(year FROM now()); -- 输出: 当前年份, 例如 2023
SELECT extract(month FROM now()); -- 输出: 当前月份, 例如 10
SELECT extract(day FROM now()); -- 输出: 当前日期, 例如 27
SELECT extract(hour FROM now()); -- 输出: 当前小时, 例如 10

示例： 按月份分析销售数据：

SELECT extract(month FROM order_date) AS order_month, SUM(total_amount) FROM orders GROUP BY order_month;

3.6 `age()`

此函数计算两个日期或时间戳之间的时间差（年龄/间隔）。

SELECT age(timestamp '2023-10-27 10:00:00', timestamp '2023-10-20 00:00:00'); -- 输出: 6 days 10:00:00
SELECT age(now(), timestamp '2000-01-01 00:00:00'); -- 输出: 从 2000 年 1 月 1 日至今的时长

示例： 计算员工入职至今的工作时长（司龄）：

SELECT first_name, last_name, age(now(), hire_date) AS employee_age FROM employees;

3.7 `date_trunc()`

此函数将时间戳截断到指定的精度，例如年、月、日、小时等。它会返回该周期的起始时刻。

SELECT date_trunc('month', now()); -- 输出: 当月的第一天零点, 例如 2023-10-01 00:00:00+00
SELECT date_trunc('year', now()); -- 输出: 当年的第一天零点, 例如 2023-01-01 00:00:00+00

示例： 按月份汇总销售数据，使用 date_trunc 确保时间维度一致：

SELECT date_trunc('month', order_date), SUM(total_amount) FROM orders GROUP BY date_trunc('month', order_date);

3.8 `to_char()`

此函数将日期或时间戳按照指定的格式转换成字符串。

SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS'); -- 输出: 指定格式的当前日期时间, 例如 2023-10-27 10:00:00
SELECT to_char(now(), 'Mon DD, YYYY'); -- 输出: 例如 Oct 27, 2023

示例： 为了在报表中展示，格式化订单日期：

SELECT order_id, to_char(order_date, 'Month DD, YYYY') AS formatted_date FROM orders;

1. 字符串处理函数

1.1 character_length() 或 char_length()

1.2 octet_length()

1.3 upper() 和 lower()

1.4 substring()

1.5 trim()

1.6 replace()

1.7 concat()

1.8 split_part()

2. 数值运算函数

2.1 abs()

2.2 ceil()

2.3 floor()

2.4 round()

2.5 random()

2.6 sqrt()

2.7 power()

2.8 trunc()