Обсуждение: No serial type

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?

This is what I get:

simon=> ALTER TABLE users ALTER COLUMN userid TYPE serial;
ERROR:  type "serial" does not exist

I'm running this version:

simon=> SELECT version();
                                          version
-----------------------------------------------------------------------------------------
  PostgreSQL 8.3.3 on i386-unknown-openbsd4.4, compiled by GCC cc
(GCC) 3.3.5 (propolice)
(1 row)

Thank you or any help.

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

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