首页
/ 正文

PostgreSQL 常用函数（手把手讲解）

更新时间: 2025-04-16 13:21:32

💡一则或许对你有用的小广告

欢迎加入小哈的星球 ，你将获得：专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...，点击查看项目介绍 ;

《从零手撸：前后端分离博客项目（全栈开发）》 2 期已完结，演示链接： http://116.62.199.48/ ;

截止目前，星球内专栏累计输出 82w+ 字，讲解图 3441+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，权限管理，Spring Cloud Alibaba 微服务等等，已有 2900+ 小伙伴加入学习，欢迎点击围观

前言

PostgreSQL 作为一款功能强大的开源关系型数据库，其丰富的内置函数是开发者高效处理数据的核心工具。无论是基础的字符串操作、数值计算，还是复杂的日期时间分析，PostgreSQL 常用函数都能显著提升开发效率。本文将从编程初学者和中级开发者的视角，系统讲解 PostgreSQL 中最常用的核心函数，并通过实际案例和代码示例，帮助读者循序渐进地掌握这些工具的使用逻辑与应用场景。

一、字符串函数：文本处理的瑞士军刀

字符串函数是 PostgreSQL 中最基础且高频使用的工具，它们能够灵活操作文本数据，例如拼接、截取、转换格式等。

1.1 字符串拼接：CONCAT 和 || 运算符

CONCAT 函数和 || 运算符可用于合并多个字符串，尤其适用于生成完整名称或地址。
示例：

-- 创建测试表  
CREATE TABLE users (  
    id SERIAL PRIMARY KEY,  
    first_name TEXT,  
    last_name TEXT,  
    email TEXT  
);  

-- 插入测试数据  
INSERT INTO users (first_name, last_name, email)  
VALUES  
('Alice', 'Johnson', 'alice.j@example.com'),  
('Bob', 'Smith', 'bob.s@example.com');  

-- 使用 CONCAT 合并名字和姓氏  
SELECT CONCAT(first_name, ' ', last_name) AS full_name  
FROM users;  
-- 输出：  
-- Alice Johnson  
-- Bob Smith

1.2 字符串截取与定位：SUBSTRING 和 POSITION

SUBSTRING 可按位置或正则表达式截取子字符串，而 POSITION 可定位字符的起始位置。
示例：

-- 提取邮箱的域名部分  
SELECT  
    SUBSTRING(email FROM '@(.*)') AS domain,  -- 使用正则表达式捕获域名  
    POSITION('@' IN email) AS at_position     -- 查找 @ 符号的位置  
FROM users;  
-- 输出：  
-- domain | at_position  
-- example.com | 8

1.3 字符串转换：UPPER、LOWER 和 LENGTH

UPPER 和 LOWER 用于转换大小写，LENGTH 返回字符串长度。
示例：

SELECT  
    email,  
    LOWER(email) AS lowercase_email,  
    LENGTH(email) AS email_length  
FROM users;

二、数学函数：数值操作的得力助手

数学函数帮助开发者对数值进行精确计算，例如四舍五入、取整、幂运算等。

2.1 常见数学操作：ROUND、CEIL 和 FLOOR

ROUND：按指定小数位数四舍五入。
CEIL 和 FLOOR：向上或向下取整。
示例：

SELECT  
    ROUND(123.456::numeric, 2) AS rounded_value,  -- 123.46  
    CEIL(-2.3) AS ceiling_value,                  -- -2  
    FLOOR(3.9) AS floor_value                     -- 3  
FROM users LIMIT 1;

2.2 幂运算与平方根：POWER 和 SQRT

示例：计算圆的面积（半径为 5）

SELECT  
    POWER(5, 2) * PI() AS circle_area;  
-- 输出：78.53981633974483

三、日期与时间函数：时间管理的工具箱

日期时间函数是处理时间戳、计算时间差、提取日期部分的必备工具。

3.1 当前时间与日期：CURRENT_TIMESTAMP 和 NOW()

这两个函数返回当前的日期和时间，常用于记录操作时间。
示例：

SELECT  
    CURRENT_TIMESTAMP AS current_time,  
    NOW() AS now_time;  
-- 输出：  
-- current_time | now_time  
-- 2023-10-05 14:30:00.123456+08 | 2023-10-05 14:30:00.123456+08

3.2 时间差与日期提取：AGE 和 DATE_PART

AGE：计算两个日期之间的差异，常用于计算年龄。
DATE_PART：提取日期中的年、月、日等部分。
示例：

-- 创建成员表并插入数据  
CREATE TABLE members (  
    id SERIAL PRIMARY KEY,  
    name TEXT,  
    birthdate DATE  
);  

INSERT INTO members (name, birthdate)  
VALUES ('Charlie', '2000-05-15');  

-- 计算年龄  
SELECT  
    name,  
    AGE(birthdate) AS age,  
    DATE_PART('year', AGE(birthdate)) AS age_years  
FROM members;  
-- 输出：  
-- Charlie | 23 years 4 mons | 23

3.3 时间间隔运算：INTERVAL

示例：计算一周后的日期

SELECT  
    CURRENT_DATE + INTERVAL '7 days' AS next_week_date;  
-- 输出：2023-10-12

四、条件函数：智能决策的分支工具

条件函数允许根据数据值动态返回结果，例如处理缺失值或分支逻辑。

4.1 条件判断：CASE WHEN

CASE WHEN 类似编程中的 if-else 语句，用于根据条件返回不同值。
示例：根据价格分类商品

-- 创建商品表  
CREATE TABLE products (  
    id SERIAL PRIMARY KEY,  
    name TEXT,  
    price NUMERIC  
);  

INSERT INTO products (name, price)  
VALUES  
('Laptop', 1200),  
('Mouse', 25);  

-- 分类价格等级  
SELECT  
    name,  
    price,  
    CASE  
        WHEN price > 1000 THEN 'High'  
        WHEN price BETWEEN 50 AND 1000 THEN 'Medium'  
        ELSE 'Low'  
    END AS price_category  
FROM products;  
-- 输出：  
-- Laptop | 1200 | High  
-- Mouse | 25 | Low

4.2 处理缺失值：COALESCE 和 NULLIF

COALESCE：返回第一个非 NULL 值。
NULLIF：当两值相等时返回 NULL。
示例：

-- 使用 COALESCE 处理缺失的电话号码  
SELECT  
    COALESCE(phone_number, 'N/A') AS contact_info  
FROM users;  

-- 使用 NULLIF 检查重复值  
SELECT  
    NULLIF('apple', 'apple') AS result;  -- 返回 NULL

五、聚合函数：数据统计的汇总专家

聚合函数对多行数据进行计算并返回单个结果，常用于统计分析。

5.1 基础聚合函数：SUM、AVG、COUNT

示例：计算订单总销售额和平均价格

-- 创建订单表  
CREATE TABLE orders (  
    id SERIAL PRIMARY KEY,  
    product_id INT,  
    sales_amount NUMERIC  
);  

INSERT INTO orders (product_id, sales_amount)  
VALUES  
(1, 1200),  
(2, 200),  
(1, 800);  

-- 统计销售额  
SELECT  
    SUM(sales_amount) AS total_sales,  
    AVG(sales_amount) AS avg_sales  
FROM orders;  
-- 输出：  
-- total_sales | avg_sales  
-- 2200 | 733.333

5.2 分组统计：GROUP BY 和 HAVING

结合 GROUP BY 和 HAVING 可按条件分组并筛选结果。
示例：按产品分类统计销售额

SELECT  
    product_id,  
    SUM(sales_amount) AS total_sales  
FROM orders  
GROUP BY product_id  
HAVING SUM(sales_amount) > 1000;  
-- 输出：  
-- product_id | total_sales  
-- 1 | 2000

六、NULL 处理函数：空值管理的艺术

PostgreSQL 中的 NULL 表示“未知值”，需通过特定函数处理以避免逻辑错误。

6.1 替换 NULL 值：COALESCE 的进阶用法

示例：为 NULL 的字段提供默认值

SELECT  
    name,  
    COALESCE(NULL, 'No Value', 'Default') AS result  -- 返回 'No Value'  
FROM members;

6.2 检测 NULL 值：IS NULL 和 IS NOT NULL

示例：筛选无电话号码的用户

SELECT * FROM users WHERE phone_number IS NULL;

七、高级函数：面向进阶场景的扩展

7.1 数组函数：ARRAY 和 ARRAY_APPEND

PostgreSQL 支持数组类型，常用函数包括 ARRAY 构建数组、ARRAY_APPEND 添加元素。
示例：

SELECT  
    ARRAY[1, 2, 3] AS my_array,  
    ARRAY_APPEND(ARRAY[1, 2], 3) AS extended_array;

7.2 JSON 函数：JSONB 操作

PostgreSQL 的 JSONB 类型支持丰富的函数，例如 JSONB_ARRAY_ELEMENTS 解析数组。
示例：解析 JSON 数组

SELECT  
    jsonb_array_elements('[{"id":1}, {"id":2}]') AS elements;

结论

PostgreSQL 常用函数如同数据库的“工具箱”，覆盖从基础操作到复杂分析的全流程场景。通过本文的讲解，读者已掌握了字符串、数学、日期、条件判断等核心函数的使用方法，并通过实际案例理解了它们的实用价值。建议读者在实践中多尝试组合函数，例如将 CASE WHEN 与聚合函数结合，或用 DATE_PART 进行时间维度分析，逐步提升数据处理能力。掌握这些函数不仅能提高代码效率，更能为构建高性能、可维护的数据库应用奠定坚实基础。

PostgreSQL 常用函数（手把手讲解）

前言

一、字符串函数：文本处理的瑞士军刀

1.1 字符串拼接：CONCAT 和 || 运算符

1.2 字符串截取与定位：SUBSTRING 和 POSITION

1.3 字符串转换：UPPER、LOWER 和 LENGTH

二、数学函数：数值操作的得力助手

2.1 常见数学操作：ROUND、CEIL 和 FLOOR

2.2 幂运算与平方根：POWER 和 SQRT

三、日期与时间函数：时间管理的工具箱

3.1 当前时间与日期：CURRENT_TIMESTAMP 和 NOW()

3.2 时间差与日期提取：AGE 和 DATE_PART

3.3 时间间隔运算：INTERVAL

四、条件函数：智能决策的分支工具

4.1 条件判断：CASE WHEN

4.2 处理缺失值：COALESCE 和 NULLIF

五、聚合函数：数据统计的汇总专家

5.1 基础聚合函数：SUM、AVG、COUNT

5.2 分组统计：GROUP BY 和 HAVING

六、NULL 处理函数：空值管理的艺术

6.1 替换 NULL 值：COALESCE 的进阶用法

6.2 检测 NULL 值：IS NULL 和 IS NOT NULL

七、高级函数：面向进阶场景的扩展

7.1 数组函数：ARRAY 和 ARRAY_APPEND

7.2 JSON 函数：JSONB 操作

结论

最新发布

C++ 实例 – 求一个数的阶乘（长文解析）

C++ 实例 – 判断素数（长文解析）

C++ 实例 – 创建各类三角形图案（千字长文）

C++ 实例 – 求两数的最大公约数（保姆级教程）

C++ 实例 – 求两数最小公倍数（长文解析）

C++ 实例 – 实现一个简单的计算器（手把手讲解）

PHP mb_substr() 函数（长文解析）

Markdown 教程（长文解析）

Markdown 标题（长文解析）

Markdown 段落格式（建议收藏）