Postgres SQL constraint a character type

Question

I have a table definition in Postgres. I would like to add a constraint to a column that is of Character datatype to have only 3 allowed values:

CREATE TABLE my_table
(
  id character varying(255) NOT NULL,
  uid character varying(255) NOT NULL,
  my_text text NOT NULL,
  is_enabled boolean NOT NULL
);

So I want the my_text column to contain only 'A', 'B' or 'C' as values.

Where can I find some documentation on this?

Answer 1

Use a check constraint:

CREATE TABLE my_table
(
  id character varying(255) NOT NULL,
  uid character varying(255) NOT NULL,
  my_text text NOT NULL,
  is_enabled boolean NOT NULL,
  constraint check_allowed check (my_text in ('A', 'B', 'C'))
);

More details in the manual: http://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS

Answer 2

If you want to be able to add caracters without modyfying the condition:

CREATE TABLE my_ref
(
  my_value varying(1) PRIMARY KEY
)

INSERT INTO my_ref VALUES( 'A' );
INSERT INTO my_ref VALUES( 'B' );
INSERT INTO my_ref VALUES( 'C' );

CREATE TABLE my_table
(
  id character varying(255) NOT NULL,
  uid character varying(255) NOT NULL,
  my_text text NOT NULL,
  is_enabled boolean NOT NULL,
  constraint check_allowed FOREIGN KEY( my_text ) REFERENCES my_ref( my_value )
);

You won't be able to add values in my_text that aren't in my_ref table.