INSERT ON EXISTING UPDATE - Sybase SQL Anywhere to PostgreSQL Migration

In Sybase SQL Anywhere, the ON EXISTING UPDATE clause of the INSERT statement allows you to update (upsert) existing rows in a table based on the primary key.

In PostgreSQL, you can use the ON CONFLICT DO UPDATE clause.

Sybase SQL Anywhere:

  -- Sample table
  CREATE TABLE colors (id INTEGER PRIMARY KEY, name VARCHAR(30));
 
  INSERT INTO colors VALUES (1, 'Green');
 
  -- Update (upsert) the value for the same primary key ID = 1   
  INSERT INTO colors ON EXISTING UPDATE VALUES (1, 'Blue');

In PostgreSQL, you can use the ON CONFLICT DO UPDATE clause as follows:

PostgreSQL:

  -- Sample table
  CREATE TABLE colors (id INTEGER PRIMARY KEY, name VARCHAR(30));
 
  INSERT INTO colors VALUES (1, 'Green');
 
  -- Update (upsert) the value for the same primary key ID = 1   
  INSERT INTO colors VALUES (1, 'Blue') 
    ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

Note that we use the special EXCLUDED table to refer to the newly inserted values in PostgreSQL.

For more information, see Sybase SQL Anywhere to PostgreSQL Migration.