Re: unique constraint with several null values

On Wed, Jul 20, 2016 at 1:48 PM, David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Wed, Jul 20, 2016 at 2:14 PM, Mark Lybarger <mlybarger(at)gmail(dot)com> wrote:

>> Another solution I can think of is to just use a trigger to
>> prevent the duplicate rows.

If you go that route you will need to use serializable
transactions, explicit locking, or trigger-based update of some
otherwise-unneeded column to avoid race conditions. See:

https://www.postgresql.org/docs/current/static/mvcc.html

That can be a perfectly valid option as long as you handle race
conditions somehow.

>> Any thoughts are certainly appreciated. I can't do much about
>> the data model itself right now, I need to protect the integrity
>> of the data.

Rather than unique constraints, you could add a unique index on the
COALESCE of each column with some impossible value.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company