Tips and tricks to kick-start Postgres year 2020

Postgres 2020 tips: cleaner dynamic SQL with format()

A short note on constructing dynamic statements in Postgres: rather than concatenating strings, the format function is preferred, and picking the right format specifier can make the code cleaner still.

Using %I as the specifier avoids having to wrap parameters in quote_ident. For example:

format('SELECT count(*) FROM %I.%I', table_schema, table_name)

For literals that would normally go through quote_nullable, the %L specifier is available as well.