Constrain input to a few different strings

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.

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.

StackExchange Database Administrators Q#23563, answer score: 16