ERROR: more than one owned sequence found in Postgres

Question

I'm setting up a identity column to my existing columns for the Patient table.
Here I would like to use GENERATED ALWAYS AS IDENTITY.

So I setup the identity column by using the following statement (previously it was serial):

ALTER TABLE Patient ALTER PatientId
   ADD GENERATED ALWAYS AS IDENTITY (START WITH 1);

For the existing patient table I have a total of 5 records. (patientId 1 to 5)
When I insert a new record after the identity setup, it will throw an error like:

more than one owned sequence found

Even after resetting the identity column, I still get the same error.

ALTER TABLE Patient ALTER COLUMN PatientId RESTART WITH 6;

Let me know if you have any solutions.

Answer 1

Update: This bug has been fixed in PostgreSQL v12 with commit 19781729f78.
The rest of the answer is relevant for older versions.

A serial column has a sequence that is owned by the column and a DEFAULT value that gets the net sequence value.

If you try to change that column into an identity column, you'll get an error that there is already a default value for the column.

Now you must have dropped the default value, but not the sequence that belongs to the serial column. Then when you converted the column into an identity column, a second sequence owned by the column was created.

Now when you try to insert a row, PostgreSQL tries to find and use the sequence owned by the column, but there are two, hence the error message.

I'd argue that this is a bug in PostgreSQL: in my opinion, it should either have repurposed the existing sequence for the identity column or given you an error that there is already a sequence owned by the column, and you should drop it. I'll try to get this bug fixed.

Meanwhile, you should manually drop the sequence left behind from the serial column. Run the following query:

SELECT d.objid::regclass
FROM pg_depend AS d
   JOIN pg_attribute AS a ON d.refobjid = a.attrelid AND
                             d.refobjsubid = a.attnum
WHERE d.classid = 'pg_class'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND d.deptype <> 'i'
  AND a.attname = 'patientid'
  AND d.refobjid = 'patient'::regclass;

That should give you the name of the sequence left behind from the serial column. Drop it, and the identity column should behave as desired.

Answer 2

This is not an answer -- apologies, but this allows me to show, with a vivid image, the crazy behavior that I (unintentionally) uncovered this morning...

All I had to do was this:

alter TABLE db.generic_items alter column generic_item_id drop default;
alter TABLE db.generic_items alter column generic_item_id add generated by default as identity;

and now when scripting the table to SQL I get (abbreviated):

CREATE TABLE db.generic_items
(
    generic_item_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    generic_item_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    generic_item_name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT pk_generic_items PRIMARY KEY (generic_item_id),
)

I am thankful for the answer posted above, by Laurenz Albe! As he explains, just delete the sequence that was used for the serial default, and this craziness goes away and the table looks normal again.