PostgreSQL vs MySQL key differences - Hands-On Comparison

PostgreSQL vs MySQL Key Differences

📖 Scenario: You are working as a junior database assistant in a company that uses both PostgreSQL and MySQL databases. Your manager wants you to understand the key differences between these two popular database systems to help decide which one to use for different projects.

🎯 Goal: Build a simple table in PostgreSQL that lists key features and differences between PostgreSQL and MySQL. This will help you and your team quickly compare and understand the two databases.

📋 What You'll Learn

Create a table named db_comparison with columns feature, postgresql, and mysql

Insert at least 5 rows describing key differences between PostgreSQL and MySQL

Use appropriate data types for the columns

Write a query to select all rows from the db_comparison table

💡 Why This Matters

🌍 Real World

Understanding key differences between PostgreSQL and MySQL helps teams choose the right database for their projects based on features, licensing, and capabilities.

💼 Career

Database administrators and developers often need to compare database systems to recommend or configure the best solution for application needs.

1

Create the db_comparison table

Write a SQL statement to create a table called db_comparison with three columns: feature as text, postgresql as text, and mysql as text.

PostgreSQL

-- Create the db_comparison table with columns feature, postgresql, and mysql
-- Your code here

Hint

Use CREATE TABLE followed by the table name and define each column with the TEXT data type.

2

Insert key differences into the table

Insert 5 rows into the db_comparison table with these exact features and descriptions:
1. ACID Compliance: PostgreSQL is fully ACID compliant, MySQL is partially ACID compliant.
2. JSON Support: PostgreSQL has advanced JSON support, MySQL has basic JSON support.
3. Extensibility: PostgreSQL supports custom functions and types, MySQL has limited extensibility.
4. Replication: PostgreSQL supports synchronous and asynchronous replication, MySQL supports asynchronous replication.
5. Licensing: PostgreSQL uses open-source BSD license, MySQL uses GPL license.

PostgreSQL

CREATE TABLE db_comparison (
    feature TEXT,
    postgresql TEXT,
    mysql TEXT
);
-- Insert 5 rows describing key differences
-- Your code here

Hint

Use INSERT INTO with multiple VALUES tuples separated by commas.

3

Write a query to select all rows

Write a SQL query to select all columns and rows from the db_comparison table.

PostgreSQL

CREATE TABLE db_comparison (
    feature TEXT,
    postgresql TEXT,
    mysql TEXT
);

INSERT INTO db_comparison (feature, postgresql, mysql) VALUES
('ACID Compliance', 'Fully ACID compliant', 'Partially ACID compliant'),
('JSON Support', 'Advanced JSON support', 'Basic JSON support'),
('Extensibility', 'Supports custom functions and types', 'Limited extensibility'),
('Replication', 'Supports synchronous and asynchronous replication', 'Supports asynchronous replication'),
('Licensing', 'Open-source BSD license', 'GPL license');

-- Select all rows from db_comparison
-- Your code here

Hint

Use SELECT * FROM followed by the table name to get all rows and columns.

4

Add a primary key column

Alter the db_comparison table to add a new column called id as a primary key with type SERIAL and make it the first column.

PostgreSQL

CREATE TABLE db_comparison (
    feature TEXT,
    postgresql TEXT,
    mysql TEXT
);

INSERT INTO db_comparison (feature, postgresql, mysql) VALUES
('ACID Compliance', 'Fully ACID compliant', 'Partially ACID compliant'),
('JSON Support', 'Advanced JSON support', 'Basic JSON support'),
('Extensibility', 'Supports custom functions and types', 'Limited extensibility'),
('Replication', 'Supports synchronous and asynchronous replication', 'Supports asynchronous replication'),
('Licensing', 'Open-source BSD license', 'GPL license');

SELECT * FROM db_comparison;

-- Add id column as SERIAL primary key
-- Your code here

Hint

Define the id column as SERIAL PRIMARY KEY in the CREATE TABLE statement before other columns.

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