SQL & databases

Query correctness, transactions, data types and database connections.

60 troubleshooting guides · Page 2 / 3

25. What does PostgreSQL timestamptz preserve about an input timestamp?

    Timestamptz represents an instant and displays it using the session timezone. It does not retain the original named timezone as a separate field.

26. How do I query a whole day without missing fractional-second timestamps?

    An inclusive upper bound such as 23:59:59 misses values with later fractional seconds and depends on timestamp precision.

27. Should prices be stored as floating point in a SQL database?

    Binary floating point approximates many decimal fractions. Repeated calculations can expose rounding differences that are inappropriate for a precise monetary contract.

28. Why does PostgreSQL JSONB not preserve my original JSON formatting or key order?

    JSONB stores a decomposed representation optimized for processing, not the original source text. Whitespace, key order and duplicate-key details are not preserved as authored.

29. How do I distinguish JSON null from SQL NULL in PostgreSQL?

    A JSON document can contain the JSON value null while the SQL column itself remains non-NULL. Missing keys add another distinct case.

30. How do I stop a recursive CTE from looping through cyclic data?

    A recursive traversal can revisit the same node indefinitely when the graph contains a cycle. A tree-shaped assumption is not a data constraint.

31. Can a WITH query change optimization or materialization behavior?

    A CTE is not universally a free inline alias. PostgreSQL can inline or materialize eligible CTEs depending on references, query properties and explicit modifiers.

32. When should UNION ALL be used instead of UNION?

    UNION removes duplicate complete rows, while UNION ALL retains them. Deduplication can add work and may remove legitimate repeated events.

33. Why does a CHECK constraint still allow a NULL value?

    A CHECK constraint accepts a result that is true or unknown; it rejects false. A comparison involving NULL can therefore pass without proving the intended value exists.

34. Why can a unique PostgreSQL column contain multiple NULL values?

    By default, NULL values are not considered equal for a uniqueness constraint. Multiple missing values can therefore coexist.

35. Why do generated database IDs have gaps after a rollback?

    Sequence allocation is designed for concurrency, not gapless numbering. A transaction can consume a value and later roll back without returning that value.

36. How can I import a large CSV without one transaction per row?

    Per-row commits add round trips and transaction overhead. A bulk mechanism can be faster, but input validation and error recovery still matter.

37. How can I add a required column without breaking old application instances?

    A rolling deployment temporarily runs old and new code together. Making a new field immediately mandatory can break writes from the old version.

38. What should I check before CREATE INDEX CONCURRENTLY?

    Concurrent index creation reduces blocking of ordinary writes but is not a free or instantaneous operation. It performs work in multiple phases and has restrictions.

39. Why does deleting many PostgreSQL rows not immediately shrink the file?

    MVCC leaves dead row versions until maintenance can reclaim them. Ordinary VACUUM generally makes space reusable inside the table rather than returning every free byte to the operating system.

40. Why can an unqualified PostgreSQL table name resolve to the wrong schema?

    Unqualified object names follow search_path. Different roles or session settings can therefore select different objects with the same name.

41. Why does an administrator test miss a row-level security bug?

    Privileged roles and table owners can have different row-security behavior from the normal application role. Testing only as an owner can hide a missing policy or an excessive privilege.

42. How do I prove a database backup is actually restorable?

    A successful backup command proves that an artifact was produced, not that the application can recover from it. Restore testing verifies the missing half.

43. Why does SQLite accept a child row whose parent does not exist?

    Foreign-key enforcement must be enabled and verified for each relevant SQLite connection. Declaring a constraint alone should not be treated as proof of enforcement.

44. Why can SQLite still return database is locked in WAL mode?

    WAL improves reader/writer coexistence but does not permit unlimited concurrent writers. Some lock transitions and checkpoint situations can still return SQLITE_BUSY.

45. Why does a SQLite WAL file keep growing?

    Checkpoint progress can be held back by readers that keep old snapshots open. Sustained writes can then append more WAL data.

46. Why can a SQLite INTEGER column contain text?

    Ordinary SQLite tables use type affinity rather than the rigid typing behavior many developers expect from other databases. Affinity attempts conversion but is not a universal type rejection rule.

47. How should dates be stored consistently in SQLite?

    SQLite does not require one dedicated datetime storage class. Mixing text, epoch numbers and local-time strings makes comparisons unreliable.

48. Why do two SQLite :memory: connections see different tables?

    A plain `:memory:` database belongs to its individual connection. Opening another connection creates another independent database.

Ask your own question · Sign in to post questions and answers.