Lists: | pgsql-general |
---|
From: | Andreas Wenk <a(dot)wenk(at)netzmeister-st-pauli(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | ALTER TABLE with TYPE serial does not work |
Date: | 2009-01-31 23:16:58 |
Message-ID: | 4984DBEA.7090301@netzmeister-st-pauli.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: ALTER TABLE with TYPE serial does not work |
Date: | 2009-02-01 02:45:31 |
Message-ID: | gm32cb$vkk$3@reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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.
From: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com> |
---|---|
To: | Andreas Wenk <a(dot)wenk(at)netzmeister-st-pauli(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: ALTER TABLE with TYPE serial does not work |
Date: | 2009-02-01 04:04:40 |
Message-ID: | 396486430901312004g50a68c15q33b7678029132861@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com> |
Cc: | Andreas Wenk <a(dot)wenk(at)netzmeister-st-pauli(dot)de>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: ALTER TABLE with TYPE serial does not work |
Date: | 2009-02-01 10:36:40 |
Message-ID: | dcc563d10902010236h1fe8ce9fj4917467d102a5395@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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.
From: | Grzegorz JaĆkiewicz <gryzman(at)gmail(dot)com> |
---|---|
To: | Andreas Wenk <a(dot)wenk(at)netzmeister-st-pauli(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: ALTER TABLE with TYPE serial does not work |
Date: | 2009-02-01 11:05:17 |
Message-ID: | 2f4958ff0902010305x120b0ae8p7e687b0d4850eb2c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Osvaldo Kussama <osvaldo(dot)kussama(at)gmail(dot)com> |
---|---|
To: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
Cc: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com>, Andreas Wenk <a(dot)wenk(at)netzmeister-st-pauli(dot)de>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: ALTER TABLE with TYPE serial does not work |
Date: | 2009-02-01 13:12:53 |
Message-ID: | 690707f60902010512q73dacca9rccc7ebceced6e406@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Andreas Wenk <a(dot)wenk(at)netzmeister-st-pauli(dot)de> |
---|---|
To: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: ALTER TABLE with TYPE serial does not work |
Date: | 2009-02-01 20:08:36 |
Message-ID: | 49860144.1000405@netzmeister-st-pauli.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Andreas Wenk <a(dot)wenk(at)netzmeister-st-pauli(dot)de> |
---|---|
To: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: ALTER TABLE with TYPE serial does not work |
Date: | 2009-02-01 20:16:17 |
Message-ID: | 49860311.5020805@netzmeister-st-pauli.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Andreas Wenk <a(dot)wenk(at)netzmeister-st-pauli(dot)de> |
---|---|
To: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: ALTER TABLE with TYPE serial does not work |
Date: | 2009-02-01 20:23:30 |
Message-ID: | 498604C2.2060108@netzmeister-st-pauli.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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