Re: UNIQUE KEY with null value...

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
>