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.