Skip to content
Advertisement

Similiar UPDATE instruction in PostgreSQL

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?

Advertisement

Answer

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)
2 People found this is helpful
Advertisement