PostgreSQL Discussion - unique index with bool

Author

unique index with bool

tmpmac@mac.com

2005-05-19, 11:23 am

CREATE UNIQUE INDEX name on table(param1,param2)
;

How to create such unique index when param2 is bool type, and this param2 should be accepted only in case of true ?

I tried: CREATE UNIQUE INDEX name on table(param1,(param2
= 'true'));
but it's not working.

Regards,
Mac

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Richard Huxton

2005-05-19, 11:23 am

tmpmac@mac.com wrote:
> CREATE UNIQUE INDEX name on table(param1,param2)
;
>
> How to create such unique index when param2 is bool type, and this
> param2 should be accepted only in case of true ?
>
> I tried: CREATE UNIQUE INDEX name on table(param1,(param2
= 'true'));
> but it's not working.

Something like:

CREATE UNIQUE INDEX my_uniq_idx ON table(param1,param2)

ALTER TABLE table ADD CONSTRAINT my_constraint CHECK (param2='' OR
param1=true);

So - separate out the test linking param1/param2 from your uniqueness
requirement.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Alban Hertroys

2005-05-19, 11:23 am

tmpmac@mac.com wrote:
> CREATE UNIQUE INDEX name on table(param1,param2)
;
>
> How to create such unique index when param2 is bool type, and this param2 should be accepted only in case of true ?
>
> I tried: CREATE UNIQUE INDEX name on table(param1,(param2
= 'true'));
> but it's not working.

CREATE UNIQUE INDEX name ON table(param1, param2) WHERE param2 = true;

Regards,

--
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban@magproductions
.nl
W: http://www.magproductions.nl

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Scott Marlowe

2005-05-19, 11:23 am

On Thu, 2005-05-19 at 09:49, tmpmac@mac.com wrote:
> CREATE UNIQUE INDEX name on table(param1,param2)
;
>
> How to create such unique index when param2 is bool type, and this param2 should be accepted only in case of true ?
>
> I tried: CREATE UNIQUE INDEX name on table(param1,(param2
= 'true'));
> but it's not working.

Not sure if this is what you want:

create unique index on table(param1, param2) where param2 is true;

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Stuart Bishop

2005-05-25, 3:23 am

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

tmpmac@mac.com wrote:
> CREATE UNIQUE INDEX name on table(param1,param2)
;
>
> How to create such unique index when param2 is bool type, and this param2 should be accepted only in case of true ?
>
> I tried: CREATE UNIQUE INDEX name on table(param1,(param2
= 'true'));
> but it's not working.

CREATE UNIQUE INDEX foo ON table(param1, (NULLIF(param2, false)))

- --
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFCk/ 6KAfqZj7rGN0oRAvZ+AJ
wLWDd8FQe5nBjFDv7ari
Z8o8rwLgCfRTdy
BP2yMApbjMDdpDqetUQn
X3A=
=k5zA
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sponsored Links