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.
Best Answer
I would add another column temporarily.
The first part can run while the database is active:
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.
Please test befor running it in production; I may have forgotten something.