On Wed, Jul 1, 2009 at 6:04 PM, Merrick<merrick@xxxxxxxxx> wrote: > I have been using postgresql for 8 years in web projects and ran into > a problem that I could not find a solution for in the archives or > through Google. > > Here is a generalized example of what I want to happen. I have a > customers table, and an orders table. I would like for each customer > to have orders that start at 1 and move up sequentially. I realize > it's probably not efficient to create a new sequence for each Yeah, plus sequences aren't guaranteed to always give a gapless sequence due to rollbacks etc. > customer, so am looking for alternate ways to accomplish the same > thing. Below is an illustrated example of the outcome I would like. I > would also like similar functionality to a sequence so duplicate > order_id's are not generated. Please keep in mind that for what I am > developing, having each customer's orders start at 1 is more of a need > than a want. The simplest method is to do something like: begin; select * from sometable where cust_id=99 order by order_id desc for update; to lock all the customer records for cust_id 99, then take the first record, which should have the highest order_id, grab that increment it and then insert the new record and commit; the transaction. Assuming your customers aren't ordering dozens of things a second, this should work with minimal locking contention. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
