How to change a table ID from serial to identity?

Question

I have the following table in Postgres 10.10:

  Table "public.client"
       Column        |  Type   | Collation | Nullable |                 Default                  
---------------------+---------+-----------+----------+------------------------------------------
 clientid            | integer |           | not null | nextval('client_clientid_seq'::regclass)
 account_name        | text    |           | not null | 
 last_name           | text    |           |          | 
 first_name          | text    |           |          | 
 address             | text    |           | not null | 
 suburbid            | integer |           |          | 
 cityid              | integer |           |          | 
 post_code           | integer |           | not null | 
 business_phone      | text    |           |          | 
 home_phone          | text    |           |          | 
 mobile_phone        | text    |           |          | 
 alternative_phone   | text    |           |          | 
 email               | text    |           |          | 
 quote_detailsid     | integer |           |          | 
 invoice_typeid      | integer |           |          | 
 payment_typeid      | integer |           |          | 
 job_typeid          | integer |           |          | 
 communicationid     | integer |           |          | 
 accessid            | integer |           |          | 
 difficulty_levelid  | integer |           |          | 
 current_lawn_price  | numeric |           |          | 
 square_meters       | numeric |           |          | 
 note                | text    |           |          | 
 client_statusid     | integer |           |          | 
 reason_for_statusid | integer |           |          | 
Indexes:
    "client_pkey" PRIMARY KEY, btree (clientid)
    "account_name_check" UNIQUE CONSTRAINT, btree (account_name)
Foreign-key constraints:
    "client_accessid_fkey" FOREIGN KEY (accessid) REFERENCES access(accessid)
    "client_cityid_fkey" FOREIGN KEY (cityid) REFERENCES city(cityid)
    "client_client_statusid_fkey" FOREIGN KEY (client_statusid) REFERENCES client_status(client_statusid)
    "client_communicationid_fkey" FOREIGN KEY (communicationid) REFERENCES communication(communicationid)
    "client_difficulty_levelid_fkey" FOREIGN KEY (difficulty_levelid) REFERENCES difficulty_level(difficulty_levelid)
    "client_invoice_typeid_fkey" FOREIGN KEY (invoice_typeid) REFERENCES invoice_type(invoice_typeid)
    "client_job_typeid_fkey" FOREIGN KEY (job_typeid) REFERENCES job_type(job_typeid)
    "client_payment_typeid_fkey" FOREIGN KEY (payment_typeid) REFERENCES payment_type(payment_typeid)
    "client_quote_detailsid_fkey" FOREIGN KEY (quote_detailsid) REFERENCES quote_details(quote_detailsid)
    "client_reason_for_statusid_fkey" FOREIGN KEY (reason_for_statusid) REFERENCES reason_for_status(reason_for_statusid)
    "client_suburbid_fkey" FOREIGN KEY (suburbid) REFERENCES suburb(suburbid)
Referenced by:
    TABLE "work" CONSTRAINT "work_clientid_fkey" FOREIGN KEY (clientid) REFERENCES client(clientid)

I would like to change clientid from a serial id (nextval('client_clientid_seq'::regclass)) to not null generated always as identity primary key.

The table has 107 records which were manually entered including clientids.

How could this be done without destroying existing data?

Thanks for all the info. Spot on it worked. – Christian Hick Dec 08 '19 at 23:33 — Christian Hick, Dec 08 '19 at 23:33

Erwin Brandstetter · Accepted Answer · 2022-07-26T12:04:29.717

BEGIN;
ALTER TABLE public.client ALTER clientid DROP DEFAULT; -- drop default

DROP SEQUENCE public.client_clientid_seq;              -- drop owned sequence

ALTER TABLE public.client
-- ALTER clientid SET DATA TYPE int,                   -- not needed: already int
   ALTER clientid ADD GENERATED ALWAYS AS IDENTITY (RESTART 108);
COMMIT;

There are two variables:

the actual name of the attached SEQUENCE. I used the default name above, but the name can differ.
the current maximum value in client.clientid. Doesn't have to be 107, just because there are currently 107 rows.

This query gets both:

SELECT pg_get_serial_sequence('client', 'clientid'), max(clientid) FROM client;

A serial column is an integer column that owns a dedicated sequence and has its default set to draw from it (as can be seen from the table definition you posted). To make it a plain integer, drop the default and then drop the sequence.

Converting the column to an IDENTITY adds its own sequence. You must drop the old owned sequence (or at least the ownership, which dies with dropping the sequence). Else you get errors like:

ERROR:  more than one owned sequence found

How to copy structure and contents of a table, but with separate sequence?

Then convert the plain integer column to an IDENTITY column, and restart with the current maximum plus 1. You must set the current value of the new internal sequence to avoid unique violations.

Wrap it all in a single transaction, so you don't mess up half way into the migration. All of these DDL commands are transactional in Postgres, can be rolled back until committed and are only visible to other transactions starting after that.

Your column was PK before and stays PK. This is orthogonal to the change.

Peter Eisentraut, the principal author of the (new in Postgres 10) IDENTITY feature, also provided a function upgrade_serial_to_identity() to convert existing serial columns. It reuses the existing sequence and instead updates system catalogs directly - which you should not do yourself unless you know exactly what you are doing. It also covers exotic corner cases. Check it out (chapter "Upgrading"):

https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/

However, the function won't work on most hosted services that do not allow direct manipulation of system catalogs. Then you are back to DDL commands as instructed at the top.

The `more than one owned sequence found` error should be gone in v12 with [commit 19781729f78](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=19781729f789f3c6b2540e02b96f8aa500460322). — Laurenz Albe, Dec 08 '19 at 19:30

score 0 · Answer 2 · answered Dec 08 '19 at 05:43

You can alter the definition, the syntax is:

ALTER TABLE table_name 
ALTER COLUMN column_name 
{ SET GENERATED { ALWAYS| BY DEFAULT } | 
  SET sequence_option | RESTART [ [ WITH ] restart ] }

Not sure if you need to alter the column with SET DEFAULT NULL first. And make sure the sequence is past the values inserted manually so there are no conflicts.

helvete · Answer 3 · 2022-08-21T09:50:54.640

I ended up using setval instead of ALTER with RESTART for the value which should the sequence be restarted with cannot be provided dynamically using a statement. The solution is to set the column default first and then to set the sequence value.

ALTER TABLE schema.table ALTER COLUMN id DROP DEFAULT;
DROP SEQUENCE schema.sequence_name;
ALTER TABLE schema.table ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;
SELECT pg_catalog.setval(
    pg_get_serial_sequence('schema.table', 'id'),
    COALESCE((SELECT MAX(id) + 1 FROM schema.table), 1),
    false
);

Few notes:

COALESCE used to handle empty tables correctly
MAX(id) + 1 + 3rd param false to setval used to prevent gaps in sequences while having minimal value of 1
utilize pg_get_serial_sequence() to get the name of the newly generated sequence to be dynamic and allow for changes in naming schemes

score -1 · Answer 4 · answered Jan 15 '22 at 17:13

I made some changes to the script posted by Madina that allowed it to work for me

SELECT 'ALTER TABLE '||table_schema||'."'||TABLE_NAME||'" ALTER '||COLUMN_NAME||' DROP DEFAULT;
'||replace('DROP SEQUENCE '''||substring(column_default, 9, length(column_default)-19), '''', '')||';
ALTER TABLE  '||table_schema||'."'||TABLE_NAME||'" ALTER '||COLUMN_NAME||' ADD GENERATED ALWAYS AS IDENTITY;
SELECT SETVAL(pg_get_serial_sequence('''||table_schema||'.'||TABLE_NAME||''', '''||COLUMN_NAME||'''),
(SELECT COALESCE(MAX('||COLUMN_NAME||'), 0) + 1 FROM '||table_schema||'.'||TABLE_NAME||'), false);'
FROM information_schema.columns
WHERE column_default LIKE 'nextval%';

score -2 · Answer 5 · answered Jul 27 '21 at 06:36

SELECT 'ALTER TABLE '||table_schema||'."'||TABLE_NAME||'" ALTER '||COLUMN_NAME||' DROP DEFAULT;
'||replace('DROP SEQUENCE '''||substring(column_default, 9, length(column_default)-19), '''', '')||'  CASCADE;
ALTER TABLE  '||table_schema||'."'||TABLE_NAME||'" ALTER COLUMN '||COLUMN_NAME||' set not null;
ALTER TABLE  '||table_schema||'."'||TABLE_NAME||'" ALTER '||COLUMN_NAME||' ADD GENERATED ALWAYS AS IDENTITY;
SELECT setval(pg_get_serial_sequence(''"'||TABLE_NAME||'"'', '''||COLUMN_NAME||'''),
(select max('||COLUMN_NAME||') from '||table_schema||'."'||TABLE_NAME||'"));'
FROM information_schema.columns
WHERE column_default LIKE 'nextval%';

the result of this query helps you to replace all serial id to generated identity

How to change a table ID from serial to identity?

5 Answers5

Linked