用VALUES()代替临时表：PostgreSQL轻量数据集的实战指南

最近帮业务团队排查一个取数慢的问题看到SQL里套了三层子查询目的只是为了把一组状态码翻译成中文名称。当时我第一反应是为什么不用PostgreSQL的VALUES()直接铺一张临时数据表这种场景实在太常见了——数据对账、批量更新状态、配置项映射、测试造数很多时候我们只是需要一小撮常量数据参与查询建一张真正的物理临时表又显得小题大做。而PostgreSQL的VALUES()恰恰就是这个问题的标准解法它能在一条SQL里临时构造出多行多列的数据行集用法上和临时表几乎一样却不需要走建表、灌数、清理这一整套流程。这篇文章我就围绕VALUES()做一次深入拆解从基本语法一直讲到和临时表的组合玩法、业务实战姿势以及我这些年踩过的类型推断、执行计划相关的坑。全部都是实测过的用法直接可以拿去用。1. 先纠正一个概念VALUES()不是函数而是一个独立的行集表达式1.1 我们最常见的VALUES()长什么样多数人认识VALUES()是从INSERT开始的INSERT INTO users (name, age) VALUES (张三, 25), (李四, 30);这里VALUES()被当作INSERT的子句使用负责提供多行插入数据。因为这个先入为主的印象当有人提到「用VALUES()生成临时表」时第一反应往往是不就是INSERT之后的数据吗但很多人在PostgreSQL里待了一两年从SELECT到窗口函数都用过却唯独忽略了VALUES()本身就具备独立作为「数据源」的能力。很多人不知道的是VALUES()在PostgreSQL里是一个完整的SQL表达式可以脱离INSERT单独出现在FROM子句中甚至可以直接作为顶层查询语句执行VALUES (1, one), (2, two), (3, three);这一条语句就能返回三行两列的数据集。列名默认是column1、column2外观上和一张表没有任何区别。也正因为它能独立产出结果行集许多官方文档里它被归为「Data Manipulation」一类的独立语法而不是INSERT专属的工具。1.2 为什么说VALUES()和临时表是「形似而神不同」标题里说的是「生成数据临时表」但严谨地说VALUES()并不会真的去数据库物理层面创建一张表。它生成的只是一段逻辑行集存在于查询执行期间查询结束就释放作用域仅限于当前语句。真正符合「临时表」语义的是CREATE TEMP TABLE或者带WITH的CTE快照。这个区别带来的直接影响是VALUES()不需要创建、删除、权限管理完全没有生命周期概念VALUES()不能加索引不能被其他会话引用VALUES()的数据完全内联在SQL文本里可读性好适合少量固定数据临时表适合数据量大、需要多次引用、甚至跨语句使用的场景所以我的结论很明确如果你的数据量在几十行以内、且只在一条SQL里用VALUES()是最优解如果数据量大、要反复用、受统计信息影响那就规规矩矩建临时表。这两个工具不是替代关系而是互补关系。2. 语法细节全拆解行数、列数、类型推断必须要懂的三条铁律2.1 基本语法括号组行逗号分行VALUES()的核心语法非常简洁VALUES (表达式1, 表达式2, ...), (表达式1, 表达式2, ...), ...;每个圆括号代表一行括号内的表达式按位置对应一列。多条记录用逗号分隔。它支持常量VALUES (1, a), (2, b)表达式VALUES (current_date, current_timestamp), (date 2024-01-01, now())子查询结果VALUES ((SELECT max(id) FROM t1), 1) —— 不过这种写法很少用别扭排序与分页VALUES (1), (3), (2) ORDER BY 1 DESC LIMIT 1;这里有一个小知识点VALUES()本身支持ORDER BY、LIMIT、OFFSET它本质上就等同于一个子查询行集凡是能在FROM后面出现的地方它基本都能参与。2.2 列类型推断PostgreSQL是怎么猜类型的这是最容易踩坑的地方。VALUES()列表中的所有行、同一位置的列PostgreSQL会按照UNION类型决议规则来推断最终列类型。简而言之几条规则所有行同一列都是整数则列类型为integer整数和浮点混用则提升为numeric或double precision有字符串常量参与时PostgreSQL会尝试把字符串隐式转换为数值类型前提是字符串内容能被转换全部都是无类型字符串字面量时默认推断为text全部都是NULL时通常推断为text类型但这个行为比较隐蔽具体看例子-- 第2列全是integer类型就是integer VALUES (1, 100), (2, 200); -- 第2列integer numeric提升为numeric VALUES (1, 100.5), (2, 200); -- 第2列100会被当整数因为同列存在整数常量 VALUES (1, 100), (2, 200); -- 第2列abc无法转数字整体变成text VALUES (1, abc), (2, def); -- 单行单列只有NULL时默认text VALUES (NULL);这个机制对纯数字字符串来说很友好但一旦你期望某列是特定类型、而PostgreSQL推断成了其他类型就会在后续JOIN或运算中报错。比如SELECT * FROM (VALUES (1), (2)) AS t(id); -- 这里id被推断为text如果拿去和integer字段做等值连接PG不一定报错 -- 但索引利用率就堪忧了。遇到这种情况我绝对不建议依赖隐式转换直接在VALUES里显式标注类型最稳妥SELECT * FROM (VALUES (1::int), (2::int)) AS t(id); -- 或者 SELECT * FROM (VALUES (1), (2)) AS t(id);类型推断的坑归根到底一句话VALUES里混合了数字和字符串时多花五秒钟把类型写清楚能省掉后面排查执行计划的一小时。2.3 别把VALUES()当函数用它没有括号参数从符合叫法的角度说PostgreSQL官方把VALUES()称为VALUES命令或者VALUES列表不是函数。虽然我们在文章里为了方便都会写成VALUES()但它的语义是「行值列表」不是「传参返回结果」。搞清楚这一点你就不会试图在VALUES里写自定义函数也不会奇怪为什么它不能直接出现在SELECT的投影列里。正确的使用姿势是把它放在FROM后面配上一个表别名和列别名就像对待一张普通表SELECT v.* FROM (VALUES (1, one), (2, two)) AS v(id, name);3. 和临时表深度配合的四种实战玩法3.1 CTE包装一条SQL内的「匿名临时表」这是我最常用的方式。数据量小、免建表直接用WITH把VALUES包起来赋予它清晰的表名和列名WITH tmp_config(key, val) AS ( VALUES (enable_log, true), (cache_ttl, 300), (max_retry, 5) ) SELECT c.key, c.val FROM tmp_config c LEFT JOIN system_config sc ON sc.config_key c.key WHERE sc.config_key IS NULL;这段SQL的作用是给定一组期望存在的配置项找到系统配置表里还缺失的项。如果不用VALUES你需要先建一张临时表、插入三行数据、再查询、再清理四条语句起步。用CTE包装后一条搞定可读性反而更好。CTE包装VALUES的另一个好处是可以参与递归查询WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n 1 FROM t WHERE n 10 ) SELECT n FROM t;这里第一行VALUES(1)就是递归的起点。虽然这种写法被generate_series替代了但在某些需要自定义递归初始集的场景里VALUESCET依然是利器。3.2 CREATE TEMP TABLE AS把VALUES固化下来如果需要跨多条语句引用同一组常量数据那就别硬塞一条SQL里了。PostgreSQL提供了很顺手的写法CREATE TEMP TABLE tmp_status_map ON COMMIT DROP AS VALUES (1, 待处理), (2, 处理中), (3, 已完成);这里有三点要提醒TEMP表只在当前会话可见会话结束自动消失不需要手动DROPON COMMIT DROP表示在事务提交后自动删除适合在某个事务里处理数据时用没有指定列名时列名默认是column1、column2正式用之前记得用列别名重新指定如果你希望这个临时表被后续多处SQL引用还可以给它加上主键或索引这一点是VALUES()本身做不到的。CREATE TEMP TABLE tmp_status_map AS SELECT v.* FROM (VALUES (1, 待处理), (2, 处理中)) AS v(id, name); ALTER TABLE tmp_status_map ADD PRIMARY KEY (id);不过说实话真正需要加索引的场景数据量少说也有几千行那时候VALUES就有点力不从心了建议直接从业务表或者外部文件导入没必要手工写常量。3.3 INSERT INTO VALUES批量灌入初始化数据VALUES()最自然的使用场景仍然是批量插入。它比逐条INSERT高效得多也远比拼多个单条INSERT的SQL清晰INSERT INTO dict_item (dict_type, item_code, item_name, sort_no) VALUES (audit_status, 1, 待审核, 10), (audit_status, 2, 审核通过, 20), (audit_status, 3, 审核拒绝, 30);这类语句在初始化配置表、填充枚举字典、植入测试数据时非常普适。可能有人会问这和ON CONFLICT结合起来是不是就是UPSERT是的这个进阶姿势我在第4节专门展开。注意插入大量数据时一次抱个几百行没问题但如果一次要插上万行建议还是用COPY或者拆成多批。VALUES列表过大时SQL文本本身就很大解析成本不可忽略。3.4 配合UPDATE做批量规则矫正这是VALUES()一个低调但极其好用的高级用法。很多时候我们需要根据一组映射关系去批量更新表里的数据比如按ID批量更新状态、按旧编码批量替换新编码。传统做法是建临时表再JOIN UPDATE实际上VALUES()完全顶得住UPDATE orders o SET status v.new_status FROM (VALUES (1001, SHIPPED), (1002, DELIVERED), (1003, CANCELLED) ) AS v(order_id, new_status) WHERE o.id v.order_id;这条语句能做到一次会话更新多行不同的值而不是一条一条UPDATE不创建任何物理表事务内即时生效逻辑清晰谁能改成什么一眼看到底在开发自测、运维临时修数据时这种写法能省一半时间。不必担心VALUES列表大小几十个映射完全不叫事。4. VALUES()在业务查询里的三个高性价比姿势4.1 白名单过滤用一条SQL替代动态拼IN业务中经常遇到「只查这几个ID」的需求。简单的做法是拼INSELECT * FROM users WHERE id IN (101, 102, 103, 105);但如果ID列表是程序动态生成的直接在SQL里用VALUES拼接反而更好维护SELECT u.* FROM users u JOIN (VALUES (101), (102), (103), (105)) AS v(id) ON v.id u.id;等一下有人会问这跟IN有什么本质区别区别在于VALUES方式可以和业务表做真正的JOIN这意味着可以随时把v当一张表去关联其他表扩展性更强可以把白名单和业务表之间的匹配结果直接用于日志输出、计数统计执行计划层面VALUES行集通常会被哈希化数据量小时效率很高如果你需要维护一个动态传入的长列表更推荐 ANY(ARRAY[...])这种写法参数绑定比拼接VALUES更安全。VALUES方案适合列表本身相对固定、写在SQL里没那么长的场景。4.2 码值映射替代DECODE CASE的「对照表」拿状态码翻译来说我见过很多人写CASE WHENSELECT order_id, CASE status_code WHEN 1 THEN 待支付 WHEN 2 THEN 已支付 WHEN 3 THEN 已发货 ELSE 未知 END AS status_name FROM orders;问题在于当状态码有二十个、而且要参与过滤和排序时CASE表达式不仅冗长还没法复用。用VALUES搭一张映射表会更优雅WITH status_map(code, name) AS ( VALUES (1, 待支付), (2, 已支付), (3, 已发货), (4, 已签收), (5, 已取消) ) SELECT o.order_id, COALESCE(m.name, 未知) AS status_name FROM orders o LEFT JOIN status_map m ON m.code o.status_code;这种写法的好处是映射关系独立成块增删状态只需要改VALUES列表业务SQL保持清爽。而且LEFT JOIN天然能处理未匹配的情况比CASE的ELSE分支更灵活。有些朋友会拿维表、字典表来抬杠说生产环境数据应该放到配置表里而不是写在代码里。我的态度是区分场景。十来个固定状态码写在SQL里一次查询用完就完何必多一次表关联如果是跨系统共享的字典当然应该进配置表。工具没有高低之分用法合适比用哪个更重要。4.3 UPSERT批量写入ON CONFLICT VALUES经典组合PostgreSQL的INSERT ON CONFLICT DO UPDATE会把新插入的数据通过excluded行引用出来配合VALUES批量写入就是一条标准的升级版UPSERTINSERT INTO product_price (product_id, price, updated_at) VALUES (P1001, 99.0, now()), (P1002, 199.0, now()), (P1003, 299.0, now()) ON CONFLICT (product_id) DO UPDATE SET price EXCLUDED.price, updated_at EXCLUDED.updated_at;这里值的来源是VALUES()目标表通过product_id唯一约束判断冲突。如果记录已存在就更新价格不存在就直接插入。一条语句完成增量更新并发安全性能也足够好。我在生产里用这个模式做过价格同步任务每小时跑一次单次几千条数据配合预编译语句整体耗时从原来的几分钟降到几十秒。要注意的是ON CONFLICT的冲突目标必须匹配唯一约束如果表上没有对应唯一索引会直接报错「no unique or exclusion constraint matching」。5. 踩坑实录类型报错、执行计划失控与隐藏限制5.1 最常见的报错列数不一致、类型不一致、无类型常量先写三个我在实际使用中遇到频率最高的错误全都有明确报错信息ERROR: VALUES lists must all be the same length这个最简单就是某行少写或多写了值。比如VALUES (1, a), (2)第二行只有一列。排查时数清楚括号内逗号数量就行。ERROR: column x is of type integer but expression is of type text典型场景VALUES里第一行写了(1, 10)第二行写了(2, 20::int)PostgreSQL推断时发现同列类型不一致无法统一。解决办法是统一显式CAST或者全部用字面量让PG自己推断。ERROR: could not determine data type of parameter $1这个出现在使用预编译语句时VALUES里有参数占位符但PostgreSQL无法从上下文推断参数类型。比如SELECT * FROM (VALUES ($1)) AS t(id)没有其他信息可推断就会报错。解决办法是显式指定类型VALUES ($1::int)。第二个坑我印象最深。同事们常写这样的语句SELECT * FROM (VALUES (100, 200), (300, 400)) AS t(price, qty)然后拿去跟numeric字段运算结果要么隐式转换出错要么精度丢失。VALUES里全是无类型字符串时PG默认按text处理不会自动证明「这些都是数字」。你需要手动标记SELECT * FROM (VALUES (100::numeric, 200::numeric), (300, 400)) AS t(price, qty)这里只有第一行标记了类型PostgreSQL会用UNION规则把第二行也推断成numeric所以后续行可以不写CAST。5.2 执行计划的隐藏问题VALUES数据源没有统计信息很多实时查询用VALUES时很顺畅但一旦VALUES参与复杂JOIN问题就来了。PostgreSQL对VALUES行集的行数估算通常是准确的因为它能数出多少行但行集本身没有统计信息——没有直方图、没有最常见值列表、没有相关性和选择率样本。这意味着当VALUES行集和一个大表做JOIN时优化器只能靠默认的选择率来估算连接结果大小。一旦估算偏差过大就可能选错连接策略。比如本来走Hash Join更快优化器以为返回行数极少选了Nested Loop结果执行效率骤降。我实际遇到的一个例子SELECT t.*, v.tag FROM big_table t JOIN (VALUES (1001, A), (1002, B), (1003, C)) AS v(id, tag) ON v.id t.id WHERE t.created_at 2024-01-01;big_table有几百万行VALUES只有三行。理论上join之后最多三行Nested Loop反而是最优的所以这里没问题。但如果VALUES列表有几百上千行而且过滤条件很少估算偏差就开始显现。遇到这类情况的手段有三个给VALUES查询加OFFSET 0把它物化为子查询这个语法在PG里其实会阻断优化器的下推并没有真正物化改为临时表加VACUUM ANALYZE或手动ANALYZE让统计信息完整用MATERIALIZEDCTE强制物化VALUES比如WITH t AS MATERIALIZED (VALUES ...)从PG 12开始CTE支持MATERIALIZED关键字可以强制物化中间结果集这样优化器就能把它当作一张独立的小表处理。实测中这种手段往往能恢复优化器的判断力。5.3 参数上限与实际列表规模别拿VALUES当无限数据集PostgreSQL本身并没有限制VALUES列表中行数的硬性上限几百行、几千行也能处理。网上流传的「最多1000行」其实是Oracle的限制不少DBA把Oracle的习惯带过来了这点要澄清一下。真正需要注意的是两点当VALUES列表行数过大时解析器和规划器需要处理大量常量节点SQL文本越长硬解析的耗时越高如果VALUES用于预编译语句每个值都可能是独立绑定参数。PostgreSQL协议对绑定参数的数量有限制通常是65535个。行数不多但列数很多时也可能触及这个限制所以我的经验阈值是单条VALUES列表控制在100行以内是舒适区1000行以上就该考虑COPY或INSERT批量装载了。造大测试数据集时别用VALUES硬编码用generate_series高效得多SELECT i, md5(i::text) FROM generate_series(1, 100000) AS i;5.4 列名默认逻辑不指定别名时查询会很难受VALUES构造出的结果默认列名是column1、column2。如果直接用SELECT * FROM (VALUES (1, a))你得到的列名就是column1、column2。虽然能用ORDER BY 1这种位置引用但一旦参与嵌套查询没名字的列会让SQL彻底变成天书。我在所有SQL里都把VALUES派生表当作正规表来对待必写别名SELECT * FROM (VALUES (1, a), (2, b)) AS demo(id, name);这句话看起来简单但对代码可读性的提升是决定性的。包括我自己写CTE也一样绝不留着column1这种默认名去跟业务字段做映射。6. 实测过程中沉淀的几个小习惯6.1 想要保持顺序就加WITH ORDINALITY有朋友问VALUES列表的行序是固定的为什么查出来顺序可能会变因为如果后面跟着ORDER BY或者优化器重排了执行计划行序就不保证。如果业务确实需要识别「这是第几行」PostgreSQL提供了干净的做法SELECT * FROM (VALUES (a, 1), (b, 2), (c, 3)) AS t(name, score) WITH ORDINALITY;这会额外加一列ordinality值为1、2、3按输入顺序排列。ETL场景、批量映射场景里这个功能非常实用。6.2 EXPLAIN ANALYZE验证执行计划别光看行数用VALUES做JOIN时我强烈建议执行一下EXPLAIN (ANALYZE, BUFFERS)去看实际计划。之前有个案例VALUES只有十几行和一张几百万行的表JOIN优化器不知道为什么把Hash Join换成了Nested Loop单次查询从3秒变成了40秒。最后通过EXPLAIN发现是选择率估算问题把VALUES接到CTE并强制物化后才恢复正常。EXPLAIN (ANALYZE, BUFFERS) WITH input(id) AS MATERIALIZED ( VALUES (101), (102), (103) ) SELECT u.* FROM users u JOIN input ON input.id u.id;这类排查思路值得固化不要假设PG对小表JOIN一定走最优路径看到实际计划和预估行数的差距再决定怎么改SQL。6.3 动态构造VALUES列表时使用格式化函数防止注入如果在程序代码里动态拼接VALUES列表一定要用参数化绑定或PostgreSQL的格式化函数否则和拼字符串构造普通SQL一样存在注入风险。推荐的做法是把整个VALUES列表当作预编译语句的参数传入或者用format()构造占位符。举个例子在Python的psycopg2里更安全的写法是rows [(1, a), (2, b)] # 使用execute_values而不是手撕字符串拼接其实这不是VALUES自身的问题而是所有动态SQL的通用纪律。但因为我见过不止一个同事栽在VALUES列表拼接上这里多提醒一句。6.4 别把VALUES和临时表对立起来按数据量定方案最后这算是我个人沉淀下来的选型经验整理成一张小表方便日常快速决策场景推荐方案理由单条SQL内的固定映射、白名单VALUES CTE免建表可读性好跨多条SQL需要重复引用同一批数据CREATE TEMP TABLE可加索引复用成本低批量插入现有表INSERT INTO ... VALUES直观高效批量提交大批量测试数据generate_series / COPY速度快避免SQL文本过长复杂JOIN且优化器判断异常CTE MATERIALIZED或临时表兜住统计信息缺失的问题这些年用下来我最大的体会是VALUES()做不了的事情别硬拿它去做它能做好的事情往往比建临时表省一半事。灵活使用PostgreSQL查询效率才能对得起它号称最先进关系型数据库的名头。