Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Check constraint on existing column with PostgresQL

I'm trying to put check constraint on existing column.

Is there any way to achieve this from PostgreSQL?

like image 651
Passionate Engineer Avatar asked Feb 12 '15 15:02

Passionate Engineer


3 Answers

Use alter table to add a new constraint:

alter table foo 
   add constraint check_positive check (the_column > 0);

More details and examples are in the manual:
http://www.postgresql.org/docs/current/static/sql-altertable.html#AEN70043

Edit

Checking for specific values is done in the same way, by using an IN operator:

alter table foo 
   add constraint check_positive check (some_code in ('A','B'));
like image 135
a_horse_with_no_name Avatar answered Oct 19 '22 03:10

a_horse_with_no_name


If you are okay with (or want) Postgres to generate a constraint name you can use the following shorthand syntax.

ALTER TABLE foo
    ADD CHECK (column_1 > 2);
like image 37
Gino Avatar answered Oct 19 '22 03:10

Gino


You can add a new constraint with with alter table command. From documentation this example:

ALTER TABLE distributors 
ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;

You will have to replace constraint name as well as table name and content by your local requirements.

like image 22
frlan Avatar answered Oct 19 '22 03:10

frlan