PostgreSQL 18 reference · String Functions
PostgreSQL FORMAT() Function
Build a string from a template: %s inserts text, %I a quoted identifier and %L a quoted literal, for safe dynamic SQL.
PostgreSQL 18 Since 9.1
Syntax
format(formatstr, arg1, ...)
Parameters
| Parameter | Description |
|---|---|
formatstr |
The template: %s value as text, %I SQL identifier, %L SQL literal, %% a percent sign; %1$s picks an argument, %-10s pads. |
arg |
Values for the placeholders. |
Return value
Text.
Examples
SELECT format('%s costs %s', name, price) AS line
FROM products
LIMIT 2;
Output
line ------------------------------- SQL for Beginners costs 29.99 Clean Code costs 37.50 (2 rows)
Run this on your computer — see “Get Started” for setup.
SELECT format('SELECT * FROM %I WHERE name = %L', 'order items', 'O''Brien') AS dynamic_sql;
Output
dynamic_sql ----------------------------------------------------- SELECT * FROM "order items" WHERE name = 'O''Brien' (1 row)
Run this on your computer — see “Get Started” for setup.
SELECT format('[%-8s][%8s]', 'left', 'right') AS padded;
Output
padded ---------------------- [left ][ right] (1 row)
Run this on your computer — see “Get Started” for setup.
format() has no %d or %.2f. Format numbers with to_char() or round() first, then insert them with
%s.
Version history
| Version | Change |
|---|---|
| PostgreSQL 9.1 | Added |
In other languages
FORMAT()· SQLite
Related
CONCAT()· Join any number of values into one string, treating NULL arguments as empty strings.TO_CHAR()· Format a date, timestamp or number as text with a pattern such as 'DD Mon YYYY' or 'FM999,990.00'.LPAD()· Pad a string on the left to a given length with spaces or another string. Longer strings are cut to that length.- SQL String Functions · tutorial