Format function for safe formatting in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to safely format a string with a variable using the format function.

PostgreSQL

SELECT format('Hello, %s!', [1]) AS greeting;

Drag options to blanks, or click blank then click option'

A'Hello'

B'World'

C'%s'

D'greeting'

Attempts:

3 left

💡 Hint

Common Mistakes

Using the placeholder '%s' as the argument instead of the actual string.

Not using quotes around the string value.

The format function replaces %s with the first argument after the format string, so 'World' is the correct value to insert.

2fill in blank

medium

Complete the code to safely format an integer into a string using the format function.

PostgreSQL

SELECT format('You have %[1] new messages.', 5) AS message;

Drag options to blanks, or click blank then click option'

Af

Bs

Cd

Di

Attempts:

3 left

💡 Hint

Common Mistakes

Using %s instead of %d for integers.

Using %f which is for floating-point numbers.

The %d placeholder is used for integers in the format function.

3fill in blank

hard

Fix the error in the code to correctly format a floating-point number with two decimal places.

PostgreSQL

SELECT format('Price: %.2[1]', 12.3456) AS formatted_price;

Drag options to blanks, or click blank then click option'

Ad

Bi

Cs

Df

Attempts:

3 left

💡 Hint

Common Mistakes

Using %d or %i which are for integers.

Using %s which is for strings.

The %f placeholder formats floating-point numbers, and .2 specifies two decimal places.

4fill in blank

hard

Fill both blanks to safely format a string and an integer using the format function.

PostgreSQL

SELECT format('User: %[1], Age: %[2]', 'Alice', 30) AS info;

Drag options to blanks, or click blank then click option'

As

Bd

Cf

Di

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up %s and %d placeholders.

Using %f for integers.

Use %s for strings and %d for integers in the format function.

5fill in blank

hard

Fill all three blanks to format a string, an integer, and a floating-point number with one decimal place.

PostgreSQL

SELECT format('Name: %[1], Count: %[2], Score: %.1[3]', 'Bob', 7, 9.876) AS result;

Drag options to blanks, or click blank then click option'

As

Bd

Cf

Di

Attempts:

3 left

💡 Hint

Common Mistakes

Using %d for floats or %f for integers.

Omitting the decimal precision for floats.

Use %s for strings, %d for integers, and %f for floating-point numbers with decimal precision.

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