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?

New contributor
Artem Repko is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • What's the data type of "O"."CreatedOn"?
    – mustaccio
    Jan 14 at 14:42

Your Answer

Artem Repko is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.