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:
Show quoted text
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