My thanks to everyone who replied.
I have decided not to implement that constraint at this time. Using a compound type will make the system more complicated and less readable, plus requires installing the package which is beyond vanilla PostgreSQL.
Now I have another exclusion constraint I'm thinking about in another and I want to verify that this will do what I mean it to do:
CREATE TABLE invoice_definitions
(
id SERIAL PRIMARY KEY NOT NULL,
customer_id INTEGER NOT NULL REFERENCES customers(id),
is_default BOOLEAN NOT NULL DEFAULT FALSE,
bill_description VARCHAR(100) NOT NULL,
itemized_description VARCHAR(100) NOT NULL,
EXCLUDE USING GIST ( customer_id WITH =, is_default WITH AND )
)
;
Basically, each customer can have several rows in this table, but only one per customer is allowed to have is_default = true. Is this exclude constraint correct?
TIA,
Herouth
