Обсуждение: ALTER TABLE with TYPE serial does not work
Hi List, I have a short question to psql. Why does this not work: postgres=# ALTER TABLE tab1 ALTER COLUMN nr TYPE serial; ERROR: type "serial" does not exist but this: postgres=# ALTER TABLE tab1 DROP COLUMN nr; ALTER TABLE postgres=# ALTER TABLE tab1 ADD COLUMN nr serial; NOTICE: ALTER TABLE will create implicit sequence "tab1_nr_seq" for serial column "tab1.nr" ALTER TABLE Sitting in front of a MAC OS X Leo with pg 8.2 Thanks a lot! Cheers Andy -- St.Pauli - Hamburg - Germany Andreas Wenk
On 2009-01-31, Andreas Wenk <a.wenk@netzmeister-st-pauli.de> wrote: > Hi List, > > I have a short question to psql. > > Why does this not work: > > postgres=# ALTER TABLE tab1 ALTER COLUMN nr TYPE serial; > ERROR: type "serial" does not exist > > but this: > > postgres=# ALTER TABLE tab1 DROP COLUMN nr; > ALTER TABLE > postgres=# ALTER TABLE tab1 ADD COLUMN nr serial; > NOTICE: ALTER TABLE will create implicit sequence "tab1_nr_seq" for > serial column "tab1.nr" > ALTER TABLE because serial isn't a type.
On Sat, Jan 31, 2009 at 3:16 PM, Andreas Wenk <a.wenk@netzmeister-st-pauli.de> wrote: > Why does this not work: > > postgres=# ALTER TABLE tab1 ALTER COLUMN nr TYPE serial; > ERROR: type "serial" does not exist serial is really just "short-hand" for making an integer column use default incrementing function. The following will fully explain what it is so that you can alter the column: http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html#DATATYPE-SERIAL -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On Sat, Jan 31, 2009 at 9:04 PM, Richard Broersma <richard.broersma@gmail.com> wrote: > On Sat, Jan 31, 2009 at 3:16 PM, Andreas Wenk > <a.wenk@netzmeister-st-pauli.de> wrote: > >> Why does this not work: >> >> postgres=# ALTER TABLE tab1 ALTER COLUMN nr TYPE serial; >> ERROR: type "serial" does not exist > > serial is really just "short-hand" for making an integer column use > default incrementing function. The following will fully explain what > it is so that you can alter the column: > http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html#DATATYPE-SERIAL Seeing as it works with adding a column, and I've seen instructions for creating a sequence, and then adding a dependency into the system tables, it's quite reasonable to expect that one day it will work with alter table alter column. But it's probably more complicated than just making it a serial type, there's probably some question of setting the sequence according to the max value in the table. I'd be surprised if it's not on the TODO list somewhere.
all you have to really do is:
create sequence foo_bar_new_column_tralala_seq;
ALTER TABLE foo_bar ADD COLUMN tralala int NOT NULL DEFAULT
nextval('foo_bar_new_column_tralala_seq');
That's all there's to it
2009/2/1 Scott Marlowe <scott.marlowe@gmail.com>: > On Sat, Jan 31, 2009 at 9:04 PM, Richard Broersma > <richard.broersma@gmail.com> wrote: >> On Sat, Jan 31, 2009 at 3:16 PM, Andreas Wenk >> <a.wenk@netzmeister-st-pauli.de> wrote: >> >>> Why does this not work: >>> >>> postgres=# ALTER TABLE tab1 ALTER COLUMN nr TYPE serial; >>> ERROR: type "serial" does not exist >> >> serial is really just "short-hand" for making an integer column use >> default incrementing function. The following will fully explain what >> it is so that you can alter the column: >> http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html#DATATYPE-SERIAL > > ... But it's probably more complicated than > just making it a serial type, there's probably some question of > setting the sequence according to the max value in the table. I'd be > surprised if it's not on the TODO list somewhere. > Like: SELECT setval('serial', max(id)) FROM distributors; ? http://www.postgresql.org/docs/current/interactive/sql-createsequence.html Osvaldo
Jasen Betts schrieb: > On 2009-01-31, Andreas Wenk <a.wenk@netzmeister-st-pauli.de> wrote: >> Hi List, >> >> I have a short question to psql. >> >> Why does this not work: >> >> postgres=# ALTER TABLE tab1 ALTER COLUMN nr TYPE serial; >> ERROR: type "serial" does not exist >> >> but this: >> >> postgres=# ALTER TABLE tab1 DROP COLUMN nr; >> ALTER TABLE >> postgres=# ALTER TABLE tab1 ADD COLUMN nr serial; >> NOTICE: ALTER TABLE will create implicit sequence "tab1_nr_seq" for >> serial column "tab1.nr" >> ALTER TABLE > > because serial isn't a type. > ah - I think this is what the error message says ;-/ ... ?? !! -- St.Pauli - Hamburg - Germany Andreas Wenk
Richard Broersma schrieb: > On Sat, Jan 31, 2009 at 3:16 PM, Andreas Wenk > <a.wenk@netzmeister-st-pauli.de> wrote: > >> Why does this not work: >> >> postgres=# ALTER TABLE tab1 ALTER COLUMN nr TYPE serial; >> ERROR: type "serial" does not exist > > serial is really just "short-hand" for making an integer column use > default incrementing function. The following will fully explain what > it is so that you can alter the column: > http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html#DATATYPE-SERIAL > > Thanks Richard, that helped. I thought maybe there is another reason because when I create a table and use serial as "type" (like I would when I use integer) it works well. I know that searial is just for my convenience as written in the manual. I failed to understand, that it is not really a type. Cheers Andy -- St.Pauli - Hamburg - Germany Andreas Wenk
Scott Marlowe schrieb: > On Sat, Jan 31, 2009 at 9:04 PM, Richard Broersma > <richard.broersma@gmail.com> wrote: >> On Sat, Jan 31, 2009 at 3:16 PM, Andreas Wenk >> <a.wenk@netzmeister-st-pauli.de> wrote: >> >>> Why does this not work: >>> >>> postgres=# ALTER TABLE tab1 ALTER COLUMN nr TYPE serial; >>> ERROR: type "serial" does not exist >> serial is really just "short-hand" for making an integer column use >> default incrementing function. The following will fully explain what >> it is so that you can alter the column: >> http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html#DATATYPE-SERIAL > > Seeing as it works with adding a column, and I've seen instructions > for creating a sequence, and then adding a dependency into the system > tables, it's quite reasonable to expect that one day it will work with > alter table alter column. But it's probably more complicated than > just making it a serial type, there's probably some question of > setting the sequence according to the max value in the table. I'd be > surprised if it's not on the TODO list somewhere. Thanks for this Scott. For me as a user it would be cool to have it ... hopefully it's on a TODO list ;-). On the other hand I don't think that this case will show up too often because the decision to have a column in a table with a incrementing sequence should be made while designing the database structure ... Cheers Andy -- St.Pauli - Hamburg - Germany Andreas Wenk