Question 417858 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Is there any chance this expression returns UNKNOWN?

Given that PostgreSQL gives no guarantees as to the order in which operands are evaluated, could this expression return UNKNOWN if the last condition is evaluated before the middle one?

"TradeOffer".status = 'Pending' AND
"TradeOffer".expiration_date IS NOT NULL AND
CURRENT_TIMESTAMP > "TradeOffer".expiration_date
Tags:

2

Answers


  1. 0

    No, because select false and null; returns false.

    Or, in the where, select 1 where false and null; returns nothing (the where condition is still false)

    Login or Signup to reply.
  2. 0

    Youu could rewrite your query, to make it deterministic again

    create table "TradeOffer"(
status Text,
  expiration_date timestamp
);

    CREATE TABLE

    INSERT INTO  "TradeOffer" VALUES('1', NULL)

    INSERT 0 1

    SELECT * FROM "TradeOffer" WHERE
"TradeOffer".status = 'Pending' AND
CASE WHEN "TradeOffer".expiration_date IS NOT NULL THEN
CURRENT_TIMESTAMP > "TradeOffer".expiration_date
  ELSE FALSE
END
    status expiration_date 
    SELECT 0

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search