pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpartial unique constraint
9+ messages / 8 participants
[nested] [flat]
* partial unique constraint
@ 2004-04-06 14:29 Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 6 replies; 9+ messages in thread
From: Robert Treat @ 2004-04-06 14:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: partial unique constraint
@ 2004-04-06 15:05 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Robert Treat <xzilla@users.sourceforge.net>
5 siblings, 0 replies; 9+ messages in thread
From: Stephan Szabo @ 2004-04-06 15:05 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: partial unique constraint
@ 2004-04-06 15:05 ries@jongert.nl
parent: Robert Treat <xzilla@users.sourceforge.net>
5 siblings, 0 replies; 9+ messages in thread
From: ries@jongert.nl @ 2004-04-06 15:05 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: partial unique constraint
@ 2004-04-06 15:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robert Treat <xzilla@users.sourceforge.net>
5 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2004-04-06 15:17 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: partial unique constraint
@ 2004-04-06 15:17 Rod Taylor <rbt@rbt.ca>
parent: Robert Treat <xzilla@users.sourceforge.net>
5 siblings, 0 replies; 9+ messages in thread
From: Rod Taylor @ 2004-04-06 15:17 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-sql
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
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: partial unique constraint
@ 2004-04-06 15:18 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Robert Treat <xzilla@users.sourceforge.net>
5 siblings, 0 replies; 9+ messages in thread
From: Achilleus Mantzios @ 2004-04-06 15:18 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: partial unique constraint
@ 2004-04-06 15:26 Paul Thomas <paul@tmsl.demon.co.uk>
parent: Robert Treat <xzilla@users.sourceforge.net>
5 siblings, 0 replies; 9+ messages in thread
From: Paul Thomas @ 2004-04-06 15:26 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-sql
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 |
+------------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: partial unique constraint
@ 2004-04-06 16:38 Robert Treat <xzilla@users.sourceforge.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Robert Treat @ 2004-04-06 16:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: partial unique constraint
@ 2004-04-06 19:54 Richard Huxton <dev@archonet.com>
parent: Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 0 replies; 9+ messages in thread
From: Richard Huxton @ 2004-04-06 19:54 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2004-04-06 19:54 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-06 14:29 partial unique constraint Robert Treat <xzilla@users.sourceforge.net>
2004-04-06 15:05 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-04-06 15:05 ` ries@jongert.nl
2004-04-06 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-06 16:38 ` Robert Treat <xzilla@users.sourceforge.net>
2004-04-06 19:54 ` Richard Huxton <dev@archonet.com>
2004-04-06 15:17 ` Rod Taylor <rbt@rbt.ca>
2004-04-06 15:18 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-04-06 15:26 ` Paul Thomas <paul@tmsl.demon.co.uk>
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