Re: 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(dot)wenk(at)netzmeister-st-pauli(dot)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(dot)wenk(at)netzmeister-st-pauli(dot)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(dot)broersma(at)gmail(dot)com> wrote:
> On Sat, Jan 31, 2009 at 3:16 PM, Andreas Wenk
> <a(dot)wenk(at)netzmeister-st-pauli(dot)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(dot)marlowe(at)gmail(dot)com>:
> On Sat, Jan 31, 2009 at 9:04 PM, Richard Broersma
> <richard(dot)broersma(at)gmail(dot)com> wrote:
>> On Sat, Jan 31, 2009 at 3:16 PM, Andreas Wenk
>> <a(dot)wenk(at)netzmeister-st-pauli(dot)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(dot)wenk(at)netzmeister-st-pauli(dot)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(dot)wenk(at)netzmeister-st-pauli(dot)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(dot)broersma(at)gmail(dot)com> wrote:
>> On Sat, Jan 31, 2009 at 3:16 PM, Andreas Wenk
>> <a(dot)wenk(at)netzmeister-st-pauli(dot)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