pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stuart Rison <rison@biochemistry.ucl.ac.uk>
To: pgsql-general <pgsql-general@postgresql.org>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: [GENERAL][SQL] Getting multiple field unique index to distinguish NULLs.
Date: Fri, 10 Sep 1999 14:02:02 +0100 (BST)
Message-ID: <Pine.LNX.4.10.9909101349380.520-100000@bsmlx17> (raw)

Dear All,

Consider the following table:

myscheme_id|level1|level2|level3|level4|function
-----------+------+------+------+------+-------------------------------
         11|     4|     5|     1|     3|Long John Silver
         12|  1242|     3|    44|     5|Metabolism
         13|     1|     2|     3|     4|Transport
          1|     1|     4|     3|      |Energy
          9|     1|     2|     3|     1|Signaling
          3|     1|     2|     3|     2|test1
         18|     1|     2|      |      |test2
         19|     1|     2|      |      |test3
         21|     1|     2|      |      |test4

This is essentially a hierarchical key set-up; each function can be
identified by a key (which can be thought of as the concatenation of
level1, level2, level3 and level4).

But you can add a function at any level (i.e. only level1 must be given)
so:

INSERT INTO myscheme_funcat (level1,function) VALUES (1,'Top level
function');

is OK.

I am trying to get the database to reject INSERTS for keys already
occupied

CREATE UNIQUE INDEX myscheme_idx ON myscheme_funcat
(level1,level2,level3,level4);

The problem is that the indexing considers all NULLs to be distinct thus:

INSERT INTO myscheme_funcat (level1,level2,level3,level4,function) VALUES
(4,5,76,NULL,'OK');
INSERT 1044737 1

but so does
INSERT INTO myscheme_funcat (level1,level2,level3,level4,function) VALUES  
(4,5,76,NULL,'Should fail because position 4.5.76 already occupied');
INSERT 1044738 1   

Works because 4,5,76,NULL is considered DISTINCT from 4,5,76,NULL (all
NULLs are different).

So, any ideas, workarounds etc.??

cheers,

S.

### Please Note New Details ###
Stuart C. G. Rison
Department of Biochemistry and Molecular Biology
Gower Street, London, WC1E 6BT, United Kingdom
Tel. 0207 504 2303, Fax. 0207 380 7193
e-mail: s.rison@biochem.ucl.ac.uk





view thread (6+ messages)  latest in thread

Message-ID: <Pine.LNX.4.10.9909101349380.520-100000@bsmlx17>
Permalink:  ../Pine.LNX.4.10.9909101349380.520-100000@bsmlx17/
Also on:    postgresql.org/message-id/Pine.LNX.4.10.9909101349380.520-100000@bsmlx17

 · 

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-sql@postgresql.org
  Cc: rison@biochemistry.ucl.ac.uk, pgsql-general@postgresql.org
  Subject: Re: [GENERAL][SQL] Getting multiple field unique index to distinguish NULLs.
  In-Reply-To: <Pine.LNX.4.10.9909101349380.520-100000@bsmlx17>
  References: <Pine.LNX.4.10.9909101349380.520-100000@bsmlx17>

* 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