PostgreSQL vs MySQL key differences - Quick Revision & Key Differences

Recall & Review

beginner

What is the main difference in licensing between PostgreSQL and MySQL?

PostgreSQL uses the open-source PostgreSQL License, which is very permissive. MySQL uses the GPL license, which requires derived works to also be open source unless a commercial license is purchased.

beginner

Which database is known for better support of advanced SQL features like window functions and common table expressions?

PostgreSQL is known for better support of advanced SQL features such as window functions, common table expressions (CTEs), and full compliance with SQL standards.

intermediate

How do PostgreSQL and MySQL differ in terms of data types support?

PostgreSQL supports a wider range of data types including arrays, JSONB, and custom types. MySQL has more limited data types and less flexible JSON support.

intermediate

Which database is generally preferred for complex analytical queries and why?

PostgreSQL is generally preferred for complex analytical queries because of its powerful query planner, support for advanced SQL features, and extensibility.

advanced

What is a key difference in replication and clustering support between PostgreSQL and MySQL?

MySQL offers built-in replication and clustering options like Group Replication and InnoDB Cluster. PostgreSQL supports replication and built-in streaming replication, but often relies on third-party tools for clustering and high availability.

Which database uses the PostgreSQL License?

A PostgreSQL

B MySQL

C Both

D Neither

PostgreSQL uses the PostgreSQL License, which is a permissive open-source license.

Which database has better native support for JSONB data type?

A Both equally

B MySQL

C PostgreSQL

D Neither

PostgreSQL supports JSONB, a binary JSON format that allows efficient querying and indexing.

Which database is known for better compliance with SQL standards?

A PostgreSQL

B Both are equal

C MySQL

D Neither

PostgreSQL is known for strong compliance with SQL standards and advanced SQL features.

Which database offers built-in clustering with Group Replication?

A PostgreSQL

B MySQL

C Both

D Neither

MySQL offers Group Replication and InnoDB Cluster for built-in clustering.

Which database is generally preferred for complex analytical queries?

A MySQL

B Neither

C Both equally

D PostgreSQL

PostgreSQL is preferred for complex analytical queries due to its advanced features and query planner.

Explain the key differences between PostgreSQL and MySQL in terms of licensing, features, and data type support.

Think about how open-source licenses affect usage and how features impact database capabilities.

Describe how replication and clustering differ between PostgreSQL and MySQL.

Consider what each database offers out of the box versus what requires extra tools.

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