SQL & databases
Query correctness, transactions, data types and database connections.
60 troubleshooting guides · Page 1 / 3
Why does WHERE value = NULL return no matching rows?
NULL represents an unknown or absent value, so ordinary equality does not evaluate to true when either operand is NULL.
Why does NOT IN stop returning rows when the subquery contains NULL?
A NULL in the candidate set can make the NOT IN predicate unknown rather than true. This is a three-valued-logic issue, not necessarily missing data.
Why does filtering a LEFT JOIN remove rows with no match?
A condition on the right-hand table in WHERE is evaluated after the outer join. NULL-extended unmatched rows can fail that condition.
Why does joining two child tables inflate a SUM?
Independent one-to-many joins multiply rows: two payments and three line items can produce six joined rows for one order. Summing afterward counts repeated values.
Why do COUNT(*) and COUNT(column) produce different totals?
COUNT(*) counts rows; COUNT(column) counts rows whose expression is not NULL. After an outer join, that distinction is especially important.
Why does SUM return NULL for an empty result instead of zero?
In PostgreSQL, most aggregates other than count return NULL when no input rows contribute. An unknown total and a known zero are not automatically equivalent.
How can I select the newest row per customer without mismatching its columns?
Selecting MAX(timestamp) beside unrelated nonaggregated columns does not reliably select the complete row that owns that timestamp.
Why does LAST_VALUE return the current row instead of the last row in the group?
Window functions operate within a window frame, not necessarily the entire partition. The default frame with ordering often ends at the current peer group.
Why does OFFSET pagination show duplicate or missing rows during updates?
OFFSET addresses positions in the current ordered result. Insertions or deletions before that position shift later pages.
Why does SELECT without ORDER BY return a different order after adding an index?
Relational results have no guaranteed row order without ORDER BY. A different execution plan can expose a different physical access order.
Why does PostgreSQL ignore an index that matches my WHERE column?
The planner compares estimated costs; an index is not always cheaper than scanning many matching rows. Stale statistics, casts or expressions can also change the plan.
Why does LOWER(email) not use my ordinary email index?
An index on the raw column is different from an index on an expression applied to that column. The planner needs a compatible access path.
How should I order columns in a PostgreSQL multicolumn B-tree index?
Column order should follow actual predicates and ordering, not a universal rule that the most selective column always comes first. Leading equality constraints often narrow the scanned range effectively.
Why does a partial index work for one query but not a parameterized version?
The planner must prove that the query condition implies the partial-index predicate. A generic parameterized plan may not know the parameter value needed for that proof.
Does a PostgreSQL foreign key automatically create an index on the child column?
No automatic child-side index should be assumed. The referenced key needs suitable uniqueness, but referencing columns may need their own workload-driven index.
Can EXPLAIN ANALYZE modify data while I am inspecting a query?
Yes. ANALYZE executes the statement to collect actual runtime information. With INSERT, UPDATE or DELETE, that includes the statement’s effects.
Why can two concurrent check-then-insert requests create duplicate rows?
A SELECT proving absence is only a momentary observation. Another transaction can insert before the following INSERT.
Why does PostgreSQL say the current transaction is aborted after a failed statement?
An error inside a transaction leaves it in a failed state until rollback. Sending unrelated statements on the same connection does not clear that state.
How should an application recover from a PostgreSQL deadlock?
A deadlock occurs when transactions wait on one another in a cycle. PostgreSQL aborts a participant so the others can continue.
Why must a serialization failure retry the entire transaction?
The failure means the transaction’s combined reads and writes could not be safely serialized. Repeating only the final write reuses assumptions from an invalid attempt.
How do I stop two requests from overwriting each other’s edits?
Reading a value, changing it in application memory and writing it back can lose an intervening update. The correct fix depends on the business operation.
When is SELECT FOR UPDATE SKIP LOCKED useful for a job queue?
Multiple workers can claim different available rows without waiting for each other’s already locked jobs. That is useful for queue-like workloads.
Why is an idle-in-transaction PostgreSQL session a problem?
The client has left a transaction open while doing no database work. Locks and old snapshots can remain active, obstructing maintenance and other requests.
Why does increasing a database connection pool sometimes make latency worse?
More connections can increase simultaneous work beyond the database’s useful capacity. CPU, memory, locks and disk then contend harder.
Ask your own question · Sign in to post questions and answers.