A function for formatting a string with placeholder

format() is a system function  for formatting a string with format specifiers, similar to sprintf() et al.

format() was added in PostgreSQL 9.1.


format ( formatstr text [, formatarg any [, ...] ])

The format specifiers are similar to those used in sprintf() implementations. format() also provides the SQL-specific format specifiers %I and %L, which treat the argument value as an SQL identifier or an SQL literal respectively. These are useful for generating dynamic queries.

Note that unlike the standard C sprintf(), format() allows format specifiers with and without position fields to be mixed in the same format string

Change history


Basic usage example for format():

postgres=# SELECT format('Hello %s', 'World');
 Hello World
(1 row)

Using the SQL-specific format specifiers %I and %L:

postgres=# SELECT format('INSERT INTO %I VALUES(%L)', 'CamelCaseTable', E'O\'Reilly');
 INSERT INTO "CamelCaseTable" VALUES('O''Reilly')
(1 row)
  • PostgreSQL documentation: format


String manipulation, System function