Similiar UPDATE instruction in PostgreSQL-CodePudding

Is there an example of a 2-columns table, (x, y - INTEGER), which given instructions:

UPDATE tab SET x = y WHERE x != y;

UPDATE tab SET x = y WHERE 1 = 1 OR (NULL IS NOT NULL);

will show different results?

CodePudding user response：

Sure:

CREATE TABLE tab (x integer, y integer);
INSERT INTO tab VALUES (NULL, 1);

/* doesn't update a single row */
UPDATE tab SET x = y WHERE x != y;

TABLE tab;

TABLE tab;
   x    │ y 
════════╪═══
 (null) │ 1
(1 row)

/* updates a row */
UPDATE tab SET x = y WHERE 1 = 1 OR (NULL IS NOT NULL);

TABLE tab;

 x │ y 
═══╪═══
 1 │ 1
(1 row)

CodePudding user response：

In the first statement, x != y evaluates to true only if x and y are not nulls. So if (x,y), are any combination of a value and a null, x will remain unchanged.

In the second statement, the where condition evaluates to true, and can just be omitted altogether - all the x values will be updated with their corresponding y values.