I have a table person which I am planing to normalize to the sixth normal form. The table has attributes username, email, and phone. The reason I want to go to the sixth normal form is keeping the change history of these columns and adding additional metadata to them (e.g. the confidence level about the data).

I am planning to create new tables person_username, person_email, person_phone and have the person view which brings them all together and has INSTEAD OF triggers so that users can insert and update the view and these changes get directed to the underlying tables.

My question in, how to enforce UNIQUE constraints when using the sixth normal form. The structure of the person_email table would look something like this:

CREATE TABLE person_email (
    person_id integer FOREIGN KEY person(id),
    username text NOT NULL,
    valid_from datetime

The data in the table might look like this:

person_id username valid_from
1 foo 2022-01-01
1 bar 2021-06-01
2 foo 2020-08-01
2 abc 2020-02-01

The rows marked as bold hold the most recent (the current) information. Both users 1 and 2 have the current username set to foo which should not be allowed. However, I cannot make username UNIQUE, since the fact that someone had used that name in the distant past does not mean that someone else could not use it now.

What I would need would be a UNIQUE (username) constraint which would only look at the most recent row for each user. How to achieve that? Is there a way to make use of the PostgreSQL EXCLUDE constraint?


1 Answer 1


Yes, you can use an exclusion constraint.

You'll need to install the additional module btree_gist to add an operator class we need for the multicolumn index backing the constraint.

There is no data type "datetime" like you display. Your values indicate date, so I'll go with that. (The actual data type matters for the implementation!)

Also, your table design with just valid_from generally works. valid_to can be derived from the next row. But for the purpose of an exclusion constraint, we need valid_to in the same row. So add that (redundantly).

ALTER TABLE person_email ADD CONSTRAINT person_email_no_username_overlap
EXCLUDE USING gist (username WITH =, daterange(valid_from, valid_to, '[]') WITH &&);

This way, the same username can never be in use in overlapping time ranges.

Note how I made upper and lower bound inclusive with '[]'. Adapt to your needs. There are subtle differences for different data types.

NULL as lower or upper bound means "unbounded".

Alternatively, you could use a daterange column to begin with. Either has pros and cons.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.