Re: Unique indexes not unique?

On Mon, 13 Jan 2003, [iso-8859-1] Jimmy Mkel wrote:

> I found that Postgres isn't behaving like I thought when using a unique index in
> combination with NULL-values...
> Is this a bug or specified in the SQL-standard? If its a bug, is it fixed in a
> recent version? We are using 7.2.3

AFAIK this is standard.

From the unique predicate (8.9),

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 corresponding column in
the other row according to Subclause 8.2, "comparison predicate", then
the result of the unique predicate is true; otherwise, the result of
the unique predicate is false.

Unique constraints are defined in terms of the unique predicate.

> And another completely unrelated question... I have got a table with a composite
> index on A andBb and an index on A
> which I query with something like this:
>
> SELECT * FROM "table"
> WHERE (a = 1 OR a = 2 OR a = 3) AND b > 1232132 AND b < 123123123213123
>
> Postgres then chooses to use the index for A three times, which is really slow
> on my table...

On my dev (7.4devel) box I see it using the composite index three times,
but you haven't given explain output for the two queries or any statistics
information so that doesn't say much.