PostgreSQL Unique Constraint Validation from Input – How to Create

indexpostgresqlpostgresql-9.2unique-constraint

I have this table name tbl with this table definition:

create table tbl (
    id serial primary key not null,
    counter integer not null default 1,
    ref_id integer not null,
    date_booked date not null
);

With these insert statement(s):

insert into tbl(counter, ref_id, date_booked) values (1, 1, '2014-07-05');
insert into tbl(counter, ref_id, date_booked) values (2, 2, '2014-07-10');
insert into tbl(counter, ref_id, date_booked) values (3, 3, '2014-07-15');
-- this should fail
insert into tbl(counter, ref_id, date_booked) values (3, 4, '2014-07-20');

I am having a problem of creating a unique constraint/index in such way tbl.counter should be unique within the month of the inputted tbl.date_booked. From the insert statements above, the 4th insert statement should fail since if violates the unique validation. The problem is I don't know how to create such validation. Though I have a bit of an idea about constraints, index, etc. it's just am confused which to choose to make this work.

Best Answer

This can be achieved by using a unique index on the combination of counter and the month of date_booked. Something like this:

create unique index idx_tbl on tbl (counter, to_char(date_booked, 'yyyymm'));

However the above will fail because to_char() is not an "immutable" function and thus cannot be used in an index (even if we know that the above is fine). The workaround for this is to create a wrapper function that is marked as immutable:

create function my_month(some_date date)
  returns varchar
  immutable 
  language sql
as  
$$
  select to_char(some_date, 'yyyymm')
$$
;

Then you can create an index using that function:

create unique index idx_tbl on tbl (counter, my_month(date_booked));

If you now insert the fourth row, you get the error:

duplicate key value violates unique constraint "idx_tbl"

If the table is big, you might want to use a number instead of a varchar in order to reduce the size of the index as well e.g. by using to_number(to_char(some_date, 'yyyymm'))

Related Question