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

Need a 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

Need a 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

Need a 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

Need a hint?

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