patternsqlModerate
PostgreSQL - Enforcing unique constraint on date column parts
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.
I want to enforce that only one row per month and year is valid i.e.
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?
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'); -- validThe 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.