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

On Mon, Sep 25, 2017 at 5:45 AM, <hvisage(at)gmail(dot)com> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 14827
> Logged by: Hendrik Visage
> Email address: hvisage(at)gmail(dot)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(at)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(at)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(at)tracsdbhvt01:~$
>
>

That's awful... I'll take a look into it.

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello