PostgreSQL upsert: INSERT … ON CONFLICT DO UPDATE and MERGE

npm init -y >/dev/null
npm install --silent --no-audit --no-fund @electric-sql/pglite@0.5.10 >/dev/null 2>&1
cat > q.mjs <<'EOF'
import { PGlite } from "@electric-sql/pglite";
const db = new PGlite();
const show = async (sql) => console.log(JSON.stringify((await db.query(sql)).rows));
await db.query(`create table users (id int primary key, email text, logins int default 0)`);
const upsert = `insert into users (id, email, logins) values (1, $1, 1)
  on conflict (id) do update set email = excluded.email, logins = users.logins + 1
  returning *`;
console.log(JSON.stringify((await db.query(upsert, ["a@example.com"])).rows));
console.log(JSON.stringify((await db.query(upsert, ["b@example.com"])).rows));
await show(`insert into users (id, email) values (1, 'c@example.com') on conflict do nothing returning *`);
await show(`merge into users u
  using (values (1, 'm@example.com'), (2, 'n@example.com')) as s(id, email) on u.id = s.id
  when matched then update set email = s.email
  when not matched then insert (id, email) values (s.id, s.email)
  returning merge_action(), u.id, u.email`);
EOF
node q.mjs

INSERT INTO table (cols) VALUES (...)
ON CONFLICT (unique_col [, ...])          -- or ON CONFLICT ON CONSTRAINT name
DO UPDATE SET col = EXCLUDED.col
  [WHERE table.updated_at < EXCLUDED.updated_at]   -- update only when newer
RETURNING *;