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
);
PostgreSQL
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL
);
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);
PostgreSQL
ALTER TABLE users
ADD COLUMN phone VARCHAR(20);
This syntax is almost the same in both databases.
Rename a Column
MySQL
ALTER TABLE users
RENAME COLUMN phone TO mobile_number;
PostgreSQL
ALTER TABLE users
RENAME COLUMN phone TO mobile_number;
Change Column Data Type
MySQL
ALTER TABLE users
MODIFY COLUMN name VARCHAR(200);
PostgreSQL
ALTER TABLE users
ALTER COLUMN name TYPE VARCHAR(200);
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;
PostgreSQL
ALTER TABLE users
DROP COLUMN phone;
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
);
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');
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
);
Insert data:
INSERT INTO products (name, price)
VALUES ('Laptop', 55000.00);
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
);
Insert data:
INSERT INTO products (name, price)
VALUES ('Laptop', 55000.00);
PostgreSQL creates a sequence behind the scenes for the SERIAL column.
Top comments (0)