I have strange behaviour of COALESCE function of PostgreSQL. Depending on arguments of this function the result is different:

    "CreatedOn", -- 7/24/2020 8:15:03 AM
    COALESCE("O"."CreatedOn", NOW()) "coalesce_with_now", -- 7/24/2020 11:15:03 AM
    COALESCE("O"."CreatedOn", TO_TIMESTAMP('2020-10-12 10:05:05.260', 'YYYY-MM-DD HH24:MI:SS.FF3')) "coalesce_with_const", -- 7/24/2020 11:15:03 AM
    COALESCE("O"."CreatedOn", NOW() at time zone 'utc') "coalesce_with_now_at_utc", -- 7/24/2020 8:15:03 AM
    COALESCE("O"."CreatedOn") "only_coalesce", -- 7/24/2020 8:15:03 AM
    CASE WHEN "O"."CreatedOn" IS NULL THEN NOW() ELSE "O"."CreatedOn" END "case_analogue", -- 7/24/2020 11:15:03 AM
    CASE WHEN "O"."CreatedOn" IS NOT NULL THEN "O"."CreatedOn" ELSE NOW() END "case_analogue2" -- 7/24/2020 11:15:03 AM
FROM "Opportunity" "O";

Original value is stored in utc. Server has timezone +3.

So it changes timezone if any other values are presenting in its arguments. I read documentation of COALESCE method and found no information about such behaviour of this function.

Version of PostgreSQL server: PostgreSQL 11.9 (Debian 11.9-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

On other instances (with newer version) I can't reproduce this case. Is it just a bug or this function depends on some server configuration params?

  • What's the data type of "O"."CreatedOn"?
    – mustaccio
    Commented Jan 14, 2022 at 14:42
  • It's timestamp without time zone Commented Jan 18, 2022 at 8:56
  • How do you populate the opportunity table? Perhaps a working example on dbfiddle.uk would help?
    – Vérace
    Commented Jan 24, 2022 at 1:08


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.