PostgreSQL vs MySQL key differences - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Primary focus difference between PostgreSQL and MySQL

Which statement best describes the primary design focus of PostgreSQL compared to MySQL?

A PostgreSQL is designed mainly for web applications, whereas MySQL is for complex analytical queries.

B PostgreSQL emphasizes advanced SQL compliance and extensibility, while MySQL focuses on speed and ease of use.

C PostgreSQL supports only NoSQL data, while MySQL supports only relational data.

D PostgreSQL is a proprietary database, and MySQL is fully open source.

💡 Hint

Think about which database is known for advanced features and which is known for simplicity.

PostgreSQL is known for its strong SQL standards compliance and extensibility features. MySQL is often chosen for its speed and simplicity, especially in web applications.

❓ query_result

intermediate

Output difference in handling JSON data

Given the following JSON column query in PostgreSQL and MySQL, which option correctly describes the difference in output behavior?

PostgreSQL

SELECT data->>'name' FROM users WHERE id = 1;

A PostgreSQL returns the JSON field as text; MySQL returns an error because ->> is not supported.

B PostgreSQL returns the JSON field as JSON; MySQL returns text.

C Both PostgreSQL and MySQL return the JSON field as text without error.

D MySQL returns the JSON field as text; PostgreSQL returns an error.

💡 Hint

Check which database supports the ->> operator for JSON extraction.

PostgreSQL supports the ->> operator to extract JSON fields as text. MySQL does not support this operator and would raise an error.

📝 Syntax

advanced

Correct syntax for UPSERT in PostgreSQL vs MySQL

Which option shows the correct UPSERT syntax for PostgreSQL but would cause a syntax error in MySQL?

PostgreSQL

INSERT INTO products (id, name) VALUES (1, 'Pen') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

A The given query is valid in PostgreSQL but causes syntax error in MySQL.

B The query causes syntax error in both PostgreSQL and MySQL.

C The query is valid in MySQL but causes syntax error in PostgreSQL.

D The query is valid in both PostgreSQL and MySQL.

💡 Hint

Consider which database supports ON CONFLICT syntax.

PostgreSQL supports the ON CONFLICT clause for UPSERT operations. MySQL uses a different syntax (ON DUPLICATE KEY UPDATE) and will raise a syntax error for this query.

❓ optimization

advanced

Index type support difference affecting query optimization

Which index type is supported by PostgreSQL but not by MySQL, impacting optimization of full-text search queries?

A HASH

B BTREE

C GIN (Generalized Inverted Index)

D SPATIAL

💡 Hint

Think about which index type is specialized for full-text search in PostgreSQL.

PostgreSQL supports GIN indexes which are efficient for full-text search. MySQL does not support GIN indexes and uses different mechanisms for full-text search.

🔧 Debug

expert

Why does this PostgreSQL query fail but works in MySQL?

Consider this query executed in both databases:

SELECT * FROM orders WHERE order_date = '2023-02-30';

Why does it fail in PostgreSQL but not in MySQL?

A Both databases reject the date, but MySQL error message is clearer.

B PostgreSQL automatically corrects invalid dates, MySQL does not.

C MySQL rejects invalid dates like '2023-02-30', PostgreSQL accepts them as valid dates.

D MySQL treats '2023-02-30' as NULL causing no results, PostgreSQL throws an error.

💡 Hint

Check how each database handles invalid date literals.

MySQL treats '2023-02-30' as NULL causing no results, PostgreSQL throws an error.

Practice

1. Which database system is known for its strong support of advanced data types and strict data integrity?

easy

A. MongoDB

B. MySQL

C. SQLite

D. PostgreSQL

Solution

1. Step 1: Understand database features

   PostgreSQL is designed with advanced features like support for JSON, arrays, and custom types, ensuring strong data integrity.

2. Step 2: Compare with MySQL

   MySQL focuses more on speed and simplicity but has less strict data integrity features compared to PostgreSQL.

3. Final Answer:

   PostgreSQL -> Option D

4. Quick Check:

   Advanced features and strong integrity = PostgreSQL [OK]

Hint: Advanced data types and integrity point to PostgreSQL [OK]

Common Mistakes:

• Confusing speed with data integrity
• Thinking MySQL has stronger data types
• Mixing up database names

2. Which of the following is the correct way to start a transaction in PostgreSQL?

easy

A. SET TRANSACTION;

B. START TRANSACTION;

C. BEGIN;

D. TRANSACTION START;

Solution

1. Step 1: Recall PostgreSQL transaction syntax

   PostgreSQL uses BEGIN; to start a transaction block.

2. Step 2: Compare other options

   While SET TRANSACTION; and START TRANSACTION; are valid in MySQL, PostgreSQL specifically uses BEGIN;.

3. Final Answer:

   BEGIN; -> Option C

4. Quick Check:

   PostgreSQL transaction start = BEGIN; [OK]

Hint: PostgreSQL uses BEGIN; to start transactions [OK]

Common Mistakes:

• Using MySQL syntax in PostgreSQL
• Adding extra keywords like TRANSACTION
• Confusing order of words

3. Given the following SQL snippet in PostgreSQL:

CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50));
INSERT INTO users (name) VALUES ('Alice'), ('Bob');
SELECT * FROM users WHERE id = 2;

What will be the output of the SELECT query?

medium

A. An error because SERIAL is not supported

B. Row with id=2 and name='Bob'

C. Row with id=1 and name='Alice'

D. Empty result set

Solution

1. Step 1: Understand SERIAL behavior in PostgreSQL

   The SERIAL type auto-increments starting at 1, so the first inserted row gets id=1, second id=2.

2. Step 2: Analyze the SELECT query

   The query selects the row where id=2, which corresponds to the second inserted user 'Bob'.

3. Final Answer:

   Row with id=2 and name='Bob' -> Option B

4. Quick Check:

   id=2 matches 'Bob' row [OK]

Hint: SERIAL auto-increments starting at 1 in PostgreSQL [OK]

Common Mistakes:

• Assuming SERIAL is unsupported
• Mixing up id values
• Expecting empty results

4. You run this MySQL query in PostgreSQL:

SELECT NOW;

But it returns an error. What is the likely cause?

medium

A. The function NOW() exists but requires parentheses in PostgreSQL

B. The query syntax is incorrect for PostgreSQL

C. PostgreSQL uses CURRENT_TIMESTAMP instead of NOW() for current time

D. NOW() is not a valid function in PostgreSQL

Solution

1. Step 1: Check function availability

   PostgreSQL supports the NOW() function and it requires parentheses, so NOW() is valid.

2. Step 2: Identify common mistake

   Sometimes users omit parentheses and write NOW without (), causing syntax errors.

3. Final Answer:

   The function NOW() exists but requires parentheses in PostgreSQL -> Option A

4. Quick Check:

   NOW() needs () in PostgreSQL [OK]

Hint: Always use parentheses with NOW() in PostgreSQL [OK]

Common Mistakes:

• Omitting parentheses after NOW
• Confusing CURRENT_TIMESTAMP with NOW
• Assuming NOW() is unsupported

5. You want to migrate a MySQL database to PostgreSQL. Which key difference should you consider to avoid data loss?

hard

A. PostgreSQL enforces stricter data types and constraints by default

B. MySQL supports JSON data type but PostgreSQL does not

C. PostgreSQL does not support transactions

D. MySQL uses SERIAL for auto-increment but PostgreSQL uses AUTO_INCREMENT

Solution

1. Step 1: Identify data type and constraint differences

   PostgreSQL enforces data types and constraints more strictly, which can cause migration errors if data doesn't comply.

2. Step 2: Evaluate other options

   MySQL supports JSON, PostgreSQL also supports JSON; PostgreSQL supports transactions; SERIAL is PostgreSQL's auto-increment, not AUTO_INCREMENT.

3. Final Answer:

   PostgreSQL enforces stricter data types and constraints by default -> Option A

4. Quick Check:

   Stricter constraints in PostgreSQL = watch for data loss [OK]

Hint: Stricter constraints in PostgreSQL can cause migration issues [OK]

Common Mistakes:

• Assuming PostgreSQL lacks JSON support
• Confusing auto-increment syntax
• Thinking PostgreSQL lacks transactions