No serial type

On Tue, Nov 18, 2008 at 10:24 AM, Simon Connah
<simon.n.connah@btopenworld.com> wrote:

Hi,
I've just started using PostgreSQL and have been reading up on it. Part of
what I wanted to do was to have a column which automatically incremented
itself by one every time I do an INSERT. From reading the manual I was lead
to believe that the correct type for this was either serial or bigserial.
But whenever I try and update my column to this type it says that it can not
find that data type. Am I doing something wrong?

Serial is a "pseudotype". It represents creating an int or bigint and
a sequence then assigning a default value for the column and setting
dependency in the db so the sequence will be dropped when the table
gets dropped. If you don't want to recreate the table, you can do
this:

create table a (i int primary key, info text);
create sequence a_i_seq;
alter table a alter column i set default nextval('a_i_seq');

not sure the alter table syntax is spot on, haven't used it in a few months.

On 18/11/2008 17:37, Scott Marlowe wrote:

create table a (i int primary key, info text);
create sequence a_i_seq;
alter table a alter column i set default nextval('a_i_seq');

You'll also need to grant SELECT and UPDATE permissions on the sequence
to the user that'll be INSERTing into the table.....this catches me
regularly. :-)

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

On 18 Nov 2008, at 17:40, Raymond O'Donnell wrote:

On 18/11/2008 17:37, Scott Marlowe wrote:

create table a (i int primary key, info text);
create sequence a_i_seq;
alter table a alter column i set default nextval('a_i_seq');

You'll also need to grant SELECT and UPDATE permissions on the
sequence
to the user that'll be INSERTing into the table.....this catches me
regularly. :-)

Ray.

Thanks guys. Looks like I misunderstood the manual. This is the first
time I've worked with
SQL and "proper" databases so still feeling a bit lost.

Simon.

"I disapprove of what you say, but I'll defend to the death your right
to say it." - Voltaire

"Scott Marlowe" <scott.marlowe@gmail.com> writes:

On Tue, Nov 18, 2008 at 10:24 AM, Simon Connah
<simon.n.connah@btopenworld.com> wrote:

But whenever I try and update my column to this type it says that it can not
find that data type. Am I doing something wrong?

Serial is a "pseudotype".

Perhaps better to say it's a macro, which is currently recognized by
column creation but not ALTER COLUMN TYPE. In recent versions you could
do "ALTER TABLE ADD COLUMN foo serial", if that helps.

I'm not sure if it'd be sensible to allow "ALTER COLUMN TYPE serial";
it seems like that might do more than you were expecting. ALTER TYPE
is a pretty low-level operation, imo.

regards, tom lane

Scott Marlowe wrote:

Serial is a "pseudotype". It represents creating an int or bigint and
a sequence then assigning a default value for the column and setting
dependency in the db so the sequence will be dropped when the table
gets dropped. If you don't want to recreate the table, you can do
this:

create table a (i int primary key, info text);
create sequence a_i_seq;
alter table a alter column i set default nextval('a_i_seq');

You could even use "create sequence a_i_seq owned by a.i". This would
cause the sequence to be dropped when the table is dropped which I think
is the default behaviour if you create a column with type serial.

Regards,
Christian

--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-B�ckler-Stra�e 2 http://www.deriva.de
D-37079 G�ttingen

Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer

On Nov 18, 2008, at 10:37 AM, Tom Lane wrote:

"Scott Marlowe" <scott.marlowe@gmail.com> writes:

On Tue, Nov 18, 2008 at 10:24 AM, Simon Connah
<simon.n.connah@btopenworld.com> wrote:

But whenever I try and update my column to this type it says that
it can not
find that data type. Am I doing something wrong?

Serial is a "pseudotype".

Perhaps better to say it's a macro, which is currently recognized by
column creation but not ALTER COLUMN TYPE. In recent versions you
could
do "ALTER TABLE ADD COLUMN foo serial", if that helps.

I'm not sure if it'd be sensible to allow "ALTER COLUMN TYPE serial";
it seems like that might do more than you were expecting. ALTER TYPE
is a pretty low-level operation, imo.

I agree, there would also be the question of the sequence's starting
value. Some people would "expect" it to find the max value already in
the column and automatically set it's start value based on that which
may in turn surprise others.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k