Format function for safe formatting in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this PostgreSQL format function?

Consider the following SQL query using the format function:

SELECT format('Hello, %s! Today is %s.', 'Alice', 'Monday');

What is the output of this query?

PostgreSQL

SELECT format('Hello, %s! Today is %s.', 'Alice', 'Monday');

A Error: format function requires numeric arguments

B Hello, %s! Today is %s.

C Hello, Alice! Today is %s.

D Hello, Alice! Today is Monday.

💡 Hint

The format function replaces placeholders like %s with the provided arguments in order.

The format function replaces each %s placeholder with the corresponding argument. Here, 'Alice' replaces the first %s and 'Monday' replaces the second %s.

❓ query_result

intermediate

What does this format call return with integer and string?

Given this SQL statement:

SELECT format('ID: %s, Name: %s', 123, 'Bob');

What is the output?

PostgreSQL

SELECT format('ID: %s, Name: %s', 123, 'Bob');

A ID: 123, Name: Bob

B ID: 123, Name: %s

C ID: %s, Name: %s

D Error: cannot mix integer and string in format

💡 Hint

The %s placeholder can accept any data type and converts it to text.

The format function converts all arguments to text when using %s, so 123 becomes '123' and 'Bob' stays 'Bob'.

📝 Syntax

advanced

Which option causes a syntax error in this format usage?

Identify which SQL statement will cause a syntax error when using the format function:

A SELECT format('Value: %s', );

B SELECT format('Value: %d', 42);

C SELECT format('Value: %s', 'text');

D SELECT format('Value: %s', 42);

💡 Hint

Check if all placeholders have matching arguments.

Option A has a missing argument after the comma, causing a syntax error.

❓ optimization

advanced

Which format usage is safest to avoid SQL injection?

You want to safely insert a user input string into a query using format. Which option correctly uses format to avoid SQL injection?

A format('SELECT * FROM users WHERE name = %I', user_input)

B format('SELECT * FROM users WHERE name = ''%s''', user_input)

C format('SELECT * FROM users WHERE name = %L', user_input)

D format('SELECT * FROM users WHERE name = %s', user_input)

💡 Hint

Use the correct format specifier to safely quote literals.

The %L specifier safely quotes and escapes string literals, preventing SQL injection. %s does not quote, %I is for identifiers.

🧠 Conceptual

expert

Why use format() with %I instead of string concatenation for identifiers?

In PostgreSQL, when dynamically building SQL queries, why is it better to use format() with the %I specifier for table or column names instead of simple string concatenation?

A Because %I converts identifiers to lowercase always, ensuring consistency.

B Because %I automatically quotes identifiers to handle special characters and reserved words safely.

C Because string concatenation is faster but less readable than format().

D Because %I escapes string literals to prevent SQL injection.

💡 Hint

Think about how identifiers with spaces or special characters are handled.

The %I specifier quotes identifiers properly, so names with spaces or reserved keywords are handled safely. String concatenation risks syntax errors or injection.