1

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?

1 Answer 1

4

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:

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:

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

2
  • 1
    @LaurenzAlbe Backported?
    – Vérace
    Sep 30, 2021 at 7:49
  • @Vérace No, identity columns were introduced in v10; I looked. Erwin was right, sorry for the noise. Sep 30, 2021 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.