How to change Postgres primary key column values?

Question

My postgres database has a column called "id" that runs from 40,000,000 to about 50,000,000. The "id" column is the primary key. I need to change the "id" column values such that they span different numbers in order to merge this database with another.

How can I go about generating code to change the values from 40,000,000 - 50,000,000 to, say, 0 - 10,000,000?

The table definition is

CREATE TABLE public.keyvaluehistory (
 id bigint NOT NULL
 DEFAULT nextval('keyvaluehistory_id_seq'::regclass),
 segkey text NOT NULL,
 dvalue double precision,
 bvalue bytea,
 tstamp timestamp with time zone,
 CONSTRAINT keyvaluehistory_pkey PRIMARY KEY (id)
);

There are no foreign keys on the table.

I can afford downtime on the order of minutes/hours.

• Vérace: Subtract 40,000,000?

Answer 1

I would add another column temporarily.

The first part can run while the database is active:

ALTER TABLE keyvaluehistory ADD new_id bigint;
CREATE SEQUENCE keyvaluehistory_new_id_seq OWNED BY keyvaluehistory.new_id;
/* update in batches to avoid table bloat */
UPDATE keyvaluehistory SET new_id = id - 39999999
 WHERE id BETWEEN 40000000 AND 40999999;
VACUUM keyvaluehistory;
UPDATE keyvaluehistory SET new_id = id - 39999999
 WHERE id BETWEEN 41000000 AND 41999999;
VACUUM keyvaluehistory;
...
SET maintenance_work_mem = '1GB';
CREATE UNIQUE INDEX CONCURRENTLY keyvaluehistory_new_pkey (new_id);

The following part locks the table and requires down time.

The most time consuming part is adding the primary key, because that requires scanning the table.

/* downtime starts here */
BEGIN;
LOCK TABLE keyvaluehistory IN ACCESS EXCLUSIVE MODE;
/* catch up */
UPDATE keyvaluehistory SET new_id = id - 39999999
 WHERE new_id IS NULL;
ALTER TABLE keyvaluehistory
 DROP CONSTRAINT keyvaluehistory_pkey;
ALTER TABLE keyvaluehistory
 DROP COLUMN id;
ALTER TABLE keyvaluehistory
 ADD CONSTRAINT keyvaluehistory_pkey USING keyvaluehistory_new_pkey;
ALTER INDEX keyvaluehistory_new_pkey RENAME TO keyvaluehistory_pkey;
ALTER TABLE keyvaluehistory RENAME new_id TO id;
ALTER SEQUENCE keyvaluehistory_new_id_seq RENAME TO keyvaluehistory_id_seq;
SELECT setval('keyvaluehistory_new_id_seq', 10000001);
COMMIT;

Please test befor running it in production; I may have forgotten something.

• Jake Lyle: This worked! There was some language/variable mismatch with the indexes and constraints but I managed to work it out. Thanks a lot!
• TmTron: @Laurenz: can you explain why VACUUM after each block avoids table-bloat?
• Laurenz Albe: @TmTron Because it removes the dead row versions created in the preceding step, so that the space can be reused by the next UPDATE. See the PostgreSQL documentation.