PostgreSQL：无序列时复制customer_config表记录至新cust_id

问题描述 #

我有一张customer_config表，数据如下：

id列是非空且唯一的，我想把cust_id=1的所有记录克隆到cust_id=2，试了下面的SQL：

INSERT INTO customer_config(cust_id, customer_name, currency_code) 
SELECT 2, customer_name, currency_code 
FROM customer_config 
WHERE cust_id = 1;

但我没法给表加序列来自动生成ID，执行后直接报错：

17:55:38 [INSERT - 0 rows, 0.583 secs] [Code: 0, SQL State: 23502] ERROR: null value in column "id" violates not-null constraint Detail: Failing row contains (4, null, sslProtocol, 1, SSL, null). ... 1 statement(s) executed, 0 rows affected, exec/fetch time: 0.583/0.000 sec [0 successful, 1 errors]

求问怎么才能成功插入这些克隆记录？

解决方案 #

问题出在你没给新记录的id赋值，又没自动生成机制，数据库只能插null，触发了非空约束。既然不能改表加序列，咱们手动生成唯一ID就行，给你两种靠谱的方法：

方法1：基于现有最大ID生成连续新ID #

先拿到当前表的最大id，然后给每条克隆记录分配max_id + 行号，保证绝对不重复：

WITH max_id AS (SELECT MAX(id) AS current_max FROM customer_config)
INSERT INTO customer_config(id, cust_id, customer_name, currency_code)
SELECT 
    max_id.current_max + ROW_NUMBER() OVER (ORDER BY cc.id),
    2,
    cc.customer_name,
    cc.currency_code
FROM customer_config cc, max_id
WHERE cc.cust_id = 1;

比如现在最大ID是4，新插入的记录就会拿到5、6、7、8这几个ID，完美避开现有数据。

方法2：用UUID当ID（如果ID列支持字符串） #

如果你的id列是字符串类型，直接生成UUID当唯一ID也很方便：

-- 先确保uuid扩展已启用（第一次用需要执行）
CREATE EXTENSION IF NOT EXISTS uuid-ossp;

INSERT INTO customer_config(id, cust_id, customer_name, currency_code)
SELECT 
    uuid_generate_v4(),
    2,
    customer_name,
    currency_code
FROM customer_config 
WHERE cust_id = 1;

要是id是整数类型，这个方法就用不了了，还是选方法1更稳妥。

内容的提问来源于stack exchange，提问作者parasuraman s