Thread: Create unique field..

All,
    I'm not sure if I'm on the right track here.. I want to create a
column that is automatically filled in with a unique number when a row is
created.. I thought that creating a sequence would do this.. and then
creating a column with a default of nextval('seqname') but it isn't doing
anything.. any help is appreciated...

Thanks,
Travis L. Williams

try this:

CREATE SEQUENCE "a_name_for_the_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1  cache 1 ;

and when creating a table, add as the first column the following:

"id" int4 DEFAULT nextval('a_name_for_the_seq'::text) NOT NULL

the value of this column will be unique and autoincremental. Plz note that
you won't specify the value of this column when you'll do your INSERT.

hope this helps
bye
Giorgio A.

----- Original Message -----
From: "Williams, Travis L, NPONS" <tlw@att.com>
To: <pgsql-novice@postgresql.org>
Sent: Thursday, June 07, 2001 9:58 PM
Subject: [NOVICE] Create unique field..


> All,
> I'm not sure if I'm on the right track here.. I want to create a
> column that is automatically filled in with a unique number when a row is
> created.. I thought that creating a sequence would do this.. and then
> creating a column with a default of nextval('seqname') but it isn't doing
> anything.. any help is appreciated...
>
> Thanks,
> Travis L. Williams
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl

One other thing you need to do is to make sure that
you don't actually put some other information into
that column.  For example, let's say that you have a
table defined like this:

processdata=# create sequence foo_seq;
CREATE
processdata=# create table foo (id int default
nextval('foo_seq'), name text);
CREATE

You might want to get a little fancier and make id a
primary key or something, but this gives you the
general idea.

Now if you were to insert a record where id was set
then your default value would get overwritten:

processdata=# insert into foo (id, name) values (100,
'Jason');
INSERT 6869711 1
processdata=# insert into foo (name) values ('Earl');
INSERT 6869712 1
processdata=# select * from foo
processdata-# ;
 id  | name
-----+-------
 100 | Jason
   1 | Earl
(2 rows)

You will notice that the insert that I did where I did
not set id used the sequence without any problems.
This is useful for cases where you *normally* want to
use the sequence but where there might be exceptions.

Hope this is helpful,

Jason

--- "Williams, Travis L, NPONS" <tlw@att.com> wrote:
> All,
>     I'm not sure if I'm on the right track here.. I
> want to create a
> column that is automatically filled in with a unique
> number when a row is
> created.. I thought that creating a sequence would
> do this.. and then
> creating a column with a default of
> nextval('seqname') but it isn't doing
> anything.. any help is appreciated...
>
> Thanks,
> Travis L. Williams
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl


__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year!  http://personal.mail.yahoo.com/

On 07 Jun 2001 14:58:17 -0500, Williams, Travis L, NPONS wrote:
> All,
>       I'm not sure if I'm on the right track here.. I want to create a
> column that is automatically filled in with a unique number when a row is
> created.. I thought that creating a sequence would do this.. and then
> creating a column with a default of nextval('seqname') but it isn't doing
> anything.. any help is appreciated...
>

CREATE TEMP TABLE tempo (a text, id serial PRIMARY KEY);
INSERT INTO tempo VALUES ('foo');
INSERT INTO tempo VALUES ('bar');
SELECT * from tempo;

foo 1
bar 2

You can leave out the PRIMARY KEY, or insted use UNIQUE, or whatever ...

cu

--
 Nabil Sayegh