Re: auto_increment

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