how to create date check in postgresql

Question

When creating a table in postgres of type time its very simple to set a time check, to make sure that what is entered has to be between two times like so:

extime       TIME           NOT NULL,
check(extime > '09:00:00' and extime < '18:00:00')

I want to do something similar with a date field

exdate       DATE           NOT NULL,

to have it between the beginning and end of june.

is anybody able to give me some help?

Mureinik · Accepted Answer · 2014-11-25 17:51:35Z

1

IMHO, the easiest approach would be to extract the month from the date, and make sure it's in June:

CREATE TABLE mytable (
    exdate DATE NOT NULL,
    CHECK (EXTRACT (MONTH FROM exdate) = 6)
);

answered Nov 25, 2014 at 17:51

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user4292810 Over a year ago

Thanks a lot, haven't had a chance to try it myself but im assuming this would be valid? (sorry for poor formatting) CHECK (EXTRACT (MONTH FROM exdate) = 6) CHECK (EXTRACT (YEAR FROM exdate) = 2015) CHECK (EXTRACT (DAY FROM exdate) > 0) CHECK (EXTRACT (DAY FROM exdate) < 31)

Mureinik Over a year ago

@ayoolaadedeji looks about right - probably missing commas between the constraints though.

Collectives™ on Stack Overflow

how to create date check in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related