PostgreSQL调整字段序列依赖：迁移跨Schema序列至表所属Schema

Here's a step-by-step guide to fix your sequence dependency issue:

1. Copy the existing sequence to your target schema #

First, we'll duplicate the sequence from schema2 to schema1, preserving all its current settings (like last value, increment, cache size, etc.):

CREATE SEQUENCE schema1.table1_seq LIKE schema2.table1_seq INCLUDING ALL;

The INCLUDING ALL clause ensures every property of the original sequence is copied—this means you won't have gaps in your ID values or unexpected resets.

2. Update the table column's default to use the new sequence #

Next, modify the gid column in schema1.table1 to point to your new local sequence:

ALTER TABLE schema1.table1 ALTER COLUMN gid SET DEFAULT nextval('schema1.table1_seq'::regclass);

3. Link the sequence to the column (best practice) #

To maintain the typical "serial" behavior (where dropping the table automatically drops the sequence), set the sequence as owned by the column:

ALTER SEQUENCE schema1.table1_seq OWNED BY schema1.table1.gid;

4. Verify everything works correctly #

Check that the column's default is now pointing to the right sequence:

SELECT column_default 
FROM information_schema.columns 
WHERE table_schema = 'schema1' 
  AND table_name = 'table1' 
  AND column_name = 'gid';

You should see nextval('schema1.table1_seq'::regclass) as the result.

Test inserting a row into schema1.table1—the gid should auto-increment using your new sequence, and you can confirm the sequence's last value with:

SELECT last_value FROM schema1.table1_seq;

Optional: Clean up the old sequence #

If you're sure the original sequence (schema2.table1_seq) isn't used by any other tables or objects, you can drop it safely:

DROP SEQUENCE schema2.table1_seq;

Before dropping, double-check for dependencies with:

SELECT * FROM pg_depend WHERE objid = 'schema2.table1_seq'::regclass;

Key considerations: #

• Permissions: Make sure your user has CREATE access on schema1, ALTER access on schema1.table1, and SELECT access on schema2.table1_seq.
• Locking: The ALTER TABLE command will take an exclusive lock on the table briefly. If your table is under heavy load, run this during a low-traffic window to avoid blocking transactions.

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