DEV Community

Rakesh Daniel
Rakesh Daniel

Posted on

MySQL vs PostgreSQL: DDL, DML

MySQL and PostgreSQL are both popular relational databases. They use SQL, but their syntax and some concepts are different.

What is DDL?

DDL means Data Definition Language.
DDL commands create or change the structure of database objects such as tables, columns, indexes, and schemas.

Common DDL commands:

  • CREATE
  • ALTER
  • DROP
  • TRUNCATE Example: if you want to add a new column to a table, that is a DDL operation.

What is DML?
DML means Data Manipulation Language.
DML commands work with the data inside a table.

Common DML commands:

  • INSERT — Create data
  • SELECT — Read data
  • UPDATE — Update data
  • DELETE — Delete data Together, these are called CRUD operations:
CRUD SQL Command Purpose
Create INSERT Add a new row
Read SELECT Get rows
Update UPDATE Change existing rows
Delete DELETE Remove rows

Create a Table

MySQL

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

PostgreSQL

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

In MySQL, we use AUTO_INCREMENT.

In PostgreSQL, we usually use SERIAL for an auto-incrementing integer ID. PostgreSQL also supports GENERATED ALWAYS AS IDENTITY, which is the modern recommended approach.

ALTER TABLE

ALTER TABLE changes the structure of an existing table.

For example, you can:

  • Add a column
  • Drop a column
  • Rename a column
  • Change a column data type
  • Add or remove constraints

Add a Column
MySQL

ALTER TABLE users
ADD COLUMN phone VARCHAR(20);
Enter fullscreen mode Exit fullscreen mode

PostgreSQL

ALTER TABLE users
ADD COLUMN phone VARCHAR(20);
Enter fullscreen mode Exit fullscreen mode

This syntax is almost the same in both databases.

Rename a Column

MySQL

ALTER TABLE users
RENAME COLUMN phone TO mobile_number;
Enter fullscreen mode Exit fullscreen mode

PostgreSQL

ALTER TABLE users
RENAME COLUMN phone TO mobile_number;
Enter fullscreen mode Exit fullscreen mode

Change Column Data Type

MySQL

ALTER TABLE users
MODIFY COLUMN name VARCHAR(200);
Enter fullscreen mode Exit fullscreen mode

PostgreSQL

ALTER TABLE users
ALTER COLUMN name TYPE VARCHAR(200);

Enter fullscreen mode Exit fullscreen mode

This is an important difference:

  • MySQL uses MODIFY COLUMN
  • PostgreSQL uses ALTER COLUMN ... TYPE

DROP TABLE and DROP COLUMN

DROP removes an object permanently.
Drop a Column

MySQL

ALTER TABLE users
DROP COLUMN phone;
Enter fullscreen mode Exit fullscreen mode

PostgreSQL

ALTER TABLE users
DROP COLUMN phone;
Enter fullscreen mode Exit fullscreen mode

In PostgreSQL, dropping a column does not immediately physically remove the column data; PostgreSQL makes the column invisible to SQL operations and later reclaims space.

Drop a Table

MySQL
DROP TABLE users;

PostgreSQL
DROP TABLE users;

TRUNCATE removes all rows quickly

MySQL

TRUNCATE TABLE users;

MySQL’s TRUNCATE TABLE empties the table completely and requires the DROP privilege.

PostgreSQL
TRUNCATE TABLE users;

PostgreSQL provides extra options:
TRUNCATE TABLE users RESTART IDENTITY;

This resets the auto-increment sequence.
PostgreSQL also supports CONTINUE IDENTITY, CASCADE, and RESTRICT with TRUNCATE.

DML: CRUD Operations

Let us use this table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

For MySQL, replace SERIAL with:

id INT AUTO_INCREMENT PRIMARY KEY

Create — INSERT MySQL and PostgreSQL

INSERT INTO users (name, email)
VALUES ('Arun', 'arun@example.com');
Enter fullscreen mode Exit fullscreen mode

Read — SELECT
SELECT * FROM users;

Get one user:

SELECT *FROM users WHERE id = 1;

Update — UPDATE
UPDATE users SET name = 'Arun Kumar' WHERE id = 1;
Always use a WHERE condition with UPDATE. Otherwise, every row in the table may be updated.

Delete — DELETE
DELETE FROM users WHERE id = 1;
Always use a WHERE condition with DELETE too. Otherwise, all rows can be deleted.

Auto Increment Key

An auto-increment key automatically creates a unique number for every new row. It is commonly used for a primary key such as id.
MySQL: AUTO_INCREMENT

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

Insert data:

INSERT INTO products (name, price)
VALUES ('Laptop', 55000.00);
Enter fullscreen mode Exit fullscreen mode

The id is generated automatically.
You can also change the next auto-increment value:
ALTER TABLE products AUTO_INCREMENT = 100;
MySQL allows setting the starting AUTO_INCREMENT value using CREATE TABLE or ALTER TABLE.

PostgreSQL: SERIAL

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

Insert data:

INSERT INTO products (name, price)
VALUES ('Laptop', 55000.00);
Enter fullscreen mode Exit fullscreen mode

PostgreSQL creates a sequence behind the scenes for the SERIAL column.

Top comments (0)