PostgreSQL – Enforcing Unique Constraint on Date Column Parts

constraintpostgresql

I'm trying to enforce a unique month and year combination on a table with a data column. e.g.

create table foo
(
    mydate date
);

I want to enforce that only one row per month and year is valid i.e.

insert into foo values ('2018-01-01'); -- valid
insert into foo values ('2018-01-15'); -- Wouldn't be valid as one row already exists for January 2018
insert into foo values ('2018-02-15'); -- valid

The day portion is irrelevant. The application should only ever insert the first day of the month but as long as there's only one row per month & year it doesn't matter.

In Oracle or SQL Server I would be able to use deterministic functions in a function based unique index so I could use the result of date_trunc('month',mydate) and it would enforce what I want, but that doesn't seem to be possible in PostgreSQL.

I also don't seem to be able to create virtual/computed/calculated fields so can't enforce it that way either.

How should I be enforcing this constraint?

Am I doing something really silly?

Best Answer

Using EXTRACT(year/month ...) works, too:

create unique index year_month_uq 
  on foo 
  ( extract(year from mydate), 
    extract(month from mydate)
  ) ;