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 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. Thank you. Customers customer_id customer_name ---------------------------------------------------------------- 1 acme corp 2 hacker corp 3 dixie corp Orders order_id customer_id timestamp ---------------------------------------------------------------- 1 1 2009-07-01 14:42 1 2 2009-07-01 14:43 2 2 2009-07-01 14:44 1 3 2009-07-01 14:44 2 1 2009-07-01 14:44 3 1 2009-07-01 14:44 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
