On Sun, 5 Mar 2006, Michael Glaesemann wrote:
On Mar 5, 2006, at 17:25 , Jeff Frost wrote:
I believe you're looking for what is called a partial index.
http://www.postgresql.org/docs/current/interactive/indexes-partial.html
create unique index foo_partial_idx on foo (id) where active;
Than
On Sun, Mar 05, 2006 at 12:02:58PM +0300, Nikolay Samokhvalov wrote:
> Unfortunately, at the moment Postgres doesn't support subqueries in
> CHECK constraints, so it's seems that you should use trigger to check
> what you need
The OP could also use a check constraint with a function if
everything
On Mar 5, 2006, at 17:25 , Jeff Frost wrote:
And would like to make a unique constraint which would only check
the uniqueness of id if active=true.
I believe you're looking for what is called a partial index.
http://www.postgresql.org/docs/current/interactive/indexes-partial.html
Note, I'v
On Mar 05 12:02, Nikolay Samokhvalov wrote:
> Unfortunately, at the moment Postgres doesn't support subqueries in
> CHECK constraints
I don't know how feasible this is but, it's possible to hide subqueries
that will be used in constraints in procedures. Here's an alternative
method to Nikolay's:
just a better way (workaround for subqueries in check constraints...):
CREATE OR REPLACE FUNCTION id_is_valid(
val INTEGER
) RETURNS boolean AS $BODY$
BEGIN
IF val IN (
SELECT id FROM foo WHERE active = TRUE AND id = val
) THEN
RETURN FALSE;
ELSE
RETURN TRUE
Unfortunately, at the moment Postgres doesn't support subqueries in
CHECK constraints, so it's seems that you should use trigger to check
what you need, smth like this:
CREATE OR REPLACE FUNCTION foo_check() RETURNS trigger AS $BODY$
BEGIN
IF NEW.active = TRUE AND NEW.id IN (
SELECT id
I have a table with the following structure:
Column | Type | Modifiers
+-+---
active | boolean | not null default true
id | integer | not null
(other columns left out)
And would like to make a unique constraint which would only
