How to change a table ID from serial to identity?

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?

Solution

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.