20

I'm trying to create an index on the cast of a varchar column to date. I'm doing something like this:

CREATE INDEX date_index ON table_name (CAST(varchar_column AS DATE));

I'm getting the error: functions in index expression must be marked IMMUTABLE But I don't get why, the cast to date doesn't depends on the timezone or something like that (which makes a cast to timestamp with time zone give this error).

Any help?

Topo
  • 4,783
  • 9
  • 48
  • 70
  • Is there a way in pg to convert a date via a specific format rule? SQL Server does not like to do such conversions if the format must be guessed. – user2246674 May 06 '13 at 19:23

2 Answers2

24

Your first error was to store a date as a varchar column. You should not do that.

The proper fix for your problem is to convert the column to a real date column.

Now I'm pretty sure the answer to that statement is "I didn't design the database and I cannot change it", so here is a workaround:

CAST and to_char() are not immutable because they can return different values for the same input value depending on the current session's settings.

If you know you have a consistent format of all values in the table (which - if you had - would mean you can convert the column to a real date column) then you can create your own function that converts a varchar to a date and is marked as immutable. 

create or replace function fix_bad_datatype(the_date varchar)
   returns date
   language sql
   immutable
as
$body$
  select to_date(the_date, 'yyyy-mm-dd');
$body$
ROWS 1
/

With that definition you can create an index on the expression:

CREATE INDEX date_index ON table_name (fix_bad_datatype(varchar_column));

But you have to use exactly that function call in your query so that Postgres uses it:

select *
from foo
where fix_bad_datatype(varchar_column) < current_date;

Note that this approach will fail badly if you have just one "illegal" value in your varchar column. The only sensible solution is to store dates as dates,

  • 2
    @Topo: `to_date()` is also just `STABLE`, not `IMMUTABLE`, even though this one could well be. [Here](http://www.postgresql.org/message-id/flat/201111290345.pAT3jEB21598@momjian.us#201111290345.pAT3jEB21598@momjian.us) is a discussion on the devel list as to why that is. – Erwin Brandstetter May 06 '13 at 19:43
  • But when I use a query like this `select * from foo where fix_bad_datatype(varchar_column) >= '2015-02-05'::date;` without using `current_date` it takes a longer time than executing it without index. Any idea? – Marlon Abeykoon Oct 07 '15 at 06:13
  • ya sure. But my problem is when I use `current_date` it is 10x faster with index as explained in your answer. But compared to that when use `'2015-02-05'::date` usage of index is slower than index less querying. I'm just expecting a relative idea. (Configs remain unchanged in both the situations) – Marlon Abeykoon Oct 07 '15 at 06:26
4

Please provide the database version, table ddl, and some example data.

Would making your own immutable function do what you want, like this? Also look into creating a new cast in the docs and see if that does anything for you.

create table emp2 (emp2_id integer, hire_date VARCHAR(100));

insert into emp2(hire_date)
select now();

select cast(hire_date as DATE)
from emp2


CREATE FUNCTION my_date_cast(VARCHAR) RETURNS DATE
    AS 'select cast($1 as DATE)'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;


CREATE INDEX idx_emp2_hire_date ON emp2 (my_date_cast(hire_date));
Kuberchaun
  • 29,160
  • 7
  • 51
  • 59