agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Nikolay Samokhvalov <samokhvalov@gmail.com>
To: pgsql-sql@postgresql.org, "Jeff Frost" <jeff@frostconsultingllc.com>
Subject: Re: Check/unique constraint question
Date: Sun, 5 Mar 2006 12:02:58 +0300
Message-ID: <e431ff4c0603050102m36b68e08w9d84e52e1f8701cb@mail.gmail.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0603050015050.29717@discord.dyndns.org>
References: <Pine.LNX.4.64.0603050015050.29717@discord.dyndns.org>

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 FROM foo WHERE active = TRUE AND id = NEW.id
    ) THEN
        RAISE EXCEPTION 'Uniqueness violation on column id (%)', NEW.id;
    END IF;

    RETURN NEW;
END
$BODY$  LANGUAGE plpgsql;

CREATE TRIGGER foo_check BEFORE INSERT OR UPDATE ON foo
    FOR EACH ROW EXECUTE PROCEDURE foo_check();

On 3/5/06, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> 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 check the
> uniqueness of id if active=true.
>
> So, the following values would be acceptable:
>
> ('f',5)
> ('f',5)
> ('t',5)
>
> But these would not be:
>
> ('t',5)
> ('t',5)
>
> Basically, I want something like:
> ALTER TABLE bar ADD CONSTRAINT foo UNIQUE(active (where active='t'),id)
>
> But the above does not appear to exist.  Is there a simple way to create a
> check constraint for this type of situation, or do I need to create a function
> to eval a check constraint?
>
> --
> Jeff Frost, Owner       <jeff@frostconsultingllc.com>
> Frost Consulting, LLC   http://www.frostconsultingllc.com/
> Phone: 650-780-7908     FAX: 650-649-1954
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


--
Best regards,
Nikolay

view thread (11+ messages)  latest in thread

Message-ID: <e431ff4c0603050102m36b68e08w9d84e52e1f8701cb@mail.gmail.com>
Permalink:  ../e431ff4c0603050102m36b68e08w9d84e52e1f8701cb@mail.gmail.com/
Also on:    postgresql.org/message-id/e431ff4c0603050102m36b68e08w9d84e52e1f8701cb@mail.gmail.com

reply

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 using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: samokhvalov@gmail.com, jeff@frostconsultingllc.com
  Subject: Re: Check/unique constraint question
  In-Reply-To: <e431ff4c0603050102m36b68e08w9d84e52e1f8701cb@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox