CODEWITHSUNDEEP
postgresqlalterpostgresql-11
CezarySzulc
CezarySzulc

Reputation: 1997

Create serial primary key for split table

I have an archive table and a production table with the same schema. Production has fresh data and archive has old data.

I would like to add id to them as serial primary key. In both tables, I have data.

My idea is to create id in the archive table:

alter table people add column id serial primary key;

Then do the same in my production table.

Production is a continuation of the archive table so the primary key should also be a continuation for it.

So I used:

alter sequence people_id_seq restart with 40;

My only issue is this commend doesn't update index. It only sets th start value for new inserts.

So should I first run

UPDATE people 
SET id = id + 40;

And the set sequence with value of number of all rows (in archive and production).

alter sequence people_id_seq restart with (40 + select count(*) from people);

I have a few tables for this process (with different number of rows in archive and production) so I'm looking for the most automatic solution for this.

Upvotes: 1

Views: 37

Answers (0)

Related Questions