PgZen (@PgZenApp) header
PgZen (@PgZenApp) profile picture

Native PostgreSQL client for Apple platforms

Joined February 2017
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 COALESCE to safely handle NULL math and defaults In SQL, any math or string concatenation involving NULL evaluates to NULL (100 + NULL = NULL). COALESCE returns the first non-null argument, making it essential for fallback values and safe calculations.
1
3
78
Always index your Foreign Keys Postgres automatically creates a unique index for ⁠PRIMARY KEY⁠ and ⁠UNIQUE⁠ constraints, but not for ⁠FOREIGN KEY⁠ columns. It causes slow ⁠JOIN⁠ queries and full table scans on the child table whenever a parent row is updated or deleted.
7
Never use ⁠FLOAT⁠ or ⁠REAL⁠ for money Floating-point types use binary approximations that cause rounding errors (⁠0.1 + 0.2 != 0.3⁠). Always store currency using exact ⁠NUMERIC(p, s)⁠ or integer cents (⁠BIGINT⁠).
10
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
JSON⁠ stores an exact text copy that Postgres must re-parse on every read. ⁠JSONB⁠ stores decomposed binary data, making reads much faster and unlocking ⁠GIN⁠ indexing and containment queries (⁠@>⁠), at the cost of only tiny write overhead.
5
Prefer ⁠IDENTITY⁠ over ⁠SERIAL⁠ Avoid ⁠SERIAL⁠ and ⁠BIGSERIAL⁠ for new tables. ⁠GENERATED ALWAYS AS IDENTITY⁠ is the modern SQL standard, handles sequence permissions cleanly, and blocks accidental manual ID overrides.
7
TIMESTAMPTZ and TIMESTAMP both use 8 bytes, but TIMESTAMP drops timezone context and causes silent DST bugs — prefer TIMESTAMPTZ for almost everything.
2
2
53
Postgres tip: text and varchar store data the same way, but char is space‑padded and slower — prefer text for almost everything.
🤖 Made with AI
23
Did you know? Postgres strictly follows the SQL standard. That's why SHOW COLUMNS and DESCRIBE are missing. In Postgres, information_schema.columns is your friend.
1
24
Coming from MySQL to PostgreSQL? 🤯 Forget SHOW TABLES⁠ and ⁠DESCRIBE⁠. Postgres uses different commands for metadata. Here is a quick cheat sheet for the CLI psql commands vs the actual SQL queries you can run. 👇
4
4
74
Do you remember when you joined Twitter? I do! #MyTwitterAnniversary