PostgreSQL INSERT...ON CONFLICT Syntax:

Basic syntax:

INSERT INTO table (columns)

VALUES (values)

ON CONFLICT (conflict_column)

DO UPDATE SET column = EXCLUDED.column;

Components:

- INSERT INTO table: Standard INSERT statement

- ON CONFLICT (conflict_column): Specifies which column(s) to check for conflicts

- DO UPDATE SET: Action to take on conflict (update existing row)

- EXCLUDED.column: Refers to proposed value that would have been inserted

Example with all components:

INSERT INTO users (user_id, username, email, updated_at)

VALUES (1, 'john_doe', 'john@example.com', NOW())

ON CONFLICT (user_id)

DO UPDATE SET

username = EXCLUDED.username,

email = EXCLUDED.email,

updated_at = NOW()

WHERE users.updated_at < EXCLUDED.updated_at;

ON CONFLICT variations:

- ON CONFLICT (column): Single column conflict

- ON CONFLICT (col1, col2): Multiple column conflict (composite key)

- ON CONFLICT ON CONSTRAINT constraint_name: Conflict on named constraint

- ON CONFLICT DO NOTHING: Ignore conflict (no update)

DO UPDATE variations:

- DO UPDATE SET col = EXCLUDED.col: Update with proposed value

- DO UPDATE SET col = table.col + EXCLUDED.col: Merge values

- DO UPDATE SET col = EXCLUDED.col WHERE condition: Conditional update

MySQL INSERT...ON DUPLICATE KEY UPDATE Syntax:

Basic syntax:

INSERT INTO table (columns)

VALUES (values)

ON DUPLICATE KEY UPDATE column = VALUES(column);

Components:

- INSERT INTO table: Standard INSERT statement

- ON DUPLICATE KEY UPDATE: Action on duplicate key error

- VALUES(column): Refers to proposed value that would have been inserted

Example with all components:

INSERT INTO users (user_id, username, email, updated_at)