Interval type without time in PostgreSQL

Question

I'm using PostgreSQL type INTERVAL in pair with java.time.Period type on application side.

What I want is to restrict insertion into INTERVAL column of all fields except year, month and day.

I know that there's [ fields ] part in INTERVAL type definition, but I can put only YEAR TO MONTH in it:

CREATE TABLE test_interval (
  col1 INTERVAL YEAR TO MONTH -- only years and month allowed
)

And I need:

-- not a real SQL
CREATE TABLE test_interval (
  col1 INTERVAL YEAR TO MONTH -- only years, month and days allowed
)

Is there any workaround for this?

Answer 1

try this :

create table test_interval 
(col1 interval
,constraint col1_check check(date_trunc('day', col1) = col1)
)