Sami Sami - 7 months ago 15
SQL Question

Constraint to check if time greater than 9 am

How to check time entry only so that any entry before is not acceptable?

CREATE TABLE demo.event(

ecode CHAR(4) UNIQUE NOT NULL PRIMARY KEY,
edesc VARCHAR(20) NOT NULL,
elocation VARCHAR(20) NOT NULL,
edate DATE NOT NULL
CONSTRAINT date_check CHECK(edate BETWEEN '2016/04/01' AND '2016/04/30'),
etime TIME NOT NULL
CONSTRAINT time_check CHECK(etime (HOUR > '08:00:00')),
emax SMALLINT NOT NULL
CONSTRAINT emax_check CHECK(emax >=1 OR emax<=1000)
);


The above code gave me an error


ERROR: column "hour" does not exist

Answer

To write a time literal, you need to use the keyword time not hour:

CONSTRAINT time_check CHECK(etime > TIME '08:00:00'),

so any entry before 9:00 am not acceptable.

contradicts the 08:00:00' you have used, > TIME '08:00:00' will allow 08:00:01 (one second after 8am). If you really only want to allowed values after 9am, then use:

CONSTRAINT time_check CHECK(etime > TIME '09:00:00'),

You should also use proper ISO formatted dates to avoid any ambiguity:

CONSTRAINT date_check CHECK(edate BETWEEN DATE '2016-04-01' AND DATE '2016-04-30')

More details on the proper syntax for date and time literals can be found in the manual:

http://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-INPUT