Builds a string from a template, substituting values for %s.
FORMAT('<template>', <value1>, <value2>, …)
In the template, %s is a slot for a value as text, %I — for a table or column name (quoted if needed), %L — for a value as an SQL literal in single quotes. More convenient than long chains of ||, especially when there are many values.
employees
| name | department | salary | bonus | phone |
|---|---|---|---|---|
| Emma | backend | 1200 | 300 | +44 7700 900123 |
| Liam | backend | 1000 | NULL | NULL |
| Olivia | frontend | 1350 | 150 | +44 7700 900456 |
| Noah | frontend | 950 | NULL | +44 7700 900789 |
| Sophia | design | 1200 | 200 | NULL |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| greeting |
|---|
| Hello, Emma! |
| sentence |
|---|
| Emma works in backend and earns 1200 |
| Liam works in backend and earns 1000 |
| Olivia works in frontend and earns 1350 |
| Noah works in frontend and earns 950 |
| Sophia works in design and earns 1200 |
| query |
|---|
| SELECT * FROM employees WHERE name = 'O''Brien' |
%L put the value in single quotes and doubled the apostrophe inside by itself. That's how SQL is built in PL/pgSQL functions without fear of SQL injection.
NULL through %s becomes an empty string: FORMAT('phone: %s', NULL) gives phone: . %L, on the other hand, writes the word NULL.
To output a percent sign itself, write %%: FORMAT('%s%%', 50) gives 50%.
Fewer values than slots in the template is an error: too few arguments for format().