Bird
Raised Fist0
PostgreSQLquery~20 mins

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

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Challenge - 5 Problems
🎖️
Format Function Master
Get all challenges correct to earn this badge!
Test your skills under time pressure!
❓ query_result
intermediate
1:30remaining
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');
AError: format function requires numeric arguments
BHello, %s! Today is %s.
CHello, Alice! Today is %s.
DHello, Alice! Today is Monday.
Attempts:
2 left
💡 Hint
The format function replaces placeholders like %s with the provided arguments in order.
❓ query_result
intermediate
1:30remaining
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');
AID: 123, Name: Bob
BID: 123, Name: %s
CID: %s, Name: %s
DError: cannot mix integer and string in format
Attempts:
2 left
💡 Hint
The %s placeholder can accept any data type and converts it to text.
📝 Syntax
advanced
1:30remaining
Which option causes a syntax error in this format usage?
Identify which SQL statement will cause a syntax error when using the format function:
ASELECT format('Value: %s', );
BSELECT format('Value: %d', 42);
CSELECT format('Value: %s', 'text');
DSELECT format('Value: %s', 42);
Attempts:
2 left
💡 Hint
Check if all placeholders have matching arguments.
❓ optimization
advanced
2:00remaining
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?
Aformat('SELECT * FROM users WHERE name = %I', user_input)
Bformat('SELECT * FROM users WHERE name = ''%s''', user_input)
Cformat('SELECT * FROM users WHERE name = %L', user_input)
Dformat('SELECT * FROM users WHERE name = %s', user_input)
Attempts:
2 left
💡 Hint
Use the correct format specifier to safely quote literals.
🧠 Conceptual
expert
2:30remaining
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?
ABecause %I converts identifiers to lowercase always, ensuring consistency.
BBecause %I automatically quotes identifiers to handle special characters and reserved words safely.
CBecause string concatenation is faster but less readable than format().
DBecause %I escapes string literals to prevent SQL injection.
Attempts:
2 left
💡 Hint
Think about how identifiers with spaces or special characters are handled.

Practice

(1/5)
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', 'new@example.com', 42)
2) format('UPDATE %I SET email = %L WHERE id = %L', 'users', 'new@example.com', 42)
3) format('UPDATE %s SET email = %L WHERE id = %s', 'users', 'new@example.com', 42)
4) format('UPDATE %I SET email = %I WHERE id = %L', 'users', 'new@example.com', 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
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard