PostgreSQL - Enforcing unique constraint on date column parts

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

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

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
as
$$
   select date_trunc('month', p_date)::date;
$$
language SQL
immutable;

Then you can use that for the index:

create unique index on foo ((start_of_month(mydate)));