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?