Use INSERT ... ON CONFLICT for safe upserts.
Avoid SELECT followed by INSERT or UPDATE in application code—it suffers from race conditions under concurrent writes. Use INSERT ... ON CONFLICT with a UNIQUE constraint or index to handle conflicts atomically.
#PostgreSQL #SQL
4
Use IS DISTINCT FROM for null-safe comparisons
In SQL, NULL = NULL and NULL != 'active' both evaluate to UNKNOWN (falsy), which silently drops NULL rows from WHERE clauses.
IS DISTINCT FROM treats NULL as a comparable value, making it much cleaner than chaining OR col IS NULL.
14
Use RETURNING to avoid extra SELECT round-trips
Don't run a second query just to fetch generated IDs, defaults, or updated values. Add RETURNING to INSERT, UPDATE, or DELETE to get the mutated rows back in a single database round-trip. #postgresql
12














