I have strange behaviour of COALESCE function of PostgreSQL. Depending on arguments of this function the result is different:
SELECT
"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?
"O"."CreatedOn"
?timestamp without time zone