Thread: Problem with identity column & related sequences
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
Jeff Hoffmann <jeff@propertykey.com> writes:
> 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;
You're right, this sort of thing does not work very well in v12 and
before. We fixed it in v13, but the changes seemed far too invasive
to risk a back-patch [1].
regards, tom lane
[1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=1281a5c90
Thanks. I was sort of expecting that answer but I didn't see where it was addressed specifically. Unfortunately I'm stuck on v12 for the time being so I guess it's back to the workaround. On Tue, Sep 28, 2021 at 2:13 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Jeff Hoffmann <jeff@propertykey.com> writes: > > 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; > > You're right, this sort of thing does not work very well in v12 and > before. We fixed it in v13, but the changes seemed far too invasive > to risk a back-patch [1]. > > regards, tom lane > > [1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=1281a5c90 -- Jeff Hoffmann Head Plate Spinner PropertyKey Office: 612-800-6444 jeff@propertykey.com