1. 从一条UPDATE语句开始理解“赋值”在PG开发里的分量
做 PostgreSQL 开发这几年，我越来越觉得“赋值”才是整个数据库应用里最容易被低估的一个动作。很多人觉得赋值不就是“给变量塞个值”吗？SELECT 查出来，写个变量，再插回去，最多注意一下数据类型，能出什么事？
但真正把一个业务系统从 MySQL 迁到 PostgreSQL、或者从零用 PG 搭数据服务之后，你会发现赋值这件事在 PG 里藏着非常多的边界条件：PL/pgSQL 的赋值语法和普通 SQL 不完全一样，动态 SQL 里赋值可能返回空结果，JSONB 的局部赋值不能直接 UPDATE，复合类型、数组、行构造函数这些 PG 特有玩法，稍不留神就会把数据搞错。这篇文章我就围绕“pg数据库开发-赋值”这一个主题，把我在实际项目里踩过的坑、总结出的方法、以及值得直接抄作业的写法统统梳理一遍。
先说清楚这篇文章适合谁看：用 PostgreSQL 写存储过程、函数、触发器的开发者，用 Python/Java/Node.js 连接 PG 做 CRUD 的工程师，以及刚上手 PG 想系统搞懂赋值机制的初学者。文章不会讲太多高深理论，重点是可落地的代码片段和“为什么这么写”背后的逻辑。毕竟赋值表面上是语法问题，本质上却是数据流设计的核心环节——你在哪一步赋了值、赋的是新值还是引用、类型有没有被隐式转换、NULL 会不会悄悄混进来，这些细节决定了数据到了库里到底是什么样子。
1.1 赋值不只是“给变量塞个值”
如果你只用过 pgAdmin 敲几条 SELECT，或者只写过 INSERT 语句，可能感受不到赋值的复杂性。但一旦开始写函数，你会发现赋值贯穿了函数执行的每一个环节：参数传进来要赋给内部变量，查询结果要赋给记录变量，循环里每一行的字段要赋给临时变量，最终结果又要赋给返回值。这一步一步的“赋值链”，其实就是数据加工和流转的过程。
我举一个最简单的例子：想根据订单 ID 查出客户名称和订单金额，再根据订单金额计算折扣后金额，最终返回一段通知文案。这个需求不复杂，但在实现过程中至少会用到三种赋值方式：
- 普通变量赋值：
customer_name := 'unknown'; - SELECT 结果赋值：
SELECT customer_name INTO v_name FROM orders WHERE order_id = p_id; - 计算表达式赋值：
v_final := v_amount * 0.9;
这三种赋值的使用时机不同、错误处理方式也不同。很多人第一篇 PL/pgSQL 代码就栽在“SELECT INTO 查不到数据会怎样”这个问题上——默认会抛异常，除非你提前配置了异常处理或者用了条件赋值。这就是赋值背后真正的门道。
1.2 数据库开发里赋值的三层含义
以我个人的理解，PG 开发中的“赋值”至少应该拆成三个层级来看，因为它们对应的问题完全不一样。
第一层是 SQL 语句层面的赋值。典型场景就是UPDATE table SET column = value和INSERT INTO table(column) VALUES (value)。这一层的赋值发生在 SQL 引擎内部，不涉及编程语言中的变量，但它是所有数据变更的最终落点。你在应用代码里写的任何插入、更新，本质上都是在向数据库的字段赋值。
第二层是 PL/pgSQL 语言层面的赋值。这一层类似普通编程语言中的变量操作，有:=运算符、有SELECT INTO、有PERFORM、有FOR ... LOOP内的迭代赋值，还有动态 SQL 的EXECUTE ... INTO。这一层的赋值才是让函数逻辑灵活起来的关键，也是大多数 PG 开发新手容易困惑的地方。
第三层是客户端编程语言层面的赋值。无论你用 Python、Java 还是 Node.js，最终都要把应用内存中的值赋给 SQL 语句里的参数位，再把数据库返回的结果集赋给应用变量。这一层的核心问题是类型映射和参数绑定，处理不好会出现“应用层看着没问题，数据库里存了一堆错误格式”的诡异现象。
后面的内容就按照这三个层级来展开，每一层我都会给出实际的代码片段和背后原理，看到最后你就能形成一套贯穿“SQL – PL/pgSQL – 客户端”的完整赋值认知。
2. SQL脚本里的赋值：从INSERT、UPDATE到RETURNING
2.1 表达式直接赋值与类型隐式转换
先把最基础的 SQL 赋值摆出来捋一遍。在 PG 的 SQL 语句里，赋值以两种形态出现：一种是 INSERT/UPDATE 中把表达式结果赋给表字段，另一种是 SELECT 列表里构造表达式输出。
-- 基础赋值：把常量、表达式赋给字段 INSERT INTO user_profile (username, age, score, created_at) VALUES ('alice', 28, 88.5, NOW()); -- UPDATE 赋值：把计算结果回写到字段 UPDATE user_profile SET score = score + 10, level = CASE WHEN score + 10 >= 100 THEN 'VIP' ELSE 'normal' END WHERE username = 'alice';这段代码看起来平平无奇，但里面已经包含了一个经典陷阱：score定义为numeric(10,2)，而88.5是 numeric 字面量，能直接赋值成功。可如果字段类型是integer，你塞一个88.5进去，PG 不会报错，但会四舍五入成89存下去。更隐蔽的是，PG 对字符串到数字的隐式转换非常宽容，'88.5'这种字符串也可以直接赋给 numeric 字段，这种宽松有时是方便，有时是灾难。
我在项目里见过最离谱的一次线上事故，就是因为某个接口把上游传过来的金额字段当成字符串处理，赋给一个numeric(12,2)字段，结果上游传了个'12,5'（欧洲风格的逗号小数），PG 直接把整条记录拒绝了，报invalid input syntax for type numeric。从那之后我给自己定了一条规矩：凡是外部传入的数值，在 SQL 层赋值之前必须先在应用层做好类型清洗，绝不依赖 PG 的隐式转换去兜底。
2.2 用子查询和CTE构造赋值源
很多时候我们要赋的值不是常量，而是另一张表里查出来的结果。这种场景我推荐用子查询或者 CTE 的方式来构造赋值源，既清晰又避免多次往返数据库。
-- 子查询赋值 UPDATE order_summary SET customer_name = ( SELECT u.name FROM users u WHERE u.id = order_summary.user_id ) WHERE order_summary.status = 'pending'; -- CTE 赋值：先构造结果集，再更新目标表 WITH bonus_data AS ( SELECT user_id, SUM(amount) AS total_bonus FROM bonus_log WHERE bonus_date >= CURRENT_DATE - INTERVAL '7 days' GROUP BY user_id ) UPDATE users u SET total_bonus_7d = b.total_bonus FROM bonus_data b WHERE u.id = b.user_id;这里需要注意一个 PG 特有的问题：当子查询返回多行时，直接赋值会报more than one row returned by a subquery used as an expression。我早期写 UPDATE 时经常遇到这个错误，排查方式主要是检查子查询的条件是否足够限缩，或者加上LIMIT 1明确选择一条。但更推荐的做法是像第二个例子那样，用 JOIN/CTE 的方式做集合匹配，一次更新多行，效率也高。
之所以推荐 CTE，还有一个原因是可读性好。你可以在 CTE 里把计算逻辑独立出来，主语句只负责赋值，后面维护的人一看就知道数据从哪来、要赋给谁，不用在一大段 UPDATE 里扒拉子查询。
2.3 RETURNING与INSERT ON CONFLICT的赋值反馈
SQL 赋值还有一个经常被忽略的方向：执行完 INSERT/UPDATE 之后，把数据库生成的值拿回来。比如自增 ID、默认时间戳、计算后的字段值。PG 用RETURNING子句来支持这种“反向赋值”，这是我用起来最顺手的功能之一。
-- 插入后拿到自增ID和默认值 INSERT INTO orders (user_id, total_amount) VALUES (1024, 299.00) RETURNING id, created_at, status; -- 更新后把新版本信息拿回来 UPDATE orders SET status = 'paid', paid_at = NOW() WHERE id = 889900 RETURNING id, status, paid_at; -- 存在则更新，不存在则插入，并统一返回同一行数据 INSERT INTO user_points (user_id, points) VALUES (2048, 10) ON CONFLICT (user_id) DO UPDATE SET points = user_points.points + EXCLUDED.points RETURNING user_id, points;RETURNING的赋值价值在于，它让应用层不需要再发第二条 SELECT 查询，减少了通信成本和时序窗口。尤其在INSERT ... ON CONFLICT场景下，无论走插入还是更新分支，最终都能拿到一样结构的返回结果，这对幂等接口的设计非常有帮助。我在做一个积分系统时，就用这个语法把“签到加积分”做成了原子操作，避免并发下积分覆盖丢失，同时直接拿回最新积分值赋给应用层变量，展示给用户。
3. PL/pgSQL 中变量赋值的完整心法
3.1 := 与 SELECT INTO 两个赋值的区别
进入 PL/pgSQL 之后，赋值语法就变成了另一种画风。PL/pgSQL 里最基础的赋值运算符是:=，这和普通 SQL 里的=是有区别的。=在 PL/pgSQL 的某些上下文里也能用作赋值（PG 从 11 版本开始放宽了限制），但为了可读性和跨版本兼容，我强烈建议你统一使用:=。
CREATE OR REPLACE FUNCTION calculate_discount( p_order_amount numeric, p_user_level text ) RETURNS numeric LANGUAGE plpgsql AS $$ DECLARE v_discount_rate numeric := 1.0; v_final_amount numeric; BEGIN -- 方式一：运算符直接赋值（等号右侧是表达式） IF p_user_level = 'VIP' THEN v_discount_rate := 0.85; ELSIF p_user_level = 'normal' THEN v_discount_rate := 0.95; END IF; -- 方式二：SELECT INTO 将查询结果赋给变量 SELECT p_order_amount * v_discount_rate INTO v_final_amount; RETURN v_final_amount; END; $$;这里要特别强调SELECT INTO和普通:=的核心差异：SELECT INTO是从查询结果里取值的，它可以一次给多个变量赋值，也可以从一条记录结构里映射字段。但它的行为有隐藏坑：如果 SELECT 没有返回任何行，PG 会把目标变量赋值为 NULL，但不会报错（除非你加了STRICT选项）；如果返回了多行，默认会报too many rows returned错误。
所以我给你的实操建议是：
- 预期最多返回一行数据时，直接
SELECT ... INTO ...，但要在后面判断一下变量是否为 NULL。 - 预期独有一行、查不到就希望报错时，加
STRICT关键字。 - 要一次取多行再逐条处理时，别用 SELECT INTO，改用
FOR ... LOOP。
3.2 动态SQL中的赋值（EXECUTE ... INTO）
动态 SQL 是 PL/pgSQL 里最能体现赋值狡黠之处的场景。当你需要拼接表名、列名，或者在运行时构造条件时，普通的静态 SQL 满足不了，你要用EXECUTE去执行字符串形式的 SQL，并把执行结果赋给变量。
CREATE OR REPLACE FUNCTION get_dynamic_value( p_table text, p_id bigint ) RETURNS text LANGUAGE plpgsql AS $$ DECLARE v_result text; BEGIN EXECUTE format('SELECT name FROM %I WHERE id = %L', p_table, p_id) INTO v_result; RETURN v_result; END; $$;动态 SQL 的赋值和静态 SQL 有两点明显不同：
第一，动态 SQL 的INTO位置很特殊，是写在EXECUTE ... 字符串之后、一个独立的INTO子句里。它同样支持一次给多变量赋值，同样遵循“查不到返回 NULL、查多行报错”的规则。
第二，动态 SQL 里拼参数必须用format()函数配合%I（标识符转义）和%L（字符串字面量转义），这是防止 SQL 注入的底线。我见过不少新人在动态 SQL 里直接'... WHERE id = ' || p_id这样拼字符串，数据量小的时候不会出事，一旦遇到恶意输入，整个库都可能被拖走。这一条必须当作红线。
另一个动态赋值的实用场景是批量生成赋值语句。比如动态生成一条 UPDATE，把传入的一批字段拼接成SET col1 = val1, col2 = val2的形式，再EXECUTE执行。这种写法灵活，但要格外小心字段名和值的数据类型匹配。
3.3 从记录映射到复合类型的赋值细节
PG 的复合类型（行类型）是赋值环节的高级玩法。你可以把一张表的完整行赋给一个复合类型变量，这样函数里就能方便地访问row.column。
CREATE OR REPLACE FUNCTION process_user( p_user_id bigint ) RETURNS text LANGUAGE plpgsql AS $$ DECLARE v_user users%ROWTYPE; v_role text; BEGIN -- 把整行记录赋给 ROWTYPE 变量 SELECT * INTO v_user FROM users WHERE id = p_user_id; -- 访问复合类型的字段，并赋值给其他变量 v_role := v_user.role; v_role := upper(v_role); RETURN format('user:%s, role:%s', v_user.username, v_role); END; $$;这里用到users%ROWTYPE声明变量，它会自动跟随表结构，字段增删时不用改代码。取出来的v_user就是一条“行类型”数据，你可以把它整体赋给另一个同类型变量，也可以把它作为参数传给另一个函数，甚至可以直接 INSERT 回原表。这种“整行搬运”能力在数据迁移、归档、日志处理里非常有用。
但复合类型的赋值也有坑：第一，字段名大小写问题，PG 对未加引号的字段名会折叠成小写，如果你混用大小写，很容易出现record "v_user" has no field "Username"之类的错误。第二，行类型进行整体重新赋值时，必须保证结构完全一致，否则要逐字段手动处理，不能像某些语言那样自动兼容。说实话，这套机制熟练掌握之后，写起数据搬运脚本来舒服得很。
3.4 游标与循环中赋值的节奏控制
游标和循环是 PL/pgSQL 里最频繁进行赋值的区域。每遍历一行，都要把游标当前行赋给记录变量，再从记录变量拆出各列赋给业务变量。这里面的节奏控制直接影响性能和代码可维护性。
CREATE OR REPLACE FUNCTION refresh_materialized_stats() RETURNS void LANGUAGE plpgsql AS $$ DECLARE v_cur CURSOR FOR SELECT user_id, SUM(amount) AS total_amount FROM payments WHERE paid_at >= CURRENT_DATE - INTERVAL '30 days' GROUP BY user_id; v_record record; v_user_id bigint; v_total numeric; BEGIN OPEN v_cur; LOOP FETCH NEXT FROM v_cur INTO v_record; EXIT WHEN NOT FOUND; -- 从 record 变量逐字段赋值 v_user_id := v_record.user_id; v_total := v_record.total_amount; -- 在这里执行业务处理 UPDATE user_stats SET month_amount = v_total WHERE user_id = v_user_id; END LOOP; CLOSE v_cur; END; $$;关于游标赋值，我要分享一个经验之谈：循环体内的赋值次数越少越好。上面这段代码，每行数据实际上做了两次赋值（record 到变量、变量到 UPDATE），性能上多多少少有损耗。如果只是把游标数据回写到一个表，更推荐FOR循环直接省略中间变量，或者干脆用一条INSERT ... SELECT完成，连游标都用不上。游标真正的价值在于处理复杂行间依赖逻辑，而不是简单搬运数据。
此外，循环里最容易出错的地方是NOT FOUND的判断。FETCH 到末尾时，PG 会把目标变量改成 NULL 并设置NOT FOUND，你要用EXIT WHEN NOT FOUND;跳出循环，否则会多处理一次空行。
4. 客户端驱动中的赋值：类型映射与参数绑定
4.1 Python psycopg2 与 psycopg3 的赋值处理
从命令行和 PL/pgSQL 里跳出来，回到应用开发视角。Python 连接 PG 最常用的是 psycopg2，新版还有 psycopg3。这两个库的赋值方式核心都是参数化查询，用%s占位符把值传给 SQL，再由驱动处理类型映射。
import psycopg2 conn = psycopg2.connect("host=localhost dbname=mydb user=pguser password=secret") cursor = conn.cursor() # 参数化赋值：用 %s 占位 user_id = 1024 user_name = "alice" cursor.execute( "INSERT INTO users (id, username) VALUES (%s, %s) RETURNING id, created_at", (user_id, user_name), ) row = cursor.fetchone() new_id, created_at = row # 把返回行解包赋值给 Python 变量 conn.commit() cursor.close() conn.close()psycopg 的赋值有一个值得记住的特点：传进去的 Python 类型会自动映射成 PG 类型。int转 integer，float转 float8，str转 text，datetime转 timestamp，None转 NULL。这种自动映射方便归方便，但遇到自定义类型（比如数组、JSONB、UUID）时就容易踩坑。
处理 JSONB 时，建议直接传 dict/list 给Json包装一下，或者提前json.dumps()转成字符串再传给驱动。处理数组时，如果你传进去的是 Python list，psycopg2 旧版会报错，需要传'{1,2,3}'这种 PG 数组字面量，而 psycopg3 支持直接传 list。这些差异平时开发时不明显，但换版本之后很容易出现赋值失败。
4.2 Java JDBC 的参数绑定与返回值取赋
Java 生态里和 PG 打交道的标准方式是 JDBC，配合连接池例如 HikariCP 使用。JDBC 的赋值特点是强类型、需要显式调用 setter 方法：
String sql = "UPDATE users SET last_login_time = ? WHERE id = ?"; try (PreparedStatement ps = connection.prepareStatement(sql)) { ps.setTimestamp(1, Timestamp.from(Instant.now())); ps.setLong(2, 1024L); int rows = ps.executeUpdate(); }JDBC 里一个常见的赋值问题是setObject和setString的边界。setString(1, name)会把字符串当成 text/varchar 赋过去，这没问题；但如果你想把一条 UUID 赋过去，直接setString也可以，PG 会做转换。真正容易出错的是日期和时间类型：java.util.Date和java.sql.Timestamp混用时，时区极容易错乱。
我个人的经验是：在 java 层和 PG 交互时，所有时间类型都统一用java.time.LocalDateTime或Instant，配合setObject让驱动自动判断。这样可以避开Timestamp的时区陷阱。
返回值赋值方面，JDBC 用ResultSet的getXxx()方法取值。这里要特别注意：大多数 PG 驱动返回的类型默认是PGobject，例如 JSONB 列调用getObject()拿到的不是 Map 而是 PGobject，需要手动解析getString()字符串之后再转 JSON。如果你不想这么麻烦，可以给驱动的连接参数加stringtype=unspecified，但那样会丧失一些类型安全，不推荐在正式环境用。
4.3 Node.js pg 模块与实际赋值代码
Node.js 的pg模块用的是 JavaScript 动态类型系统，类型映射比 Java 舒服，但也有一些需要记住的怪癖：
const { Client } = require('pg'); const client = new Client({ connectionString: 'postgres://pguser:secret@localhost/mydb' }); async function main() { await client.connect(); const userId = 2048; const tags = ['PG', 'database']; // 参数化赋值：$1、$2 占位 const result = await client.query( `INSERT INTO articles (author_id, tags) VALUES ($1, $2) RETURNING id`, [userId, tags] ); const articleId = result.rows[0].id; await client.end(); }pg模块的参数占位符是$1、$2这种编号形式，数组可以直接传 JavaScript 数组，驱动会自动把它转成 PG 数组字面量。JSONB 传对象时，需要把对象JSON.stringify()成字符串再传，或者干脆把字段类型改成json，驱动也会自动做序列化。
我在 Node 侧踩过比较深的坑是pg模块默认把所有查询结果的值都当成字符串返回，除非你给特定类型配置了pg.types.setTypeParser()解析器。比如bigint列，默认返回的是字符串，而不是 JS 的 number，因为超出安全整数范围时会丢精度。如果不提前设置，你拿到的 id 可能是一个字符串，赋给前端变量后可能出现类型不一致问题。这个细节在对接第三方接口时尤其容易出现。
4.4 常用 ORM 中的赋值陷阱
现在很多项目都用了 ORM，比如 Python 的 SQLAlchemy、Java 的 MyBatis/JPA、Node 的 TypeORM/Prisma。ORM 把赋值抽象成了“模型属性赋值”，看起来省事，但底层还是在做 SQL 参数绑定。
SQLAlchemy 的经典赋值写法：
from sqlalchemy import create_engine, text engine = create_engine("postgresql+psycopg2://pguser:secret@localhost/mydb") with engine.connect() as conn: conn.execute( text("UPDATE users SET score = score + :inc WHERE id = :uid"), {"inc": 10, "uid": 1024}, ) conn.commit()ORM 赋值最常见的坑有两个：一个是隐式事务，很多 ORM 默认不在你调用完session.commit()之前真正执行 SQL，所以你更新了对象属性之后没 commit，数据其实没变化。另一个是懒加载之后的赋值，如果你先取出一个对象，然后给它的关联属性赋值，ORM 可能额外发一条 SELECT，N+1 问题就这么来的。
所以我建议用 ORM 时把赋值操作显式化：要么严谨地走session.add()/session.commit()流程，要么对于批量更新直接用 core 级别的 API 写参数化赋值，别在 ORM 大对象状态同步上面过度纠结。
5. 高级赋值场景：数组、JSON、复合结构与批量数据
5.1 数组赋值与 unnest/array_agg 的配合
PG 的数组类型是它相对其他关系型数据库的一大特色。数组赋值既可以是整个数组整体赋值，也可以是数组元素级别的操作。
-- 整体赋值：把数组字面量赋给 integer[] 字段 UPDATE products SET tags = ARRAY['new', 'hot', 'sale'] WHERE id = 1001; -- 用查询构造数组再赋值：把某类订单的ID收集成一个数组 UPDATE campaign_log SET applied_order_ids = ( SELECT array_agg(id ORDER BY created_at DESC) FROM orders WHERE campaign_id = 55 AND status = 'paid' ) WHERE campaign_id = 55;与数组赋值相反的是unnest，它把数组展开成多行，常用于批量插入。数据搬运时典型的组合用法是：
-- 把数组展开成行，逐字段赋值插入 INSERT INTO order_tag_map (order_id, tag) SELECT o.id, t.tag FROM unnest(ARRAY[1001,1002,1003]) AS o(id) CROSS JOIN LATERAL unnest(ARRAY['a','b','c']) AS t(tag);数组赋值中最容易翻车的场景是数组维度不一致，比如ARRAY[1,2]赋给一个integer[][]字段，PG 会报维度不匹配错误。另外，数组下标默认从 1 开始，这和很多编程语言从 0 开始不同，你在 PL/pgSQL 里用v_arr[0]取值时，拿到的永远是 NULL，除非你显式改了数组下标范围。
5.2 JSON/JSONB 的局部赋值与更新技巧
JSONB 的处理是 PG 开发中高频出现的赋值需求。很多人以为更新 JSONB 字段就是整体 SET 一个新对象，其实 PG 提供了一整套针对 JSONB 内部的赋值操作符，完全可以在不替换整个对象的前提下更新某个字段。
-- 整体赋值 UPDATE user_detail SET profile = '{"nickname": "alice", "age": 28}' WHERE user_id = 2048; -- 局部赋值：用 jsonb_set 更新单个键 UPDATE user_detail SET profile = jsonb_set( profile, '{age}', '29', true ) WHERE user_id = 2048; -- 局部赋值：无中生有，直接添加新键 UPDATE user_detail SET profile = profile || '{"vip_level": 3}' WHERE user_id = 2048; -- 删除键 UPDATE user_detail SET profile = profile - 'temporary_flag' WHERE user_id = 2048;用jsonb_set时需要格外注意：第二个参数是路径数组，不是字符串默认值。'{age}'表示根目录下的 age 键，'{a,b,c}'表示嵌套路径 a -> b -> c。第四个参数true表示“当路径不存在时自动创建”，如果设成 false，路径不存在时函数会返回 NULL，再把 NULL 赋给字段，那就等于把原先的 JSON 清空了。这个错误我见过不止一次，后果很隐秘——业务上只是更新一个子字段，结果整条 profile 变空了。
还有个细节：JSON/JSONB 的类型在 PG 里长得差不多，但 JSONB 是二进制存储、支持索引，JSON 是原始文本存储。赋值时尽量统一用 JSONB，尤其需要做 WHERE 条件筛选的场景，给属性列建 GIN 索引之后，查找速度差别很大。
5.3 复合类型字段赋值与行构造函数
PG 允许你定义自己的复合类型，比如先 CREATE TYPE，然后在表里用它作为字段类型。赋值时，可以直接用行构造函数ROW(...)或者类型名 + 括号的方式。
-- 创建复合类型 CREATE TYPE address_type AS ( city text, street text, zip text ); -- 创建使用复合类型字段的表 CREATE TABLE company ( id bigint PRIMARY KEY, address address_type ); -- 复合类型整体赋值 INSERT INTO company (id, address) VALUES (1, ROW('上海', '南京路', '200001')); -- 复合类型内部字段单独更新 UPDATE company SET address = ROW( COALESCE(address.city, '未知'), '新街道', address.zip ) WHERE id = 1;说句老实话，复合类型字段在业务系统里用得并不多，因为它会让查询和索引设计变得复杂。但在一些特定领域（比如物联网设备上报的状态快照、GIS 的地理坐标描述），复合类型能让数据组织非常紧凑。如果你正在用的表里已经存在复合类型字段，赋值时要记住：整个 ROW 的字段顺序必须和类型定义完全一致，少一个多一个都会报错。想只改其中某个子字段时，最稳妥的方法是先把整行读出来，修改后再整体赋回去。
5.4 批量插入时的多行赋值优化
批量操作是数据库赋值性能优化的重点。逐行 INSERT 显然效率极低，PG 提供了多行 VALUES 和INSERT ... SELECT，以及 COPY 协议等批量赋值方式。日常开发中最直接有效的写法是：
-- 多行VALUES批量赋值 INSERT INTO user_tag (user_id, tag) VALUES (1001, 'a'), (1001, 'b'), (1002, 'a'), (1002, 'c'); -- 从查询结果批量赋值 INSERT INTO user_tag (user_id, tag) SELECT user_id, unnest(tags) FROM temp_import_data;在应用侧做批量赋值时，一条重要经验是控制单条 SQL 的VALUES行数。不是越多越好，我实测下来，单条批量语句包含 500 到 2000 行左右是一个合理区间，具体取决于字段数量和行宽度。太多行会导致 SQL 解析和 WAL 写入压力过大，反而下降。
另外，批量插入遇到重复数据时，强烈建议使用ON CONFLICT DO UPDATE而不是先查询再插入。这既减少了一次往返，又能避免并发下两条进程同时判断“不存在”导致的主键冲突报错。
6. 实战问题排查：我踩过的六个赋值相关坑
6.1 千万别让 UPDATE 的 SET 赋值误伤全表
这是每个 PG 开发早晚会遇到的问题。写 UPDATE 的时候漏了 WHERE 条件，导致赋值覆盖整张表。有一次我们在测试环境做数据订正，一条UPDATE users SET status = 'active'直接把全表用户状态覆盖了，还好是测试库。
从赋值语义上讲，不带 WHERE 的 UPDATE 是完全合法的赋值操作，PG 不会拦截。因此我建议：
- 在 psql、pgAdmin 这些交互环境，运行 UPDATE 前先全局搜一遍有没有 WHERE。
- 对重要表开启
statement_timeout配置，短时间锁表能自动中断。 - 在事务里执行 UPDATE，确认影响行数后再 COMMIT。如果你用 psql 客户端，加
-c "BEGIN;"之后先SELECT COUNT(*)估算，再 UPDATE，最后才 COMMIT。
6.2 时间日期类型的隐式转换惹的祸
给 timestamp 字段赋值时，字符串和时区尤其容易出问题。PG 对 timestamp 的字符串解析很宽松，'2025-01-01'能赋进去，'2025/01/01'也能赋进去，但格式化不一致会导致排序和比较结果混乱。
我在项目里统一采用两条规则：第一，应用层所有时间都转成 ISO 8601 标准字符串，例如2025-01-01 08:30:00+08，交给 PG 时带时区信息。第二，表中的“事件时间”字段统一用timestamptz（带时区时间戳）而不是timestamp（不带时区），这样即使不同客户端传进来的时区不同，PG 内部也会标准化存储。这条规则帮我省掉了大量时区相关的线上投诉。
6.3 自增序列与显式主键赋值的矛盾
很多人给有自增主键的表 INSERT 时，习惯性写了id字段。如果传入的 id 和序列当前值冲突，后面再插入不指定 id 时，PG 会直接报主键冲突，因为序列没有跟着跳过去。
解决方案是给序列做同步：
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));如果批量导入带 id 的数据后，记得补上这一句。另外，PG 的GENERATED BY DEFAULT AS IDENTITY列允许显式赋值，但赋值后也要重置序列。这个坑尤其在从 CSV 恢复数据、从 MySQL 迁移数据时高频出现。
6.4 字段顺序导致批量赋值错位
批量 INSERT 或 COPY 时，字段顺序必须严格对齐。PG 的 INSERT 如果不指定字段列表，就会按照表定义顺序赋值。表结构调整过之后，你的列写法和表的实际顺序一旦错位，数据类型不兼容时 PG 会直接报错，可要是恰好类型兼容，就会把数据填到错误的列里。
我的教训是一次从 Excel 导入客户资料时，源文件的列顺序是姓名、电话、城市，但表结构是姓名、城市、电话，我没写字段列表，结果所有客户的电话和城市互换。从那以后，所有 INSERT 语句我强制要求写字段列表：
INSERT INTO customers (name, city, phone) VALUES ('张三', '上海', '138xxxx');6.5 空字符串与 NULL 的赋值误区
这是赋值语义最微妙的坑。在 PG 里，空字符串''和 NULL 是完全不同的概念。空的 text 值存得进去，但IS NULL判断为 false；NULL 表示“未知值”，和空字符串比较返回 NULL 而不是 true 或 false。
业务中如果把一个可能为空的表单字段直接赋值给 NOT NULL 字段，空字符串通常能存进去，但如果客户端把空字符串当成 NULL 传，就会收到 NOT NULL 约束违规错误。反过来，有些字段允许为空，但你想存空字符串表示“主动清空”，又发现该列存了 NULL。解决方法是明确约定：API 层的空值口径是 NULL 还是空字符串，在赋值之前统一做转换。
-- 将空字符串转成 NULL UPDATE customers SET phone = NULLIF(phone, '') WHERE id = 1234;6.6 动态表名与对象名转义
最后说一个动态 SQL 里常见的赋值隐患。把表名、列名拼接进 SQL 时，如果用字符串直接拼，一旦表名里含特殊字符就会被 PG 拒绝，甚至引发注入风险。解决方法是format()的%I标识符转义。
EXECUTE format('UPDATE %I SET %I = %L WHERE id = %L', v_table, v_column, v_value, v_id);%I会把对象名自动加上双引号并正确处理内部引号，%L会把值安全地转成字符串字面量。动态赋值的时候，凡是对象名一律%I，数据值一律%L，这个习惯能让你避开绝大多数 SQL 拼写问题和注入风险。
结尾：一条贯穿始终的个人经验
写了这么多，其实核心就一句话：赋值不是语法动作的堆砌，而是数据在“应用 – SQL引擎 – 表字段 – 变量”之间流动的每一个节点。每个节点都有它自己的规则和坑，把规则摸透了，赋值就是顺水推舟的事；摸不透，线上事故可能就藏在一次看似普通的 UPDATE 里。
我个人在实际操作中体会最深的一点是，平时写代码的时候多花 10 秒确认“这个赋值操作的输入类型是什么、输出类型是什么、查不到数据会怎样、是空字符串还是 NULL”，能省掉后面几十倍的排查时间。建议你把文章里的这些写法当成一份 checklist：新写的 SQL 语句过一遍字段顺序，函数里的 SELECT INTO 想清楚多行和 NULL 的情况，客户端参数化的类型映射做到心中有数。这套习惯养成了，PG 开发里的赋值问题基本不会再来烦你。
