Why PostgreSQL advanced features matter - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this query using JSONB operators?

Given a table products with a details column of type jsonb, what will this query return?

SELECT details->>'color' AS color FROM products WHERE details @> '{"size": "M"}';

PostgreSQL

SELECT details->>'color' AS color FROM products WHERE details @> '{"size": "M"}';

A Syntax error due to JSON syntax

B All colors of products where size is 'M'

C All products with any size and color 'M'

D All sizes of products where color is 'M'

💡 Hint

The operator @> checks if the JSONB column contains the specified JSON.

The query filters products whose details JSONB contains the key-value pair size='M'. It then selects the color value from details.

🧠 Conceptual

intermediate

Why use PostgreSQL's CTEs (WITH clauses)?

Which of the following best explains why Common Table Expressions (CTEs) are useful in PostgreSQL?

A They replace the need for transactions in multi-step operations.

B They automatically create indexes on tables used in the query.

C They allow breaking complex queries into simpler parts and can improve readability and maintainability.

D They are used only for creating temporary tables.

💡 Hint

Think about how CTEs help organize queries.

CTEs let you define temporary named result sets that can be referenced within a query, making complex queries easier to read and maintain.

📝 Syntax

advanced

Which query correctly uses the UPSERT feature in PostgreSQL?

You want to insert a new user or update their email if the username already exists. Which query is correct?

PostgreSQL

Table: users(username TEXT PRIMARY KEY, email TEXT)

A INSERT INTO users(username, email) VALUES('alice', '[e-mail]') ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email;

B INSERT INTO users(username, email) VALUES('alice', '[e-mail]') ON DUPLICATE KEY UPDATE email = VALUES(email);

C INSERT INTO users(username, email) VALUES('alice', '[e-mail]') ON CONFLICT DO NOTHING;

D UPDATE users SET email = '[e-mail]' WHERE username = 'alice' ELSE INSERT INTO users(username, email) VALUES('alice', '[e-mail]');

💡 Hint

PostgreSQL uses ON CONFLICT syntax for UPSERT.

Option A uses the correct PostgreSQL syntax for UPSERT with ON CONFLICT and EXCLUDED keyword.

❓ optimization

advanced

How does PostgreSQL's partial index improve query performance?

Consider a table orders with a status column. You create a partial index on status = 'pending'. What is the main benefit?

A The index duplicates the entire table for faster access.

B The index automatically updates all rows regardless of status.

C The index prevents insertion of rows with status other than 'pending'.

D The index is smaller and faster because it only includes rows where status is 'pending'.

💡 Hint

Think about how indexing fewer rows affects speed and size.

Partial indexes include only rows matching the condition, making them smaller and faster for queries filtering on that condition.

🔧 Debug

expert

Why does this PostgreSQL query raise an error?

Given the query:

SELECT * FROM employees WHERE department = 'Sales' AND salary > (SELECT AVG(salary) FROM employees);

Does it raise an error about subquery returning more than one row? Why?

A The subquery returns a single value, so the error must be from elsewhere.

B The subquery returns multiple rows because it lacks a GROUP BY clause and is correlated incorrectly.

C The subquery returns multiple rows because AVG(salary) is not aggregated properly.

D The subquery returns multiple rows because the table has duplicate salaries.

💡 Hint

Check if the subquery is correlated or not and what it returns.

The subquery uses AVG(), an aggregate function that always returns a single scalar value regardless of the number of rows. It is not correlated and uses proper aggregation, so it does not return multiple rows.

Practice

1. Which of the following is a key advantage of PostgreSQL's advanced features?

easy

A. They allow storing complex data types like JSON and arrays.

B. They make the database only work with simple text data.

C. They remove the need for any indexes.

D. They prevent any data from being updated.

Solution

1. Step 1: Understand PostgreSQL advanced features

   PostgreSQL supports complex data types such as JSON, arrays, and custom types, which allow flexible data storage.

2. Step 2: Compare options with this knowledge

   They allow storing complex data types like JSON and arrays. correctly states this advantage, while others describe incorrect or impossible behaviors.

3. Final Answer:

   They allow storing complex data types like JSON and arrays. -> Option A

4. Quick Check:

   Advanced features = complex data support [OK]

Hint: Remember: PostgreSQL handles complex data types easily [OK]

Common Mistakes:

• Thinking PostgreSQL only supports simple text
• Believing indexes are not needed
• Assuming data cannot be updated

2. Which of the following is the correct syntax to create a table with a JSONB column in PostgreSQL?

easy

A. CREATE TABLE data (info JSONB);

B. CREATE TABLE data (info JSON);

C. CREATE TABLE data (info TEXT[]);

D. CREATE TABLE data (info BLOB);

Solution

1. Step 1: Recall JSONB column syntax in PostgreSQL

   PostgreSQL uses JSONB as a binary JSON storage type, declared as JSONB in table definitions.

2. Step 2: Check each option

   CREATE TABLE data (info JSONB); uses JSONB correctly. CREATE TABLE data (info JSON); uses JSON (also valid but not JSONB). CREATE TABLE data (info TEXT[]); uses TEXT array, not JSONB. CREATE TABLE data (info BLOB); uses BLOB which is not PostgreSQL syntax.

3. Final Answer:

   CREATE TABLE data (info JSONB); -> Option A

4. Quick Check:

   JSONB column syntax = CREATE TABLE ... (info JSONB) [OK]

Hint: Use JSONB for efficient JSON storage in PostgreSQL [OK]

Common Mistakes:

• Confusing JSON and JSONB types
• Using TEXT[] instead of JSONB
• Using BLOB which is not PostgreSQL type

3. Given the table users(id SERIAL PRIMARY KEY, data JSONB) with data:
{"name": "Alice", "age": 30} in the data column, what does this query return?

SELECT data->>'name' FROM users WHERE data->>'age' = '30';

medium

A. Returns all data rows regardless of age.

B. Returns the age 30 as a number.

C. Returns the name 'Alice' for users aged 30.

D. Returns an error due to wrong JSON syntax.

Solution

1. Step 1: Understand JSONB operators in the query

   The operator ->> extracts JSON object field as text. The WHERE clause filters rows where age equals '30' as text.

2. Step 2: Analyze query result

   The SELECT returns the 'name' field as text for rows matching age '30'. So it returns 'Alice'.

3. Final Answer:

   Returns the name 'Alice' for users aged 30. -> Option C

4. Quick Check:

   data->>'name' with age filter = 'Alice' [OK]

Hint: ->> extracts text from JSONB fields [OK]

Common Mistakes:

• Confusing -> and ->> operators
• Expecting numeric type instead of text
• Ignoring WHERE filter on JSONB field

4. Identify the error in this PostgreSQL query using JSONB:

SELECT data->'name' FROM users WHERE data->>'age' = 30;

medium

A. The JSONB column must be cast to text before querying.

B. The operator -> cannot be used in SELECT.

C. The query is correct and will run without errors.

D. The comparison value 30 should be a string '30'.

Solution

1. Step 1: Check WHERE clause comparison

   data->>'age' extracts text, so comparing to number 30 causes type mismatch.

2. Step 2: Correct the comparison

   Comparison should be to string '30' to match extracted text value.

3. Final Answer:

   The comparison value 30 should be a string '30'. -> Option D

4. Quick Check:

   Compare JSON text with string '30' [OK]

Hint: Compare JSON text fields with strings, not numbers [OK]

Common Mistakes:

• Using numeric 30 instead of string '30'
• Thinking -> operator is invalid in SELECT
• Trying to cast JSONB unnecessarily

5. You want to store user preferences as key-value pairs and query them efficiently. Which PostgreSQL feature best supports this need?

hard

A. Storing preferences in separate tables without indexes.

B. Using JSONB columns with GIN indexes.

C. Using arrays of text without indexes.

D. Storing preferences as plain text in VARCHAR columns.

Solution

1. Step 1: Identify data storage needs

   User preferences as key-value pairs fit well into JSONB columns for flexible schema.

2. Step 2: Consider query efficiency

   GIN indexes on JSONB columns speed up key-value queries efficiently.

3. Step 3: Evaluate other options

   Plain text or arrays lack flexibility and indexing; separate tables without indexes are slow.

4. Final Answer:

   Using JSONB columns with GIN indexes. -> Option B

5. Quick Check:

   JSONB + GIN index = fast key-value queries [OK]

Hint: Use JSONB with GIN index for fast key-value queries [OK]

Common Mistakes:

• Ignoring indexing for JSONB queries
• Using plain text which is inflexible
• Not using JSONB for key-value data