From: Richard Huxton <dev@archonet.com>
To: Muhyiddin A.M Hayat <middink@indo.net.id>
To: pgsql-sql@postgresql.org
Subject: Re: auto_increment
Date: Sat, 20 Sep 2003 12:55:53 +0100
Message-ID: <200309201255.53950.dev@archonet.com> (raw)
In-Reply-To: <007901c37f60$058de880$1f00a8c0@middinkcomp>
References: <001d01c37f3e$983ed3a0$2363a8c0@ckeskin>
<200309201105.30370.dev@archonet.com>
<007901c37f60$058de880$1f00a8c0@middinkcomp>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, middink@indo.net.id
Subject: Re: auto_increment
In-Reply-To: <200309201255.53950.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox