Remove "identity flag" from a column in PostgreSQL

January 21, 2022

I have some tables in PostgreSQL 12.9 that were declared as something like

-- This table is written in old style
create table old_style_table_1 (
    id bigserial not null primary key,
    ...
);

-- This table uses new feature
create table new_style_table_2 (
    id bigint generated by default as identity,
    ...
);

Second table seems to be declared using the identity flag introduced in 10th version.

Time went by, and we have partitioned the old tables, while keeping the original sequences:

CREATE TABLE partitioned_old_style_table_1 (LIKE old_style_table_1 INCLUDING DEFAULTS) PARTITION BY HASH (user_id);
CREATE TABLE partitioned_new_style_table_2 (LIKE new_style_table_2 INCLUDING DEFAULTS) PARTITION BY HASH (user_id);

DDL for their id columns seems to be id bigint default nextval('old_style_table_1_id_seq') not null and id bigint default nextval('new_style_table_2_id_seq') not null.

Everything has worked fine so far. Partitioned tables proved to be a great boon and we decided to retire the old tables by dropping them.

DROP TABLE old_style_table_1, new_style_table_2;
-- [2BP01] ERROR: cannot drop desired object(s) because other objects depend on them 
-- Detail: default value for column id of table old_style_table_1 depends on sequence old_style_table_1_id_seq
-- default value for column id of table new_style_table_2 depends on sequence new_style_table_2_id_seq

After some pondering I’ve found out that sequences may have owners in postgres, so I opted to change them:

ALTER SEQUENCE old_style_table_1_id_seq OWNED BY partitioned_old_style_table_1.id;
DROP TABLE old_style_table_1;
-- Worked out flawlessly

ALTER SEQUENCE new_style_table_2_id_seq OWNED BY partitioned_new_style_table_2.id;
ALTER SEQUENCE new_style_table_2_id_seq OWNED BY NONE;
-- Here's the culprit of the question:
-- [0A000] ERROR: cannot change ownership of identity sequence

So, apparently the fact that this column has pg_attribute.attidentity set to 'd' forbids me from:

• changing the default value of the column:

ALTER TABLE new_style_table_2 ALTER COLUMN id SET DEFAULT 0;
-- [42601] ERROR: column "id" of relation "new_style_table_2" is an identity column

• dropping the default value:

ALTER TABLE new_style_table_2 ALTER COLUMN id DROP DEFAULT;
-- [42601] ERROR: column "id" of relation "new_style_table_2" is an identity column
-- Hint: Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead.

• dropping the identity, column or the table altogether (new tables already depend on the sequence):

ALTER TABLE new_style_table_2 ALTER COLUMN id DROP IDENTITY IF EXISTS;
-- or
ALTER TABLE new_style_table_2 DROP COLUMN id;
-- or
DROP TABLE new_style_table_2;
-- result in
-- [2BP01] ERROR: cannot drop desired object(s) because other objects depend on them 
-- default value for column id of table partitioned_new_style_table_2 depends on sequence new_style_table_2_id_seq

I’ve looked up the documentation, it provides the way to SET IDENTITY or ADD IDENTITY, but no way to remove it or to change to a throwaway sequence without attempting to drop the existing one.

➥ So, how am I able to remove an identity flag from the column-sequence pair so it won’t affect other tables that use this sequence?

UPD: Tried running UPDATE pg_attribute SET attidentity='' WHERE attrelid=16816; on localhost, still receive [2BP01] and [0A000]. :/

Though I managed to execute the DROP DEFAULT value bit, but it seems like a dead end.

>Solution :

I don’t think there is a safe and supported way to do that (without catalog modifications). Fortunately, there is nothing special about sequences that would make dropping them a problem. So take a short down time and: