HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

PostgreSQL - Enforcing unique constraint on date column parts

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqluniqueenforcingcolumndateconstraintparts

Problem

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?

Solution

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

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

Code Snippets

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

Context

StackExchange Database Administrators Q#210736, answer score: 10

Revisions (0)

No revisions yet.