How to change a table ID from serial to identity?

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.

How to convert primary key from integer to serial?
Auto increment table column
How to copy structure and contents of a table, but with separate sequence?

How to change a table ID from serial to identity?

Related

Recent Posts