Thread: indexing
Hi, I am new to postgresql, Please can you guide me: I have created a table and I would like all the entries to be indexed with a number when the data is entered into the database,hence giving it a new id, every time some data is entered. e.g. I.D. | Name 1 | Jack 2 | Jill Does pgsql do this automatically? or do I have to create a seprate ID field like above, and check the database each timethe data is entered and find out the highest number then increment it by 1 and then enter the data with the incrementednumber. If it automatically indexes the names when they are entered in to the database, Please can you show me how to find this index. Thanks, Si _____________________________________________________________ Pick up your email anywhere in the world ---> http://www.remail.net
You can use the serial type to make the id, although serial only guarantees uniqueness not sequential numbers. something like: create table foo ( id serial primary key, name text ); On Mon, 26 Feb 2001, si wrote: > Hi, > > I am new to postgresql, Please can you guide me: > > I have created a table and I would like all the entries to be indexed with a number when the data is entered into the database,hence giving it a new id, every time some data is entered. > > e.g. > > I.D. | Name > 1 | Jack > 2 | Jill > > > Does pgsql do this automatically? or do I have to create a seprate ID field like above, and check the database each timethe data is entered and find out the highest number then increment it by 1 and then enter the data with the incrementednumber. > > If it automatically indexes the names when they are entered in to the database, Please can you show me how to find thisindex. > > Thanks, > Si > > _____________________________________________________________ > Pick up your email anywhere in the world ---> http://www.remail.net >
Use an sequence. Add the ID column to your create table statement and set
the default to use the next value from the sequence. This will give each
entry a unique id number. The syntax would be as follows.
create sequence customer_id start 1 increment 1 maxvalue 2147483647 minvalue
1 cache 1;
create table customer(
cust_id int unique not null default nextval('customer_id'),
last_name varchar(....
)
This will create a database sequence, and then each time you insert a
customer, if you don't specify a cust_id, it will default to the next value
in the sequence.
Tim White
----- Original Message -----
From: "si" <s@remail.net>
To: <pgsql-admin@postgresql.org>
Sent: Monday, February 26, 2001 9:04 AM
Subject: [ADMIN] indexing
> Hi,
>
> I am new to postgresql, Please can you guide me:
>
> I have created a table and I would like all the entries to be indexed with
a number when the data is entered into the database, hence giving it a new
id, every time some data is entered.
>
> e.g.
>
> I.D. | Name
> 1 | Jack
> 2 | Jill
>
>
> Does pgsql do this automatically? or do I have to create a seprate ID
field like above, and check the database each time the data is entered and
find out the highest number then increment it by 1 and then enter the data
with the incremented number.
>
> If it automatically indexes the names when they are entered in to the
database, Please can you show me how to find this index.
>
> Thanks,
> Si
>
> _____________________________________________________________
> Pick up your email anywhere in the world ---> http://www.remail.net
On Mon, 26 Feb 2001, si wrote:
> Hi,
>
> I am new to postgresql, Please can you guide me:
>
> I have created a table and I would like all the entries to be indexed with a number when the data is entered into the
database,hence giving it a new id, every time some data is entered.
>
> e.g.
>
> I.D. | Name
> 1 | Jack
> 2 | Jill
>
>
> Does pgsql do this automatically? or do I have to create a seprate ID field like above, and check the database each
timethe data is entered and find out the highest number then increment it by 1 and then enter the data with the
incrementednumber.
Well, it's not automatic, per se. What you want is to create an id
field, and use a sequence for that id field. If you use the SERIAL data
type for the id column when you create the table, the sequence will be created for you.
When you insert data, either insert nothing into the id field ( like
INSERT INTO table(name) VALUES ('Jack'), which will cause the next
sequence value to be used. Otherwise, use the function
nextval('some_table_seq') where "some_table_seq" is the name of your id
sequence during insert (use \ds to view your sequences).
This is not automatically indexed unless you make it a primary key
during creation. See the PostgreSQL docs for more info.
Jeff
--
Errors have occurred.
We won't tell you where or why.
Lazy programmers.
-- Hacking haiku