How to add a serial ID to a table?

Question

I have a table has more than 1000000 rows which was imported from CSV file. The table did not have a primary key, I want to add one with incremented bigint to the table. This is my table DDL:

CREATE TABLE public.ecdict (
    word varchar(256) NULL,
    phonetic varchar(512) NULL,
    definition varchar(80000) NULL,
    "translation" varchar(80000) NULL,
    pos varchar(100) NULL,
    collins int4 NULL,
    oxford int4 NULL,
    tag varchar(128) NULL,
    bnc int4 NULL,
    frq int4 NULL,
    exchange varchar(128) NULL,
    detail varchar(2000) NULL,
    audio varchar(8) NULL,
    id int8 NULL
);

The id column is new add and all data be NULL, I tried to use this command:

update ecdict c
set id = c2.seqnum
from (
   select c2.*, row_number() over () as seqnum
   from ecdict c2
) c2
where c2.pkid = c.pkid;

But I could not found an unique column for the table for join, what should I do to add the id column with incremented bigint values?

Answer 1

Add the id column like this, and the column will be filled with serial numbers automatically, and indexed as PK:

ALTER TABLE public.ecdict ADD COLUMN id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY;

Requires Postgres 10 or later. Use a serial column for older (or any) versions:

ALTER TABLE public.ecdict ADD COLUMN id bigserial PRIMARY KEY;

See:

• Auto increment table column

Triggers a whole-table rewrite, of course. And numbers are assigned arbitrarily by current physical order of rows.

Alternatively, you could add a serial or IDENTITY column to your table before importing data from your CSV and not assign it in the process, then it defaults to serial numbers automatically.

Aside: Why bigint? integer should be good enough for 1M rows - unless you expect a lot of growth and/or churn.

To answer your original question: you could use the system column ctid like this:

UPDATE ecdict c
SET    id = c2.seqnum
FROM  (SELECT ctid, row_number() OVER () AS seqnum FROM ecdict) c2
WHERE  c.ctid = c2.ctid;

See:

• How do I decompose ctid into page and row numbers?

But the first advice is much better for the case at hand.