How can you have a combined primary key for one table?

Question

I have exactly the same question as posted here.
But in my case I do not use MySQL but Postgres. Can this be done with Postgres? All things indicate I can not use a combined index in Postgres, so assuming it can not: what solution would be most recommended?

---

Original question:

I have a table that contains information on invoices of a company. However, this company has two branches, and each of them has a unique invoicing sequence; a "Serie A" and "Serie B", so to speak. However, this is one single company and I do not want to create two invoice tables. Rather, I somehow want to have two different auto-increments for one table. (...)

What I am doing right now is not using the primary key as invoice number (which would be ideal), but rather using a secondary column with the invoice id, which is incremented manually (well, using a PHP script, but it's still not automatic), by checking the latest invoice for that particular series.

This is my current setup:

CREATE TABLE `invoices` (
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `invoicenumber` mediumint unsigned NOT NULL,
  `branch` enum('A','B') NOT NULL,
  `date` date NOT NULL,
  `client` varchar(100) NOT NULL
) COMMENT='' ENGINE='InnoDB';

To check the latest invoice, I run:

SELECT MAX(invoicenumber+1) AS new_invoice_number FROM invoices WHERE branch = 'A'

Answer 1

Postgres has sequences, and you can definitely use more than 1 of them for the same table. For example, the following code generates value for invoiceNumber column based on invoiceType in the trigger :

create sequence seq_1;
create sequence seq_2;

create table table1(invoiceType varchar(10) not null,
invoiceNumber int not null,
constraint PK_table1 PRIMARY KEY (invoiceType,invoiceNumber),
constraint CHK_invoiceType CHECK (invoiceType IN ('Type1','Type2')));

CREATE OR REPLACE FUNCTION table1_seq_generator() RETURNS trigger AS $body_start$
    BEGIN
        IF NEW.invoiceType = 'Type1' THEN 
          NEW.invoiceNumber := nextval('seq_1');
        ELSIF NEW.invoiceType ='Type2' THEN
          NEW.invoiceNumber := nextval('seq_2');
        END IF;
        RETURN NEW;
    END;
$body_start$ LANGUAGE plpgsql;

CREATE TRIGGER biTable1 BEFORE INSERT ON table1
FOR EACH ROW
WHEN (NEW.invoiceNumber IS NULL) 
   EXECUTE PROCEDURE table1_seq_generator();

---------------Testing ----------------------------
   INSERT INTO table1(invoiceType) values ('Type1');
   INSERT INTO table1(invoiceType) values ('Type2');
   INSERT INTO table1(invoiceType) values ('Type1');
   INSERT INTO table1(invoiceType) values ('Type2');
   SELECT * FROM table1;

You don't necessary have to do it in trigger, application code itself can get nextval for different sequence.

Also, I'd not use invoiceType,invoiceNumber as primary key constraint, but rather have surrogate pk and unique constraint on invoiceType,invoiceNumber (or branch,invoiceNumber according to your naming ).