Thread: Weirdess when altering serial column type

Should that sequence really stick around as an integer, numeric and text 
field???


test=# create table test (a serial);
NOTICE:  CREATE TABLE will create implicit sequence "test_a_seq" for 
serial column "test.a"
NOTICE:  CREATE TABLE will create implicit sequence "test_a_seq" for 
serial column "test.a"
CREATE TABLE
test=# \d test                          Table "public.test" Column |  Type   |                      Modifiers
--------+---------+----------------------------------------------------- a      | integer | not null default
nextval('public.test_a_seq'::text)

test=# alter table test alter a type integer;
ALTER TABLE
test=# \d test                          Table "public.test" Column |  Type   |                      Modifiers
--------+---------+----------------------------------------------------- a      | integer | not null default
nextval('public.test_a_seq'::text)

test=# alter table test alter a type numeric;
ALTER TABLE
test=# \d test                          Table "public.test" Column |  Type   |                      Modifiers
--------+---------+----------------------------------------------------- a      | numeric | not null default
nextval('public.test_a_seq'::text)

test=# alter table test alter a type text;
ALTER TABLE
test=# \d test                         Table "public.test" Column | Type |                      Modifiers
--------+------+----------------------------------------------------- a      | text | not null default
nextval('public.test_a_seq'::text)

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Should that sequence really stick around as an integer, numeric and text 
> field???

What are you unhappy about exactly?  We expended a fair amount of sweat
to make it behave just like that ...
        regards, tom lane

>>Should that sequence really stick around as an integer, numeric and text 
>>field???
> 
> What are you unhappy about exactly?  We expended a fair amount of sweat
> to make it behave just like that ...

It's confused the odd IRC user (pgsql newbie).  Seems like it breaks the 
'serial type' illusion...

I presume they have to drop the default, then drop the sequence to get 
rid of it.

Chris

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> I presume they have to drop the default, then drop the sequence to get 
> rid of it.

Hmm.  Right at the moment I don't think you *can* get rid of it, short
of dropping the column altogether.

regression=# create table z(f1 serial);
NOTICE:  CREATE TABLE will create implicit sequence "z_f1_seq" for serial column "z.f1"
CREATE TABLE
regression=# drop sequence z_f1_seq;
ERROR:  cannot drop sequence z_f1_seq because table z column f1 requires it
HINT:  You may drop table z column f1 instead.
regression=# alter table z alter f1 drop default;
ALTER TABLE
regression=# drop sequence z_f1_seq;
ERROR:  cannot drop sequence z_f1_seq because table z column f1 requires it
HINT:  You may drop table z column f1 instead.

Possibly it would be better if the implicit dependency led from the
sequence to the column default rather than directly to the column ...
but I'm too tired to think of the possible consequences.  I think we
may have considered and rejected that idea ...
        regards, tom lane

Any further thoughts on this?

Tom Lane wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> 
>>I presume they have to drop the default, then drop the sequence to get 
>>rid of it.
> 
> 
> Hmm.  Right at the moment I don't think you *can* get rid of it, short
> of dropping the column altogether.
> 
> regression=# create table z(f1 serial);
> NOTICE:  CREATE TABLE will create implicit sequence "z_f1_seq" for serial column "z.f1"
> CREATE TABLE
> regression=# drop sequence z_f1_seq;
> ERROR:  cannot drop sequence z_f1_seq because table z column f1 requires it
> HINT:  You may drop table z column f1 instead.
> regression=# alter table z alter f1 drop default;
> ALTER TABLE
> regression=# drop sequence z_f1_seq;
> ERROR:  cannot drop sequence z_f1_seq because table z column f1 requires it
> HINT:  You may drop table z column f1 instead.
> 
> Possibly it would be better if the implicit dependency led from the
> sequence to the column default rather than directly to the column ...
> but I'm too tired to think of the possible consequences.  I think we
> may have considered and rejected that idea ...
> 
>             regards, tom lane