PostgreSQL - Enforcing unique constraint on date column parts

Question

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?

Answer 1

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

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

• user1822: That just occurred to me too ;)

Answer 2

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)));

• user1822: @jutky: thanks, actually it should use the parameter's name p_date