0

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.

1

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

| improve this answer | |
  • Thanks. Can I ask why we need to create a function so serve as a wrapper to make date_booked formatted as yyyymm immutable? – David B Jul 10 '14 at 7:26
  • 1
    @NanoJava Because to_char() is not marked as immutable (that's why you can't use it in an index definition) and you can not change a built-in function. – a_horse_with_no_name Jul 10 '14 at 7:36
  • @NanoJava to_char() is not immutable, because it can depend on the current locale, f.ex. mon: abbreviated lower case month name (3 chars in English, localized lengths vary) – pozs Jul 10 '14 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.