PostgreSQL Check constraint not picking up two conditions within the code

Question

I've run into an issue with adding a check constraint to one of my tables in my database. The check constraint's purpose is to make sure that times past 12:00 on a Wednesday cannot exist. This is what I've got:

alter table module_daytime
    add CONSTRAINT CHK_TimeWED CHECK (Time BETWEEN '9:00' AND '12:00' AND Day='Wednesday')

I have already added data that abides to this rule into the table, however, when I try to add it, it still says that a row in my table violates said rule. What is wrong?

Are you sure you're using MySQL? MySQL does not do anything with CHECK constraints. There will be no error message. — Bill Karwin, Nov 09 '17 at 21:28
Please run the query `SELECT @@version;` and tell us what it says. — Bill Karwin, Nov 09 '17 at 21:29
That is one of the things I hate about MySql: it dosen't even bother to raise an error when you create a check constraint, it simply ignores it. — Zohar Peled, Nov 09 '17 at 21:32
@ZoharPeled I dislike that about MySQL too, but since the OP says the definition of the CHECK constraint *did* cause an error, I think they are not using MySQL. — Bill Karwin, Nov 09 '17 at 21:33
"oops" so fix the question. don't place such important into into just a comment. i.e. in future change the tags and headings for corrections of that sort. — Paul Maxwell, Nov 10 '17 at 03:05
is "Time" a timestamp? You will have to cast it to `time` if it is. — teppic, Nov 10 '17 at 03:14
What is the **exact** error you get? **[Edit]** your question - do **not** post code or additional information in comments. — , Nov 10 '17 at 07:04

score 0 · Answer 1 · answered Nov 10 '17 at 03:12

I cannot reproduce your issue (see below). Can you try to reproduce your table and test data for JUST that constraint.

create table module_daytime("time" time, day varchar(20) );

✓

insert into module_daytime values ('12:00', 'Wednesday');

1 rows affected

select * from module_daytime;

time     | day      
:------- | :--------
12:00:00 | Wednesday

alter table module_daytime
    add CONSTRAINT CHK_TimeWED CHECK (Time BETWEEN '9:00' AND '12:00' AND Day='Wednesday');

✓

insert into module_daytime values ('12:00', 'Wednesday');

1 rows affected

insert into module_daytime values ('12:01', 'Wednesday');

ERROR:  new row for relation "module_daytime" violates check constraint "chk_timewed"
DETAIL:  Failing row contains (12:01:00, Wednesday).

dbfiddle here

PostgreSQL Check constraint not picking up two conditions within the code

1 Answers1