First off, gaps in a sequence are to be expected. Ask yourself if you really need to remove them. Your life gets simpler if you just live with it. To get gap-less numbers, the (often better) alternative is to use a VIEW with row_number(). Example in this related answer:
Here are some recipes to remove gaps.
1. New, pristine table
Avoids complications with unique violations and table bloat and is fast. Only for simple cases where you are not bound by FK references, views on the table or other depending objects, or by concurrent access. Do it in one transaction to avoid accidents:
BEGIN;
LOCK tbl; -- optionally: IN SHARE MODE to allow concurrent reads
CREATE TABLE tbl_new (LIKE tbl INCLUDING ALL);
INSERT INTO tbl_new -- no target list in this case
SELECT row_number() OVER (ORDER BY id), data -- all columns in default order
FROM tbl;
ALTER SEQUENCE tbl_id_seq OWNED BY tbl_new.id; -- make new table own sequence
DROP TABLE tbl;
ALTER TABLE tbl_new RENAME TO tbl;
SELECT setval('tbl_id_seq', max(id)) FROM tbl; -- reset sequence
COMMIT;
CREATE TABLE tbl_new (LIKE tbl INCLUDING ALL) copies the structure incl. constraints and defaults from the original table. Then make the new table column own the sequence:
And reset it to the new maximum:
This carries the advantage that the new table is bloat-free and clustered on id.
2. UPDATE in place
This produces a lot of dead rows and requires (auto-) VACUUM later.
If the serial column is also the PRIMARY KEY (like in your case) or has a UNIQUE constraint, you must avoid unique violations in the process. The (cheaper) default for PK / UNIQUE constraints is to be NOT DEFERRABLE, which forces a check after every single single row. All details under this related question on SO:
You could define your constraint as DEFERRABLE (which makes it more expensive).
Or you can drop the constraint and add it back when you are done:
BEGIN;
LOCK tbl;
ALTER TABLE tbl DROP CONSTRAINT tbl_pkey; -- remove PK
UPDATE tbl t -- intermediate unique violations are ignored now
SET id = t1.new_id
FROM (SELECT id, row_number() OVER (ORDER BY id) AS new_id FROM tbl) t1
WHERE t.id = t1.id;
SELECT setval('tbl_id_seq', max(id)) FROM tbl; -- reset sequence
ALTER TABLE tbl ADD CONSTRAINT tbl_pkey PRIMARY KEY(id); -- add PK back
COMMIT;
Neither is possible while you have FOREIGN KEY constraints referencing the column(s) because (per documentation):
The referenced columns must be the columns of a non-deferrable unique
or primary key constraint in the referenced table.
You would need to (lock all involved tables and) drop / recreate FK constraints and update all FK values manually (see option 3.). Or you have to move values out of the way with a second UPDATE to avoid conflicts. For instance, assuming you have no negative numbers:
BEGIN;
LOCK tbl;
UPDATE tbl SET id = id * -1; -- avoid conflicts
UPDATE tbl t
SET id = t1.new_id
FROM (SELECT id, row_number() OVER (ORDER BY id DESC) AS new_id FROM tbl) t1
WHERE t.id = t1.id;
SELECT setval('tbl_id_seq', max(id)) FROM tbl; -- reset sequence
COMMIT;
Drawbacks as mentioned above.
3. Temp table, TRUNCATE, INSERT
One more option if you have plenty of RAM. This combines some of the advantages of the first two ways. Almost as fast as option 1. and you get a pristine, new table without bloat but keep all constraints and dependencies in place like in option 2.
However, per documentation:
TRUNCATE cannot be used on a table that has foreign-key references
from other tables, unless all such tables are also truncated in the
same command. Checking validity in such cases would require table
scans, and the whole point is not to do one.
Bold emphasis mine.
You could drop FK constraints temporarily and use data-modifying CTEs to update all FK columns:
SET temp_buffers = 500MB; -- example value, see 1st link below
BEGIN;
CREATE TEMP TABLE tbl_tmp AS
SELECT row_number() OVER (ORDER BY id) AS new_id, *
FROM tbl
ORDER BY id; -- order here to use index (if one exists)
-- drop FK constraints in other tables referencing this one
-- which takes out an exclusive lock on those tables
TRUNCATE tbl;
INSERT INTO tbl
SELECT new_id, data -- list all columns in order
FROM tbl_tmp; -- rely on established order in tbl_tmp
-- ORDER BY id; -- only to be absolutely sure (not necessary)
-- example for table "fk_tbl" with FK column "fk_id"
UPDATE fk_tbl f
SET fk_id = t.new_id -- set to new ID
FROM tbl_tmp t
WHERE f.fk_id = t.id; -- match on old ID
-- add FK constraints in other tables back
COMMIT;
Related, with more details:
