Coalesce и Case-When с To_Date не работают должным образом (ошибка Postgres?)

Я использую Postgres 9.1. Следующий запрос не работает должным образом. Coalesce должен вернуть первое ненулевое значение. Однако этот запрос возвращает null (1?) вместо даты (2).

select COALESCE(
    TO_DATE('','yyyymmdd'), --(1)
    TO_DATE('20130201','yyyymmdd') --(2)
    );

--(1) this evaluates independently to null
--(2) this evaluates independently to the date,
--    and therefore is the first non-null value

Что я делаю не так? Любое обходное решение?

Редактировать: это может вообще не иметь никакого отношения к Coalesce. Я провел несколько экспериментов с конструкциями Case When; оказывается, Postgres имеет эту большую уродливую ошибку, когда он обрабатывает TO_DATE('','yyyymmdd') как не null, даже если его выбор возвращает null.

[PS: зачеркнуто выше, чтобы не вводить в заблуждение. В Postgres нет ошибки, но он не считает пустые строки нулевыми. См. ответ.]


postgresql sql to-date postgresql-9.1
person ADTC    schedule 01.07.2013    source источник
comment
В версии 9.0 to_date('', 'yyyymmdd') равно 0001-01-01 BC. Как вы думаете, почему to_date('', 'yyyymmdd') должно быть NULL? В документации для to_date нигде не упоминается значение NULL. Я не могу найти никаких упоминаний о том, что делает to_date, если в строке не упоминается шаблон, но я сделал только беглый обзор. У меня нет копии стандарта под рукой, поэтому я не знаю, что он должен сказать.   -  person mu is too short    schedule 01.07.2013
comment
Кроме того, стоит отметить, что to_date('00000000', 'yyyymmdd') также равно 0001-01-01 BC, поэтому, возможно, отсутствует означает ноль.   -  person mu is too short    schedule 01.07.2013
comment
to_date('',...) не имеет смысла быть NULL; вы даете ему ненулевой ввод. Во всяком случае, это должна быть ошибка. выбор его дает ненулевой (но все же ужасный) результат 0001-01-01 BC. Здесь нет доказательств наличия ошибки, и из вашего поста не ясно, что именно вы думаете об этой предполагаемой ошибке, поскольку вы не определяете точное ожидаемое поведение. Вы ожидаете, что PostgreSQL будет обрабатывать пустую строку и null как одно и то же?   -  person Craig Ringer    schedule 01.07.2013
comment
@muistooshort Я не добавлял тег «postgresql-9.1», потому что этот вопрос не казался конкретным для версии 9.1 (проверьте описание тега). Я только упомянул версию, чтобы все знали, какой синтаксис ожидать. Но все равно :-)   -  person ADTC    schedule 01.07.2013
comment
@CraigRinger, к сожалению, да. Исходя из Oracle, я склонен рассматривать '' как null - я пытаюсь избавиться от этой привычки, поскольку Postgres этого не делает. В любом случае, когда я запускаю запрос с пустой строкой, он фактически возвращает null со странной ошибкой/предупреждением: PgManager.exe: не может преобразовать значение '0001-01-01 BC'. Возвращается 1 строка ‹‹ Я думаю, что он пытается вернуть (1), но оценка не удалась. Я использую EMS SQL Manager только для выполнения запросов, поэтому пока не знаю, как поведение будет отличаться в автоматизированной среде (будет ли это stop ошибка или просто аномалия) условие, возвращающее null).   -  person ADTC    schedule 01.07.2013
comment
@ADTC Для меня это похоже на ошибку в EMS SQL Manager. Попробуйте это в psql, и вы получите ожидаемый результат: SELECT to_date('','yyyymmdd'); возвращает 0001-01-01 BC, допустимую и разумную дату. Лично я думаю, что он должен возвращать ошибку, но поскольку это не так, клиент должен принять результат. Похоже, клиенту, который вы используете, может понадобиться помощь в понимании этой даты; что делает SELECT DATE '0001-01-01 BC'; в клиенте? Немного пугает то, что ваш инструмент преобразует результат в NULL с предупреждением, это почти уровень хитрости, подобный MySQL.   -  person Craig Ringer    schedule 01.07.2013
comment
Попробовал, то же предупреждение с нулевым возвращаемым значением ... В любом случае, благодаря ответу и комментариям здесь, я лучше понимаю обработку Postgres NULL и обработку даты. Меня не волнуют ошибки в EMS; пока я могу писать более качественные запросы, я счастлив :)   -  person ADTC    schedule 01.07.2013

Ответы (1)


arrow_upward
7
arrow_downward 

SELECT TO_DATE('','yyyymmdd');

не оценивается как NULL, так как вы передаете пустую строку вместо NULL в качестве аргумента для TO_DATE()

Это будет успешно оценено как NULL

SELECT TO_DATE(NULL,'yyyymmdd');

Если вы ожидаете пустую строку и хотите рассматривать ее как NULL, вы можете использовать NULLIF()

SELECT TO_DATE(NULLIF(dt, ''),'yyyymmdd')
  FROM 
(
  SELECT CAST('' AS VARCHAR(32)) dt
) q

При этом ваш пример кода, который оценивает (1) как NULL

SELECT COALESCE(
    TO_DATE(NULLIF('', ''),'yyyymmdd'),       --(1)
    TO_DATE(NULLIF('20130201',''),'yyyymmdd') --(2)
);

и возвращается

|                        COALESCE |
-----------------------------------
| February, 01 2013 00:00:00+0000 |

Вот демонстрация SQLFiddle.

person peterm    schedule 01.07.2013
comment
Но можете ли вы оправдать, что TO_DATE('','yyyymmdd') является 0001-01-01 BC или to_date(null, 'yyyymmdd') является NULL? Эмпирические результаты заставляют моего внутреннего специалиста по базе данных съеживаться. - person mu is too short; 01.07.2013
comment
Что ж, to_date(null, 'yyyymmdd') быть NULL ИМХО совершенно нормально и совместимо с любой СУБД, о которой я могу думать. Теперь STR_TO_DATE('', '%Y%m%d') в Mysql также дает нулевую дату, которая оказывается 0000-00-00. Oracle обрабатывает пустую строку как NULL, поэтому SELECT TO_DATE('') FROM dual просто дает вам NULL. - person peterm; 01.07.2013
comment
Да, to_date с null должно быть null, это нормально и разумно. Я немного в ужасе от того, что to_date('',...) возвращает что-либо, кроме ошибки; Я могу только предположить, что это решение было принято для совместимости с другими базами данных. - person Craig Ringer; 01.07.2013
comment
Я согласен с тем, что to_date(null, ...) само по себе является null, это единственная разумная вещь, которая может произойти в SQL, но я не вижу оправдания тому, что '' является 0000-00-00. Однако использование MySQL в качестве примера при обсуждении разумного поведения базы данных подталкивает ее :) - person mu is too short; 01.07.2013
comment
Я обнаружил, что в моем экземпляре БД 9.1 запрос в моем вопросе возвращает null, а не 1 февраля, как я ожидаю, и вы получаете. Я полагаю, что это одна из вещей, которые не согласуются между реализациями Postgres (я обнаружил, что SQLFiddle не очень надежен). Но спасибо за совет NULLIF(). Это работает! - person ADTC; 01.07.2013
comment
@ADTC Всегда пожалуйста. Я рад, что смог помочь :) Удачи. - person peterm; 01.07.2013