Create one sequence per owner and write a function/trigger that will check the owner id for the record and call the appropriate nextval(sequence) to get the next value for that owner and replace the id with the generated value. If new owner IDs are going to be generated, however, this will not be maintainable. I would seriously question whether what you are asking is truly necessary. Using the owner ID a timestamp field you can retrieve the records from this table in the same order they were added. You can then calculate the "row number" in the resultant query to get the sequencing desired. Thus you can use the sequence as it was designed to generate a unique integer value for the table whenever necessary (unique, but not necessarily sequential/without-gaps). You are trying to give contextual meaning to the values generated by serial/sequence but that is not what they are designed for. If you really need to create the "index id" at the time of record creation you should write you own ID generator routine - making sure you deal with concurrency issues. David J. -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Zhi-Qiang Lei Sent: Friday, February 25, 2011 8:10 AM To: pgsql-general@xxxxxxxxxxxxxx Subject: The scope of sequence Dear All, I've got a composite key in a table. The keys are ID and owner ID. The type of the ID should be a serial, additionally I hope the ID to be increasable per owner ID. ( The ID will be increased with the different owner ID) Does anyone know how to implement it? Thanks in advance. Best regards, Zhi-Qiang Lei zhiqiang.lei@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
