Constrain input to a few different strings

Question

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.

Answer 1

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.