Обсуждение: Unique Index

Hi,

i have a unique index on a table over multiple columns. If now one of
the records has a null value in one of the indexed columns i can insert
the same record multiple times.

Is this a problem within postgres or expected?

Example:

index unique, btree (colA, colB, colC);

would still allow me to insert

AAAA, ,CCCC
AAAA, ,CCCC


Thanks for any help on that.
A

On Thu, 20 Jan 2005, Alex wrote:

> i have a unique index on a table over multiple columns. If now one of
> the records has a null value in one of the indexed columns i can insert
> the same record multiple times.
>
> Is this a problem within postgres or expected?

Expected. NULLs are effectively not considered as the same for the
purposes of UNIQUE.

The predicate basically functions as:

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.

# alex@meerkatsoft.com / 2005-01-20 01:35:32 +1100:
> i have a unique index on a table over multiple columns. If now one of
> the records has a null value in one of the indexed columns i can insert
> the same record multiple times.
>
> Is this a problem within postgres or expected?

    In SQL, NULL means "unknown value". How could you assert that two
    NULLs are equal?

--
If you cc me or remove the list(s) completely I'll most likely ignore
your message.    see http://www.eyrie.org./~eagle/faqs/questions.html

Roman Neuhauser wrote:
> # alex@meerkatsoft.com / 2005-01-20 01:35:32 +1100:
>
>>i have a unique index on a table over multiple columns. If now one of
>>the records has a null value in one of the indexed columns i can insert
>>the same record multiple times.
>>
>>Is this a problem within postgres or expected?
>
>
>     In SQL, NULL means "unknown value". How could you assert that two
>     NULLs are equal?
>
which doesn't make mathematical sense.
mathwise null is an empty result.
so setting the logic up using the math logic, null values are always equal.

--
========================================

only plain text format email accepted.

smaller file size, no virus transfer
no proprietary file formats.

========================================

According to Date you should never use NULLs. This is because a NULL can
mean many different things. It can mean not known (e.g. I know he has an
age but I don't know what it is), It can be not applicable (e.g. in a
Party table of organizations and people, people would be of a certain sex
but an organization would not), It can mean a number divided by zero, It
can also mean I don't know if the value is unknown or if the value is not
applicable etc. etc. In Date's view there is an infinite number of
meanings for null and for a database to handle it correctly would require
an equivalent set of operators for each type of null.

I think though that Nulls are a very useful feature of SQL databases. It's
just that when I write a program, I have to know in a particular context
what a null means and handle it appropriately (of course controlling nulls
in an index is a different matter). I don't think there is only one
accepted way to use nulls.

> Roman Neuhauser wrote:
>> # alex@meerkatsoft.com / 2005-01-20 01:35:32 +1100:
>>
>>>i have a unique index on a table over multiple columns. If now one of
>>>the records has a null value in one of the indexed columns i can insert
>>>the same record multiple times.
>>>
>>>Is this a problem within postgres or expected?
>>
>>
>>     In SQL, NULL means "unknown value". How could you assert that two
>>     NULLs are equal?
>>
> which doesn't make mathematical sense.
> mathwise null is an empty result.
> so setting the logic up using the math logic, null values are always
> equal.
>
> --
> ========================================
>
> only plain text format email accepted.
>
> smaller file size, no virus transfer
> no proprietary file formats.
>
> ========================================
>

Null values are a big surprise to almost every end-user (though the
programmers are OK with them).

Look at the astonishment on the face of your end user when you tell them
that:
SELECT COUNT(*) FROM clothing WHERE clothing_color = 'green'
+
SELECT COUNT(*) FROM clothing WHERE NOT clothing_color = 'green'

Is not the count of all clothing because clothing without a color
recorded will not be counted.

Or (perhaps better yet, violating trichotomy) ...
If <Some_column> has a null numeric value, then ALL of the following are
FALSE for that case:

Some_column < 0
Some_column > 0
Some_column = 0
Some_column <> 0 // This is the one that many find surprising
Some_column <= 0
Some_column >= 0

You can probably see why Null values can do strange things in (for
instance) an index.

Even at that, I think that being able to insert more than one null value
into a unique index should be considered as a bug (or diagnosed as an
error).

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
vhikida@inreach.com
Sent: Wednesday, January 19, 2005 3:30 PM
To: J. Greenlees
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unique Index

According to Date you should never use NULLs. This is because a NULL can
mean many different things. It can mean not known (e.g. I know he has an
age but I don't know what it is), It can be not applicable (e.g. in a
Party table of organizations and people, people would be of a certain
sex
but an organization would not), It can mean a number divided by zero, It
can also mean I don't know if the value is unknown or if the value is
not
applicable etc. etc. In Date's view there is an infinite number of
meanings for null and for a database to handle it correctly would
require
an equivalent set of operators for each type of null.

I think though that Nulls are a very useful feature of SQL databases.
It's
just that when I write a program, I have to know in a particular context
what a null means and handle it appropriately (of course controlling
nulls
in an index is a different matter). I don't think there is only one
accepted way to use nulls.

"Dann Corbit" <DCorbit@connx.com> writes:
> Or (perhaps better yet, violating trichotomy) ...
> If <Some_column> has a null numeric value, then ALL of the following are
> FALSE for that case:

> Some_column < 0
> Some_column > 0
> Some_column = 0
> Some_column <> 0 // This is the one that many find surprising
> Some_column <= 0
> Some_column >= 0

It's worse than that: the above do *not* yield FALSE, they yield NULL.
Which does act like FALSE in a simple WHERE clause, but there are other
cases (like CHECK clauses) where it doesn't.  "x NOT IN (SELECT ...)"
is a case that newbies routinely get bitten by.

> Even at that, I think that being able to insert more than one null value
> into a unique index should be considered as a bug (or diagnosed as an
> error).

Direct your complaints to the ISO SQL standards committee.

            regards, tom lane

The ISO SQL Standard does not even define an index, and so any index is
an extension to the standard (though primary keys and foreign keys imply
them).

At least in the SQL Standard that I have (ANSI/ISO/IEC 9075-1-1999 and
related documents) has no definition of an index.  Perhaps the newer
version contains such a definition.

So, in the creation of an extension, I think it is up to the programmer
to do whatever is best.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, January 19, 2005 4:18 PM
To: Dann Corbit
Cc: vhikida@inreach.com; J. Greenlees; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unique Index

"Dann Corbit" <DCorbit@connx.com> writes:
> Or (perhaps better yet, violating trichotomy) ...
> If <Some_column> has a null numeric value, then ALL of the following
are
> FALSE for that case:

> Some_column < 0
> Some_column > 0
> Some_column = 0
> Some_column <> 0 // This is the one that many find surprising
> Some_column <= 0
> Some_column >= 0

It's worse than that: the above do *not* yield FALSE, they yield NULL.
Which does act like FALSE in a simple WHERE clause, but there are other
cases (like CHECK clauses) where it doesn't.  "x NOT IN (SELECT ...)"
is a case that newbies routinely get bitten by.

> Even at that, I think that being able to insert more than one null
value
> into a unique index should be considered as a bug (or diagnosed as an
> error).

Direct your complaints to the ISO SQL standards committee.

            regards, tom lane

On Wed, 19 Jan 2005, Dann Corbit wrote:

> Even at that, I think that being able to insert more than one null value
> into a unique index should be considered as a bug (or diagnosed as an
> error).

AFAICT the UNIQUE constraint that it's used to model explicitly allows
multiple NULLs in the spec so I don't see making it error as being
terribly workable.

True, but the standard says nothing about the creation of an index, so
you can make it behave in any way that you see fit.

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Wednesday, January 19, 2005 4:27 PM
To: Dann Corbit
Cc: vhikida@inreach.com; J. Greenlees; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unique Index


On Wed, 19 Jan 2005, Dann Corbit wrote:

> Even at that, I think that being able to insert more than one null
value
> into a unique index should be considered as a bug (or diagnosed as an
> error).

AFAICT the UNIQUE constraint that it's used to model explicitly allows
multiple NULLs in the spec so I don't see making it error as being
terribly workable.

On Wed, 19 Jan 2005, Dann Corbit wrote:

> True, but the standard says nothing about the creation of an index, so
> you can make it behave in any way that you see fit.

The unique index is however used to model the unique constraint in
PostgreSQL which I had thought was clear from my statement so giving the
unique index behavior which makes it unable to model the constraint
wouldn't be terribly workable (without rewriting the constraint to be
modeled in a separate fashion).

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
> Sent: Wednesday, January 19, 2005 4:27 PM
> To: Dann Corbit
> Cc: vhikida@inreach.com; J. Greenlees; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Unique Index
>
>
> On Wed, 19 Jan 2005, Dann Corbit wrote:
>
> > Even at that, I think that being able to insert more than one null
> value
> > into a unique index should be considered as a bug (or diagnosed as an
> > error).
>
> AFAICT the UNIQUE constraint that it's used to model explicitly allows
> multiple NULLs in the spec so I don't see making it error as being
> terribly workable.
>

On Wed, 19 Jan 2005, Stephan Szabo wrote:

>
> On Wed, 19 Jan 2005, Dann Corbit wrote:
>
> > True, but the standard says nothing about the creation of an index, so
> > you can make it behave in any way that you see fit.
>
> The unique index is however used to model the unique constraint in
> PostgreSQL which I had thought was clear from my statement so giving the
> unique index behavior which makes it unable to model the constraint
> wouldn't be terribly workable (without rewriting the constraint to be
> modeled in a separate fashion).

Actually, thinking about it, I think I generally disagree with the idea
that unique is the correct word to use in any case.  It's used in both the
constraint and in the predicate to have its current meaning (as described
in my earlier message) and I think the word distinct already the semantics
of treating two NULLs in the fashion such that an index wouldn't allow
two.

I actually just wanted to know if there is a way around this problem.
Obviously it is implemented that way for whatever reason.

I still though think some arguments given in some of the replies, while
probably correct, are besides the point.

I use a unique index that may contain null values. On an insert or
update I can now not rely on the exception thrown but actually have to
write a select statement to check if the same row exists, which I
believe defies ONE purpose of having unique indices. Whether Null is
associated with "unknown value", "divided by zero"... or however one
wants to interpret it is not the issue here, in my view NULL in the same
column have the same value or at least should be treated the same. (If I
want to differentiate the state, I would use a code instead of NULL as a
NULL does not give any indication of its meaning, thus we could safely
assume they are treated as equal).

Maybe there could be an option in the creation of the index to indicate
on how to use NULL values.

How do other DBMS handle this?

A





Tom Lane wrote:

>"Dann Corbit" <DCorbit@connx.com> writes:
>
>
>>Or (perhaps better yet, violating trichotomy) ...
>>If <Some_column> has a null numeric value, then ALL of the following are
>>FALSE for that case:
>>
>>
>
>
>
>>Some_column < 0
>>Some_column > 0
>>Some_column = 0
>>Some_column <> 0 // This is the one that many find surprising
>>Some_column <= 0
>>Some_column >= 0
>>
>>
>
>It's worse than that: the above do *not* yield FALSE, they yield NULL.
>Which does act like FALSE in a simple WHERE clause, but there are other
>cases (like CHECK clauses) where it doesn't.  "x NOT IN (SELECT ...)"
>is a case that newbies routinely get bitten by.
>
>
>
>>Even at that, I think that being able to insert more than one null value
>>into a unique index should be considered as a bug (or diagnosed as an
>>error).
>>
>>
>
>Direct your complaints to the ISO SQL standards committee.
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>
>

Tom Lane <tgl@sss.pgh.pa.us> writes:

> "Dann Corbit" <DCorbit@connx.com> writes:
>
> > Even at that, I think that being able to insert more than one null value
> > into a unique index should be considered as a bug (or diagnosed as an
> > error).
>
> Direct your complaints to the ISO SQL standards committee.

The SQL standard generally treats NULLs as a escape hatch for constraints.

That's true for CHECK constraints as well: if you have a "CHECK a>0"
constraint but a is nullable then a NULL value is allowed even though the
check constraint can't be verified.

This isn't an unreasonable approach. Once you have NULLs it makes more sense
to make them useful than to treat them as just another value. You can always
make the column NOT NULL if you don't want any exceptions to your constraints.

--
greg

Alex <alex@meerkatsoft.com> writes:

> I actually just wanted to know if there is a way around this problem. Obviously
> it is implemented that way for whatever reason.

The way around is to make all the columns NOT NULL. For most applications
unique indexes don't make much sense on nullable columns.

> (If I want to differentiate the state, I would use a code instead of NULL as
> a NULL does not give any indication of its meaning, thus we could safely
> assume they are treated as equal).

I think you have that backwards. You use NULL in the case where you want all
cases to compare as unknown values. If you want them to compare as known
values then you should use a special value.

In other words, NULL has special properties. You should use it if those
special properties are what you want. If you want the system to enforce a
unique constraint on the special value then you probably don't want to be
using NULL for that special state.

Specifically if you find yourself saying "we could safely assume they are
treated equal" then NULL is almost certainly not what you want to represent
that. NULL never compares equal to anything.

--
greg

Dann Corbit wrote:
> True, but the standard says nothing about the creation of an index, so
> you can make it behave in any way that you see fit.

But I thought we are talking about unique _constraint_ here (which is
certainly regulated by the standard).

--
dave

On Jan 20, 2005, at 16:03, David Garamond wrote:

> Dann Corbit wrote:
>> True, but the standard says nothing about the creation of an index, so
>> you can make it behave in any way that you see fit.
>
> But I thought we are talking about unique _constraint_ here (which is
> certainly regulated by the standard).

They could conceivably be separated. The standard likewise doesn't say
anything about how the unique constraint is enforced. In PostgreSQL, a
"unique" index is used to enforce the constraint, but the unique index
is not intrinsically tied to the unique constraint.

Michael Glaesemann
grzm myrealbox com

>I actually just wanted to know if there is a way around this problem.
>Obviously it is implemented that way for whatever reason.
>
> I still though think some arguments given in some of the replies, while
> probably correct, are besides the point.

Sorry. I was hoping someone else would answer.
>
> I use a unique index that may contain null values. On an insert or update
> I can now not rely on the exception thrown but actually have to write a
> select statement to check if the same row exists, which I believe defies
> ONE purpose of having unique indices. Whether Null is associated with
> "unknown value", "divided by zero"... or however one wants to interpret it
> is not the issue here, in my view NULL in the same column have the same
> value or at least should be treated the same. (If I want to differentiate
> the state, I would use a code instead of NULL as a NULL does not give any
> indication of its meaning, thus we could safely assume they are treated as
> equal).
>
> Maybe there could be an option in the creation of the index to indicate on
> how to use NULL values.

I can think of two options.

One was mentioned already. If only one row can have a null value then it
seems to me that you should make it a non null and null would have a special
code.

If it really needs to be null. Then a rather messy solution would be to have
a second column (I'll call it a null indicator) which can only be 1 or null
and have a unique index on it.

colA        ind
------       -----
1             null
2             null
3             null
null         1

>
> How do other DBMS handle this?

Oracle is the same.
>
> A
>
>
>
>
>
> Tom Lane wrote:
>
>>"Dann Corbit" <DCorbit@connx.com> writes:
>>
>>>Or (perhaps better yet, violating trichotomy) ...
>>>If <Some_column> has a null numeric value, then ALL of the following are
>>>FALSE for that case:
>>>
>>
>>
>>>Some_column < 0
>>>Some_column > 0
>>>Some_column = 0 Some_column <> 0 // This is the one that many find
>>>surprising
>>>Some_column <= 0
>>>Some_column >= 0
>>>
>>
>>It's worse than that: the above do *not* yield FALSE, they yield NULL.
>>Which does act like FALSE in a simple WHERE clause, but there are other
>>cases (like CHECK clauses) where it doesn't.  "x NOT IN (SELECT ...)"
>>is a case that newbies routinely get bitten by.
>>
>>
>>>Even at that, I think that being able to insert more than one null value
>>>into a unique index should be considered as a bug (or diagnosed as an
>>>error).
>>>
>>
>>Direct your complaints to the ISO SQL standards committee.
>>
>> regards, tom lane
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 8: explain analyze is your friend
>>
>>
>>
>
>
>

Hi,

Am Mittwoch, den 19.01.2005, 15:02 -0800 schrieb J. Greenlees:
> Roman Neuhauser wrote:
> > # alex@meerkatsoft.com / 2005-01-20 01:35:32 +1100:
> >
> >>i have a unique index on a table over multiple columns. If now one of
> >>the records has a null value in one of the indexed columns i can insert
> >>the same record multiple times.
> >>
> >>Is this a problem within postgres or expected?
> >
> >
> >     In SQL, NULL means "unknown value". How could you assert that two
> >     NULLs are equal?
> >
> which doesn't make mathematical sense.
> mathwise null is an empty result.
> so setting the logic up using the math logic, null values are always equal.

What kind of mathematics you are speaking?
For example you have "infinity" where infinity is never
equal to infinity.
Same with null. Which is "unknown" or "undefined"
So if x is undefined and y is undefined you cannot
assume x=y  - because if you assume this, then
they would not be undefined anymore.

q.e.d.

Regards
Tino

On Thu, 20 Jan 2005 15:20:26 +1100, Alex <alex@meerkatsoft.com> wrote:
> I actually just wanted to know if there is a way around this problem.
> Obviously it is implemented that way for whatever reason.

Well, if you really need it, partial indexes are your friends! :)

For clarity, let's say you have:
CREATE TABLE foo (
   a int,
   b int,
   c int,
);
And an INDEX:
CREATE UNIQUE INDEX foo_abc_index ON foo (a,b,c);

Now, you want to make sure a and b are UNIQUE, when c is null; just do:
CREATE UNIQUE INDEX foo_abN_index ON foo (a,b) WHERE c IS NULL;

Or even, to make b UNIQUE when a and c are null:
CREATE UNIQUE INDEX foo_NbN_index ON foo (b) WHERE a IS NULL AND c IS NULL;

You need to create such partial indexes for each set of columns
you want to be unique-with-null.

Don't worry about "index bloat".  These additional indexes will be used
only when your main (foo_abc_index) is not used, so there won't be
any duplicate data in them.

Isn't PostgreSQL great? :)

   Regards,
      Dawid

On Thu, Jan 20, 2005 at 04:32:37PM +0900, Michael Glaesemann wrote:
>
> On Jan 20, 2005, at 16:03, David Garamond wrote:
>
> >Dann Corbit wrote:
> >>True, but the standard says nothing about the creation of an index, so
> >>you can make it behave in any way that you see fit.
> >
> >But I thought we are talking about unique _constraint_ here (which is
> >certainly regulated by the standard).
>
> They could conceivably be separated. The standard likewise doesn't say
> anything about how the unique constraint is enforced. In PostgreSQL, a
> "unique" index is used to enforce the constraint, but the unique index
> is not intrinsically tied to the unique constraint.

Ofcourse, but then you'd have to create another UNIQUE index type just
to handle the constraint, so you could define two types of unique:

CREATE UNIQUE INDEX blah on (...)
CREATE POSTGRESQL_STRANGE_UNIQUE INDEX blah on  (...)

the former being used by CREATE CONSTRAINT. After all, we'd just be
incompatable with everyone else, no point using a standard keyword.

This is just more confusing when there are already perfectly workable
solutions to the problem as given. Don't use NULL when it's not
appropriate.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Greg Stark <gsstark@mit.edu> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Direct your complaints to the ISO SQL standards committee.

> The SQL standard generally treats NULLs as a escape hatch for constraints.

Not for UNIQUE constraints.  SQL92 section 4.10 "Integrity constraints":

         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.

The short answer to this thread is that the OP is misusing nulls,
and should pick some non-null value to be his "placeholder".

            regards, tom lane

Tino Wildenhain wrote:
> Hi,
>
> Am Mittwoch, den 19.01.2005, 15:02 -0800 schrieb J. Greenlees:
>
>>Roman Neuhauser wrote:
>>
>>># alex@meerkatsoft.com / 2005-01-20 01:35:32 +1100:
>>>
>>>
>>>>i have a unique index on a table over multiple columns. If now one of
>>>>the records has a null value in one of the indexed columns i can insert
>>>>the same record multiple times.
>>>>
>>>>Is this a problem within postgres or expected?
>>>
>>>
>>>    In SQL, NULL means "unknown value". How could you assert that two
>>>    NULLs are equal?
>>>
>>
>>which doesn't make mathematical sense.
>>mathwise null is an empty result.
>>so setting the logic up using the math logic, null values are always equal.
>
>
> What kind of mathematics you are speaking?
> For example you have "infinity" where infinity is never
> equal to infinity.
> Same with null. Which is "unknown" or "undefined"
> So if x is undefined and y is undefined you cannot
> assume x=y  - because if you assume this, then
> they would not be undefined anymore.
>
> q.e.d.
>
> Regards
> Tino
>
>
or null as in empty.
an empty result set is a null set, zero results.
declare a variable, but never assign a value, it has a default value of
null from the declaration.
( basically any content of memory space allocated that was not actually
empty is the content, but it's a null value to the app. )

--
========================================

only plain text format email accepted.

smaller file size, no virus transfer
no proprietary file formats.

========================================

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Greg Stark <gsstark@mit.edu> writes:
> > Tom Lane <tgl@sss.pgh.pa.us> writes:
> >> Direct your complaints to the ISO SQL standards committee.
>
> > The SQL standard generally treats NULLs as a escape hatch for constraints.

Huh? I thought I was agreeing with you. By "escape hatch" I meant that having
a NULL value in the indexed column allowed multiple records with otherwise
identical values because the NULL compared unequal. Ie, that the unique index
doesn't apply to the record with NULL columns.


> Not for UNIQUE constraints.  SQL92 section 4.10 "Integrity constraints":
>
>          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.

That's ambiguous. Does it mean no two rows have all non-null columns that are
all identical? Or does it mean no two rows have columns that excluding any
null columns are identical.

It actually sounds more like the latter to me which would mean Postgres's
interpretation is wrong.


> The short answer to this thread is that the OP is misusing nulls,
> and should pick some non-null value to be his "placeholder".

We're in "violent agreement".


--
greg

Dawid Kuroczko <qnex42@gmail.com> writes:

> Don't worry about "index bloat".  These additional indexes will be used
> only when your main (foo_abc_index) is not used, so there won't be
> any duplicate data in them.

The main index will have _all_ the tuples in them, even where some of the
columns are NULL, so this will in fact use extra space. It will also cause
extra i/o on every update of a record with NULL in one of the columns.

To minimize the extra space you could make it

Dawid Kuroczko <qnex42@gmail.com> writes:

 CREATE UNIQUE INDEX foo_ab_index ON foo (a,b) WHERE c IS NULL AND a is NOT NULL and b IS NOT NULL;
 CREATE UNIQUE INDEX foo_ac_index ON foo (a,c) WHERE b IS NULL AND a is NOT NULL and c IS NOT NULL;
 CREATE UNIQUE INDEX foo_bc_index ON foo (b,c) WHERE a IS NULL AND b is NOT NULL and c IS NOT NULL;
 CREATE UNIQUE INDEX foo_a_index ON foo (a)   WHERE a IS NOT NULL AND b IS NULL and c is NULL;
 CREATE UNIQUE INDEX foo_b_index ON foo (b)   WHERE b IS NOT NULL AND a IS NULL and c is NULL;
 CREATE UNIQUE INDEX foo_c_index ON foo (c)   WHERE c IS NOT NULL AND a IS NULL and b is NULL;

To avoid indexing the same tuples in multiple indexes.

None of this will prevent you from inserting multiple <null,null,null> records
though.


--
greg

Greg Stark <gsstark@mit.edu> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Not for UNIQUE constraints.  SQL92 section 4.10 "Integrity constraints":
>>
>> 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.

> That's ambiguous. Does it mean no two rows have all non-null columns that are
> all identical? Or does it mean no two rows have columns that excluding any
> null columns are identical.

OK, try the more formal definition in 8.9 <unique predicate>

         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.

(11.7 defines the UNIQUE constraint in terms of the unique predicate)

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm sure this won't work for some reason, but something similar might;
why not create a unique index on a constant where all three are null;
something along these lines (in addition to the others):

CREATE UNIQUE INDEX foo_trio_index ON foo (1) WHERE c IS NULL AND a IS
NULL and b IS NULL;

On Jan 20, 2005, at 10:57 AM, Greg Stark wrote:

> Dawid Kuroczko <qnex42@gmail.com> writes:
>
>> Don't worry about "index bloat".  These additional indexes will be
>> used
>> only when your main (foo_abc_index) is not used, so there won't be
>> any duplicate data in them.
>
> The main index will have _all_ the tuples in them, even where some of
> the
> columns are NULL, so this will in fact use extra space. It will also
> cause
> extra i/o on every update of a record with NULL in one of the columns.
>
> To minimize the extra space you could make it
>
> Dawid Kuroczko <qnex42@gmail.com> writes:
>
>  CREATE UNIQUE INDEX foo_ab_index ON foo (a,b) WHERE c IS NULL AND a
> is NOT NULL and b IS NOT NULL;
>  CREATE UNIQUE INDEX foo_ac_index ON foo (a,c) WHERE b IS NULL AND a
> is NOT NULL and c IS NOT NULL;
>  CREATE UNIQUE INDEX foo_bc_index ON foo (b,c) WHERE a IS NULL AND b
> is NOT NULL and c IS NOT NULL;
>  CREATE UNIQUE INDEX foo_a_index ON foo (a)   WHERE a IS NOT NULL AND
> b IS NULL and c is NULL;
>  CREATE UNIQUE INDEX foo_b_index ON foo (b)   WHERE b IS NOT NULL AND
> a IS NULL and c is NULL;
>  CREATE UNIQUE INDEX foo_c_index ON foo (c)   WHERE c IS NOT NULL AND
> a IS NULL and b is NULL;
>
> To avoid indexing the same tuples in multiple indexes.
>
> None of this will prevent you from inserting multiple <null,null,null>
> records
> though.
>
>
> --
> greg
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
- -----------------------------------------------------------
Frank D. Engel, Jr.  <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB79hL7aqtWrR9cZoRAglUAJ9sT3SypLYDZhx6Dkysfr7aLHQttwCeNLs8
/J4jFlWMLcMMxbQ3/nj55eA=
=4Bbe
-----END PGP SIGNATURE-----



___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com

On Wed, 2005-01-19 at 22:20, Alex wrote:

>
> Maybe there could be an option in the creation of the index to indicate
> on how to use NULL values.
>
> How do other DBMS handle this?

http://troels.arvin.dk/db/rdbms/

"Frank D. Engel, Jr." <fde101@fjrhome.net> writes:

> I'm sure this won't work for some reason, but something similar might; why not
> create a unique index on a constant where all three are null; something along
> these lines (in addition to the others):
>
> CREATE UNIQUE INDEX foo_trio_index ON foo (1) WHERE c IS NULL AND a IS NULL and
> b IS NULL;
>

Huh. Hadn't thought of creating an index on a constant. It works if you put an
extra set of parentheses in:

CREATE UNIQUE INDEX foo_abc_index ON foo ((1)) WHERE ...


--
greg

It is clear to me that only allowing a single null value will not
violate the explanation below.

It would be equally true that allowing multiple null values would not
violate it.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Thursday, January 20, 2005 8:03 AM
To: Greg Stark
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unique Index

Greg Stark <gsstark@mit.edu> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Not for UNIQUE constraints.  SQL92 section 4.10 "Integrity
constraints":
>>
>> 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.

> That's ambiguous. Does it mean no two rows have all non-null columns
that are
> all identical? Or does it mean no two rows have columns that excluding
any
> null columns are identical.

OK, try the more formal definition in 8.9 <unique predicate>

         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.

(11.7 defines the UNIQUE constraint in terms of the unique predicate)

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Thu, 20 Jan 2005, Dann Corbit wrote:

> It is clear to me that only allowing a single null value will not
> violate the explanation below.

Given two rows in T with one column each
(NULL), (NULL)

Find two rows such that the value of each column in one row is non-null
and equal to the value of the corresponding column in the other row
according to 8.2. If there are no such rows the unique predicate returns
true (ie the constraint is satisfied).

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Huh?

... the value of EACH COLUMN in one row is NOT NULL and IS EQUAL to ...

In order for values to be equal in SQL, neither one can be null.  For
this condition to hold, it is more than "clear" that at least one row
must contain *NO* *NULL* *VALUES* (that means zero columns in that row
may contain null values).  Since *ALL* columns in the other row must be
EQUAL to the corresponding column in that row, none of them can be null
either.  Therefore, the uniqueness predicate evaluates to false, and
each of the two rows is considered unique compared to the other as soon
as any null value shows up in either row.

There is *no* ambiguity here!

On Jan 20, 2005, at 1:55 PM, Dann Corbit wrote:

> It is clear to me that only allowing a single null value will not
> violate the explanation below.
>
> It would be equally true that allowing multiple null values would not
> violate it.
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
> Sent: Thursday, January 20, 2005 8:03 AM
> To: Greg Stark
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Unique Index
>
> Greg Stark <gsstark@mit.edu> writes:
>> Tom Lane <tgl@sss.pgh.pa.us> writes:
>>> Not for UNIQUE constraints.  SQL92 section 4.10 "Integrity
> constraints":
>>>
>>> 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.
>
>> That's ambiguous. Does it mean no two rows have all non-null columns
> that are
>> all identical? Or does it mean no two rows have columns that excluding
> any
>> null columns are identical.
>
> OK, try the more formal definition in 8.9 <unique predicate>
>
>          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.
>
> (11.7 defines the UNIQUE constraint in terms of the unique predicate)
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>
>
- -----------------------------------------------------------
Frank D. Engel, Jr.  <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB8AX07aqtWrR9cZoRAlu+AJ0YCv6VEID7MR5BHOf6rRl+gyPeLgCghImV
Um7r0pyp2vfsCVArSOkswMM=
=kzpI
-----END PGP SIGNATURE-----



___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com

Would the constraint not be satisfied if each combination (including
NULL) were not also forced to be unique?

I maintain that the constraint is still satisfied.

So, it is satisfied if I stuff thousands of NULL values in there.

And it is satisfied if I only allow a single NULL value.

With multiple columns, it might be set such that each combination must
be unique, like binary counting.

Let me also state that I agree: allowing null values in a unique index
is ludicrous.  But if it is allowed, I think forcing the combinations to
be single valued makes more sense than allowing any number of them.

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Thursday, January 20, 2005 11:14 AM
To: Dann Corbit
Cc: Tom Lane; Greg Stark; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unique Index


On Thu, 20 Jan 2005, Dann Corbit wrote:

> It is clear to me that only allowing a single null value will not
> violate the explanation below.

Given two rows in T with one column each
(NULL), (NULL)

Find two rows such that the value of each column in one row is non-null
and equal to the value of the corresponding column in the other row
according to 8.2. If there are no such rows the unique predicate returns
true (ie the constraint is satisfied).

"Dann Corbit" <DCorbit@connx.com> writes:

> Would the constraint not be satisfied if each combination (including
> NULL) were not also forced to be unique?
>
> I maintain that the constraint is still satisfied.
>
> So, it is satisfied if I stuff thousands of NULL values in there.
>
> And it is satisfied if I only allow a single NULL value.

You're misreading it. It's not a question of what you allow, it's a question
of what's in the table. The database *must* allow anything in the table that
would satisfy the constraint.

Of course the constraint is satisfied if there's only one NULL value. But the
constraint is also satisfied if there are more than one. So the database has
to allow you to do either of these since there's no constraint that they
violate.

> Let me also state that I agree: allowing null values in a unique index
> is ludicrous.  But if it is allowed, I think forcing the combinations to
> be single valued makes more sense than allowing any number of them.

In fact allowing NULLs in columns involved in a unique constraint is an
optional feature of the SQL standard. But if it's allowed it is required to be
done the way Postgres does it.

Perhaps you're just thinking of primary keys (which, btw, are *not* allowed to
be nullable according to the spec). NULLs are much more likely in a situation
where it's not a key field, just a bit of incidental data that we know should
be unique.

Consider for example a user table where there's a phone number field. Now not
all users enter a phone number, but when they do we want to make sure it's
unique. Does it make sense to restrict the database to a single user with an
unknown phone number?

Or consider a product database. Some products have ISBNs, namely books, but
not all do. If the product has an ISBN then it really ought to be unique, no
other product should have the same ISBN. But any number of products can be
non-books and not have an ISBN.



--
greg

On Thu, 20 Jan 2005, Dann Corbit wrote:

> Would the constraint not be satisfied if each combination (including
> NULL) were not also forced to be unique?

The constraint would be satisfied, however cases that the constraint is
satisfied for would not be allowed.  The case I gave below is one for
which I argue the constraint is satisfied because the search condition is
true.  The definition above would appear to not allow that case and as
such appears to be contrary to the definition of the constraint.

> Let me also state that I agree: allowing null values in a unique index
> is ludicrous.  But if it is allowed, I think forcing the combinations to
> be single valued makes more sense than allowing any number of them.

I think that'd be better termed a DISTINCT index to use SQL terminology.

> On Thu, 20 Jan 2005, Dann Corbit wrote:
>
> > It is clear to me that only allowing a single null value will not
> > violate the explanation below.
>
> Given two rows in T with one column each
> (NULL), (NULL)
>
> Find two rows such that the value of each column in one row is non-null
> and equal to the value of the corresponding column in the other row
> according to 8.2. If there are no such rows the unique predicate returns
> true (ie the constraint is satisfied).
>

Yes.
I was wrong.

Sorry about the noise.

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Thursday, January 20, 2005 12:01 PM
To: Dann Corbit
Cc: Tom Lane; Greg Stark; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Unique Index

On Thu, 20 Jan 2005, Dann Corbit wrote:

> Would the constraint not be satisfied if each combination (including
> NULL) were not also forced to be unique?

The constraint would be satisfied, however cases that the constraint is
satisfied for would not be allowed.  The case I gave below is one for
which I argue the constraint is satisfied because the search condition
is
true.  The definition above would appear to not allow that case and as
such appears to be contrary to the definition of the constraint.

> Let me also state that I agree: allowing null values in a unique index
> is ludicrous.  But if it is allowed, I think forcing the combinations
to
> be single valued makes more sense than allowing any number of them.

I think that'd be better termed a DISTINCT index to use SQL terminology.

> On Thu, 20 Jan 2005, Dann Corbit wrote:
>
> > It is clear to me that only allowing a single null value will not
> > violate the explanation below.
>
> Given two rows in T with one column each
> (NULL), (NULL)
>
> Find two rows such that the value of each column in one row is
non-null
> and equal to the value of the corresponding column in the other row
> according to 8.2. If there are no such rows the unique predicate
returns
> true (ie the constraint is satisfied).
>

Am Donnerstag, den 20.01.2005, 06:09 -0800 schrieb J. Greenlees:
> Tino Wildenhain wrote:
> > Hi,
> >
> > Am Mittwoch, den 19.01.2005, 15:02 -0800 schrieb J. Greenlees:
> >
> >>Roman Neuhauser wrote:
> >>
> >>># alex@meerkatsoft.com / 2005-01-20 01:35:32 +1100:
> >>>
> >>>
> >>>>i have a unique index on a table over multiple columns. If now one of
> >>>>the records has a null value in one of the indexed columns i can insert
> >>>>the same record multiple times.
> >>>>
> >>>>Is this a problem within postgres or expected?
> >>>
> >>>
> >>>    In SQL, NULL means "unknown value". How could you assert that two
> >>>    NULLs are equal?
> >>>
> >>
> >>which doesn't make mathematical sense.
> >>mathwise null is an empty result.
> >>so setting the logic up using the math logic, null values are always equal.
> >
> >
> > What kind of mathematics you are speaking?
> > For example you have "infinity" where infinity is never
> > equal to infinity.
> > Same with null. Which is "unknown" or "undefined"
> > So if x is undefined and y is undefined you cannot
> > assume x=y  - because if you assume this, then
> > they would not be undefined anymore.
> >
> > q.e.d.
> >
> > Regards
> > Tino
> >
> >
> or null as in empty.
> an empty result set is a null set, zero results.
> declare a variable, but never assign a value, it has a default value of
> null from the declaration.
> ( basically any content of memory space allocated that was not actually
> empty is the content, but it's a null value to the app. )

No. Empty result set is just a set without elements. zero-length
list or tuple or whatever your programming language uses :-)

[] != null/undefined/None