
ALTER TABLE mytable ALTER COLUMN description SET DEFAULT 'B2'.

ALTER TABLE mytable ADD COLUMN description varchar (100) DEFAULT 'A1' Modifies the column to use a different default value. Adds the column with the old default value. How do I decompose ctid into page and row numbers?īut the first advice is much better for the case at hand. Referencing the most recent docs, this operation can be done using two statements.We then specify our action, which is add.
PSQL ADD COLUMN HOW TO
To answer your original question: you could use the system column ctid like this: UPDATE ecdict cįROM (SELECT ctid, row_number() OVER () AS seqnum FROM ecdict) c2 Hey everyone, today were going to look at how to make an existing column the primary key to a table in Postgres. And numbers are assigned arbitrarily by current physical order of rows.Īlternatively, 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.Īside: Why bigint? integer should be good enough for 1M rows - unless you expect a lot of growth and/or churn.

Triggers a whole-table rewrite, of course. 1 Answer Sorted by: 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 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
