MySQL vs PostgreSQL vs SQLite

SQL is a standard, but every database speaks its own dialect: the standard core plus its own extras and quirks. Most of what you have learned in this course works everywhere. The rest, such as how to limit rows, create automatic IDs or join text, changes from one database to the next.

You will meet more than one database in your career. Django and Python projects often start on SQLite, many startups choose PostgreSQL, banks and large companies often run Microsoft SQL Server, and the browser's Try-it editor in this course is SQLite. This lesson compares the four on the points that trip people up most, and ends with how the SQL standard grew from the 1970s to today.

Four databases at a glance

Server databases run as a separate program that many users connect to over a network. SQLite has no server at all: your program reads and writes the database file directly, which makes it tiny and easy, but less suited to many people writing at once.

Limiting rows

-- MySQL, PostgreSQL, SQLite
SELECT product_name, price FROM products ORDER BY price DESC LIMIT 3 OFFSET 2;

-- SQL standard: PostgreSQL, and SQL Server 2012 or later
SELECT product_name, price FROM products ORDER BY price DESC
OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;

-- SQL Server: first rows only
SELECT TOP 3 product_name, price FROM products ORDER BY price DESC;

LIMIT is not in the SQL standard, but it is so popular that three of the four support it. MySQL does not accept the standard FETCH form, and SQL Server does not accept LIMIT. MySQL also allows the short form LIMIT 2, 3 (skip 2, take 3), which you will see in older PHP code. The first form runs in the browser:

SELECT product_id, product_name, price
FROM products
ORDER BY price DESC
LIMIT 3 OFFSET 2;

product_id | product_name        | price
-----------+---------------------+------
9          | 1TB External SSD    | 6499
12         | Graphics Tablet     | 4599
2          | Mechanical Keyboard | 3499
(3 rows)

Automatic IDs

Each database has its own way to number new rows:

-- MySQL
CREATE TABLE reviews (review_id INT AUTO_INCREMENT PRIMARY KEY, rating INT NOT NULL);

-- PostgreSQL 10 or later (standard SQL); older code uses: review_id SERIAL PRIMARY KEY
CREATE TABLE reviews (review_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, rating INT NOT NULL);

-- SQLite: an INTEGER PRIMARY KEY is numbered automatically
CREATE TABLE reviews (review_id INTEGER PRIMARY KEY, rating INTEGER NOT NULL);

-- SQL Server: start at 1, add 1 each time
CREATE TABLE reviews (review_id INT IDENTITY(1,1) PRIMARY KEY, rating INT NOT NULL);

GENERATED ... AS IDENTITY is the form from the SQL standard and is preferred over SERIAL in new PostgreSQL code. In SQLite, an INTEGER PRIMARY KEY column becomes the table's internal row number; the extra AUTOINCREMENT keyword only stops old numbers from being reused and is rarely needed.

Text, quotes and comparisons

Joining text is the classic difference:

Watch the NULL rules too. MySQL's CONCAT() returns NULL if any argument is NULL, while SQL Server's and SQLite's concat() treat NULL as empty text. In PostgreSQL, || with a NULL gives NULL but CONCAT() skips NULLs. COALESCE() is standard and behaves the same everywhere.

PostgreSQL has one more trap: it turns unquoted names into lower case. CREATE TABLE "Customers" with quotes creates a name that you must then always quote with the same capitals. Stick to lower-case names without quotes and the problem never arises in any database.

Booleans and data types

MySQL has a BOOLEAN type, but it is really TINYINT(1), and TRUE is just 1. SQLite has no boolean type either and stores 0 and 1. PostgreSQL has a true boolean type, and its psql client shows results as t and f. SQL Server uses a BIT column holding 0 or 1. So a comparison in the column list prints 1 and 0 in MySQL and the browser:

SELECT product_name, stock,
       stock > 0 AS in_stock,
       stock BETWEEN 1 AND 20 AS low_stock
FROM products
WHERE category = 'Accessories'
ORDER BY stock;

product_name        | stock | in_stock | low_stock
--------------------+-------+----------+----------
HD Webcam           | 0     | 0        | 0
Graphics Tablet     | 12    | 1        | 1
Mechanical Keyboard | 45    | 1        | 0
USB-C Hub           | 60    | 1        | 0
Laptop Stand        | 75    | 1        | 0
Wireless Mouse      | 120   | 1        | 0
(6 rows)

Data types differ more deeply:

• SQLite uses dynamic typing. A column's declared type is only a preference, called its type affinity, and SQLite will store the text 'abc' in an INTEGER column. Since version 3.37 you can declare a table STRICT to make it reject wrong types. SQLite also has no date type: dates are stored as text such as '2025-04-18' and handled with functions like date() and strftime().
• PostgreSQL is the strictest and the richest: it refuses to compare a number with text without an explicit cast, and adds types such as UUID, arrays, JSONB and TIMESTAMPTZ.
• SQL Server uses NVARCHAR for Unicode text (Hindi, the ₹ sign) and DATETIME2 for dates with times.
• MySQL sits in between: it converts types for you where it can, which is convenient but can hide mistakes.

Insert or update: upserts

"Insert this row, or update it if the key already exists" is called an upsert, and every database spells it differently. Here the shop adds 50 pen drives to stock:

-- MySQL 8.0.19 or later
INSERT INTO products (product_id, product_name, category, price, stock)
VALUES (10, '64GB Pen Drive', 'Storage', 549.00, 50) AS new
ON DUPLICATE KEY UPDATE stock = products.stock + new.stock;

-- PostgreSQL 9.5 or later, and SQLite 3.24 or later
INSERT INTO products (product_id, product_name, category, price, stock)
VALUES (10, '64GB Pen Drive', 'Storage', 549.00, 50)
ON CONFLICT (product_id) DO UPDATE SET stock = products.stock + excluded.stock;

-- SQL Server (PostgreSQL 15 or later also has MERGE)
MERGE products AS target
USING (VALUES (10, '64GB Pen Drive', 'Storage', 549.00, 50))
      AS src (product_id, product_name, category, price, stock)
ON target.product_id = src.product_id
WHEN MATCHED THEN UPDATE SET stock = target.stock + src.stock
WHEN NOT MATCHED THEN INSERT (product_id, product_name, category, price, stock)
     VALUES (src.product_id, src.product_name, src.category, src.price, src.stock);

The ON CONFLICT version runs in the browser, but not in MySQL, so it is not a Try-it example here. MERGE is the form in the SQL standard.

Other everyday differences

CURRENT_TIMESTAMP and COALESCE() are standard and work in all four, so prefer them in code that must move between databases. The safest habit of all: develop and test on the same database you use in production. An app built on SQLite and deployed on MySQL will meet the differences above on the day it goes live.

Most everyday SQL is still identical. This query uses only standard features (LEFT JOIN, GROUP BY, CASE, COUNT), so it runs unchanged in all four databases:

SELECT c.city,
       COUNT(o.order_id) AS orders,
       CASE
         WHEN COUNT(o.order_id) >= 2 THEN 'repeat'
         WHEN COUNT(o.order_id) = 1 THEN 'one-time'
         ELSE 'none yet'
       END AS buyer_type
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.state IN ('Haryana', 'Maharashtra')
GROUP BY c.city
ORDER BY orders DESC, c.city;

city     | orders | buyer_type
---------+--------+-----------
Rohtak   | 4      | repeat
Mumbai   | 2      | repeat
Gurugram | 1      | one-time
Pune     | 0      | none yet
(4 rows)

A short history of the SQL standard

SQL began at IBM. In 1970 Edgar F. Codd published the relational model, the idea of storing data in tables linked by values. In the mid-1970s, Donald Chamberlin and Raymond Boyce designed a query language for it called SEQUEL, later renamed SQL because of a trademark. In 1979 the company that became Oracle released the first commercial SQL database. ANSI published the first SQL standard in 1986, and ISO adopted it in 1987.

No database implements the whole standard, and every vendor adds features before they are standardised; LIMIT and AUTO_INCREMENT are examples. Window functions are part of the 2003 standard, yet MySQL only added them in version 8.0 in 2018.

Key points

• Every database speaks a dialect: standard SQL plus its own extras.
• Row limits: LIMIT in MySQL, PostgreSQL and SQLite; TOP or OFFSET ... FETCH in SQL Server.
• Auto IDs: AUTO_INCREMENT, GENERATED AS IDENTITY (or SERIAL), INTEGER PRIMARY KEY, IDENTITY(1,1).
• || joins text in PostgreSQL and SQLite but means OR in MySQL; use CONCAT() in MySQL.
• Upserts: ON DUPLICATE KEY UPDATE (MySQL), ON CONFLICT (PostgreSQL, SQLite), MERGE (SQL Server, standard).
• SQLite has dynamic types and no date type; PostgreSQL is strict; MySQL converts types quietly.
• The standard grew from SQL-86 to SQL:2023; prefer standard features such as COALESCE, CASE and JOIN ... ON for portable code.

-- PostgreSQL
SELECT o.order_id, c.first_name || ' ' || c.last_name AS customer
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_date DESC
LIMIT 3;

-- SQL Server
SELECT TOP 3 o.order_id, c.first_name + ' ' + c.last_name AS customer
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_date DESC;