Format function for safe formatting in PostgreSQL

Introduction

The format function helps you build text safely by inserting values into a string without mistakes or errors.

When you want to create a message that includes numbers or names safely.

When you need to build a query or command with values inserted in the right place.

When you want to avoid errors from mixing text and numbers in one string.

When you want to keep your text clear and easy to read with placeholders.

When you want to prevent mistakes from manual string joining.

Syntax

PostgreSQL

format(format_string, value1, value2, ...)

The format_string uses placeholders like %s for strings, %I for identifiers (like table or column names), and %L for literals (values).

Values are safely inserted in place of placeholders, avoiding errors or injection risks.

Examples

Inserts the string 'Alice' into the placeholder %s.

PostgreSQL

SELECT format('Hello, %s!', 'Alice');

Safely inserts table name and value into a query string.

PostgreSQL

SELECT format('SELECT * FROM %I WHERE id = %L', 'users', 42);

Inserts numbers as strings into the message.

PostgreSQL

SELECT format('Price: %s, Quantity: %s', 9.99, 5);

Sample Program

This query creates a message showing a user's name and points safely formatted.

PostgreSQL

SELECT format('User %s has %s points.', 'Bob', 150);

Important Notes

Use %I for identifiers to avoid SQL injection when inserting table or column names.

Use %L for literal values to safely quote strings or numbers.

The format function returns text, so you can use it anywhere text is expected.

Summary

The format function helps build text safely with placeholders.

Use %s for strings, %I for identifiers, and %L for literals.

It prevents errors and injection by inserting values correctly.