Thread: 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