pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Allardyce <dave@pod13.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Table Constraints with NULL values
Date: Fri, 19 Oct 2001 02:53:39 -0400
Message-ID: <11522.1003474419@sss.pgh.pa.us> (raw)
In-Reply-To: <026601c15849$b4f38680$6100a8c0@pod13.com>
References: <026601c15849$b4f38680$6100a8c0@pod13.com>

"David Allardyce" <dave@pod13.net> writes:
> However, shouldn't any values that are not NULL violate the constraint if
> the same values exist already?

No.  Postgres is conforming to the SQL standard in this.  SQL92 saith
in section 4.10:

         A unique constraint is satisfied if and only if no two rows in
         a table have the same non-null values in the unique columns. In
         addition, if the unique constraint was defined with PRIMARY KEY,
         then it requires that none of the values in the specified column or
         columns be the null value.

There's a slightly different statement in the definition of the UNIQUE
predicate, section 8.9:

         2) If there are no two rows in T such that the value of each column
            in one row is non-null and is equal to the value of the cor-
            responding column in the other row according to Subclause 8.2,
            "<comparison predicate>", then the result of the <unique predi-
            cate> is true; otherwise, the result of the <unique predicate>
            is false.

AFAICT the intent is that all the comparison columns must be non-null
(and equal) for two rows to be considered to violate the uniqueness
constraint.

The standard *does* use the concept that you are after: section
3.1 Definitions saith

         h) distinct: Two values are said to be not distinct if either:
            both are the null value, or they compare equal according to
            Subclause 8.2, "<comparison predicate>". Otherwise they are
            distinct. Two rows (or partial rows) are distinct if at least
            one of their pairs of respective values is distinct. Otherwise
            they are not distinct. The result of evaluating whether or not
            two values or two rows are distinct is never unknown.

         i) duplicate: Two or more values or rows are said to be duplicates
            (of each other) if and only if they are not distinct.

which is terminology that they carefully refrain from using in defining
uniqueness constraints.  Had they meant what you want the behavior to
be, ISTM they'd have defined uniqueness constraints by saying "all the
rows must be distinct".


> -- Excerpt from the Postgres CREATE TABLE documentation ---
> ...
> UNIQUE Constraint
> ...
> The column definitions of the specified columns do not have to include a NOT
> NULL constraint to be included in a UNIQUE constraint. Having more than one
> null value in a column without a NOT NULL constraint, does not violate a
> UNIQUE constraint. (This deviates from the SQL92 definition, but is a more
> sensible convention. See the section on compatibility for more details.)

I believe that this statement is wrong, and that we do follow the spec.
There have been a number of arguments about this in the past though...
evidently whoever touched this doc page last had the opposite opinion.

			regards, tom lane



view thread (4+ messages)  latest in thread

Message-ID: <11522.1003474419@sss.pgh.pa.us>
Permalink:  ../11522.1003474419@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/11522.1003474419@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, dave@pod13.net
  Subject: Re: Table Constraints with NULL values
  In-Reply-To: <11522.1003474419@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox