EXT-Rothermel, Peter M wrote:
I have a table where I would like the primary key to be generated during the insert. Here is a simplified example: CREATE TABLE employee_type { tname varchar(10) PRIMARY KEY, id_prefix char(1) ; ... }tname | id_prefix --------------+----------worker | W manager | M executive | E CREATE TABLE employee { id varchar(10) PRIMARY KEY, type varchar(10) REFERENCES employee_type ... } When an employee of type 'worker' is inserted the id generated will have a prefix "W" followed by a 6-digit number. W000001, W000002 .. When the employee type is 'manager' the employee id is M000001, M000002 ... When the employee type is 'executive' the employee id is E000001, E000002 ... The sequences for each employee type are separate. CREATE SEQUENCE worker_seqnum MINVALUE 1 MAXVALUE 999999 ; CREATE SEQUENCE manger_seqnum MINVALUE 1 MAXVALUE 999999 ; CREATE SEQUENCE executive_seqnum MINVALUE 1 MAXVALUE 999999 ; I have thought about using the serial data type for the employee.id but I also want to automate the prepending of the { W, M, E } prefix. Any suggestions?
Do not put the worker type and worker sequence value in the same column ... you're violating normal form. Keep them in separate columns in the employee table, drop the type column from employee (it's redundant given the first character of your proposed primary key which, as I said, should be a separate column anyway), define a compound primary key (id_prefix, id) on it, and use a view for presentation in the concatenated format you desire along with the redundant (but more verbose) type value by way of a join. Secondly, you don't need the three separate sequences, but you do need to sort of roll your own based on my description appearing at
http://archives.postgresql.org/pgsql-general/2004-04/msg00940.php -- BMT -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
