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.

Subtract 40,000,000?

Vérace
– Vérace

2019-10-07 01:16:04 +00:00
Commented Oct 7, 2019 at 1:16 — Vérace
– Vérace, Commented Oct 7, 2019 at 1:16

Laurenz Albe · Accepted Answer · 2019-10-08 05:46:14Z

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.

This worked! There was some language/variable mismatch with the indexes and constraints but I managed to work it out. Thanks a lot! — Jake Lyle
– Jake Lyle, Commented Oct 8, 2019 at 22:19
@Laurenz: can you explain why VACUUM after each block avoids table-bloat? — TmTron
– TmTron, Commented May 30, 2021 at 12:44
@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. — Laurenz Albe
– Laurenz Albe, Commented May 31, 2021 at 1:39

Stack Exchange Network

How to change Postgres primary key column values?

1 Answer 1

Your Answer

Linked

Hot Network Questions

How to change Postgres primary key column values?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions