Why PostgreSQL has rich data types - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why does PostgreSQL support many data types?

PostgreSQL offers many built-in data types like arrays, JSON, and geometric types. Why is having many data types useful?

A It allows storing different kinds of data efficiently and supports complex queries.

B It makes the database slower because it has to handle many types.

C It limits the database to only simple text and numbers.

D It forces users to convert all data to strings before storing.

💡 Hint

Think about how different data types help store and query data better.

PostgreSQL's rich data types let you store data in the best format, making queries faster and easier. For example, JSON types let you store structured data without converting it to text.

❓ query_result

intermediate

What is the output of this PostgreSQL query with JSON data?

Given a table 'users' with a JSON column 'info' storing {"age": 30, "city": "NY"}, what does this query return? SELECT info->>'city' FROM users WHERE info->>'age' = '30';

PostgreSQL

CREATE TABLE users (id SERIAL PRIMARY KEY, info JSON);
INSERT INTO users (info) VALUES ('{"age": 30, "city": "NY"}');
SELECT info->>'city' FROM users WHERE info->>'age' = '30';

A NULL

B 30

C Syntax error

D NY

💡 Hint

The ->> operator extracts JSON text values.

The query filters rows where the JSON 'age' is '30' as text, then extracts the 'city' value, which is 'NY'.

📝 Syntax

advanced

Which option correctly creates a table with an array column in PostgreSQL?

You want to create a table 'products' with a column 'tags' that stores an array of text values. Which SQL statement is correct?

A CREATE TABLE products (id SERIAL PRIMARY KEY, tags [TEXT]);

B CREATE TABLE products (id SERIAL PRIMARY KEY, tags TEXT[]);

C CREATE TABLE products (id SERIAL PRIMARY KEY, tags ARRAY OF TEXT);

D CREATE TABLE products (id SERIAL PRIMARY KEY, tags TEXT ARRAY);

💡 Hint

PostgreSQL uses square brackets after the type for arrays.

PostgreSQL defines array columns by adding [] after the base type, like TEXT[]. Other options are invalid syntax.

❓ optimization

advanced

How does using PostgreSQL's native JSONB type improve query performance?

Compared to storing JSON as plain text, what advantage does the JSONB data type provide in PostgreSQL?

A JSONB stores data in a binary format allowing indexing and faster queries.

B JSONB stores data as plain text making it easier to read.

C JSONB disables indexing to save space.

D JSONB automatically compresses data but slows down queries.

💡 Hint

Think about how binary storage can help with searching and indexing.

JSONB stores JSON data in a binary form that supports indexing, which speeds up search and retrieval compared to plain text JSON.

🔧 Debug

expert

What error does this PostgreSQL query raise?

Consider this query: SELECT * FROM orders WHERE order_date = '2023-02-30'; What error will PostgreSQL produce?

A ERROR: syntax error at or near "2023-02-30"

B ERROR: column "order_date" does not exist

C ERROR: invalid input syntax for type date: "2023-02-30"

D No error, returns zero rows

💡 Hint

Check if the date value is valid.

February 30th is not a valid date, so PostgreSQL raises an invalid input syntax error for the date type.

Practice

1. Why does PostgreSQL provide a wide variety of data types?

easy

A. To force users to use only text data

B. To make the database slower and more complex

C. To limit the types of data users can store

D. To store different kinds of data accurately and efficiently

Solution

1. Step 1: Understand PostgreSQL data types purpose

   PostgreSQL offers many data types to handle various real-world data accurately.

2. Step 2: Analyze the effect of rich data types

   Rich data types help keep data accurate and make queries efficient by matching data to the right type.

3. Final Answer:

   To store different kinds of data accurately and efficiently -> Option D

4. Quick Check:

   Rich data types = Accurate & efficient storage [OK]

Hint: Rich data types match data needs for accuracy [OK]

Common Mistakes:

• Thinking data types slow down the database
• Believing all data should be stored as text
• Assuming fewer data types improve performance

2. Which of the following is the correct way to declare a column with a JSON data type in PostgreSQL?

easy

A. CREATE TABLE data (info jsontext);

B. CREATE TABLE data (info textjson);

C. CREATE TABLE data (info json);

D. CREATE TABLE data (info json_data);

Solution

1. Step 1: Recall PostgreSQL JSON data type syntax

   The correct data type for JSON in PostgreSQL is simply json.

2. Step 2: Check each option's syntax

   Only CREATE TABLE data (info json); uses the exact and valid json type; others are invalid or do not exist.

3. Final Answer:

   CREATE TABLE data (info json); -> Option C

4. Quick Check:

   JSON type syntax = json [OK]

Hint: Use exact PostgreSQL data type names [OK]

Common Mistakes:

• Adding extra words to data type names
• Using non-existent data types
• Confusing JSON with text types

3. Given the table CREATE TABLE measurements (id SERIAL, data jsonb); and the insert INSERT INTO measurements (data) VALUES ('{"temp": 22, "unit": "C"}');, what will the query SELECT data->>'temp' FROM measurements; return?

medium

A. 22

B. "22"

C. temp

D. Error

Solution

1. Step 1: Understand JSONB operator ->>

   The operator ->> extracts the JSON value as text, so data->>'temp' returns the value of key 'temp' as text.

2. Step 2: Analyze the inserted JSON value

   The value for 'temp' is 22 (a number), extracted as text '22' without quotes in output.

3. Final Answer:

   22 -> Option A

4. Quick Check:

   JSONB ->> extracts text value = 22 [OK]

Hint: ->> extracts JSON value as text [OK]

Common Mistakes:

• Expecting quotes around numbers
• Confusing -> and ->> operators
• Thinking it returns the key name

4. Identify the error in this PostgreSQL table creation: CREATE TABLE users (id INT, preferences jsonb DEFAULT '{}');

medium

A. Default value '{}' is invalid for jsonb type

B. The default '{}' should be cast to jsonb explicitly

C. Missing NOT NULL constraint on id

D. jsonb type cannot have default values

Solution

1. Step 1: Check default value for jsonb column

   PostgreSQL requires default JSON values to be cast explicitly to jsonb type.

2. Step 2: Analyze the given default '{}'

   The '{}' is a string and must be cast as '{}'::jsonb to be valid.

3. Final Answer:

   The default '{}' should be cast to jsonb explicitly -> Option B

4. Quick Check:

   Default JSON values need explicit cast [OK]

Hint: Cast string defaults to jsonb with ::jsonb [OK]

Common Mistakes:

• Assuming '{}' alone is valid jsonb default
• Ignoring type casting for defaults
• Thinking jsonb cannot have defaults

5. You want to store a list of tags for each article in PostgreSQL. Which data type best supports flexible, searchable, and efficient storage of multiple tags?

hard

A. ARRAY of TEXT

B. TEXT

C. JSONB

D. VARCHAR(255)

Solution

1. Step 1: Consider data type features for storing multiple tags

   TEXT stores plain text but not multiple values; VARCHAR limits length; JSONB is flexible but heavier for simple lists.

2. Step 2: Evaluate ARRAY of TEXT for tags

   ARRAY of TEXT stores multiple text values efficiently and supports searching individual tags easily.

3. Final Answer:

   ARRAY of TEXT -> Option A

4. Quick Check:

   ARRAY suits multiple searchable tags best [OK]

Hint: Use ARRAY for multiple simple values [OK]

Common Mistakes:

• Choosing plain TEXT for multiple tags
• Using VARCHAR which limits length
• Overcomplicating with JSONB for simple lists