PostgreSQL создает индекс при переносе из строки на дату

Я пытаюсь создать индекс на листе столбца varchar на сегодняшний день. Я делаю что-то вроде этого:

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

Я получаю сообщение об ошибке: functions in index expression must be marked IMMUTABLE Но я не понимаю, почему актер на данный момент не зависит от часового пояса или чего-то подобного (что делает приведение в метку времени с часовым поясом, давая эту ошибку).

Любая помощь?

Ответ 1

Ваша первая ошибка заключалась в том, чтобы сохранить дату в качестве столбца varchar. Вы не должны этого делать.

Правильное исправление для вашей проблемы - преобразовать столбец в реальный столбец date.

Теперь я уверен, что ответ на этот вопрос: "Я не проектировал базу данных, и я не могу ее изменить", поэтому обходной путь:

CAST и to_char() не являются неизменяемыми, потому что они могут возвращать разные значения для одного и того же входного значения в зависимости от текущих настроек сеанса.

Если вы знаете, что у вас есть согласованный формат всех значений в таблице (что - если бы вы имели - означало бы, что вы можете преобразовать столбец в реальный столбец date), тогда вы можете создать свою собственную функцию, которая преобразует varchar к дате и обозначается как неизменный.

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
/

С помощью этого определения вы можете создать индекс для выражения:

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

Но вы должны использовать именно этот вызов функции в своем запросе, чтобы Postgres его использовал:

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

Обратите внимание, что этот подход сильно не удастся, если у вас есть только одно "незаконное" значение в столбце varchar. Единственное разумное решение -, чтобы хранить даты как date s,

Ответ 2

Укажите версию базы данных, таблицу ddl и некоторые примеры данных.

Будет ли ваша собственная неизменная функция делать то, что вы хотите, например? Также изучите создание нового акта в документах и посмотрите, что для вас что-то делает.

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