> select * from guests; > user_id | guest_id | name > ---------+----------+------------ > 1 | 1 | Mark Twain > 1 | 2 | Anna Black > 2 | 3 | John Black > (3 rows) > > I want to have a different result of insert command: > > user_id | guest_id | name > ---------+----------+------------ > 1 | 1 | Mark Twain > 1 | 2 | Anna Black > 2 | 1 | John Black > > Sequence guests_guest_id_seq is not connected with the field user_id. It > increases in spite of values of user_id. How can I solve this problem?
If possible, design your application to be happy with the way it is already. Those keys shouldn't really be user visible anyway. If you really have to have per-user guest IDs (and, I'm guessing, contiguous sequences of guest IDs) you'll have to do a fair bit of work. The usual approach seems to be using a trigger function to trap inserts and deletes and rewrite the guest_id field appropriately. If you don't need contiguous guest IDs - ie you're happy with a sequence like "1 3 4 5 8" after IDs 2, 6 and 7 have been DELETEd - then you can emulate a sequence with a per-user counter. Eg: CREATE TABLE user ( user_id SERIAL PRIMARY KEY, guest_id_ctr INTEGER ); CREATE TABLE guest ( user_id INTEGER, guest_id INTEGER, PRIMARY KEY(user_id, guest_id) ); then do inserts into guest with a sequence of operations like this (assuming the user_id of interest is "111"): UPDATE user SET guest_id_ctr = guest_id_ctr + 1 WHERE user_id = 111 RETURNING guest_id_ctr; -- Now, using the value obtained with the previous statement, say "4": INSERT INTO guest (user_id, guest_id) VALUES (111, 4); That assumes you're using a version of PostgreSQL new enough to support UPDATE ... RETURNING. If not, you need to use SELECT FOR UPDATE to obtain the value, followed by a separate UPDATE statement to actually increment it. If you do need contiguous values of guest_ids within a given user_id then you'll need to use a different approach, most likely PL/PgSQL triggers or the use of function wrappers for DML operations on the table. However, in most cases an application requirement of contiguous IDs is a design fault that should be fixed, rather than hacked around in the database. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
