Useful Postgres techniques

Some notes on useful SQL commands and when they might be useful.

postgressql

This note describes key SQL commands useful for analytical queries rather than traditional relational database operations. (Joins are covered extensively elsewhere.) It concludes with helpful resources and guides.

For a comprehensive guide to SQL in data analysis, see Haki Benita’s article. This post consolidates my notes and key resources.

EXPLAIN and ANALYZE

EXPLAIN describes Postgres’s execution plan with an estimated cost. ANALYZE executes the query and returns actual cost and time.

I use these commands to check:

  1. Is the query using the indexes I intended it to use?
  2. The cost of the query whilst I am trying to optimise it for production purposes.

Common table expression to hold settings

Common table expressions (CTEs) are temporary tables lasting for the duration of a query—useful for organizing complex logic and passing values through a query.

CTEs are more performant after PG 12.

One useful pattern: use a CTE to define variables referenced later in the query.

WITH settings AS (
	SELECT
		(SELECT 1256 AS id_object),
		(SELECT '2021-02-02'::timestamp AS lower_date),
		(SELECT '2021-02-12 23:59:59'::timestamp AS upper_date)
)
SELECT *
FROM settings

This CTE can be included in your original query using a clause such as:

WITH timestamp BETWEEN (SELECT lower_date FROM settings)
AND (SELECT upper_date FROM settings)

NULLIF

NULLIF is a conditional function that returns NULL when two expressions are equal—useful for avoiding divide-by-zero errors.

LAG

LAG calculates differences between rows based on how data is PARTITIONED and ORDERED. Full documentation.

  • PARTITIONED: how to group the data before lag calculation
  • ORDERED: how to order data within each partition

CROSSTAB (Pivot tables)

CROSSTAB pivots rows into columns—useful for direct visualization. Creating dynamic columns requires complex SQL; see this Stack Overflow answer and this one.

My current preferred solution is to export the data at this stage to Pandas for further manipulation.

dbt and macros

dbt applies software engineering principles to SQL analytics. Its strengths include version control and documentation (the lineage map is particularly valuable).

However, dbt has a critical limitation: when a model rebuilds, its table drops—causing queries from production apps to hang. This happened to me, and I learned the hard way.

To work around it, I created a macro that generates a static table for the web app, dropping the last four characters of the model name to avoid conflicts. Not elegant, but it works.

The macro (where the argument is the dbt model name to protect):

A macro for importing CTEs directly into models can be seen here.

GENERATE_SERIES

GENERATE_SERIES creates a range of equally spaced values. Two common uses:

  1. Generate sample data for testing
  2. Return zero values for time periods with no data (by creating a complete date range and left-joining it against actual data)

Here are some useful articles:

  1. Fake data and better weekly reporting.
  2. Use generate series to get continous results.

Resources

  1. SQL for data analysis—comprehensive reference for analytical SQL commands
  2. GitLab’s SQL style guide—production-quality code practices
  3. PostgreSQL documentation—authoritative reference
  4. Mode SQL tutorial—practical introduction