11

Hi I can't seem to get a constraint working the way I expect in postgreSQL. From within pgadmin I execute the following SQL query.

-- Check: "TypeCheck"

-- ALTER TABLE "ComLog" DROP CONSTRAINT "TypeCheck";

ALTER TABLE "ComLog"
  ADD CONSTRAINT "TypeCheck" CHECK ("Type" SIMILAR TO 'email|post|IRL|minutes');
COMMENT ON CONSTRAINT "TypeCheck" ON "ComLog" IS 'One of email|post|IRL|minutes';

When executed this is converted to.

-- Check: "TypeCheck"

-- ALTER TABLE "ComLog" DROP CONSTRAINT "TypeCheck";

ALTER TABLE "ComLog"
  ADD CONSTRAINT "TypeCheck" CHECK ("Type" ~ similar_escape('email|post|IRL|minutes'::text, NULL::text));
COMMENT ON CONSTRAINT "TypeCheck" ON "ComLog" IS 'One of email|post|IRL|minutes';

I expect this to limit my input for the Types column to one of email post IRL or minutes. However when entering table data this constraint fails when i enter one of these types. The Types column is of character type. Does anyone know how to fix this. Thanks.

  • 3
    Unless I'm not understanding something, is there a reason it wouldn't be much easier to read to do CHECK (type in ('email','post','IRL','minutes') ? – rfusca Sep 2 '12 at 23:55
15

Change your constraint to

CHECK (type IN ('email','post','IRL','minutes'))

This will be converted by the parser into:

CHECK (type = ANY( ARRAY['email','post','IRL','minutes']))

That should do what you are looking at.

However I have to wonder if it wouldn't be better to do this:

CREATE TABLE comlog_types (
     type text
);

And then add a foreign key to enforce the constraint. This would make it easier to add types in the future.

|improve this answer|||||
  • Thanks, that's solved it perfectly. I might look into changing to the foreign key method in the future. – wookie1 Sep 3 '12 at 0:20
  • 2
    Yeah, it does work nicely. But you may also want to check out PostgreSQL's enum data type. – XåpplI'-I0llwlg'I - Feb 24 '15 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.