From: Richard Huxton <dev@archonet.com>
To: "Muhyiddin A.M Hayat" <middink@indo.net.id>,
	<pgsql-sql@postgresql.org>
Subject: Re: auto_increment
Date: Sat, 20 Sep 2003 12:55:53 +0100
User-Agent: KMail/1.5
References: <001d01c37f3e$983ed3a0$2363a8c0@ckeskin>
	<200309201105.30370.dev@archonet.com>
	<007901c37f60$058de880$1f00a8c0@middinkcomp>
In-Reply-To: <007901c37f60$058de880$1f00a8c0@middinkcomp>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200309201255.53950.dev@archonet.com>

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