Thread

1. Row ID and auto-increment?

   Raymond Chui <raymond.chui@noaa.gov> — 2001-02-14T21:08:12Z

   If I create a table like
   
   create table tablename (
   aNum    integer not null,
   name    varchar(10)
   );
   
   If I do select * from tablename;
   
   q1. Is there such thing rowid similar to Oracle in PostgreSQL?
   q2. How do I make aNum auto increment by 1? Need to write
   a trigger? how to write that?
   I want to enforce column aNum 0,1,2,.....n.
   I want to prevent data entry people input 0,1,4,5,8,...n.
   Thank you very much in advance!
   
   
   
   
   --Raymond
   
   

2. Re: Row ID and auto-increment?

   Dan McGrath <dmcgrath19@home.com> — 2001-02-14T23:16:13Z

   Ok, the type your looking for to auto create a sequence and increment by
   one is called SERIAL (an int4 field). And yes, pgsql has oid's. Heres
   what your table would look like with serial type's:
   
   CREATE TABLE tablename (
       item_id    SERIAL,
       name       VARCHAR(10)
   );
   
   The serial type will implicitly create a sequence which defines the
   incrememnt to step by, starting vaule, end value and so on. The field
   will have a new default value of DEFAULT NEXTVAL('tablename_seq'::text)
   or something similar.
   
   If your looking for oid's, they are always there and can be selected as
   follows:
   
   SELECT oid,ietm_id,name FROM tablename;
   
   This will list the oid's that were assigned during INSERT into the
   table. Keep in mind that oid's are not giving to VIEW's and that when
   you dump a db and restore it, the oid's are NOT preserved. So using
   oid's in any form on foreign key or table lookup/join is a bad idea!
   They should mainly be used when trying to distinguish between similar
   values in a table (ie: in case some fields are the same, the oid will
   always be unique).
   
   Hope this helps....
   
   
   Dan
   
   Raymond Chui wrote:
   
   > If I create a table like
   >
   > create table tablename (
   > aNum    integer not null,
   > name    varchar(10)
   > );
   >
   > If I do select * from tablename;
   >
   > q1. Is there such thing rowid similar to Oracle in PostgreSQL?
   > q2. How do I make aNum auto increment by 1? Need to write
   > a trigger? how to write that?
   > I want to enforce column aNum 0,1,2,.....n.
   > I want to prevent data entry people input 0,1,4,5,8,...n.
   > Thank you very much in advance!
   >
   > --Raymond
   
   
   

3. Re: Row ID and auto-increment?

   Brent R. Matzelle <bmatzelle@yahoo.com> — 2001-02-20T15:34:20Z

   You can create an auto incrementing field with SERIAL.  Take a
   look at the FAQ
   (http://www.postgresql.org/docs/faq-english.html#4.16.1).  
   
   Brent
   
   --- Raymond Chui <raymond.chui@noaa.gov> wrote:
   > If I create a table like
   > 
   > create table tablename (
   > aNum    integer not null,
   > name    varchar(10)
   > );
   > 
   > If I do select * from tablename;
   > 
   > q1. Is there such thing rowid similar to Oracle in PostgreSQL?
   > q2. How do I make aNum auto increment by 1? Need to write
   > a trigger? how to write that?
   > I want to enforce column aNum 0,1,2,.....n.
   > I want to prevent data entry people input 0,1,4,5,8,...n.
   > Thank you very much in advance!
   > 
   > 
   > 
   > 
   > --Raymond
   > 
   > > begin:vcard 
   > n:Chui;Raymond
   > tel;fax:(301)713-0963
   > tel;work:(301)713-0624 Ext. 168
   > x-mozilla-html:TRUE
   > url:http://members.xoom.com/rchui/
   > org:NWS, NOAA
   > version:2.1
   > email;internet:Raymond.Chui@noaa.gov
   > title:SA, DBA
   > note:ICQ #: 16722494
   > adr;quoted-printable:;;NOAA, NWS, Office of Hydrology,
   > OH=0D=0A1325 East-West Highway, Room 8112;Silver
   > Spring;MD;20910-3283;U.S.A.
   > x-mozilla-cpt:;-6384
   > fn:Raymond Chui
   > end:vcard
   > 
   
   
   __________________________________________________
   Do You Yahoo!?
   Get personalized email addresses from Yahoo! Mail - only $35 
   a year!  http://personal.mail.yahoo.com/