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?

2 Answers 2


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

create unique index year_month_uq 
  on foo 
  ( extract(year from mydate), 
    extract(month from mydate)
  ) ;
  • That just occurred to me too ;)
    – user1822
    Jun 27, 2018 at 14:42

date_trunc() is not marked immutable because some input parameters can make it dependent on the environment so that it might return different results in different situations - which is not allowed for immutable functions. Because of that, you can't use it in an index.

However we know that date_trunc('month', mydate)::date is safe because it does not depend on the locale or time zone or something else. So it's possible to create your own function that is marked as immutable to be used in the index definition:

create function start_of_month(p_date date)
  returns date
   select date_trunc('month', p_date)::date;
language SQL

Then you can use that for the index:

create unique index on foo ((start_of_month(mydate)));
  • @jutky: thanks, actually it should use the parameter's name p_date
    – user1822
    Jan 17, 2020 at 8:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.