Re: [BUGS] BUG #14827: "ALTER TABLE... IF NOT EXISTS...ADD..BIGSERIAL" leaves extra sequences

On Tue, Sep 26, 2017 at 10:04 AM, Fabrízio de Royes Mello <fabriziomello@gmail.com> wrote:
>
> On Mon, Sep 25, 2017 at 5:45 AM, <hvisage@gmail.com> wrote:
> >
> > The following bug has been logged on the website:
> >
> > Bug reference:      14827
> > Logged by:          Hendrik Visage
> > Email address:      hvisage@gmail.com
> > PostgreSQL version: 9.6.5
> > Operating system:   Debian 9
> > Description:
> >
> > Goodday,
> >  With an alter table to add an UID field if it doesn't exist, the process
> > creates a sequence, but when the column does exist, the created sequence,
> > that isn't used, is just left there, and subsequent runs keeps adding extra
> > sequences.
> >
> > postgres@tracsdbhvt01:~$ cat test-serial.sql
> > create database test;
> > \c test
> > create table test_serial ( teststring varchar(5));
> > alter table test_serial add column if not exists uid BIGSERIAL;
> > alter table test_serial add column if not exists uid BIGSERIAL;
> > \d
> > \d test_serial
> > postgres@tracsdbhvt01:~$ psql -p 5433 < test-serial.sql
> > CREATE DATABASE
> > You are now connected to database "test" as user "postgres".
> > CREATE TABLE
> > ALTER TABLE
> > NOTICE:  column "uid" of relation "test_serial" already exists, skipping
> > ALTER TABLE
> >                   List of relations
> >  Schema |         Name         |   Type   |  Owner
> > --------+----------------------+----------+----------
> >  public | test_serial          | table    | postgres
> >  public | test_serial_uid_seq  | sequence | postgres
> >  public | test_serial_uid_seq1 | sequence | postgres
> > (3 rows)
> >
> >                                   Table "public.test_serial"
> >    Column   |         Type         |                         Modifiers
> > ------------+----------------------+-----------------------------------------------------------
> >  teststring | character varying(5) |
> >  uid        | bigint               | not null default
> > nextval('test_serial_uid_seq'::regclass)
> >
> > postgres@tracsdbhvt01:~$
> >
> >
>
> That's awful... I'll take a look into it.
>