FORMAT

Build a formatted string by substituting arguments into a format template.

Category: stringReturns: VARCHARDialect: PostgreSql

Syntax

FORMAT(fmt, arg1, arg2, ...)

Description

## Overview Builds a string by substituting each positional argument into the format template according to the placeholder type. FORMAT is the safest way to compose dynamic SQL fragments because %I quotes identifiers and %L quotes literals with proper escaping, defusing most injection attacks that string concatenation would expose. For simple display strings, %s is a plain text substitution similar to standard printf. For SQL generation, prefer %I for table and column names and %L for user-provided values. ## Behavior - Returns NULL when the format template is NULL. - Returns the template unchanged when no placeholders are present. - Supported specifiers: %s (plain), %I (SQL identifier), %L (SQL literal), %% (literal percent sign). - %s of NULL produces the literal text NULL in the output. - %I of NULL produces the literal text NULL (unquoted) and usually signals a bug; validate identifiers upstream. - %L of NULL produces the SQL keyword NULL (unquoted), suitable for direct inclusion in SQL. - The number of arguments must match the number of placeholders; extras are ignored silently and shortages raise an error. - Operates on Unicode code points; produces valid UTF-8 output. ## Compatibility - Matches the common SQL FORMAT specification for the %s, %I, %L, and %% specifiers. - Does not support printf-style width, precision, or type modifiers.

Parameters

Name	Type	Description
`fmt`		Specifies the format template. Supported placeholders: %s inserts the argument's text representation; %I quotes the argument as a SQL identifier (double quotes with embedded double-quote escaping); %L quotes the argument as a SQL literal (single quotes with embedded quote escaping and NULL support); %% inserts a literal percent sign.
`args`		Specifies the values substituted into the template. The count must match the number of placeholders.

Examples

-- Basic substitution
SELECT FORMAT('Hello, %s!', 'world');  -- 'Hello, world!'

-- Multiple substitutions
SELECT FORMAT('%s has %s items', 'Alice', 5);  -- 'Alice has 5 items'

-- Identifier quoting with %I
SELECT FORMAT('SELECT * FROM %I', 'my table');  -- 'SELECT * FROM "my table"'

-- Literal quoting with %L handles embedded quotes
SELECT FORMAT('WHERE name = %L', 'O''Brien');  -- 'WHERE name = ''O''''Brien'''

-- Literal percent
SELECT FORMAT('progress: %s%%', 42);  -- 'progress: 42%'

-- NULL in format string returns NULL
SELECT FORMAT(CAST(NULL AS VARCHAR), 'x');  -- NULL

Pitfalls

FORMAT does not support printf-style width, precision, or type specifiers. %d, %f, and %05d are not recognised.
%I and %L are the only safe way to insert user input into dynamic SQL. A plain %s substitution is vulnerable to injection if the value came from user input.
%s of NULL inserts the literal text 'NULL', which is often not what you want in user-visible output. COALESCE arguments before passing them if you want an empty string fallback.
A mismatched number of arguments silently ignores extras but raises an error on shortage. Keep your placeholder count in sync with your arguments.