Format function for safe formatting in PostgreSQL - Step-by-Step Execution

Concept Flow - Format function for safe formatting

Start with format string

↓

Identify placeholders like %s, %I, %L

↓

Replace placeholders with arguments safely

↓

Return formatted string

↓

Use formatted string in query or output

The format function takes a string with placeholders and safely replaces them with given arguments, preventing errors and injection.

Execution Sample

PostgreSQL

SELECT format('Hello, %s! Your id is %s.', 'Alice', 42);

Formats a greeting string by safely inserting a name and an id.

Execution Table

Step	Action	Input	Placeholder Found	Replacement	Resulting String
1	Start with format string	'Hello, %s! Your id is %s.'	None yet	'Alice' and 42 to insert	Hello, %s! Your id is %s.
2	Find first placeholder	'Hello, %s! Your id is %s.'	%s	Replace with 'Alice'	Hello, Alice! Your id is %s.
3	Find second placeholder	Hello, Alice! Your id is %s.	%s	Replace with 42	Hello, Alice! Your id is 42.
4	Return final formatted string	Hello, Alice! Your id is 42.	None	No more replacements	Hello, Alice! Your id is 42.

💡 All placeholders replaced, formatting complete.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
format_string	'Hello, %s! Your id is %s.'	Hello, Alice! Your id is %s.	Hello, Alice! Your id is 42.	Hello, Alice! Your id is 42.
arguments	['Alice', 42]	['Alice', 42]	['Alice', 42]	['Alice', 42]

Key Moments - 2 Insights

Why do we use %s instead of directly concatenating strings?

Using %s placeholders with format safely inserts values, preventing errors and SQL injection, as shown in steps 2 and 3 where replacements happen securely.

What happens if there are more placeholders than arguments?

The format function will raise an error because it cannot replace all placeholders, stopping before step 4.

Visual Quiz - 3 Questions

Look at the execution table, what is the string after step 2?

A Hello, Alice! Your id is 42.

B Hello, %s! Your id is 42.

C Hello, Alice! Your id is %s.

D Hello, %s! Your id is %s.

💡 Hint

Check the 'Resulting String' column at step 2 in the execution_table.

At which step are all placeholders replaced?

A Step 3

B Step 2

C Step 4

D Step 1

💡 Hint

Look for the step where the last %s is replaced in the execution_table.

If the second argument was missing, what would happen?

A The format function returns the string with one placeholder left.

B The format function raises an error.

C The missing placeholder is replaced with NULL automatically.

D The function ignores the missing argument and continues.

💡 Hint

Refer to key_moments about missing arguments causing errors.

Concept Snapshot

format(format_string, args...)
- Replaces placeholders like %s safely
- Prevents SQL injection
- Supports %I for identifiers, %L for literals
- Returns a formatted string
- Use for dynamic query building

Full Transcript

The PostgreSQL format function takes a string with placeholders and safely replaces them with provided arguments. It scans the string for placeholders like %s, then replaces each with the corresponding argument in order. This prevents errors and injection risks. For example, formatting 'Hello, %s! Your id is %s.' with 'Alice' and 42 results in 'Hello, Alice! Your id is 42.'. If placeholders outnumber arguments, an error occurs. This function is useful for building safe dynamic queries.

Practice

1. What is the main purpose of the format() function in PostgreSQL?

easy

A. To safely build text strings with placeholders for values

B. To execute SQL queries directly

C. To create new tables in the database

D. To delete rows from a table

Solution

1. Step 1: Understand the function's role

   The format() function is designed to build text strings safely by inserting values into placeholders.

2. Step 2: Compare with other options

   Options A, B, and C describe other SQL operations, not string formatting.

3. Final Answer:

   To safely build text strings with placeholders for values -> Option A

4. Quick Check:

   format() = safe string building [OK]

Hint: Remember: format() builds safe strings with placeholders [OK]

Common Mistakes:

• Thinking format() executes queries
• Confusing format() with table operations
• Ignoring the safety aspect of format()

2. Which placeholder should you use with format() to safely insert a table name (identifier)?

easy

A. %L

B. %s

C. %I

D. %d

Solution

1. Step 1: Identify placeholder types

   In format(), %I is used for identifiers like table or column names.

2. Step 2: Eliminate other placeholders

   %s is for strings, %L for literals, and %d is not a valid placeholder in PostgreSQL's format.

3. Final Answer:

   %I -> Option C

4. Quick Check:

   Identifier placeholder = %I [OK]

Hint: Use %I for identifiers like table names [OK]

Common Mistakes:

• Using %s for identifiers
• Confusing %L with %I
• Using invalid placeholders like %d

3. What is the output of this query?

SELECT format('SELECT * FROM %I WHERE name = %L', 'users', 'Alice');

medium

A. SELECT * FROM users WHERE name = Alice

B. SELECT * FROM users WHERE name = 'Alice'

C. SELECT * FROM 'users' WHERE name = 'Alice'

D. SELECT * FROM users WHERE name = Alice;

Solution

1. Step 1: Understand placeholders used

   %I inserts the identifier without quotes, so 'users' becomes users. %L inserts the literal with quotes, so 'Alice' becomes 'Alice'.

2. Step 2: Construct the output string

   The output is: SELECT * FROM users WHERE name = 'Alice'

3. Final Answer:

   SELECT * FROM users WHERE name = 'Alice' -> Option B

4. Quick Check:

   Identifier no quotes, literal with quotes = SELECT * FROM users WHERE name = 'Alice' [OK]

Hint: Remember %I no quotes, %L adds quotes around literals [OK]

Common Mistakes:

• Adding quotes around identifiers
• Missing quotes around literals
• Confusing %s with %I or %L

4. Identify the error in this usage of format():

SELECT format('INSERT INTO %I VALUES (%s)', 'products', 100);

medium

A. Using %s for a numeric value causes a syntax error

B. Missing quotes around the table name

C. No error, the query is correct

D. Should use %L for the numeric value to be safe

Solution

1. Step 1: Check placeholder for numeric value

   %s inserts the value as a string without quotes, which can cause errors or injection risks.

2. Step 2: Use %L for safe literal insertion

   Using %L safely quotes and escapes the numeric literal, preventing errors.

3. Final Answer:

   Should use %L for the numeric value to be safe -> Option D

4. Quick Check:

   Numeric literals need %L for safety [OK]

Hint: Use %L for all literal values, even numbers [OK]

Common Mistakes:

• Using %s for literals without quotes
• Assuming %s works for all types
• Ignoring injection risks with %s

5. You want to build a dynamic query to update a user's email safely. Which format() call is correct?

1) format('UPDATE %I SET email = %s WHERE id = %L', 'users', '[e-mail]', 42)
2) format('UPDATE %I SET email = %L WHERE id = %L', 'users', '[e-mail]', 42)
3) format('UPDATE %s SET email = %L WHERE id = %s', 'users', '[e-mail]', 42)
4) format('UPDATE %I SET email = %I WHERE id = %L', 'users', '[e-mail]', 42)

hard

A. Option 2

B. Option 1

C. Option 3

D. Option 4

Solution

1. Step 1: Check placeholders for each part

   Table name is an identifier, so %I is correct. Email is a string literal, so %L is needed. ID is a literal number, so %L is safe.

2. Step 2: Evaluate each option

   Option 2 uses %I for table, %L for email and id, which is correct. Option 1 uses %s for email (unsafe). Option 3 uses %s for table and id (wrong). Option 4 uses %I for email (wrong).

3. Final Answer:

   Option 2 -> Option A

4. Quick Check:

   Identifiers %I, literals %L = Option 2 [OK]

Hint: Use %I for identifiers, %L for all literal values [OK]

Common Mistakes:

• Using %s for literals
• Using %I for string literals
• Mixing placeholders incorrectly