Row ID and auto-increment?
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
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:
Show quoted text
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
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/