The SQL FORMAT Function: String Templates with %s, %I and %L in PostgreSQL

When you need to assemble a string from pieces -- a greeting, a table path, or a whole query for dynamic SQL -- concatenation with || quickly turns into an unreadable soup of quotes. PostgreSQL gives you format(), which works from a template like C's printf and can safely interpolate identifiers and literals.

Basic syntax and %s

format(template, args...) takes a template string and substitutes arguments for the specifiers. The simplest is %s, which inserts a value as text:

SELECT format('Hi %s, id=%s', name, id) AS greeting
FROM users
WHERE country = 'US';

Things to know about %s:

• Any argument is coerced to text via its own ::text, so numbers, dates, and booleans just work.
• NULL becomes an empty string, not the word NULL -- a common surprise.
• To insert a literal percent sign, double it: %%.

-- NULL becomes an empty string, not the text 'NULL'
SELECT format('name=[%s]', NULL) AS demo;   -- name=[]
SELECT format('100%% done') AS pct;          -- 100% done

%I and %L: safe dynamic SQL

The main reason to love format() is the %I and %L specifiers. %I renders an argument as an identifier (a table or column name), and %L renders it as a string literal, with all quote escaping handled.

-- %I quotes an identifier, %L quotes a literal
SELECT format('SELECT * FROM %I WHERE email = %L', 'users', 'a@b.com');
-- SELECT * FROM users WHERE email = 'a@b.com'

This is your protection against SQL injection in dynamic queries. Compare it with naive concatenation inside a function:

CREATE FUNCTION count_by_status(tbl text, st text)
RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE
  n bigint;
BEGIN
  -- Safe: %I and %L handle quoting and escaping for us
  EXECUTE format('SELECT count(*) FROM %I WHERE status = %L', tbl, st)
  INTO n;
  RETURN n;
END;
$$;

SELECT count_by_status('orders', 'paid');

If this used '... WHERE status = ''' || st || '''' instead, a value of st like x'' OR ''1''=''1 would break the query. %L escapes the apostrophes automatically, and %I correctly quotes a name like weird table or a reserved word such as order.

Gotcha: do not pass a schema-qualified public.orders to a single %I -- you would get one identifier "public.orders". Pass the parts separately: format('%I.%I', 'public', 'orders').

Positional specifiers

When one argument is needed several times, the positional form %n$ is handy. The digit is the argument number, starting at one:

-- %1$ refers to the first argument, reused twice
SELECT format('%1$s <%2$s> aka %1$s', name, email)
FROM users
LIMIT 3;

The template stays short, and you avoid listing an argument twice. The positional form mixes freely with %I and %L:

SELECT format(
  'INSERT INTO %1$I (email) VALUES (%2$L) -- into %1$I',
  'users', 'new@b.com'
);

FORMAT vs concatenation

You can build the same result with ||, but the cost is readability and safety. Compare two versions of a notification line:

-- Concatenation: hard to read, easy to misplace a quote
SELECT 'Order ' || o.id || ' for ' || u.name
       || ': ' || o.amount || ' (' || o.status || ')'
FROM orders o JOIN users u ON u.id = o.user_id;

-- format(): the template reads like the output
SELECT format('Order %s for %s: %s (%s)', o.id, u.name, o.amount, o.status)
FROM orders o JOIN users u ON u.id = o.user_id;

Why format() usually wins:

• The template is visible as a whole, without the visual noise of quotes and ||.
• NULL does not poison the whole string: with concatenation 'a' || NULL yields NULL, while in format() it is just an empty substitution.
• For dynamic SQL, %I/%L give you protection that || cannot offer at all.

MySQL and ClickHouse

An important portability note: MySQL has a function with the same name FORMAT, but it does something else -- it formats a number with grouping separators, it does not build a string from a template:

-- MySQL: FORMAT formats a NUMBER, not a template
SELECT FORMAT(1234567.891, 2);   -- 1,234,567.89

The template equivalent in MySQL is CONCAT, CONCAT_WS (with a separator), and the printf-like MAKE_SET/ELT for niche cases. There is no direct equivalent of %I/%L; for safe dynamic SQL use prepared statements with ? placeholders. ClickHouse offers a format() with Python-style braces {0}, {1}:

-- ClickHouse: positional braces, not percent specifiers
SELECT format('Hi {0}, id={1}', name, toString(id)) FROM users;

Bottom line: in PostgreSQL format() is both a convenient printf for reports and the one correct way to build dynamic SQL, thanks to %I and %L. In MySQL and ClickHouse the same-named functions do something entirely different -- check the docs.