Home > mailing lists

Thread: auto_increment

auto_increment

From

"Muhyiddin A.M Hayat"

Date:

20 September 2003, 05:57:30

How to Create auto_increment field in PostreSQL.

Can I create them using Trigger.

Re: auto_increment

From

Oliver Elphick

Date:

20 September 2003, 06:23:15

On Sat, 2003-09-20 at 06:10, Muhyiddin A.M Hayat wrote:
> How to Create auto_increment field in PostreSQL.
> Can I create them using Trigger.

Use the SERIAL datatype.  See also the functions nextval(), currval()
and setval().

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "But my God shall supply all your need according to his     riches in glory
byChrist Jesus."     Philippians 4:19

Re: auto_increment

From

"Muhyiddin A.M Hayat"

Date:

20 September 2003, 09:36:34

Ok, but if i do rollback, the auto_increment don't roolback.

How to use nextval(), currval() and setval() functions.

----- Original Message -----

From: Cavit Keskin
To: 'Muhyiddin A.M Hayat'
Sent: Saturday, September 20, 2003 2:15 PM
Subject: RE: [SQL] auto_increment

Create table tablename(
id serial,
..
);

Serial type is integer and autoincrement
When you create this table creates automatic sequence tablename_id_seq
Stored sequence last value;
Try execSQL : select * from tablename_id_seq;

My english is very very little and bad

Re: auto_increment

From

Richard Huxton

Date:

20 September 2003, 10:05:49

On Saturday 20 September 2003 09:43, Muhyiddin A.M Hayat wrote:
> Ok, but if i do rollback, the auto_increment don't roolback.

It's not supposed to.

> How to use nextval(), currval() and setval() functions.

Something like: INSERT INTO my_table(nextval('my_sequence_name'),'aaa',1);
But you'll get the same problem.

What are you trying to do with the auto-increment? If you want to guarantee 
that the numbers go up in sequence and have no gaps (e.g. 1,2,3,4,5 NOT 
1,2,4,6) then you'll need to do some more work.

Think about what you want to have happen when three clients insert rows at the 
same time and one rolls back. Once you've decided what you want, ask again if 
you need some help.

--  Richard Huxton Archonet Ltd

Re: auto_increment

From

"Muhyiddin A.M Hayat"

Date:

20 September 2003, 11:01:04

Where/How can i put this below sql statement, to set value of
guest_guest_id_seq before i do insert to table SELECT setval('guest_guest_id_seq', max(guest.guest_id)) FROM guest;

i have been try

CREATE TRIGGER "before_insert_guest_update_room_number" BEFORE INSERT
ON "public"."guest" FOR EACH ROW
EXECUTE PROCEDURE "public"."generate_guest_id"();

but error



----- Original Message ----- 
From: "Richard Huxton" <dev@archonet.com>
To: "Muhyiddin A.M Hayat" <middink@indo.net.id>; <pgsql-sql@postgresql.org>
Sent: Saturday, September 20, 2003 6:05 PM
Subject: Re: [SQL] auto_increment


> On Saturday 20 September 2003 09:43, Muhyiddin A.M Hayat wrote:
> > Ok, but if i do rollback, the auto_increment don't roolback.
>
> It's not supposed to.
>
> > How to use nextval(), currval() and setval() functions.
>
> Something like:
>   INSERT INTO my_table(nextval('my_sequence_name'),'aaa',1);
> But you'll get the same problem.
>
> What are you trying to do with the auto-increment? If you want to
guarantee
> that the numbers go up in sequence and have no gaps (e.g. 1,2,3,4,5 NOT
> 1,2,4,6) then you'll need to do some more work.
>
> Think about what you want to have happen when three clients insert rows at
the
> same time and one rolls back. Once you've decided what you want, ask again
if
> you need some help.
>
> -- 
>   Richard Huxton
>   Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Re: auto_increment

From

Martin Marques

Date:

20 September 2003, 11:17:09

El Sáb 20 Sep 2003 03:23, Oliver Elphick escribió:
> On Sat, 2003-09-20 at 06:10, Muhyiddin A.M Hayat wrote:
> > How to Create auto_increment field in PostreSQL.
> > Can I create them using Trigger.
>
> Use the SERIAL datatype.  See also the functions nextval(), currval()
> and setval().

Also to add, the auto increment is done through sequences.

I would suggest seeing the docs on SEQUENCE, and SERIAL data type.

--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica                      Universidad Nacional
        del Litoral 
-----------------------------------------------------------------

Re: auto_increment

From

Martin Marques

Date:

20 September 2003, 11:26:22

Why do you want it to rollback?

El Sáb 20 Sep 2003 05:43, Muhyiddin A.M Hayat escribió:
> Ok, but if i do rollback, the auto_increment don't roolback.
> How to use nextval(), currval() and setval() functions.
>
> ----- Original Message -----
>   From: Cavit Keskin
>   To: 'Muhyiddin A.M Hayat'
>   Sent: Saturday, September 20, 2003 2:15 PM
>   Subject: RE: [SQL] auto_increment
>
>
>   Create table tablename(
>
>     id serial,
>
>     ....
>
>   );

--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica                      Universidad Nacional
        del Litoral 
-----------------------------------------------------------------

Re: auto_increment

From

Richard Huxton

Date:

20 September 2003, 11:56:26

On Saturday 20 September 2003 11:14, Muhyiddin A.M Hayat wrote:
> Where/How can i put this below sql statement, to set value of
> guest_guest_id_seq before i do insert to table
>   SELECT setval('guest_guest_id_seq', max(guest.guest_id)) FROM guest;
>
> i have been try
>
> CREATE TRIGGER "before_insert_guest_update_room_number" BEFORE INSERT
> ON "public"."guest" FOR EACH ROW
> EXECUTE PROCEDURE "public"."generate_guest_id"();
>
> but error

The whole idea of sequences is that you don't need to keep altering them. 
Usual usage would be something like:

INSERT INTO guest (guest_id, room_number) 
VALUES (nextval('guest_guest_id_seq'), 123);

Or, if you have defined guest_id as a SERIAL (which just sets DEFAULT to the 
nextval() call for you).

INSERT INTO guest (guest_id, room_number)
VALUES (DEFAULT, 123);
or
INSERT INTO guest (room_number)
VALUES (123);

So long as you always use the sequence, then guest_id will get a different 
number each time.

If you already have some entries in guest, and create the sequence later, then 
before you start you'll want to call setval(), but you'll only need to do 
this once, to skip the numbers you have already used.

Does that make it clearer?
--  Richard Huxton Archonet Ltd

Re: auto_increment

From

Christopher Browne

Date:

20 September 2003, 14:10:19

In the last exciting episode, middink@indo.net.id ("Muhyiddin A.M Hayat") wrote:
> Ok, but if i do rollback, the auto_increment don't roolback.

Right, it's not supposed to.

Think about the situation where you have 5 clients connecting to the
database and adding records to this table.

The current functionality of sequences means that with a little
cacheing of values, they can all be hammering the table with inserts
and never need to worry about what the other is doing.

If the increment was rolling back by one when an INSERT was rolled
back, that would mean that the cache size was just 1, and access to
that sequence would have to be serialized across all accessors, which
would slow it down incredibly.
-- 
(format nil "~S@~S" "cbbrowne" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/oses.html
"Let me blow that up a bit more for you."
-- Colin Powell, Discussing a picture of the intelligence compound in  Iraq

Re: auto_increment

From

Date:

20 September 2003, 17:24:07

> How to Create auto_increment field in PostreSQL.

Its called the SERIAL datatype:

create table test_table ( field1 serial,
constraint test_table_pkey primary key (field1));

> Can I create them using Trigger.

Yes, alternatively, but I'm told that is not recommended because the
server source code that manages the special sequence table is highly
optimized for this specific functionality.

~Berend Tober

Re: auto_increment

From

sad

Date:

22 September 2003, 05:12:16

On Saturday 20 September 2003 10:23, you wrote:
> On Sat, 2003-09-20 at 06:10, Muhyiddin A.M Hayat wrote:
> > How to Create auto_increment field in PostreSQL.
> > Can I create them using Trigger.
>
> Use the SERIAL datatype.  See also the functions nextval(), currval()
> and setval().

I believe it is better to EXPLICITLY declare a SEQUENCE and
set the default value nextval() to the field.

Re: auto_increment

From

Bruno Wolff III

Date:

22 September 2003, 14:16:20

On Mon, Sep 22, 2003 at 09:15:04 +0400, sad <sad@bankir.ru> wrote:
> On Saturday 20 September 2003 10:23, you wrote:
> > On Sat, 2003-09-20 at 06:10, Muhyiddin A.M Hayat wrote:
> > > How to Create auto_increment field in PostreSQL.
> > > Can I create them using Trigger.
> >
> > Use the SERIAL datatype.  See also the functions nextval(), currval()
> > and setval().
> 
> I believe it is better to EXPLICITLY declare a SEQUENCE and 
> set the default value nextval() to the field.

The disadvantage of that system is that the dependency isn't detected.
With serial type you won't be able to drop the sequence without
dropping the column and when you drop the table, the sequence will
get dropped as well. (The preceding applies to 7.4. Things may not
be checked as well in 7.3.)