PostgreSQL for MySQL users: what changes
45 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)PostgreSQL (often just "Postgres") is a free, open-source relational database, like MySQL. Most of the SQL you know works unchanged; what changes is a short list of details: how ids are generated, how upserts are written, how strict types and text comparisons are, and the client you type into.
Install PostgreSQL or run it with Docker, and connect with psql
psql is PostgreSQL's command-line client, the twin of the mysql client. You type SQL ending in ; and use short backslash commands, such as \dt, for everything else.
Install PostgreSQL 16 from postgresql.org on Windows, or with Postgres.app or brew install postgresql@16 on macOS. Or run it in Docker, as below.
# Run PostgreSQL 16 in Docker (any computer with Docker Desktop)docker run --name pg16 -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:16docker exec -it pg16 psql -U postgres# Or, with PostgreSQL installedpsql -U postgrespsql is PostgreSQL's command-line client, the twin of the mysql client: you connect with psql -U postgres, type SQL ending in ;, and use short backslash commands for everything else. \l lists databases, \c shoplite switches to one (like USE shoplite;), \dt lists tables, \d products describes one table and \q quits. PostgreSQL 16 is a good version to learn on. Install it the usual way, or, if you already use Docker, run it in a container: docker run starts a ready server in one command and docker exec opens psql inside it. The default admin user is postgres and the server listens on port 5432 (MySQL uses 3306).
There is no USE statement: a psql session is connected to one database at a time, and \c reconnects to another. Backslash commands are psql features, not SQL, so they need no semicolon and do not work in other tools. The Windows installer also adds pgAdmin, a graphical tool like Workbench, and SQL Shell (psql) in the Start menu; if psql is not found in Command Prompt, add C:\Program Files\PostgreSQL\16\bin to PATH. Postgres.app on macOS creates a database user with your macOS user name, so plain psql connects as you.
Example 1 · First steps in psql
psql -U postgresPassword for user postgres:
psql (16.15)
Type "help" for help.
postgres=# CREATE DATABASE shoplite_demo;
CREATE DATABASE
postgres=# \c shoplite_demo
You are now connected to database "shoplite_demo" as user "postgres".
shoplite_demo=# \dt
Did not find any relations.
shoplite_demo=# CREATE TABLE cities (name VARCHAR(40) PRIMARY KEY);
CREATE TABLE
shoplite_demo=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | cities | table | postgres
(1 row)
shoplite_demo=# \qWhat this code does
- The prompt shows the current database:
postgres=#at first,shoplite_demo=#after\c. The#means you are an admin user. - psql prints a short status after each command (
CREATE DATABASE,CREATE TABLE) and a row count under every result, such as(1 row). - Tables live in a schema (a folder inside the database),
publicby default, which is why\dtshows a Schema column.
Identity columns instead of AUTO_INCREMENT, and RETURNING
An identity column is PostgreSQL's standard way to number rows: GENERATED ALWAYS AS IDENTITY replaces MySQL's AUTO_INCREMENT. A hidden counter, called a sequence, hands out the next number.
MySQL numbers rows with AUTO_INCREMENT and gives the new id back with LAST_INSERT_ID(). PostgreSQL spells both differently.
Example 2 · Identity column, RETURNING and GENERATED ALWAYS
CREATE TABLE upi_users ( user_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, upi_id VARCHAR(50) NOT NULL UNIQUE, name VARCHAR(60) NOT NULL);INSERT INTO upi_users (upi_id, name) VALUES ('asha@okaxis', 'Asha Rao')RETURNING user_id;INSERT INTO upi_users (upi_id, name)VALUES ('ravi@ybl', 'Ravi Kumar'), ('meena@paytm', 'Meena Iyer')RETURNING user_id, upi_id;INSERT INTO upi_users (user_id, upi_id, name) VALUES (10, 'kabir@ibl', 'Kabir Singh');CREATE TABLE
user_id
---------
1
(1 row)
INSERT 0 1
user_id | upi_id
---------+-------------
2 | ravi@ybl
3 | meena@paytm
(2 rows)
INSERT 0 2
ERROR: cannot insert a non-DEFAULT value into column "user_id"
DETAIL: Column "user_id" is an identity column defined as GENERATED ALWAYS.
HINT: Use OVERRIDING SYSTEM VALUE to override.What this code does
- Each INSERT with RETURNING prints the new rows' columns, then the status
INSERT 0 n(n = rows inserted). - The last INSERT types an id into a
GENERATED ALWAYScolumn and is refused; psql shows the error with a DETAIL and a HINT line. - Unlike the mysql client, psql does not stop a script at the first error; it reports it and carries on with the next statement.
GENERATED ALWAYS refuses an id you type yourself, which protects the counter; GENERATED BY DEFAULT accepts one. With BY DEFAULT, typed ids do not move the sequence, so the next generated id can collide with a row that already exists, and you must reset it with setval. Older code uses SERIAL, which does the same job less strictly.
RETURNING at the end of INSERT, UPDATE or DELETE gives back columns of the changed rows, such as the new id. In MySQL you call LAST_INSERT_ID() afterwards; in PostgreSQL the id comes back from the INSERT itself, even for several rows at once.
ON CONFLICT instead of ON DUPLICATE KEY, ILIKE, and stricter types
An upsert inserts a row, or updates the existing one when its key is already taken. PostgreSQL writes it ON CONFLICT ... DO UPDATE; MySQL writes ON DUPLICATE KEY UPDATE.
A wallet row must be created the first time and topped up after that. PostgreSQL has its own spelling for this.
Example 3 · Upsert with ON CONFLICT, search with ILIKE
CREATE TABLE wallets ( upi_id VARCHAR(50) PRIMARY KEY, name VARCHAR(60) NOT NULL, balance NUMERIC(10,2) NOT NULL);INSERT INTO wallets VALUES ('asha@okaxis', 'Asha Rao', 500.00), ('ravi@ybl', 'Ravi Kumar', 120.00);
-- Top up Asha: the row exists, so add to her balanceINSERT INTO wallets VALUES ('asha@okaxis', 'Asha Rao', 200.00)ON CONFLICT (upi_id) DO UPDATE SET balance = wallets.balance + EXCLUDED.balance;
-- Insert only if new: the row exists, so nothing happensINSERT INTO wallets VALUES ('ravi@ybl', 'Ravi K', 0.00)ON CONFLICT (upi_id) DO NOTHING;
SELECT * FROM wallets WHERE name ILIKE 'a%' OR name ILIKE '%KUMAR' ORDER BY upi_id;CREATE TABLE INSERT 0 2 INSERT 0 1 INSERT 0 0 upi_id | name | balance -------------+------------+--------- asha@okaxis | Asha Rao | 700.00 ravi@ybl | Ravi Kumar | 120.00 (2 rows)
What this code does
EXCLUDED.balanceis the 200.00 from the attempted row, so Asha's balance becomes 500.00 + 200.00 = 700.00.DO NOTHINGreportsINSERT 0 0: zero rows inserted, and Ravi's name and balance stay as they were.ILIKEmatches "Asha Rao" with'a%'and "Ravi Kumar" with'%KUMAR', whatever the case.
Example 4 · Stricter types in PostgreSQL
SELECT 7 / 2 AS int_division, 7 / 2.0 AS decimal_division, 'Asha' = 'asha' AS same_name;SELECT 1 + '2 apples'; int_division | decimal_division | same_name
--------------+--------------------+-----------
3 | 3.5000000000000000 | f
(1 row)
ERROR: invalid input syntax for type integer: "2 apples"
LINE 1: SELECT 1 + '2 apples';
^What this code does
- Two integers give an integer:
7 / 2is 3. Write7 / 2.0or cast with::numericwhen you want the fraction. 'Asha' = 'asha'is false (f): text comparison is case-sensitive.- Text that is not a number cannot be added to a number; PostgreSQL stops with an error and points at the value.
Example 5 · The same expressions in MySQL
SELECT 7 / 2 AS division, 1 + '2 apples' AS text_sum, 'Asha' = 'asha' AS same_name;+----------+----------+-----------+ | division | text_sum | same_name | +----------+----------+-----------+ | 3.5000 | 3 | 1 | +----------+----------+-----------+
What this code does
- MySQL's
/always returns a decimal, so 7 / 2 is 3.5000. '2 apples'is quietly read as 2 (with a warning), so the sum is 3; PostgreSQL refused the same thing.- The default collation ignores case, so
'Asha' = 'asha'is 1 (true). Code that relies on this behaves differently after a move to PostgreSQL.
In INSERT ... ON CONFLICT (upi_id) DO UPDATE SET balance = wallets.balance + EXCLUDED.balance, EXCLUDED is the row you tried to insert, like VALUES(col) or the new-row alias in MySQL's ON DUPLICATE KEY UPDATE. ON CONFLICT DO NOTHING skips the row instead.
ILIKE is LIKE that ignores case: name LIKE 'a%' does not match "Asha", but name ILIKE 'a%' does. PostgreSQL is also stricter with types: two integers divide as integers, and text that is not a number is an error, not a silent 0 or partial number. Examples 4 and 5 show both databases.
Predict the output
Predict the PostgreSQL result
Both queries run in psql. Pick what each one prints.
Round 1 of 2
FULL OUTER JOIN, case-sensitive text and other differences
A FULL OUTER JOIN keeps the rows of both tables, matched where possible and padded with NULLs where not. PostgreSQL has it; MySQL does not.
Hostel rooms and students must be listed together, including empty rooms and a student with no room.
Example 6 · FULL OUTER JOIN and case-sensitive text
CREATE TABLE rooms (room_no INT PRIMARY KEY, block CHAR(1) NOT NULL);CREATE TABLE allotments (student VARCHAR(40) PRIMARY KEY, room_no INT);INSERT INTO rooms VALUES (101, 'A'), (102, 'A'), (201, 'B');INSERT INTO allotments VALUES ('Aditya', 101), ('Bhavana', 101), ('Chirag', NULL);
SELECT r.room_no, r.block, a.studentFROM rooms rFULL OUTER JOIN allotments a ON a.room_no = r.room_noORDER BY r.room_no, a.student;
SELECT 'Asha' = 'asha' AS plain, LOWER('Asha') = LOWER('asha') AS lowered, 'Asha' ILIKE 'asha' AS ilike;CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 3
room_no | block | student
---------+-------+---------
101 | A | Aditya
101 | A | Bhavana
102 | A |
201 | B |
| | Chirag
(5 rows)
plain | lowered | ilike
-------+---------+-------
f | t | t
(1 row)What this code does
- Rooms 102 and 201 have no student and Chirag has no room; FULL OUTER JOIN keeps all of them, with NULLs (shown as blanks) on the missing side.
- PostgreSQL sorts NULLs last in ascending order, so Chirag's row comes at the end; MySQL puts NULLs first.
- For a case-insensitive comparison, compare
LOWER(...)of both sides or useILIKE.
In MySQL, which has no FULL OUTER JOIN, you write a LEFT JOIN UNION a RIGHT JOIN instead. Text comparisons are case-sensitive in PostgreSQL: 'Asha' = 'asha' is false, while MySQL's default collation says true. So a UNIQUE e-mail column accepts both asha@x.com and Asha@x.com; a unique index on LOWER(email) restores the MySQL behaviour.
The rest is a translation list you will meet when porting a script: DATETIME → TIMESTAMP; TINYINT(1) → BOOLEAN (shown as t / f); DECIMAL works (it is NUMERIC); no UNSIGNED; DATE_FORMAT(d, '%Y-%m') → to_char(d, 'YYYY-MM'); IFNULL → COALESCE; DATE(x) → x::date; CONCAT works, and 'a' || 'b' joins text (in MySQL || means OR); names quoted with MySQL back-ticks → double quotes, such as "order"; in PostgreSQL, double quotes are never strings; foreign key columns are not indexed for you (MySQL adds an index); ENUM → a CHECK or CREATE TYPE; REGEXP → ~.
Find the bug
Find the MySQL-only line
This script ran in MySQL. Click the line PostgreSQL will not accept.
Create the users table.
Round 1 of 3Next step
Check your understanding
11 quick questions