Horton

Thread

  1. partial unique constraint

    Robert Treat <xzilla@users.sourceforge.net> — 2004-04-06T14:29:32Z

    Trying to come up with the proper syntax to meet the following criteria:

create table foo (bar integer, baz boolean UNIQUE (bar, baz = true)); 

note the above syntax is not correct, but should demonstrate what i'm
trying to do; I want to add a unique constraint such that we only allow
one case of bar and baz = true...  i can have unlimited bar and baz =
false, and there can be multiple bar and baz = true if the bars are
different...  did some doc reading and mail list searching but a valid
syntax for this seems to be escaping me... 

btw I'm pretty sure I could do this with an external trigger, but am
wondering about a constraint oriented approach

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

  2. Re: partial unique constraint

    Stephan Szabo <sszabo@megazone.bigpanda.com> — 2004-04-06T15:05:15Z

    On Tue, 6 Apr 2004, Robert Treat wrote:

> Trying to come up with the proper syntax to meet the following criteria:
>
> create table foo (bar integer, baz boolean UNIQUE (bar, baz = true));
>
> note the above syntax is not correct, but should demonstrate what i'm
> trying to do; I want to add a unique constraint such that we only allow
> one case of bar and baz = true...  i can have unlimited bar and baz =
> false, and there can be multiple bar and baz = true if the bars are
> different...  did some doc reading and mail list searching but a valid
> syntax for this seems to be escaping me...
>
> btw I'm pretty sure I could do this with an external trigger, but am
> wondering about a constraint oriented approach

You can't precisely with a constraint (AFAIR sql is marginally limited in
that way at least in 92 and 99), but you should be able to do it with a
partial unique index something like
 create unique index foo_i on foo(bar) where baz is true;

  3. Re: partial unique constraint

    ries@jongert.nl — 2004-04-06T15:05:37Z

    You should create a functional index here.

Ries

> -----Oorspronkelijk bericht-----
> Van: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]Namens Robert Treat
> Verzonden: dinsdag 6 april 2004 16:30
> Aan: pgsql-sql@postgresql.org
> Onderwerp: [SQL] partial unique constraint
> 
> 
> Trying to come up with the proper syntax to meet the 
> following criteria:
> 
> create table foo (bar integer, baz boolean UNIQUE (bar, baz = true)); 
> 
> note the above syntax is not correct, but should demonstrate what i'm
> trying to do; I want to add a unique constraint such that we 
> only allow
> one case of bar and baz = true...  i can have unlimited bar and baz =
> false, and there can be multiple bar and baz = true if the bars are
> different...  did some doc reading and mail list searching but a valid
> syntax for this seems to be escaping me... 
> 
> btw I'm pretty sure I could do this with an external trigger, but am
> wondering about a constraint oriented approach
> 
> Robert Treat
> -- 
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
> 
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

  4. Re: partial unique constraint

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-04-06T15:17:03Z

    Robert Treat <xzilla@users.sourceforge.net> writes:
> Trying to come up with the proper syntax to meet the following criteria:
> create table foo (bar integer, baz boolean UNIQUE (bar, baz = true)); 

The correct way to do it is with a separate CREATE INDEX command:

create table foo (bar integer, baz boolean);
create unique index fooi on foo (bar) where baz = true;

Personally I'd spell that last as just "where baz" ...

			regards, tom lane

  5. Re: partial unique constraint

    Rod Taylor <rbt@rbt.ca> — 2004-04-06T15:17:49Z

    On Tue, 2004-04-06 at 10:29, Robert Treat wrote:
> Trying to come up with the proper syntax to meet the following criteria:
> 
> create table foo (bar integer, baz boolean UNIQUE (bar, baz = true)); 

Tt takes 2 steps.

CREATE TABLE ...
CREATE UNIQUE INDEX ... (bar) WHERE baz = true;

-- 
Rod Taylor <rbt [at] rbt [dot] ca>

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc

  6. Re: partial unique constraint

    Achilleas Mantzios <achill@matrix.gatewaynet.com> — 2004-04-06T15:18:03Z

    O kyrios Robert Treat egrapse stis Apr 6, 2004 :

> Trying to come up with the proper syntax to meet the following criteria:
> 
> create table foo (bar integer, baz boolean UNIQUE (bar, baz = true)); 

You could simply leave your table as is.
A null vale on baz (equivalent of 'false') will have no impact on the 
constraint.

A 't' value (equivalent to 'true') will enforce the constraint.

In other words you can convert your problem as
baz == null <==> baz='false'
baz == 't' <==> baz='true'
(you must never use baz='f')

> 
> note the above syntax is not correct, but should demonstrate what i'm
> trying to do; I want to add a unique constraint such that we only allow
> one case of bar and baz = true...  i can have unlimited bar and baz =
> false, and there can be multiple bar and baz = true if the bars are
> different...  did some doc reading and mail list searching but a valid
> syntax for this seems to be escaping me... 
> 
> btw I'm pretty sure I could do this with an external trigger, but am
> wondering about a constraint oriented approach
> 
> Robert Treat
> 

-- 
-Achilleus

  7. Re: partial unique constraint

    Paul Thomas <paul@tmsl.demon.co.uk> — 2004-04-06T15:26:02Z

    On 06/04/2004 15:29 Robert Treat wrote:
> Trying to come up with the proper syntax to meet the following criteria:
> 
> create table foo (bar integer, baz boolean UNIQUE (bar, baz = true));
> 
> note the above syntax is not correct, but should demonstrate what i'm
> trying to do; I want to add a unique constraint such that we only allow
> one case of bar and baz = true...  i can have unlimited bar and baz =
> false, and there can be multiple bar and baz = true if the bars are
> different...  did some doc reading and mail list searching but a valid
> syntax for this seems to be escaping me...
> 
> btw I'm pretty sure I could do this with an external trigger, but am
> wondering about a constraint oriented approach

What about

create table foo (bar integer, baz boolean);

create unique index foo_bar_baz on foo(bar, baz) where baz = true;

-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for 
Business             |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

  8. Re: partial unique constraint

    Robert Treat <xzilla@users.sourceforge.net> — 2004-04-06T16:38:41Z

    On Tue, 2004-04-06 at 11:17, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > Trying to come up with the proper syntax to meet the following criteria:
> > create table foo (bar integer, baz boolean UNIQUE (bar, baz = true)); 
> 
> The correct way to do it is with a separate CREATE INDEX command:

I think I had initially abandoned looking at that type of solution after
having run across this paragraph in the docs while looking for the
proper constraint syntax:

"Note:  The preferred way to add a unique constraint to a table is ALTER
TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique
constraints could be considered an implementation detail that should not
be accessed directly."

The subliminal mind is a powerful force eh? Perhaps that paragraph
should be modified... but I'm not sure if it should expanded to include
thoughts along the lines of Stephan's response or maybe just drop the
"should not be accessed directly" bit...

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

  9. Re: partial unique constraint

    Richard Huxton <dev@archonet.com> — 2004-04-06T19:54:07Z

    On Tuesday 06 April 2004 17:38, Robert Treat wrote:
> On Tue, 2004-04-06 at 11:17, Tom Lane wrote:
> > Robert Treat <xzilla@users.sourceforge.net> writes:
> > > Trying to come up with the proper syntax to meet the following
> > > criteria: create table foo (bar integer, baz boolean UNIQUE (bar, baz =
> > > true));
> >
> > The correct way to do it is with a separate CREATE INDEX command:
>
> I think I had initially abandoned looking at that type of solution after
> having run across this paragraph in the docs while looking for the
> proper constraint syntax:
>
> "Note:  The preferred way to add a unique constraint to a table is ALTER
> TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique
> constraints could be considered an implementation detail that should not
> be accessed directly."
>
> The subliminal mind is a powerful force eh? Perhaps that paragraph
> should be modified... but I'm not sure if it should expanded to include
> thoughts along the lines of Stephan's response or maybe just drop the
> "should not be accessed directly" bit...

Anyone know if there is any way to do this in one of the later SQL standards? 
The CREATE INDEX thing is a bit of a hack, and I certainly wouldn't have 
thought of it either.

-- 
  Richard Huxton
  Archonet Ltd