如果你在 PostgreSQL 里折腾过一批临时数据大概率都经历过这样的别扭时刻想给某个查询喂一组固定参数不想建表又嫌UNION ALL写起来太长。或者从 MySQL 迁过来习惯了SELECT 1 AS id, a AS name UNION ALL SELECT 2, b到了 PostgreSQL 里总觉得哪里不对。其实 PostgreSQL 内置了一个被大多数人低估的语法结构VALUES()。它看着不起眼但用好了能直接把 SQL 写得又短又清楚省掉一堆临时表的创建和清理工作。这篇就专门聊透这个功能。先说清楚它解决的痛点日常开发里一次性数据到处都是——报表里需要一张映射表、接口测试需要注入几行参数、批量更新需要按条件配不同的值。常规做法要么建一张真表再清掉要么写一长串UNION ALL。前者笨重后者啰嗦。VALUES()用一个表达式就能生成一张虚拟表用完即弃不落盘、不占连接、不产生事务负担。对刚上手 PostgreSQL 的朋友来说这个概念越早掌握越好对于从 MySQL 转过来的老手这篇文章也能帮你把两条 SQL 的语法差异一次对齐。1. 先认识VALUES()这个隐形的表很多 PostgreSQL 使用者第一次见到VALUES是在INSERT INTO ... VALUES (...)里以为它只是插入数据的语法糖。实际上VALUES在 PostgreSQL 里是一个完整的、可以独立使用的查询语句它本身就返回一张表。这是它与 MySQL 最大的不同之一。1.1 单独执行也能返回结果集在 MySQL 里你几乎不会单独执行VALUES (1, 2);这样的语句MySQL 8.0.19 之后虽然支持了VALUES行构造器但语义和使用场景都有限。但在 PostgreSQL 里你可以直接敲VALUES (1, a), (2, b);回车之后客户端会返回一个两列两行的结果集列名默认叫column1、column2。这就很有意思了——VALUES本身就是查询而不仅仅是用在 INSERT 后的子句。它能出现在SELECT语句里任何需要表的地方这路子一下就拓宽了。正因为 PostgreSQL 把VALUES当作表来对待它才有资格放在FROM后面、跟JOIN配合、被WITH子句引用。这也是用它生成临时表最核心的理论基础你需要的不是物理存在的临时表而是在查询执行期间存在的虚拟数据集。1.2 和SELECT ... UNION ALL的本质对比先看一段最常见的写法SELECT 1 AS id, 苹果 AS name UNION ALL SELECT 2 AS id, 香蕉 AS name UNION ALL SELECT 3 AS id, 橘子 AS name;这段代码能跑但问题在于它本质是三段查询结果的纵向拼接。每一段都要写SELECT列名还要在第一段里定义好。如果行数增加到 20 行代码就要写 20 段SELECT字符数爆炸改起来也容易漏。同样一份数据用VALUES是这样的SELECT * FROM (VALUES (1, 苹果), (2, 香蕉), (3, 橘子)) AS t(id, name);一个SELECT后面跟一组括号就搞定了。你只写一次列定义在AS t(id, name)里数据就是纯数据没有重复的SELECT噪音。对追求简洁的开发者来说这种表达方式的爽感用过一次就回不去。1.3 自动列名与手动别名的区别直接执行VALUES (1, a), (2, b);时列名是column1、column2。但在FROM子句里你不光要给整个派生表起别名还应该给每一列定义别名不然查询里引用列名会变得很难受-- 不推荐列名是 column1 和 column2 SELECT column1 FROM (VALUES (1, a), (2, b)) AS t; -- 推荐直接定义好列名 SELECT id FROM (VALUES (1, a), (2, b)) AS t(id, name);这一步算是使用VALUES()生成数据集最基本也最容易忽略的规范。从我的经验看不主动起列别名代码当时能跑等过一个月回来再看没注释的情况下你根本分不清column1到底代表 id 还是序号。2.VALUES()在 FROM 子句中的正确打开方式把它放进FROM子句、当成一张只存在于内存里的临时表是VALUES()最有价值的用法。下面按使用频率从高到低讲几种场景。2.1 把枚举映射直接写死在 SQL 里省掉一张关联表业务上经常需要把代码字段翻译成业务名称。比如订单表里存着status字段0 待支付、1 已支付、2 已发货、3 已完成。以前我见过两种处理方式一种是代码里写if-else翻译另一种是建一张状态字典表。两种都有代价——前者在纯 SQL 报表里根本使不上劲后者则要为这么点映射关系多维护一张表。用VALUES()直接构建映射关系是第三种解法。看这个例子SELECT o.order_id, o.status, s.status_name FROM orders o LEFT JOIN ( VALUES (0, 待支付), (1, 已支付), (2, 已发货), (3, 已完成) ) AS s(status_code, status_name) ON o.status s.status_code;这等于在查询里临时造了一张只有 4 行的字典表不需要建表、不需要导入数据、不需要权限变更。LEFT JOIN保证了即使订单表里出现未知状态订单本身也不会丢只是状态名显示为 NULL。少数几个需要翻译的字段、几十个枚举值这个办法都很合适。需要注意一个细节VALUES里有中文时PostgreSQL 客户端一定要确保连接编码是UTF8不然查询看着是对的中文全变成了乱码。这个跟VALUES()本身无关但就因为这个场景下写死数据的比例特别高所以踩坑概率也高。2.2 批量指定参数替代一串 OR 条件有时候你有一个 ID 列表要根据这批 ID 去查数据。最偷懒的写法是SELECT * FROM users WHERE id 101 OR id 202 OR id 303;再聪明一点用INSELECT * FROM users WHERE id IN (101, 202, 303);IN已经不错了但遇到复杂条件就绕不过去了。比如你要查这批用户的姓名、等级、注册日期的部分组合信息且每条记录匹配的条件都不一样。再极端一点你的过滤条件不是单列而是多列组合比如(city, level)两个字段要同时匹配一组组合。这时候IN就写不了了但VALUES()可以SELECT u.* FROM users u JOIN ( VALUES (上海, VIP), (北京, 普通), (广州, VIP) ) AS filter(city, level) ON u.city filter.city AND u.level filter.level;这个用法相当于把一组组合条件变成一张表去 JOIN。能推及到任意多列组合的批量匹配——这在手工处理数据、临时核对数据时特别高效。2.3 生成笛卡尔积做矩阵式测试还有一种偏测试向的玩法。你想对两个枚举维度做全组合测试比如促销活动里region × channel的每一种组合都造一条数据。手写 6 个组合能忍写 30 个组合就很崩溃了。用VALUES()配合CROSS JOIN代码非常干净SELECT region.region_name, channel.channel_name FROM ( VALUES (华东区), (华北区), (华南区) ) AS region(region_name) CROSS JOIN ( VALUES (小程序), (APP), (网页), (门店) ) AS channel(channel_name);执行结果直接返回 12 行组合数据而且逻辑清清楚楚。这种写法在生成测试用例、做完备性检查时非常实用——一眼就能看出你覆盖了哪些组合有没有漏项。2.4 单独使用时的行数限制小提醒VALUES列表支持非常多的行但前提是客户端发送的语句在内存中放得下。如果你在VALUES里塞了几十万条数据那已经不是临时生成一张表的范畴了而应该考虑COPY命令或者bulk insert。换句话说VALUES()适合几十到几千行级别的一次性数据不适合巨大的批量导入任务。把它当成查询级别的便利工具而不是数据装载工具认知就不会跑偏。3. 结合 INSERT既能批量写入也能防重复VALUES的另一个高频用法在INSERT上而且 PostgreSQL 的INSERT ... VALUES配合ON CONFLICT能做到有则更新、无则插入这是很多人迁移过来后爱不释手的原因之一。3.1 常规多行插入一条语句搞定新手可能只写过单行插入INSERT INTO products (name, price) VALUES (键盘, 299);实际上 PostgreSQL 允许在一个VALUES后面跟多组括号INSERT INTO products (name, price) VALUES (键盘, 299), (鼠标, 129), (显示器, 1499);呢这算是最基础的用 VALUES 生成临时数据行然后入库的形态。有个注意点这一整条多行插入语句在 PG 里是一个原子操作要么全部成功要么全部失败。行数多的时候性能也明显优于一条条分开执行——省去了多次 round trip。3.2 可加 RETURNING 子句立刻拿到生成的主键这是多行插入时非常顺手的功能。插入后立刻把id拿回来INSERT INTO products (name, price) VALUES (键盘, 299), (鼠标, 129) RETURNING id, name;执行完能看到每行插入后的真实主键 ID不用再写一条查询回头找。配合应用层做后续关联插入时这个特性节省的代码量非常可观。3.3 ON CONFLICT 实现存在则更新PostgreSQL 的INSERT ... VALUES ... ON CONFLICT提供了天然的有则更新能力。比如一批配置数据你想在conf_key有唯一约束的情况下插入新值并更新已有值INSERT INTO app_config (conf_key, conf_value) VALUES (page_size, 20), (cache_ttl, 300) ON CONFLICT (conf_key) DO UPDATE SET conf_value EXCLUDED.conf_value;这里的EXCLUDED指代本次想要插入但被唯一键挡下来的那一行。这种写法对脚本里跑一批种子数据、重复跑不会报错的诉求简直是量身定做。跑一遍是初始化跑两遍是更新没有任何心理负担。3.4 结合 SELECT * FROM (VALUES ...) 做异构数据入库有一种情况是你手里的数据不是整齐划一的全量插入而是需要先和已有表做关联判断。比如从 Excel 里整理出了用户等级列表想给已有用户批量更新等级。你先得把这份列表变成一个可 JOIN 的数据集UPDATE users u SET level tmp.level FROM ( VALUES (zhangsanexample.com, VIP), (lisiexample.com, 普通), (wangwuexample.com, VIP) ) AS tmp(email, level) WHERE u.email tmp.email;这段 SQL 的可读性非常好一个FROM (VALUES ...)的临时数据集被 UPDATE 直接引用。这是用 VALUES 生成数据临时表的典型场景没有建临时表、没有额外文件逻辑全在一条语句里。加上WHERE u.email tmp.email只更新匹配的用户其他用户不受影响。4. 高阶组合玩法CTE、类型转换与动态语句里的 VALUES如果只把VALUES()用在简单查询里那其实还没完全榨干它的能力。它跟 CTE、类型转换、动态 SQL 组合起来能解决不少刁钻需求。4.1 配合 WITH 子句一份数据多处引用当你要在同一个查询里多次使用同一组数据时WITH可以把VALUES()定义的虚拟表具名化WITH price_tier AS ( VALUES (0, 100, 低价区), (100, 500, 中价区), (500, 10000, 高价区) ) SELECT p.product_name, p.price, t.tier_name FROM products p JOIN price_tier t ON p.price t.min_price AND p.price t.max_price;有了 CTE这个price_tier可以在同一个查询的多个子句里反复引用而不必重复写VALUES。对于逻辑复杂的报表 SQL这种写法能把参数区显式提出来放在最前面让后续代码非常清爽。4.2 类型推断问题别让 PostgreSQL 替你猜VALUES()构造的临时表列类型默认来自第一行的字面值这个机制在绝大多数情况下能用但偶尔会坑你一把。举几个典型场景。第一日期类型。如果你写SELECT * FROM (VALUES (2024-01-01), (2024-02-01)) AS t(d);PostgreSQL 会把d推断成文本类型而不是日期类型。你后面的查询一旦用到日期函数就得先CAST(d AS DATE)。更好的做法是在第一行就明确指定类型SELECT * FROM (VALUES (2024-01-01::DATE), (2024-02-01)) AS t(d);第二混合类型。比如一列里既有数字又有字符串PostgreSQL 会尽量找一个公共类型或直接报错-- 这样会报错 SELECT * FROM (VALUES (1), (下次一定)) AS t(id);解决办法要么统一换成字符串要么第一行使用CAST把类型锁成TEXT。根据我的实际经验在VALUES构造临时表时凡是第一行用了强类型转换的后面基本不会再出幺蛾子凡是偷懒不转的早晚会被类型问题绊倒。4.3 动态 SQL 中拼接 VALUES 列表我做过一个权限配置工具前端把一批用户ID 角色编码组合传给后端后端要把这批数据写进关系表。最顺的方案不是一条条插入而是直接用程序拼接出VALUES列表import psycopg2 data [(101, editor), (102, viewer), (103, admin)] values_sql , .join( cur.mogrify((%s, %s), item).decode(utf-8) for item in data ) sql f INSERT INTO user_roles (user_id, role_code) VALUES {values_sql} ON CONFLICT (user_id, role_code) DO NOTHING cur.execute(sql)这里用mogrify做参数安全化的目的只有一个防止 SQL 注入。拼 SQL 时最忌讳直接把用户输入拼进字符串那样既危险又容易出语法错误。而用参数化之后再拼接既保留了VALUES批量插入的高效又保证了安全性。这个方法我在多个项目里用过数据量在几千行以内时性能都能接受。4.4 与 generate_series 配合生成连续日期VALUES()和生成函数可以混用。比如你要生成一份从某个开始日期到结束日期、每个日期对齐一个固定标签的报表SELECT generate_series(2024-01-01::DATE, 2024-01-07::DATE, 1 day) AS date, v.remark FROM ( VALUES (活动周), (限时折扣) ) AS v(remark);这样能生成两组 7 行数据一组备注是活动周另一组是限时折扣。生成连续日期和写死标签的组合在测试和报表补数时用得很多。本质上这是笛卡尔积思路的延伸跟 2.3 里的矩阵生成是一致的但合并了序列函数之后更灵活。4.5VALUES在 FROM 中的别名要求凡是把VALUES放在FROM子句里当派生表PostgreSQL 强制要求给整个虚拟表一个别名。这跟SELECT里的子查询必须加别名是同一个规则-- 必须的写法 SELECT * FROM (VALUES (1, a)) AS t(id, name); -- 少个别名就报错 SELECT * FROM (VALUES (1, a)) AS t; -- 列名还是 column1如果你忘了别名PostgreSQL 会直接报syntax error at or near VALUES。这种报错经常把新手搞蒙因为明明VALUES第一行语法没问题。解决办法就一句话给虚拟表起别名顺便把列名一起定义了。5. 踩坑记录类型、别名、括号与 MySQL 迁移误区这儿写几个我在实际使用中真正遇到过的坑不光是语法层面的还有思维惯性层面的。每个都记录解决过程你也可以跳到你卡住的那一条看。5.1 括号不配对报错却指向 VALUESVALUES的语法里每个数据行都是圆括号包裹。行数一多很容易漏括号或多加一个逗号。有个常见的报错是syntax error at or near ,看起来毫无头绪。我的排查方法是拿一个支持括号配对的编辑器把光标放在第一个括号里按高亮顺着检查最后一对括号是否闭合。另一个习惯是写完VALUES列表后从最后一行往前读——这能快速发现是不是最后一行多了个逗号。5.2 在 FROM 子句里不能直接 ORDER BY / LIMIT 吗单独执行VALUES (1),(2),(3) ORDER BY 1是允许的但把它作为派生表放进 FROM 子句时你要排序得在外面包一层SELECTSELECT * FROM ( VALUES (3), (1), (2) ) AS t(n) ORDER BY n DESC;如果你试图在VALUES内部直接加ORDER BY再放进 FROM 里语法上会出问题。原因是 PostgreSQL 对子查询的排序不做保证更合理的做法是外套一层查询。这个坑说大不大但是一旦忘了外层ORDER BY返回顺序很有可能不是你以为的顺序。5.3 从 MySQL 迁过来的典型语法平移误区MySQL 单独执行VALUES (1, 2);在旧版本里直接报错。很多从 MySQL 迁到 PostgreSQL 的人第一次知道VALUES能独立返回结果集时都很惊喜但随后会在两个地方翻车。第一列别名写法不同。MySQL 子查询里你写AS t(id, name)也能过但 PostgreSQL 会严格检查列别名的数量是否跟VALUES的列数一致。少一个都不行。-- 报错VALUES 列表有 2 列但列别名只提供了 1 个 SELECT * FROM (VALUES (1, a), (2, b)) AS t(id);第二对INSERT ... VALUES里日期字符串的处理差异。MySQL 会很宽松地接受2024-01-01写入 DATE 列PostgreSQL 在大部分情况下也能自动转型但如果你的字符串格式稍微暧昧比如01/02/2024PG 会直接报错而 MySQL 可能按字符串原样写入。所以我一直建议写INSERT ... VALUES时日期字符串最好显式加::DATE或CAST省得折腾参数格式。5.4 NULL 值的类型推断一个很容易出的问题列表里第一行写了NULL后续写了字符串。PostgreSQL 会怎么推断它会根据第一行的字面值把整列推断成text因为 NULL 是伪类型没有携带类型信息。反过来如果第一行是数字后面某行写了 NULL那整列是数字类型NULL 没问题。但如果你整列全是 NULL那这一列会被推断成 text 类型。假如后续要跟别的表做 JOIN类型对不上就报错了。解决办法依然是在第一行显式声明类型SELECT * FROM ( VALUES (NULL::BIGINT, 缺省), (1001::BIGINT, 正常) ) AS t(uid, note);5.5 不要轻易拿 VALUES() 替代所有临时表场景最后说一个思路层面的提醒。VALUES()确实方便但它终究活在查询语句里没法创建索引、没法被多个会话共享、数据量大时也没有优化空间。如果遇到如下场景还是老老实实建临时表更靠谱场景用 VALUES()用 CREATE TEMP TABLE查询内一次性映射适合没必要多次 JOIN 同一数据集可以用 CTE VALUES也可以数据量超过几千行不推荐推荐数据需要复用给多个查询/会话不行推荐需要在数据集上建索引不行推荐需要先清洗再反复调试不推荐推荐从实践角度看VALUES()是轻量临时数据集的最优解但它替代不了重量级临时表。做好选择的关键是判断数据集的生命周期一句话之内用还是整个会话用。前者交给我这篇文章介绍的方式后者交给临时表答案就清晰了。6. 实战参考一条 SQL 搞定多表关联映射分享一个我实际做过的需求把前面提到的技术点串起来。当时要生成一份经营日报报表里需要把省份 渠道翻译成区域负责团队而映射关系比较特殊不是一张现成的表是运营临时发给我的一份 20 行的群聊文本记录。建表维护显得重用CASE WHEN又太蠢。我当时直接把映射关系写成了VALUES段。WITH team_map AS ( SELECT * FROM ( VALUES (上海, 小程序, 华东一队), (浙江, APP, 华东一队), (江苏, 网页, 华东二队), (广东, 小程序, 华南一队), (广东, APP, 华南一队), (四川, 门店, 西南一队) ) AS t(province, channel, team) ) SELECT r.report_date, r.province, r.channel, r.gmv, m.team FROM daily_report r LEFT JOIN team_map m ON r.province m.province AND r.channel m.channel;这段 SQL 跑出来直接就是最终报表运营后面更新映射关系时我只需改VALUES里的前几行连表结构都不用动。整个过程从拿到文本到出数大概只花了十几分钟比临时建表、导数据、关联查询那套流程节约了大量时间。这算是我实际项目里最满意的VALUES()落地场景。它给我最大的启示是PostgreSQL 的表不一定非要物理存在VALUES()让数据跟着查询走成为了可能。把这份思路用到自己的日常工作中你会发现之前很多需要建表的数据其实都能用这种轻快的方式解决。