Преобразование значения VARCHAR в отформатированную дату, игнорируя ошибки

У меня есть столбец, содержащий даты в формате yyyy-mm-dd. Теперь я запускаю запрос SELECT, который преобразует эту дату в dd/mm/yyyy, используя TO_CHAR(dob :: DATE, 'dd/mm/yyyy') AS dob, который отлично работает. Теперь проблема, с которой я столкнулся, заключается в том, что в этом столбце есть несколько плохих записей, ниже приведен образец таблицы с хорошей записью и плохой записью:

| id |    dob     |
|----|------------|
|  1 | 2019-12-31 | // this returns 31/12/2019
|  2 | 31-12-2019 | // BAD RECORD, this returns an error
|----|------------|

Ошибка, которую я получаю на id 2:

ERROR:  date/time field value out of range: "31-12-2019"
HINT:  Perhaps you need a different "datestyle" setting.
SQL state: 22008

Я бы хотел условно проверить, в порядке ли TO_CHAR(dob :: DATE, 'dd/mm/yyyy'), иначе просто используйте доб без преобразования. Любой способ осуществить это?

Я использую Postges 12


postgresql date to-char postgresql-12
person Clint_A    schedule 20.03.2020    source источник
comment
Большая проблема заключается в том, что вы храните здесь даты в виде текста. Столбец с датой бита нужно очистить, но Postgres — не лучшее место для этого.   -  person Tim Biegeleisen    schedule 20.03.2020
comment
Да, первоначальный дизайн системы требовал только текста просто для целей отображения. Но позже была добавлена ​​функция, когда пользователи требовали определения возраста по дню рождения. Это было намного позже   -  person Clint_A    schedule 20.03.2020
comment
Вы можете определить функцию для преобразования, использующую блок try/catch, и использовать ее в операторе select.   -  person Renato    schedule 20.03.2020
comment
stackoverflow.com/a/10307443 или stackoverflow .com/questions/32791975 или stackoverflow.com/a/43761300   -  person a_horse_with_no_name    schedule 20.03.2020

Ответы (2)


arrow_upward
1
arrow_downward

Не рекомендуется хранить даты в виде строк, и ваша проблема доказывает это.
Если вы уверены, что все значения в этом столбце, имеющие формат YYYY-MM-DD, являются действительными датами, используйте оператор LIKE:

CASE 
  WHEN dob LIKE '____-__-__' THEN TO_CHAR(dob::DATE, 'dd/mm/yyyy') 
  ELSE dob
END AS dob
person forpas    schedule 20.03.2020

arrow_upward
0
arrow_downward

Я смог решить эту проблему, создав функцию, аналогичную выбранному ответу в ссылке, опубликованной @a_horse_with_no_name> обновить столбец vachar до даты в postgreSQL< /а> . Функция:

create or replace function format_date_or_return_string(str varchar)
returns text language plpgsql as $$
begin
    return to_char(to_date(str, 'yyyy-mm-dd), 'dd-mm-yyyy');
exception
    when others then return str;
end $$;

Таким образом, SELECT будет форматировать хорошие записи по желанию и игнорировать плохие записи.

SELECT id, format_date_or_return_string(dob) из выборки данных в вопросе вернет:

| 1 | 31/12/2019 | // good record
| 2 | 31-12-2019 | // ignored bad record
person Clint_A    schedule 20.03.2020