Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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"


  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"


  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.

share|improve this question
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
up vote 9 down vote accepted

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.

share|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
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 posting your answer, you agree to the privacy policy and terms of service.

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