Thread: ALTER TABLE with TYPE serial does not work

ALTER TABLE with TYPE serial does not work

From

Andreas Wenk

Date:

31 January 2009, 19:17:06

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

Re: ALTER TABLE with TYPE serial does not work

From

Jasen Betts

Date:

31 January 2009, 22:45:41

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.

Re: ALTER TABLE with TYPE serial does not work

From

Richard Broersma

Date:

01 February 2009, 00:04:48

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

Re: ALTER TABLE with TYPE serial does not work

From

Scott Marlowe

Date:

01 February 2009, 06:54:07

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.

Re: ALTER TABLE with TYPE serial does not work

From

Grzegorz Jaśkiewicz

Date:

01 February 2009, 07:07:53

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

Re: ALTER TABLE with TYPE serial does not work

From

Osvaldo Kussama

Date:

01 February 2009, 09:13:24

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

Re: ALTER TABLE with TYPE serial does not work

From

Andreas Wenk

Date:

01 February 2009, 16:08:51

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

Re: ALTER TABLE with TYPE serial does not work

From

Andreas Wenk

Date:

01 February 2009, 16:16:24

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

Re: ALTER TABLE with TYPE serial does not work

From

Andreas Wenk

Date:

01 February 2009, 16:23:38


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