Re: Identity columns should own only one sequence

On 2019-04-14 17:51, Laurenz Albe wrote:
> Identity columns don't work if they own more than one sequence.

Well, they shouldn't, because then how do they know which sequence they
should use?

> So if one tries to convert a "serial" column to an identity column,
> the following can happen:
>
> test=> CREATE TABLE ser(id serial);
> CREATE TABLE
> test=> ALTER TABLE ser ALTER id ADD GENERATED ALWAYS AS IDENTITY;
> ERROR: column "id" of relation "ser" already has a default value
>
> Hm, ok, let's drop the column default value.
>
> test=> ALTER TABLE ser ALTER id DROP DEFAULT;
> ALTER TABLE
>
> Now it works:
>
> test=> ALTER TABLE ser ALTER id ADD GENERATED ALWAYS AS IDENTITY;
> ALTER TABLE
>
> But not very much:
>
> test=> INSERT INTO ser (id) VALUES (DEFAULT);
> ERROR: more than one owned sequence found

You also need to run

ALTER SEQUENCE ser_id_seq OWNED BY NONE;

because dropping the default doesn't release the linkage of the sequence
with the table. These are just weird artifacts of how serial is
implemented, but that's why identity columns were added to improve
things. I don't think we need to make things more complicated here.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services