3

I'm using Postgres 9.1. The following query does not work as expected. Coalesce should return the first non-null value. However, this query returns null (1?) instead of the date (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

What am I doing wrong? Any workaround?

Edit: This may have nothing to do with Coalesce at all. I tried some experiments with Case When constructs; it turns out, Postgres has this big ugly bug where it treats TO_DATE('','yyyymmdd') as not null, even though selecting it returns null.

[PS: Strike-out above to avoid misleading. Postgres doesn't have a bug, but rather does not treat empty strings as null. See answer.]

ADTC
  • 8,999
  • 5
  • 68
  • 93
  • In 9.0, `to_date('', 'yyyymmdd')` is `0001-01-01 BC`. Why do you think `to_date('', 'yyyymmdd')` should be NULL? The [documentation for `to_date`](http://www.postgresql.org/docs/current/interactive/functions-formatting.html) makes no mention of NULL anywhere. I can't find any mention of what `to_date` does if the string doesn't mention the template but I've only done a cursory review. I don't have a copy of the standard handy so I don't know what it has to say. – mu is too short Jul 01 '13 at 04:15
  • Also, it is worth noting that `to_date('00000000', 'yyyymmdd')` is also `0001-01-01 BC` so perhaps *missing* means *zero*. – mu is too short Jul 01 '13 at 04:19
  • It doesn't make sense for `to_date('',...)` to be `NULL`; you're giving it a non-null input. If anything it should be an error. "selecting it" produces the non-null (but still horrible) result `0001-01-01 BC`. There's no evidence of a bug here, and it isn't clear from your post exactly what you think this supposed bug would be since you don't define the exact behaviour you expect. Are you expecting PostgreSQL to treat the empty string and null as the same thing? – Craig Ringer Jul 01 '13 at 05:13
  • @muistooshort I didn't add the 'postgresql-9.1' tag, because this question didn't seem _specific_ to the 9.1 version (check the tag description). I only mentioned the version so that everyone knows what syntax to expect. But whatever **:-)** – ADTC Jul 01 '13 at 06:50
  • @CraigRinger unfortunately, yes. Coming from Oracle, I tend to treat '' as null - I am trying to kick this habit since Postgres does not do so. Anyway, when I run the query with the empty string, it actually returns, null with a weird error/warning: **PgManager.exe: cannot convert value '0001-01-01 BC'. 1 rows returned** << I think it tries to return (1) but evaluation fails. I'm only using EMS SQL Manager to run the queries, so I don't yet know how the behavior will be different in an automated environment (whether it will be a _stop_ error, or just an anomaly condition that returns null). – ADTC Jul 01 '13 at 06:56
  • 2
    @ADTC Sounds like a bug in EMS SQL Manager to me. Try it in `psql` and you'll get the expected result: `SELECT to_date('','yyyymmdd');` returns `0001-01-01 BC`, a valid and reasonable date. Personally I think it *should* return an error, but since it doesn't, the client should accept the result. Sounds like the the client you're using might need some help understanding that date; what does `SELECT DATE '0001-01-01 BC';` in the client do? It's kind of scary that your tool converts a result into a `NULL` with a *warning*, that's almost a MySQL-like level of dodgy. – Craig Ringer Jul 01 '13 at 07:37
  • Tried it, same warning with null return value... Anyway thanks to the answer and comments here I have a better understanding of Postgres NULL handling and date handling. I don't really care about bugs in EMS; as long as I can write better queries I'm happy `:)` – ADTC Jul 01 '13 at 07:47

1 Answers1

8
SELECT TO_DATE('','yyyymmdd');

doesn't evaluates to NULL since you passing an empty string instead of NULL as an argument to TO_DATE()

This will successfully evaluate to NULL

SELECT TO_DATE(NULL,'yyyymmdd');

If you expect an empty string and want to treat it as a NULL you can use NULLIF()

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

That being said your sample code that evaluates (1) as NULL

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

and returns

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

Here is SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157
  • But can you justify `TO_DATE('','yyyymmdd')` being `0001-01-01 BC` or `to_date(null, 'yyyymmdd')` being `NULL`? Empirical results make my inner database guy cringe. – mu is too short Jul 01 '13 at 04:42
  • Well `to_date(null, 'yyyymmdd')` being `NULL` IMHO is totally normal and consistent across any RDBMS I can think of. Now `STR_TO_DATE('', '%Y%m%d')` in Mysql will also gives the zero date which happens to be `0000-00-00`. Oracle treats an empty string as NULL so `SELECT TO_DATE('') FROM dual` just gives you NULL. – peterm Jul 01 '13 at 04:54
  • 2
    Yep, `to_date` with `null` should be `null`, that's sane and sensible. I'm kind of horrified by `to_date('',...)` returning anything except an error; I can only assume it was a decision made for compatibility with other databases. – Craig Ringer Jul 01 '13 at 05:10
  • I'll agree that `to_date(null, ...)` being `null` itself is about the only sensible thing to happen in SQL but I see no justification for `''` being `0000-00-00`. Using MySQL as an example while arguing about sensible database behavior is pushing it though :) – mu is too short Jul 01 '13 at 05:13
  • I find that in my 9.1 DB instance, the query in my question returns _null_, not February 1 as I expect and you get. I suppose it's one of the things that isn't consistent across implementations of Postgres (SQLFiddle, I found, is not very reliable). But thanks for the `NULLIF()` tip. **It works!** – ADTC Jul 01 '13 at 06:46