PostgreSQL VALUES 列表详解

概述

VALUES 提供了一种生成"常量表"的方法，该表可以在查询中使用，而无需实际在磁盘上创建和填充表。这是 PostgreSQL 中一个非常实用的特性，特别适用于快速创建临时数据集、测试查询或在复杂查询中提供静态数据。

基本语法

sql

VALUES ( expression [, ...] ) [, ...]

INFO

语法说明

每个带括号的表达式列表都会在表中生成一行
所有行必须具有相同数量的元素（列数）
每列中对应的条目必须具有兼容的数据类型
使用与 UNION 相同的规则来确定结果的数据类型

基础示例与分析

示例 1：创建简单的常量表

问题陈述：需要创建一个包含数字和对应英文单词的临时表用于测试。

解决方案：

sql

VALUES (1, 'one'), (2, 'two'), (3, 'three');

输入和输出：

 column1 | column2
---------+---------
       1 | one
       2 | two
       3 | three
(3 rows)

分析过程：

VALUES 关键字启动常量表定义
每个 (值1, 值2) 代表表中的一行
PostgreSQL 自动推断数据类型：第一列为 integer，第二列为 text
默认列名为 column1、column2 等

示例 2：使用表别名和列别名

问题陈述：为了使查询结果更具可读性，需要为 VALUES 表指定有意义的列名。

解决方案：

sql

SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num, letter);

输入和输出：

 num | letter
-----+--------
   1 | one
   2 | two
   3 | three
(3 rows)

分析过程：

AS t (num, letter) 为整个 VALUES 表设置别名 t
(num, letter) 指定了列的名称，替换默认的 column1、column2
这种方式提高了查询的可读性和可维护性

VALUES 与 SELECT UNION 的等效性

等效性演示

VALUES 语句实际上等效于一系列 SELECT UNION 操作：

VALUES 语法等效的 SELECT UNION

sql

VALUES (1, 'one'), (2, 'two'), (3, 'three');

sql

SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';

两种语法产生完全相同的结果，但 VALUES 语法更简洁。

性能比较

TIP

性能提示 VALUES 语法在解析阶段更高效，特别是对于大量常量行的情况。PostgreSQL 查询优化器能够更好地识别和优化 VALUES 表达式。

实际业务场景应用

场景 1：批量插入预定义数据

业务需求：为新系统初始化用户角色和权限级别。

sql

-- 创建角色表
CREATE TABLE user_roles (
    role_id INTEGER,
    role_name VARCHAR(50),
    permission_level INTEGER
);

-- 使用 VALUES 批量插入初始数据
INSERT INTO user_roles (role_id, role_name, permission_level)
VALUES
    (1, 'Admin', 10),
    (2, 'Manager', 7),
    (3, 'Employee', 5),
    (4, 'Guest', 1);

分析过程：

VALUES 提供了一种简洁的方式来插入多行预定义数据
比多个单独的 INSERT 语句更高效
在事务中作为一个原子操作执行

场景 2：数据转换和映射

业务需求：将数字状态码转换为人可读的状态描述。

sql

-- 查询订单状态，包含状态描述
SELECT
    o.order_id,
    o.status_code,
    sm.status_description,
    o.created_at
FROM orders o
JOIN (
    VALUES
        (1, '待付款'),
        (2, '已付款'),
        (3, '已发货'),
        (4, '已完成'),
        (5, '已取消')
) AS sm(status_code, status_description) ON o.status_code = sm.status_code
WHERE o.created_at >= '2024-01-01';

输入和输出示例：

 order_id | status_code | status_description | created_at
----------+-------------+--------------------+------------
     1001 |           2 | 已付款             | 2024-01-15
     1002 |           3 | 已发货             | 2024-01-16
     1003 |           1 | 待付款             | 2024-01-17
(3 rows)

分析过程：

VALUES 创建临时映射表，避免创建永久的查找表
JOIN 操作将状态码转换为描述性文本
这种方法适用于映射关系相对稳定且数据量不大的场景

场景 3：生成测试数据

业务需求：为性能测试生成示例产品数据。

sql

-- 生成测试产品数据
WITH test_categories AS (
    SELECT * FROM (
        VALUES
            ('ELEC', '电子产品', 0.08),
            ('CLTH', '服装', 0.13),
            ('BOOK', '图书', 0.00),
            ('FOOD', '食品', 0.06),
            ('HOME', '家居用品', 0.10)
    ) AS t(category_code, category_name, tax_rate)
)
SELECT
    category_code,
    category_name,
    tax_rate,
    CASE
        WHEN tax_rate = 0 THEN '免税'
        WHEN tax_rate <= 0.08 THEN '低税率'
        ELSE '标准税率'
    END AS tax_level
FROM test_categories
ORDER BY tax_rate;

输入和输出：

 category_code | category_name | tax_rate | tax_level
---------------+---------------+----------+-----------
 BOOK          | 图书          |     0.00 | 免税
 FOOD          | 食品          |     0.06 | 低税率
 ELEC          | 电子产品      |     0.08 | 低税率
 HOME          | 家居用品      |     0.10 | 标准税率
 CLTH          | 服装          |     0.13 | 标准税率
(5 rows)

高级用法

与 CTE (Common Table Expression) 结合

sql

-- 分析不同年龄段的用户分布
WITH age_groups AS (
    SELECT * FROM (
        VALUES
            ('青少年', 13, 18),
            ('青年', 19, 35),
            ('中年', 36, 55),
            ('老年', 56, 100)
    ) AS ag(group_name, min_age, max_age)
),
user_age_stats AS (
    SELECT
        ag.group_name,
        COUNT(u.user_id) as user_count,
        ROUND(AVG(u.age), 1) as avg_age
    FROM users u
    JOIN age_groups ag ON u.age BETWEEN ag.min_age AND ag.max_age
    GROUP BY ag.group_name, ag.min_age
    ORDER BY ag.min_age
)
SELECT
    group_name,
    user_count,
    avg_age,
    ROUND(100.0 * user_count / SUM(user_count) OVER(), 2) as percentage
FROM user_age_stats;

在子查询中使用 VALUES

sql

-- 查找特定类型的产品
SELECT
    product_name,
    category,
    price
FROM products
WHERE category IN (
    SELECT category
    FROM (VALUES ('Electronics'), ('Books'), ('Clothing')) AS t(category)
)
AND price > 100;

数据类型处理

自动类型推断

PostgreSQL 使用与 UNION 相同的规则来确定 VALUES 表中每列的数据类型：

sql

-- 演示类型推断
SELECT pg_typeof(column1), pg_typeof(column2), pg_typeof(column3)
FROM (
    VALUES
        (1, 1.5, '2024-01-01'::date),
        (2, 2.0, '2024-01-02'::date)
) AS t(column1, column2, column3)
LIMIT 1;

输出：

 pg_typeof | pg_typeof |  pg_typeof
-----------+-----------+-------------
 integer   | numeric   | date
(1 row)

显式类型转换

当需要特定数据类型时，可以使用显式转换：

sql

VALUES
    (1::bigint, 'A'::char(1), true::boolean),
    (2::bigint, 'B'::char(1), false::boolean);

性能考量和最佳实践

性能对比测试

sql

-- 测试 VALUES vs 临时表性能
EXPLAIN ANALYZE
SELECT * FROM (
    VALUES
        (1, 'test1'), (2, 'test2'), (3, 'test3'),
        (4, 'test4'), (5, 'test5')
) AS t(id, name)
WHERE id > 2;

WARNING

性能注意事项

VALUES 适用于小到中等规模的数据集（通常少于 1000 行）
对于大量数据，考虑使用临时表或 CTE
在 JOIN 操作中，VALUES 表没有索引，大数据量时性能可能不佳

最佳实践

列名命名：

sql

-- 推荐：使用有意义的列别名
SELECT * FROM (VALUES (1, 'A'), (2, 'B')) AS t(id, code);

-- 不推荐：使用默认列名
VALUES (1, 'A'), (2, 'B');

数据类型一致性：

sql

-- 正确：确保同列数据类型兼容
VALUES (1, 'one'), (2, 'two'), (3, 'three');

-- 错误：数据类型不兼容
-- VALUES (1, 'one'), ('two', 2), (3.0, 'three');

复杂表达式：

sql

-- VALUES 中可以包含复杂表达式
VALUES
    (1, CURRENT_DATE, 'active'),
    (2, CURRENT_DATE + INTERVAL '1 day', 'pending'),
    (3, CURRENT_DATE + INTERVAL '1 week', 'scheduled');

与其他 SQL 特性的集成

配合窗口函数

sql

-- 为 VALUES 数据添加排名
SELECT
    name,
    score,
    RANK() OVER (ORDER BY score DESC) as ranking
FROM (
    VALUES
        ('Alice', 95),
        ('Bob', 87),
        ('Charlie', 92),
        ('David', 87),
        ('Eve', 98)
) AS students(name, score);

在 CASE 表达式中使用

sql

-- 复杂的条件映射
SELECT
    user_id,
    status_code,
    CASE status_code
        WHEN ANY(SELECT status FROM (VALUES (1), (2), (3)) AS t(status))
        THEN '处理中'
        ELSE '已完成'
    END as status_group
FROM user_orders;

常见错误和解决方案

错误 1：列数不匹配

sql

-- 错误示例
-- VALUES (1, 'A'), (2, 'B', 'C');  -- 第二行有3列，第一行只有2列

-- 正确做法
VALUES (1, 'A', NULL), (2, 'B', 'C');

错误 2：数据类型冲突

sql

-- 错误示例
-- VALUES (1, 'text'), ('not_number', 'text');  -- 第一列类型不一致

-- 正确做法
VALUES (1, 'text'), (0, 'text');  -- 或者使用 NULL

实战练习

练习 1：创建月份映射表

创建一个将月份数字转换为中文月份名称的查询：

Details

解决方案

sql

SELECT
    month_num,
    month_name,
    CASE
        WHEN month_num IN (3, 4, 5) THEN '春季'
        WHEN month_num IN (6, 7, 8) THEN '夏季'
        WHEN month_num IN (9, 10, 11) THEN '秋季'
        ELSE '冬季'
    END as season
FROM (
    VALUES
        (1, '一月'), (2, '二月'), (3, '三月'), (4, '四月'),
        (5, '五月'), (6, '六月'), (7, '七月'), (8, '八月'),
        (9, '九月'), (10, '十月'), (11, '十一月'), (12, '十二月')
) AS months(month_num, month_name)
ORDER BY month_num;

练习 2：生成分页导航数据

为分页组件生成页码数据：

Details

解决方案

sql

WITH pagination AS (
    SELECT page_num,
           CASE
               WHEN page_num = 1 THEN '首页'
               WHEN page_num = 10 THEN '末页'
               ELSE page_num::text
           END as page_label,
           page_num = 5 as is_current
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t(page_num)
)
SELECT
    page_num,
    page_label,
    is_current,
    CASE WHEN is_current THEN 'active' ELSE 'normal' END as css_class
FROM pagination
ORDER BY page_num;

总结

VALUES 列表是 PostgreSQL 中一个强大而灵活的特性，主要优势包括：

简洁性：提供比多个 SELECT UNION 更简洁的语法
灵活性：可以在任何需要表表达式的地方使用
高效性：对于小到中等规模的数据集性能优异
可读性：使查询更加清晰和易于理解

在实际开发中，VALUES 特别适用于：

快速原型开发和测试
数据映射和转换
批量插入预定义数据
创建临时查找表

掌握 VALUES 的使用将大大提高您在 PostgreSQL 中处理常量数据和临时数据集的效率。

PostgreSQL VALUES 列表详解 ​

概述 ​

基本语法 ​

基础示例与分析 ​

示例 1：创建简单的常量表 ​

示例 2：使用表别名和列别名 ​

VALUES 与 SELECT UNION 的等效性 ​

等效性演示 ​

性能比较 ​

实际业务场景应用 ​

场景 1：批量插入预定义数据 ​

场景 2：数据转换和映射 ​

场景 3：生成测试数据 ​

高级用法 ​

与 CTE (Common Table Expression) 结合 ​

在子查询中使用 VALUES ​

数据类型处理 ​

自动类型推断 ​

显式类型转换 ​

性能考量和最佳实践 ​

性能对比测试 ​

最佳实践 ​

与其他 SQL 特性的集成 ​

配合窗口函数 ​

在 CASE 表达式中使用 ​

常见错误和解决方案 ​

错误 1：列数不匹配 ​

错误 2：数据类型冲突 ​

实战练习 ​

练习 1：创建月份映射表 ​

练习 2：生成分页导航数据 ​

总结 ​

PostgreSQL VALUES 列表详解

概述

基本语法

基础示例与分析

示例 1：创建简单的常量表

示例 2：使用表别名和列别名

VALUES 与 SELECT UNION 的等效性

等效性演示

性能比较

实际业务场景应用

场景 1：批量插入预定义数据

场景 2：数据转换和映射

场景 3：生成测试数据

高级用法

与 CTE (Common Table Expression) 结合

在子查询中使用 VALUES

数据类型处理

自动类型推断

显式类型转换

性能考量和最佳实践

性能对比测试

最佳实践

与其他 SQL 特性的集成

配合窗口函数

在 CASE 表达式中使用

常见错误和解决方案

错误 1：列数不匹配

错误 2：数据类型冲突

实战练习

练习 1：创建月份映射表

练习 2：生成分页导航数据

总结