Re: Row ID and auto-increment?

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