On Sat, 22 Jan 2005, Tino Wildenhain wrote: > > INSERT INTO test (id, name) > > SELECT COALESCE(MAX(id)+1, 1), 'name' FROM test > > > > Ofcourse this gives problems when two clients are inserting a record at > > the same time. (duplicate primary keys) But, i can't use a sequence in my > > application (the pk consists of more than just a sequence) > > > > one solution would be to do a 'LOCK TABLE test IN SHARE MODE' before > > inserting. This solves my problem but i'm not sure if its the > > best way to deal with this kind of concurrency problems ? Is there a > > better way ? > > Of course. The solution to this problem is inherent to a good database > and has therefore been done long long ago ;) I agree that a serial would be better. But I think there are situations where a serial isn't convenient Like when you want an primary key which consists of the current year and an sequence number. Like ('05', 1), ('05', 2), ('05', 3) etc. With a sequence you must write extra code to reset the sequence every year and you get into trouble if someone inserts data from the previous year... A 'MAX(id)+1' is much simpler and cleaner then. Ralph. ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend