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.