PostgreSQL迁移脚本实现主键递增的行复制问题

Got it, let's break down how to solve this problem—you need to duplicate a row without specifying every column name (since tables can have arbitrary columns), while avoiding that duplicate primary key error. The approach varies a bit by database, so here are the most reliable methods:

For PostgreSQL (matches your error message) #

Since PostgreSQL uses sequences for auto-incrementing keys, you can leverage built-in functions to dynamically replace the primary key value without listing all columns:

Method 1: Using nextval and EXCLUDE #

This works if your primary key is backed by a sequence (the standard setup for SERIAL or IDENTITY columns):

INSERT INTO table_name
SELECT nextval(pg_get_serial_sequence('table_name', 'id')), t.* EXCEPT (id)
FROM table_name t
WHERE t.id = 255;

• pg_get_serial_sequence('table_name', 'id') automatically fetches the sequence associated with your id column.
• nextval() gets the next valid value from that sequence.
• t.* EXCEPT (id) excludes the original id from the selected columns, so we don't duplicate it.

Method 2: Using row subtraction (PostgreSQL 12+) #

If you prefer a more concise syntax:

INSERT INTO table_name
SELECT (t).*
FROM (
  SELECT ROW(t.*) - ROW(t.id) AS t
  FROM table_name t
  WHERE t.id = 255
) AS subquery;

This removes the id field from the row record, and PostgreSQL will auto-generate a new value for the primary key if it's set to IDENTITY or uses a sequence.

For MySQL #

MySQL doesn't have the EXCLUDE clause, but you can use dynamic SQL to build the insert statement automatically, avoiding manual column listing:

-- Set your table, primary key column, and source ID here
SET @table_name = 'table_name';
SET @pk_column = 'id';
SET @source_id = 255;

-- Dynamically build the INSERT query
SELECT CONCAT(
  'INSERT INTO ', @table_name, ' (',
  GROUP_CONCAT(COLUMN_NAME SEPARATOR ','),
  ') SELECT ',
  GROUP_CONCAT(CASE WHEN COLUMN_NAME = @pk_column THEN 'NULL' ELSE COLUMN_NAME END SEPARATOR ','),
  ' FROM ', @table_name, ' WHERE ', @pk_column, ' = ', @source_id, ';'
) INTO @sql_query
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name AND TABLE_SCHEMA = DATABASE();

-- Execute the generated query
PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This queries the INFORMATION_SCHEMA to get all columns, replaces the primary key column with NULL (so MySQL uses the auto-increment value), and runs the dynamic INSERT.

General Notes #

• Unique Constraints: If your table has other unique constraints besides the primary key, you'll need to adjust those values too (e.g., update a unique email or username column in the duplicated row) to avoid additional constraint violations.
• Non-Sequence/Non-Auto-Increment PKs: If your primary key isn't auto-generated (e.g., a UUID), replace nextval() or NULL with a method to generate a new unique value (e.g., gen_random_uuid() for PostgreSQL).

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