做数据库开发的人十有八九都干过这种事临时要查一批数据为了几个简单行先CREATE TEMP TABLE再INSERT再SELECT最后还要记得清理一套流程下来原本五分钟能做完的事硬生生耗了二十分钟。PostgreSQL 里其实有一个非常轻量的解决办法就是用VALUES()列表直接构造一张“临时表”。它不需要建表、不需要清理一条 SQL 就能把散乱的数据变成一张可查询、可 JOIN、可参与更新的表。这篇内容围绕VALUES()在 PostgreSQL 中的使用展开包含三种标准写法、六个可复用的实战场景以及几个我踩过的类型推断和性能坑。无论你是后端开发、数据分析师还是正在从 MySQL 转向 PostgreSQL 的朋友这篇都适合。1. VALUES() 函数是什么为什么说它能生成临时表1.1 一条 VALUES 就是一张只读表PostgreSQL 的VALUES本质上就是“行构造器列表”标准语法是VALUES (1, 张三), (2, 李四);单独执行这句话PostgreSQL 会返回一个结果集默认列名叫column1、column2。也就是说你已经得到了一张只有两列、两行的“表”。如果想把这张表拿到查询里用最常见的写法是SELECT * FROM (VALUES (1, 张三), (2, 李四)) AS v(id, name);这里的关键是括号包住VALUES列表作为一个子查询表AS v(id, name)同时指定了表别名和列别名查询里就可以用v.id、v.name来引用这些数据。很多人会把VALUES当成“INSERT 语句里的一个子句”其实它是 SQL 标准里独立存在的语法。PostgreSQL 把这一点贯彻得很彻底任何能写表的地方理论上都可以放一个VALUES列表。当然实际使用时要遵守语法限制不是所有位置都允许直接贴VALUES但通过FROM (VALUES ...) AS t(...)这种包装它就能扮演临时表的角色。这种做法的价值在于当手头只有几条数据不想到数据库里建正式表、不想污染业务库又确实需要把它们当成表来查询、连接、比较时VALUES是最快的一条路。1.2 同样叫 VALUESPostgreSQL 和 MySQL 差别不小很多从 MySQL 过来的人会对 PostgreSQL 的VALUES感到陌生。MySQL 8.0 开始也支持类似标准 SQL 的VALUES语句但写法上多了一个ROW关键字-- MySQL 8.0 SELECT * FROM (VALUES ROW(1, 张三), ROW(2, 李四)) AS t(id, name); -- PostgreSQL SELECT * FROM (VALUES (1, 张三), (2, 李四)) AS t(id, name);另一件容易混淆的事是 MySQL 老版本在INSERT ... ON DUPLICATE KEY UPDATE里大量使用的VALUES()函数INSERT INTO t (id, c) VALUES (1, 100) ON DUPLICATE KEY UPDATE c VALUES(c);这个VALUES(c)在 MySQL 里表示“引用本次插入的 c 字段值”而不是构造表。MySQL 8.0.20 开始已经不建议用这种写法了。PostgreSQL 里对应功能用的是EXCLUDED伪表INSERT INTO t (id, c) VALUES (1, 100) ON CONFLICT (id) DO UPDATE SET c EXCLUDED.c;所以“同样叫 VALUES干的不是同一件事”这在迁移 SQL 时是个很常见的坑。认清这一点之后再看 PostgreSQL 的VALUES列表思路就顺了。1.3 为什么这个功能被大多数人放进了抽屉我观察到的现象是越是有经验的 DBA越习惯“数据先落表再查询”一旦要临时弄几个参数、几个映射关系第一反应就是建临时表。这样没错但对于偶发性的排查工作成本偏高。VALUES之所以被低估主要原因是它看起来太“基础”了。很多人只在INSERT里见过INSERT INTO t (a, b) VALUES (1, 2), (3, 4);一旦遇到“我要把几个 id 拿来查它的业务数据”反而会绕一大圈去建表。实际上这两件事是同源的VALUES列表本身就能当表用。把这个意识建立起来很多临时数据问题都可以在一屏 SQL 内解决不用创建对象、不用清理对象、不用等事务提交体验非常爽。2. 用 VALUES() 生成临时表的三种标准姿势2.1 最灵活的方案CTE 公共表表达式如果这批数据只需要在当前这一条 SQL 里使用我推荐用 CTE 包一层。比如我要模拟一份购物清单然后做筛选WITH wish_list(id, name, price) AS ( VALUES (1, 机械键盘, 899), (2, 显示器, 2499), (3, 升降桌, 1599) ) SELECT * FROM wish_list WHERE price 1000;这里WITH wish_list(id, name, price)中的三列并不是强制指定类型只是给VALUES结果取了列名。PostgreSQL 会根据值自己推断类型899是 integer2499是 integer1599是 integer所以price列会被识别成 integer就可以直接做price 1000这种过滤。CTE 的好处是可以定义多个临时块后面的块引用前面的块可以被SELECT、UPDATE、DELETE引用不产生物理表也不残留任何数据。适合“一次性查询脚本”和“临时数据分析”。如果一份数据要在多个不同 SQL 之间反复用CTE 就不够了请看下一种。2.2 真正落地CREATE TEMP TABLE 批量 INSERT如果数据量稍大或者同一批数据要在多个查询里反复引用建议直接建真正的临时表。临时表只在当前会话有效会话结束自动清理不会污染业务库。最稳的写法是先建表、后插数据这样类型完全可控BEGIN; CREATE TEMP TABLE tmp_user_map ( old_id int, new_id int ) ON COMMIT DROP; INSERT INTO tmp_user_map(old_id, new_id) VALUES (101, 1001), (102, 1002), (103, 1003); -- 在这里同一事务里的其他 SQL 都可以引用 tmp_user_map COMMIT;ON COMMIT DROP表示事务提交时自动删除这张临时表。这个选项非常适合在自动化脚本、存储过程或者迁移程序里使用它保证用完即焚不残留。这里有一个非常容易踩的坑如果你不在事务里直接执行CREATE TEMP TABLE t(x int) ON COMMIT DROP; INSERT INTO t VALUES (1);第二句大概率会报错因为CREATE TEMP TABLE自己就构成一个隐式事务命令结束即提交ON COMMIT DROP已经触发表没了。所以在 psql 里单独测试时要么先把BEGIN;打出来要么干脆不用ON COMMIT DROP。如果不需要自定义类型也可以快速一点CREATE TEMP TABLE tmp_user_map_quick AS SELECT * FROM (VALUES (101, 1001), (102, 1002) ) AS v(old_id, new_id);这种写法省了一步INSERT列名也清晰但类型完全靠VALUES自动推断。如果列里有 NULL、字符串数字混排等情况建议还是先CREATE TEMP TABLE再INSERT免得后续各种类型问题。2.3 轻量内联FROM (VALUES ...) AS v(...) 直接当表第三种姿势最轻量适用场景也最广直接在FROM子句里包一个VALUES子查询。SELECT v.id, v.name, v.email FROM (VALUES (1, 张三, zhangsanexample.com), (2, 李四, lisiexample.com) ) AS v(id, name, email) WHERE v.id IN (1, 2);这种写法尤其适合充当“驱动表”或“过滤表”。比如你有一个很长的业务查询需要把结果限制在某几个指定组合上就可以用VALUES构造一个过滤表再和主查询做JOIN或EXISTS。这里要注意两点表别名必须写否则 PostgreSQL 会报语法错误列别名建议显式给否则只能用默认的column1、column2去引用可读性很差。三种方式的取舍我整理成了表格方式生命周期适用场景CTE当前 SQL 内一次性的筛选、映射、比较临时表当前会话或当前事务多个 SQL 反复引用数据量稍大FROM 内联 VALUES当前查询子句中作为驱动表和过滤表随用随走3. 实战场景六个可以直接抄的 SQL3.1 映射表驱动 UPDATE告别一堆 CASE WHEN业务系统里经常遇到编码调整比如老编码A001要映射成新编码X001。如果不建映射表很多人会写一堆CASE WHEN或者一条一条UPDATE。前者让 SQL 膨胀后者来回访问数据库。用VALUES映射表驱动UPDATE是最顺手的方案WITH mapping(old_code, new_code) AS ( VALUES (A001, X001), (A002, X002), (A003, X003) ) UPDATE product p SET category_code m.new_code FROM mapping m WHERE p.category_code m.old_code;这里UPDATE ... FROM mapping m是 PostgreSQL 的独特写法mapping就是一张内联表。如果映射关系只有几十条这种方式非常直观一条 SQL 完成全部更新。我自己实际使用时会额外留意一个隐患如果mapping中old_code有重复UPDATE ... FROM会导致目标行被连接匹配多次最终更新成哪一个值具有不确定性。所以写批量更新前最好先对映射表做一次去重WITH mapping(old_code, new_code) AS ( SELECT DISTINCT ON (old_code) old_code, new_code FROM (VALUES (A001, X001), (A001, X099), (A002, X002) ) AS t(old_code, new_code) ORDER BY old_code, new_code DESC ) -- 后续 UPDATE 逻辑同上这样虽然多写了一点但至少结果可预期。3.2 多字段组合匹配用行构造器 IN在统计“某个部门 某个职级”的人群时很多写法是拼接字符串WHERE dept || - || job_level IN (...)。这种写法在索引、类型安全上都不太优雅。PostgreSQL 原生支持多字段组合匹配SELECT u.name, u.dept, u.job_level FROM users u WHERE (u.dept, u.job_level) IN ( SELECT d, l FROM (VALUES (dev, P5), (ops, P6) ) AS filter(d, l) );(u.dept, u.job_level)是行构造器PostgreSQL 会把整行当成一个比较单位用它去匹配VALUES里的每一行。这段 SQL 的含义就是只要用户的“部门 职级”组合等于(dev, P5)或(ops, P6)就命中。如果是单字段匹配更推荐数字数组的写法WHERE u.status ANY (ARRAY[active, pending]);只有在多字段组合、且组合列表需要频繁修改时VALUES方式才是最优解。因为它修改起来就是改几行文本维护成本很低。3.3 快速造测试数据核对报表口径我刚工作那会儿做报表核对最喜欢先建临时表再插数据。现在直接用VALUES构造“期望结果”再跟线上数据对比干净利落。比如核对两天的收入数据WITH expected(account_date, amount) AS ( VALUES (DATE 2025-06-01, 12345.67), (DATE 2025-06-02, 23456.78) ) SELECT e.account_date, e.amount AS expected_amount, r.amount AS real_amount, r.amount - e.amount AS diff FROM expected e LEFT JOIN real_report r USING (account_date);为什么这里写DATE 2025-06-01而不是直接写2025-06-01因为VALUES列表里的字符串在没有上下文时PostgreSQL 会把它推断成未知类型或文本。如果real_report.account_date是 date 类型USING (account_date)就可能遇到类型不匹配的问题。提前显式转成DATE能省掉后面一连串麻烦。这种“期望表 实际表”对比的思路非常通用。造 10 行、20 行期望数据成本极低不需要建表、不需要插数据写完就能跑。做数据核对的人值得把这个技巧焊在脑子里。3.4 与 INSERT 组合先处理后入库VALUES最常见的身份还是INSERT的数据来源。多行插入大家都懂INSERT INTO temp_result(id, name) VALUES (1, a), (2, b);但如果入库前需要做转换、去重、加时间戳直接INSERT ... VALUES就不够灵活了。更好的做法是把VALUES当来源表用INSERT INTO ... SELECT包一层INSERT INTO target(id, name, created_at) SELECT id, name, now() FROM (VALUES (1, a), (2, b) ) AS v(id, name) ON CONFLICT (id) DO UPDATE SET name EXCLUDED.name;这种写法有几点好处SELECT子句里可以做任意表达式计算比如now()、upper(name)、id 1000可以加WHERE过滤后再入库可以直接接ON CONFLICT实现“存在就更新”或“存在就跳过”。批量数据量在几十到几百行时这个方式写起来快、执行也稳。但如果到了几千上万行我会改用后面会说到的COPY或generate_series方案尽量别把巨大列表硬塞进 SQL 文本里。3.5 LATERAL 搭配 VALUES构造一对多测试行做接口联调、权限测试时经常需要给每个实体准备多个状态。比如每个门店要生成 10 天的测试日期或者每个订单要生成几个状态流转记录。这类“一对多”的测试数据用LATERAL配合VALUES非常优雅。先看一个最简版本SELECT s.store_id, days.dt FROM (VALUES (1, 华东店), (2, 华南店), (3, 华北店) ) AS s(store_id, store_name) CROSS JOIN LATERAL generate_series( DATE 2025-06-01, DATE 2025-06-10, INTERVAL 1 day ) AS days(dt);这里generate_series不是VALUES但它展示了LATERAL的思路右侧的集合函数可以引用左侧的行。如果想给每个用户生成一组固定的状态枚举可以这样SELECT u.user_id, status_list.status FROM (VALUES (1, zhangsan), (2, lisi) ) AS u(user_id, user_name) CROSS JOIN LATERAL ( VALUES (pending), (paid), (canceled) ) AS status_list(status);最终会得到每个用户 3 行状态数据。这个技巧在写接口 mock 数据和测试权限矩阵时非常实用不需要建任何正式表跑完就完。3.6 CSV 验证与应用层批量参数发送日常还会遇到一类需求别人甩给你一个 CSV让你“先看看数据质量”。如果数据量不大懒得配COPY可以先挑几行转成VALUES列表直接做类型转换测试SELECT col1::bigint AS user_id, col2::date AS signup_date, col3::numeric AS amount FROM (VALUES (100001, 2025-05-01, 199.50), (100002, 2025-05-02, 88.00) ) AS t(col1, col2, col3);这段 SQL 会在执行阶段强制把字符串转成目标类型。如果 CSV 里存在坏数据它会直接暴露出来方便提前排查比正式导入后再报错要省事很多。应用层开发也有类似需求后端从接口拿到一批 id要把这些 id 传给 SQL 做过滤。循环拼OR id ?很烂拼超大IN (?, ?, ?)又容易撞参数上限。一种折中方案是拼一个VALUES子查询SELECT id, name FROM users WHERE id IN ( SELECT id FROM (VALUES (1001), (1002), (1003) ) AS v(id) );在 Python 的psycopg2里甚至可以直接用execute_values这个工具函数它本质上是帮你拼好VALUES列表再发给 PostgreSQL。这套组合对于“批量发参数”的常见诉求非常友好能减少数据库往返又比动态拼一堆OR条件安全、整洁。4. 类型推断、排序限制与性能雷区4.1 类型推断顺序不同结果都可能不同PostgreSQL 对VALUES列表的每一列会做“公共类型推断”。这句话听起来简单实际踩坑不少。最常见的问题是同一列里混了不同类型。看这段 SQLSELECT * FROM (VALUES (1), (abc)) AS t(x);第一行值是1PostgreSQL 倾向于把这一列推断成 integer接着尝试把abc转成 integer结果直接报错。但如果不小心把顺序换一下SELECT * FROM (VALUES (abc), (1)) AS t(x);这一列会先看到文本于是被推断成 text数字1会被自动转成字符串1查询反而能跑通。同样的数据因为顺序不同结果类型完全不同。这在大型临时数据核对时是非常隐蔽的坑可能导致下游计算全部走错类型。另一个常见问题是NULL。NULL没有类型如果某列全是NULL或者只有个别非空值类型推断很容易出现意外。保险做法是给关键列显式转换SELECT x 1 AS next_x FROM ( VALUES (CAST(NULL AS integer)), (5), (10) ) AS t(x);如果你需要严格控制类型尤其要避免“顺序决定类型”这种隐式逻辑最可靠的还是先建TEMP TABLE并用CREATE TABLE声明好列类型再往里插数据。这样VALUES里的值会被自动转换遇到问题也会在插入阶段就报出来。4.2 列名、排序、行长度这些避坑细节除了类型还有几个小细节经常让人卡壳。首先是列名。如果不指定列别名结果列就叫column1、column2。你可以访问它们比如SELECT column1 FROM (VALUES (1), (2)) AS t;但代码可读性很差而且一旦查询写长后面维护的人根本不知道column1代表什么。所以我建议任何时候都显式指定列别名。第二是排序和分页。VALUES本身也可以带ORDER BY和LIMITVALUES (1, b), (2, a) ORDER BY 1 LIMIT 1;这里ORDER BY 1是按第一列排序。不过这只是VALUES独立语句的语法一旦把VALUES放进FROM子查询再想排序和分页最好再包一层SELECTSELECT * FROM ( VALUES (1, b), (2, a) ) AS v(id, name) ORDER BY id DESC LIMIT 1;第三是行长度必须一致。VALUES列表每一行的列数必须相同否则直接报ERROR: VALUES lists must all be the same length这类错误通常是因为复制粘贴时某行漏了一个值排查时从最后几行找最容易发现。第四是字符串转义。VALUES里如果出现单引号比如人名OBrien要写成两个单引号VALUES (1, OBrien);如果字符串里特殊符号很多可以用 PostgreSQL 的美元引用VALUES (1, $$a b c \ d$$);这在处理接口返回的长文本时非常省心。4.3 性能边界多少行以内适合用 VALUESVALUES不是银弹它也有性能边界。根据我的实际经验几十行随手写最舒服几百行依然没问题一两千行可以接受但 SQL 文本会变得很长上万行不要用解析成本、网络传输成本都上来了不如COPY或建临时表。为什么会有这个边界因为VALUES列表本质上是把数据写死在 SQL 文本里PostgreSQL 需要解析每一行、推断类型、生成目标列表。数据量一大解析和规划阶段的开销占比就明显了。另一个问题是优化器对VALUES子查询的行数估算不一定精准在复杂 JOIN 场景下可能选出次优执行计划。如果真的需要上万行测试数据我推荐用generate_seriesSELECT i, user_ || i AS name, md5(i::text) AS token FROM generate_series(1, 100000) AS i;这样可以快速生成百万行数据集开销远小于硬拼VALUES。如果是在复杂查询里必须使用一个中等规模的VALUES集又担心优化器估算不准可以强制 PostgreSQL 把 CTE 物化WITH tmp AS MATERIALIZED ( VALUES (1, a), (2, b) ) SELECT ...MATERIALIZED会把这个结果先实体化再参与后续 JOIN执行计划更可控。这个语法在 PostgreSQL 12 以后的版本可用。4.4 常见报错与排查速查表我把平时遇到频率最高的VALUES相关报错整理成了速查表症状可能原因处理方式VALUES lists must all be the same length某行少了或多了一列逐行检查每行列数是否一致invalid input syntax for type integer同一列混入数字和文本显式 CAST或统一成 textcolumn reference x is ambiguousVALUES子查询没给列别名给AS t(col1, col2)operator does not exist: integer text类型推断不一致使用显式类型转换ON COMMIT DROP 后表不存在没有包在显式事务中先BEGIN;再 CREATE INSERT优化器行数估算不准导致查询慢大VALUES列表参与复杂 JOIN改用临时表或MATERIALIZEDCTE这些坑基本都是我实际碰到过的。出现问题的第一反应不是怀疑 PostgreSQL而是先确认VALUES列表里每一列的类型和长度大概率能快速定位。5. MySQL 迁移和应用层适配时的几点提醒5.1 MySQL 迁移 PostgreSQLVALUES 语法要改这几年 MySQL 转 PostgreSQL 的团队越来越多。如果你也在做迁移遇到VALUES时至少要改三处第一标准VALUES行的写法。MySQL 需要ROW()PostgreSQL 不需要。-- MySQL INSERT INTO t(a, b) VALUES ROW(1, 2), ROW(3, 4); -- PostgreSQL INSERT INTO t(a, b) VALUES (1, 2), (3, 4);第二INSERT ... ON DUPLICATE KEY UPDATE里的VALUES()函数PostgreSQL 用EXCLUDED伪表。-- MySQL ON DUPLICATE KEY UPDATE c VALUES(c); -- PostgreSQL ON CONFLICT (id) DO UPDATE SET c EXCLUDED.c;第三MySQL 的VALUES子查询在部分版本/驱动里还不够通用PostgreSQL 则非常灵活地支持FROM (VALUES ...) AS t(...)。迁移时如果遇到“这里为什么不能用表别名”的问题优先查一查版本差异。这些差异不复杂但容易在批量迁移时集体踩雷。建议迁完先用一个几百行的VALUES用例测试再跑正式任务。5.2 Python 应用层如何用 VALUES 减少数据库往返应用层最常见的错误是循环执行单条INSERT。比如for item in data_list: cursor.execute(INSERT INTO t(id, name) VALUES (%s, %s), item)数据量稍大这就是灾难。改用VALUES多行插入后一次网络往返完成from psycopg2.extras import execute_values execute_values( cursor, INSERT INTO t(id, name) VALUES %s, [(1, a), (2, b), (3, c)] )execute_values会安全地帮你把参数拼成VALUES列表。如果用的是 SQLAlchemy则可以走 Core 的insert().values([...])或 ORM 的session.bulk_save_objects底层也会利用批处理减少往返。需要注意的是应用层生成VALUES列表时一定要让数据库驱动做参数绑定不要自己手工拼接字符串。一方面是为了防止 SQL 注入另一方面是让驱动正确处理特殊字符、NULL 和类型转换。结尾最后分享一点我自己的习惯。现在做临时数据排查时我的第一反应不是建表而是先想这条数据能不能用VALUES缩成一条 SQL比如要看某几个状态组合下的用户数、要核对几个口径值、要批量刷一批映射关系我都会先尝试用VALUES列表把问题压缩在一个查询里。速度快还不留垃圾表。还有个小技巧在 psql 里调试特别长的VALUES子查询时先只保留 10 行跑通逻辑确认类型、别名、JOIN 路径都没问题后再把完整数据贴进去。否则几千行的列表一出错你连从第几行开始排查都找不着北。这个习惯帮我省下了大量无用功你也可以试试。