PostgreSQL FORMAT() function creates a formatted string by replacing placeholders with specified values. It is commonly used to generate dynamic text, SQL queries and customized output.
Syntax
SELECT FORMAT(format_string, value_1, value_2, ...);Where:
- format_string: The template string containing format specifiers.
- value_1, value_2, ...: Values inserted into the placeholders.
Common Format Specifiers
- %s: Formats the value as a string.
- %I: Formats the value as an SQL identifier (table or column name).
- %L: Formats the value as an SQL literal.
Example: Format a String
Query:
SELECT FORMAT('Welcome to %s!', 'PostgreSQL');Output:
- FORMAT() function replaces the %s placeholder with the string 'PostgreSQL'.
Working
Consider the following customers table for the examples below:
Use FORMAT() with Table Data
Query:
SELECT FORMAT('%s %s', first_name, last_name) AS full_name
FROM customers;
Output:
- FORMAT() replaces each %s placeholder with the corresponding column value.
Use FORMAT() with Position Specifiers
Query:
SELECT FORMAT(
'%1$s purchased a %2$s. %1$s rated it 5 stars.',
'John',
'Laptop'
);
Output:
- %1$s and %2$s let you reuse the same arguments multiple times.
Build a Dynamic SQL Statement
Query:
SELECT FORMAT(
'SELECT * FROM %I WHERE customer_id = %L;',
'customers',
101
) AS Query;
Output:
- %I formats table/column names, while %L formats SQL literal values.