UPDATE Statement - SQL Server to PostgreSQL Migration

In SQL Server and PostgreSQL, the UPDATE statement updates rows in the table, however, there are some differences in syntax, clauses and restrictions.

SQL Server allows you to use the qualified columns i.e. table.column in the SET and WHERE clauses:

SQL Server:

  -- Use qualified columns in SET and WHERE clauses
  UPDATE t1
  SET t1.c1 = 0
  WHERE t1.c1 IS NULL;
  /* Ok */

PostgreSQL:

In PostgreSQL, you can qualify the columns in the WHERE clause only:

  -- Qualified columns are not allowed in SET
  UPDATE t1
  SET t1.c1 = 0
  WHERE t1.c1 IS NULL;
  /* ERROR:  column "t1" of relation "t1" does not exist
      HINT:  SET target columns cannot be qualified with the relation name. */
 
  -- Qualified columns can be used in WHERE
  UPDATE t1
  SET c1 = 0
  WHERE t1.c1 IS NULL;
  /* Ok */

For more information, see SQL Server to PostgreSQL Migration.