I am using postgresql-12.8. I am using I am making use of an identity
column for part of a scripts to process some updated data. Because of
the way the script is called I don't necessarily know if this column
is going to exist in the table I am working on so I have a step that
will conditionally create the column if it doesn't already exist, i.e.
alter table mytable add column if not exists unique_id integer
generated always as identity;
This works great if the unique_id column doesn't exist. If the column
does exist, I get the notice
NOTICE: column "unique_id" of relation "mytable" already exists, skipping
ALTER TABLE
As far as the messages are concerned everything worked as expected.
The problem is that even though the column already exists it skipped
the first part of the command it and seems to have followed through at
least a portion of the second part and created a second sequence to
handle the generated identity value even though an existing sequence
already exists for the existing column. Then when I try to update the
table I end up getting an error
ERROR: more than one owned sequence found
which I guess makes sense based on what happened but it seems like the
"if not exists" should short circuit the whole thing and result in
nothing changing. Now I'm stuck & I have to effectively drop the
column and re-add the column. I found some references to other "more
than one owned sequence" issues from a couple of years back but this
seems to be a different issue. My question is whether this is the
expected behavior and if so is there another way to get what I want
from a similar command (or commands)? Right now I'm going through a
rather clunky plpgsql function to check if the column exists instead
of relying on the "if not exists logic".
--
Jeff Hoffmann
PropertyKey