From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Jimmy Mäkelä <jimmy.makela@agent25.se>
Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Re: Unique indexes not unique?
Date: Mon, 13 Jan 2003 04:08:04 -0800 (PST)
Message-ID: <20030113040014.Q53527-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <D1045567F50DD311AB1B00508B3188E9026546D8@RINGHALS>
On Mon, 13 Jan 2003, [iso-8859-1] Jimmy Mäkelä 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.
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: sszabo@megazone23.bigpanda.com, jimmy.makela@agent25.se
Subject: Re: Unique indexes not unique?
In-Reply-To: <20030113040014.Q53527-100000@megazone23.bigpanda.com>
* 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