Postgresql identity column “no owned sequence”

Postgresql 10 introduced identity column which offers better manageability than the old fashioned serial. Refer to this post for more detail

I followed the post above and converted most of serial columns into identity, worked all good until one day after I accidentally dropped the sequence associated with the identity column, I started receiving “no owned sequence” for insert statements.

When I tried the following statement to drop this identity, I ended up with “no owned sequence”

alter TABLE tableX alter column colSeq drop identity;

When I tried the following to recreate the entity, I received “colSeq is already an identity column”

alter TABLE tableX alter column colSeq add generated by default as identity;

My first solution was to drop this column and then re-create it with “GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY” however this means the sequence column will be at the end of the table which is not convenient (if I don’t recreate the whole table) and more importantly I had to recreate my publication and subscription for my logical replication.

After a closer look at the migration function provided by the 2ndquadrant post, I came with the following solution

a. update the column type to non-identity

UPDATE pg_attribute
SET attidentity = ''
WHERE attrelid = tableX::regclass
AND attname = 'colSeq';

b. upgrade it to an identity type column

alter TABLE tableX alter column colSeq add generated by default as identity;

c. restart the sequence

ALTER TABLE tableX ALTER COLUMN colSeq RESTART WITH xxxx;

where xxx is the max (the previous colSeq ) + 1