UPSERT (INSERT ON CONFLICT)
UPSERT (INSERT ON CONFLICT) is a SQL statement in the PostgreSQL Specific category. PostgreSQL upsert: INSERT or UPDATE if there's a conflict on a unique/primary key. The syntax is INSERT INTO table VALUES (...) ON CONFLICT (column) DO UPDATE SET col=value;. It returns rows affected. A typical example: INSERT INTO users (id, name, count) VALUES (1, 'Alice', 1) ON CONFLICT (id) DO UPDATE SET count = users.count + 1; -- If user 1 exists, increment count; otherwise insert -- DO NOTHING variant: INSERT INTO users (id, name) VALUES (1, 'Bob') ON CONFLICT (id) DO NOTHING; A close relative is SERIAL, which postgreSQL auto-increment type. Creates an INTEGER column with a sequence. Equivalent to AUTO_INCREMENT in MySQL. A close relative is RETURNING, which postgreSQL extension that returns values from INSERT/UPDATE/DELETE statements (like OUTPUT in SQL Server). A close relative is ARRAY, which postgreSQL supports array columns and array operations. Can be multi-dimensional.