I'm trying to put check constraint on existing column.
Is there any way to achieve this from PostgreSQL?
asked Feb 12, 2015 at 15:22
Passionate Engineer
10.5k26 gold badges104 silver badges174 bronze badges
-
2Sure. What did you try so far? Did you check the documentation?frlan– frlan2015-02-12 15:23:32 +00:00Commented Feb 12, 2015 at 15:23
-
@frlan Postgres is a perfectly acceptable alternative name for PostgreSQLuser330315– user3303152015-02-13 07:53:01 +00:00Commented Feb 13, 2015 at 7:53
Add a comment
|
4 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'));
4 Comments
Passionate Engineer
How do you put enum checks in the constraint?
frlan
What is an enum check?
frlan
My question was directed to @Passionate Developer
Passionate Engineer
Like constriant for ("ABC", "DEF")
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);
Comments
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.
Comments
This should do it:
create table test (
id serial
);
alter table test
add constraint id_not_null
check (id is not null);
