From: Jackson, DeJuan <djackson@cpsgroup.com>
To: Stuart Rison <rison@biochemistry.ucl.ac.uk>
To: pgsql-general <pgsql-general@postgreSQL.org>
To: pgsql-sql <pgsql-sql@postgreSQL.org>
Subject: RE: [GENERAL][SQL] Getting multiple field unique index to disting uish NULLs.
Date: Fri, 10 Sep 1999 10:44:38 -0500
Message-ID: <D05EF808F2DFD211AE4A00105AA1B5D251A350@cpsmail> (raw)
Use 0 or -1 instead of NULL.
DEJ
> -----Original Message-----
> From: Stuart Rison [SMTP:rison@biochemistry.ucl.ac.uk]
> Sent: Friday, September 10, 1999 8:02 AM
> To: pgsql-general; pgsql-sql
> Subject: [GENERAL][SQL] Getting multiple field unique index to
> distinguish NULLs.
>
> 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
>
>
>
> ************
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: djackson@cpsgroup.com, rison@biochemistry.ucl.ac.uk, pgsql-general@postgreSQL.org
Subject: Re: [GENERAL][SQL] Getting multiple field unique index to disting uish NULLs.
In-Reply-To: <D05EF808F2DFD211AE4A00105AA1B5D251A350@cpsmail>
References: <D05EF808F2DFD211AE4A00105AA1B5D251A350@cpsmail>
* 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