pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tzvetan Tzankov <tzankov@noxis.net>
To: pgsql-admin@postgresql.org
Subject: Re: UNIQUE KEY with null value...
Date: Wed, 02 Nov 2005 01:22:55 +0200
Message-ID: <dk8tcb$vu9$1@news.hub.org> (raw)
In-Reply-To: <4367AF54.6010909@unimedijui.com.br>
References: <4367AF54.6010909@unimedijui.com.br>

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
> 



view thread (5+ messages)

Message-ID: <dk8tcb$vu9$1@news.hub.org>
Permalink:  ../dk8tcb$vu9$1@news.hub.org/
Also on:    postgresql.org/message-id/dk8tcb$vu9$1@news.hub.org

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: tzankov@noxis.net
  Subject: Re: UNIQUE KEY with null value...
  In-Reply-To: <dk8tcb$vu9$1@news.hub.org>
  References: <4367AF54.6010909@unimedijui.com.br> <dk8tcb$vu9$1@news.hub.org>

* 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