pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: UNIQUE KEY with null value...
5+ messages / 5 participants
[nested] [flat]
* Re: UNIQUE KEY with null value...
@ 2005-11-01 17:28 Eric Faulhaber <ecf@goldencode.com>
parent: Diego <diego@unimedijui.com.br>
3 siblings, 0 replies; 5+ messages in thread
From: Eric Faulhaber @ 2005-11-01 17:28 UTC (permalink / raw)
To: Diego <diego@unimedijui.com.br>; +Cc: pgsql-admin
Diego wrote:
> Hi,
> I´m a brazilian programmer!
>
> I have a question about Postgres UNIQUE KEY. Look:
>
> (Step 1)
> CREATE TABLE test (
> id integer not null primary key,
> id_uni integer not null,
> cod integer,
> name varchar(40),
> constraint test_uk unique(id_uni, cod)
> )
>
> (Step 2)
> insert into test (id, id_uni, cod, name)
> values (1, 1, null, 'A')
>
> (Step 3)
> insert into test (id, id_uni, cod, name)
> values (2, 1, null, 'B') =========> [Have the same UNIQUE KEY]
>
> Why postgresql don´t break insertion in this case????
>
> Thanks
> Diego Ziquinatti
> Brazil
Diego,
In PostgreSQL, NULL is not considered a unique value, as it represents
an "unknown" value. From the docs regarding unique constraints:
"...null values are not considered equal in this comparison. That means
even in the presence of a unique constraint it is possible to store an
unlimited number of rows that contain a null value in at least one of
the constrained columns."
See:
http://www.postgresql.org/docs/8.0/interactive/ddl-constraints.html#AEN1996
Regards,
Eric Faulhaber
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: UNIQUE KEY with null value...
@ 2005-11-01 17:32 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Diego <diego@unimedijui.com.br>
3 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2005-11-01 17:32 UTC (permalink / raw)
To: Diego <diego@unimedijui.com.br>; +Cc: pgsql-admin
On Tue, 1 Nov 2005, Diego wrote:
> Hi,
> I´m a brazilian programmer!
>
> I have a question about Postgres UNIQUE KEY. Look:
>
> (Step 1)
> CREATE TABLE test (
> id integer not null primary key,
> id_uni integer not null,
> cod integer,
> name varchar(40),
> constraint test_uk unique(id_uni, cod)
> )
>
> (Step 2)
> insert into test (id, id_uni, cod, name)
> values (1, 1, null, 'A')
>
> (Step 3)
> insert into test (id, id_uni, cod, name)
> values (2, 1, null, 'B') =========> [Have the same UNIQUE KEY]
>
> Why postgresql don´t break insertion in this case????
AFAICS, SQL (at least 92) says that those do not violate UNIQUE.
The unique predicate on which the UNIQUE constraint is defined says:
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.
The unique constraint for the above is effectively
UNIQUE (SELECT id_uni, col FROM test)
The two rows in question look like
(1,null) and (1,null).
There are no rows in that would make the predicate return false AFAICS.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: UNIQUE KEY with null value...
@ 2005-11-01 17:43 Jim C. Nasby <jnasby@pervasive.com>
parent: Diego <diego@unimedijui.com.br>
3 siblings, 0 replies; 5+ messages in thread
From: Jim C. Nasby @ 2005-11-01 17:43 UTC (permalink / raw)
To: Diego <diego@unimedijui.com.br>; +Cc: pgsql-admin
http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html
For the purpose of a unique constraint, null values are not considered
equal.
On Tue, Nov 01, 2005 at 03:09:24PM -0300, Diego wrote:
> Hi,
> I?m a brazilian programmer!
>
> I have a question about Postgres UNIQUE KEY. Look:
>
> (Step 1)
> CREATE TABLE test (
> id integer not null primary key,
> id_uni integer not null,
> cod integer,
> name varchar(40),
> constraint test_uk unique(id_uni, cod)
> )
>
> (Step 2)
> insert into test (id, id_uni, cod, name)
> values (1, 1, null, 'A')
>
> (Step 3)
> insert into test (id, id_uni, cod, name)
> values (2, 1, null, 'B') =========> [Have the same UNIQUE KEY]
>
> Why postgresql don?t break insertion in this case????
>
> Thanks
> Diego Ziquinatti
> Brazil
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 5+ messages in thread
* UNIQUE KEY with null value...
@ 2005-11-01 18:09 Diego <diego@unimedijui.com.br>
0 siblings, 4 replies; 5+ messages in thread
From: Diego @ 2005-11-01 18:09 UTC (permalink / raw)
To: pgsql-admin
Hi,
I´m a brazilian programmer!
I have a question about Postgres UNIQUE KEY. Look:
(Step 1)
CREATE TABLE test (
id integer not null primary key,
id_uni integer not null,
cod integer,
name varchar(40),
constraint test_uk unique(id_uni, cod)
)
(Step 2)
insert into test (id, id_uni, cod, name)
values (1, 1, null, 'A')
(Step 3)
insert into test (id, id_uni, cod, name)
values (2, 1, null, 'B') =========> [Have the same UNIQUE KEY]
Why postgresql don´t break insertion in this case????
Thanks
Diego Ziquinatti
Brazil
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: UNIQUE KEY with null value...
@ 2005-11-01 23:22 Tzvetan Tzankov <tzankov@noxis.net>
parent: Diego <diego@unimedijui.com.br>
3 siblings, 0 replies; 5+ messages in thread
From: Tzvetan Tzankov @ 2005-11-01 23:22 UTC (permalink / raw)
To: pgsql-admin
there are answers why it is that, here is how you can surround the problem
you can create the unique index after you create the table, something like
CREATE TABLE test (
id integer not null primary key,
id_uni integer not null,
cod integer,
name varchar(40));
CREATE UNIQUE INDEX test_id_uni_cod ON test (id_uni, cod) WHERE cod IS
NOT NULL;
CREATE UNIQUE INDEX test_id_uni ON test (id_uni) WHERE cod IS NULL;
Diego wrote:
> Hi,
> I´m a brazilian programmer!
>
> I have a question about Postgres UNIQUE KEY. Look:
>
> (Step 1)
> CREATE TABLE test (
> id integer not null primary key,
> id_uni integer not null,
> cod integer,
> name varchar(40),
> constraint test_uk unique(id_uni, cod)
> )
>
> (Step 2)
> insert into test (id, id_uni, cod, name)
> values (1, 1, null, 'A')
>
> (Step 3)
> insert into test (id, id_uni, cod, name)
> values (2, 1, null, 'B') =========> [Have the same UNIQUE KEY]
>
> Why postgresql don´t break insertion in this case????
>
> Thanks
> Diego Ziquinatti
> Brazil
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-11-01 23:22 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-01 18:09 UNIQUE KEY with null value... Diego <diego@unimedijui.com.br>
2005-11-01 17:28 ` Eric Faulhaber <ecf@goldencode.com>
2005-11-01 17:32 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-11-01 17:43 ` Jim C. Nasby <jnasby@pervasive.com>
2005-11-01 23:22 ` Tzvetan Tzankov <tzankov@noxis.net>
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