PostgreSQL vs SQL

Learning Focus

Use this lesson to separate the SQL standard from PostgreSQL-specific features, avoid "it worked on another DB" surprises, and understand how PostgreSQL compares to MySQL in detail.

Fundamental Definitions

SQL (Structured Query Language)

• What it is:
  • Standard programming language for managing relational databases
  • ANSI/ISO standardized (with variations across implementations)
  • Used for:
    • Querying data (SELECT)
    • Data manipulation (INSERT, UPDATE, DELETE)
    • Database schema management (CREATE, ALTER, DROP)
    • Access control (GRANT, REVOKE)

PostgreSQL

• What it is:
  • Open-source object-relational database management system (ORDBMS)
  • One of the most SQL-standard-compliant implementations
  • Licensed under the permissive PostgreSQL License (similar to MIT/BSD)
  • Uses SQL as its query language, with extensive extensions
  • Popular for complex applications, analytics, geospatial, and multi-model workloads

Key Differences: SQL vs PostgreSQL

Aspect	SQL	PostgreSQL
Nature	Language standard	Database software
Variants	ANSI/ISO standard syntax	Specific implementation with extensions
Portability	Concepts apply to all RDBMS	PostgreSQL-specific features and behavior
Extensions	Standard defines the baseline	Adds RETURNING, JSONB, DISTINCT ON, CTEs, etc.
Usage	Used to communicate with databases	A database that understands (and extends) SQL
Licensing	N/A (language)	PostgreSQL License (permissive, free)

The Relationship Explained

graph TD
    A[SQL Standard] --> B[PostgreSQL]
    A --> C[MySQL]
    A --> D[Oracle]
    A --> E[SQL Server]
    A --> F[SQLite]

    style A fill:#f9f,stroke:#333
    style B fill:#336791,stroke:#333,color:#fff

• SQL is the language used to interact with relational databases
• PostgreSQL is one of many database systems that understand SQL
• PostgreSQL is widely considered the most standards-compliant open-source RDBMS

Key Similarities Between SQL and PostgreSQL

1. Both deal with relational databases
2. Use the same basic SQL syntax for CRUD operations
3. Support primary keys, foreign keys, and indexes
4. Use similar data types (INTEGER, VARCHAR, DATE, BOOLEAN, etc.)
5. Support transactions with BEGIN, COMMIT, ROLLBACK

PostgreSQL-Specific Features

While PostgreSQL uses standard SQL, it adds powerful extensions:

1. RETURNING Clause

Verify the result of DML operations without a separate query:

INSERT INTO users (email)
VALUES ('dev@example.com')
RETURNING user_id, email;

UPDATE orders SET status = 'shipped'
WHERE order_id = 42
RETURNING *;

DELETE FROM sessions
WHERE expires_at < now()
RETURNING session_id;

2. DISTINCT ON

Select the first row per group (not available in MySQL):

SELECT DISTINCT ON (customer_id)
       customer_id, order_date, total
FROM orders
ORDER BY customer_id, order_date DESC;

3. Upsert with ON CONFLICT

Safe idempotent writes:

INSERT INTO users (email)
VALUES ('dev@example.com')
ON CONFLICT (email)
DO UPDATE SET email = EXCLUDED.email
RETURNING user_id;

4. JSONB Native Support

Store, index, and query JSON documents:

CREATE TABLE events (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  data  jsonb NOT NULL
);

-- Query nested JSON fields
SELECT data->>'event_type' AS event_type
FROM events
WHERE data @> '{"source": "api"}';

-- Create GIN index for fast lookups
CREATE INDEX idx_events_data ON events USING gin (data);

5. Array Data Types

CREATE TABLE tags (
  article_id int PRIMARY KEY,
  labels     text[] NOT NULL
);

SELECT * FROM tags WHERE 'postgresql' = ANY(labels);

6. Advanced Identity Columns

-- SQL-standard identity (preferred over SERIAL)
CREATE TABLE products (
  product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL
);

7. Common Table Expressions (CTEs) — Fully Optimized

WITH monthly_sales AS (
  SELECT date_trunc('month', order_date) AS month,
         sum(total) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month, revenue
FROM monthly_sales
ORDER BY month;

Cross-Platform SQL Example

Standard SQL (Works in Most RDBMS):

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    hire_date DATE
);

SELECT name, hire_date
FROM employees
WHERE hire_date > '2020-01-01'
ORDER BY name;

PostgreSQL-Specific Version:

CREATE TABLE employees (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    hire_date date NOT NULL DEFAULT CURRENT_DATE
);

SELECT name, hire_date
FROM employees
WHERE hire_date > CURRENT_DATE - INTERVAL '3 years'
ORDER BY name
LIMIT 10;

Common Misconceptions

❌ "PostgreSQL is a different language from SQL"

✅ Truth: PostgreSQL implements SQL with standards-compliant extensions

❌ "All SQL is the same across databases"

✅ Truth: While ~80% is similar, each RDBMS has unique syntax, functions, and behavior

❌ "Learning PostgreSQL means learning SQL"

✅ Truth: Learning PostgreSQL means learning SQL through PostgreSQL's highly compliant implementation

❌ "PostgreSQL is slower than MySQL"

✅ Truth: PostgreSQL excels at complex queries, writes, and concurrent workloads; performance depends on the use case

---

PostgreSQL vs MySQL: Comprehensive Comparison

This is one of the most important comparisons in the open-source database world. Both are excellent databases, but they have fundamentally different design philosophies.

Design Philosophy

Aspect	PostgreSQL	MySQL
Philosophy	Correctness, standards compliance, extensibility	Speed, simplicity, ease of use
Classification	Object-Relational DBMS (ORDBMS)	Relational DBMS (RDBMS)
License	PostgreSQL License (permissive, BSD-like)	GPL + Commercial (Oracle-owned)
First Release	1996 (roots from 1986 as POSTGRES)	1995
Governance	Community-driven, no single owner	Oracle Corporation

SQL Standards Compliance

Feature	PostgreSQL	MySQL
SQL standard compliance	Most compliant open-source DB	Partial compliance, many proprietary extensions
FULL OUTER JOIN	✅ Supported	❌ Not supported
INTERSECT / EXCEPT	✅ Supported	✅ Supported (since 8.0)
Window functions	✅ Full support (since 8.4)	✅ Supported (since 8.0)
CTEs (WITH queries)	✅ Full support, optimized	✅ Supported (since 8.0)
CHECK constraints	✅ Enforced	⚠️ Parsed but ignored before 8.0.16
RETURNING clause	✅ Supported	❌ Not supported
DISTINCT ON	✅ Supported	❌ Not supported
Lateral joins	✅ Supported	✅ Supported (since 8.0)

Data Types

Type Category	PostgreSQL	MySQL
Boolean	Real boolean type (true/false)	TINYINT(1) treated as boolean
Text	text (unlimited), varchar(n)	VARCHAR, TEXT, MEDIUMTEXT, LONGTEXT
JSON	json and jsonb (binary, indexable)	JSON (text-based, limited indexing)
Arrays	✅ Native array types	❌ Not supported natively
UUID	✅ Native uuid type	❌ Stored as CHAR(36) or BINARY(16)
Network	inet, cidr, macaddr	❌ Not supported
Range types	int4range, tsrange, daterange	❌ Not supported
Geometric	point, line, polygon, circle	❌ Limited spatial support
Enum	✅ True enum type	✅ Enum type (stored differently)
Identity columns	GENERATED ALWAYS AS IDENTITY (SQL standard)	AUTO_INCREMENT (MySQL-specific)

Performance Characteristics

Workload	PostgreSQL Strength	MySQL Strength
Simple reads (PK lookups)	Fast	Slightly faster (especially InnoDB)
Complex queries (joins, subqueries)	Significantly better optimizer	Adequate for simple joins
Write-heavy workloads	Better (MVCC, no read locks)	Adequate with InnoDB
Concurrent users	Better (MVCC by design)	Adequate with InnoDB row locking
Full-text search	Built-in (tsvector/tsquery)	Built-in (InnoDB full-text)
Geospatial	PostGIS (industry standard)	Basic spatial support
JSON operations	JSONB with GIN indexing	JSON with generated columns
Bulk loading	COPY command (very fast)	LOAD DATA INFILE (fast)
Connection overhead	Process-per-connection (needs pooling)	Thread-per-connection (lighter)

Architecture Differences

Feature	PostgreSQL	MySQL
Concurrency model	MVCC (readers never block writers)	InnoDB uses MVCC; MyISAM uses table locking
Storage engines	Single, unified engine	Pluggable (InnoDB, MyISAM, Memory, etc.)
Replication	Streaming + Logical	Binary log + Group replication
Connection model	Process-per-connection	Thread-per-connection
Tablespaces	✅ Supported	✅ Supported (InnoDB)
Partitioning	Declarative (range, list, hash)	Similar partitioning support
Parallel queries	✅ Parallel scans, joins, aggregates	Limited parallelism
Materialized views	✅ Built-in	❌ Not built-in (workarounds exist)

Extensibility

Feature	PostgreSQL	MySQL
Extensions	✅ Rich ecosystem (PostGIS, pg_stat_statements, TimescaleDB, Citus)	❌ Plugins exist but limited
Custom types	✅ Create your own data types	❌ Not supported
Custom operators	✅ Define custom operators	❌ Not supported
Custom index methods	✅ GIN, GiST, SP-GiST, BRIN, custom	B-tree, Hash, Full-text, Spatial
Procedural languages	PL/pgSQL, PL/Python, PL/Perl, PL/v8, PL/R	Stored procedures (SQL, limited)
Foreign Data Wrappers	✅ Query external data sources as tables	❌ Limited (FEDERATED engine, deprecated)

Security

Feature	PostgreSQL	MySQL
Authentication	pg_hba.conf (granular, per-database, per-user, per-host)	User table with host patterns
Row-Level Security	✅ Built-in RLS policies	❌ Not supported natively
Column-Level Permissions	✅ Supported	✅ Supported
SSL/TLS	✅ Full support	✅ Full support
Audit logging	Via pgaudit extension	Via enterprise plugin or audit log

Ecosystem and Tooling

Area	PostgreSQL	MySQL
Primary CLI	psql (powerful, scriptable)	mysql CLI
Primary GUI	pgAdmin, DBeaver, DataGrip	MySQL Workbench, DBeaver, DataGrip
ORM support	Excellent (Django, SQLAlchemy, ActiveRecord, Prisma)	Excellent (all major ORMs)
Cloud offerings	AWS RDS/Aurora, GCP Cloud SQL/AlloyDB, Azure	AWS RDS/Aurora, GCP Cloud SQL, Azure
Community	Large, developer-focused	Large, web-application-focused
Documentation	Exceptional (detailed, authoritative)	Good, with many community resources

PostgreSQL Strengths Summary

1. SQL standards compliance — most compliant open-source database
2. Advanced data types — JSONB, arrays, ranges, network types, UUID
3. Extensibility — custom types, operators, index methods, procedural languages
4. MVCC by design — high concurrency without read locks
5. PostGIS — industry-leading geospatial support
6. Row-Level Security — fine-grained access control built in
7. Sophisticated query planner — handles complex queries efficiently
8. RETURNING clause — verify DML results without extra queries
9. Permissive license — no corporate ownership concerns

PostgreSQL Weaknesses

1. Connection model — process-per-connection can be expensive at scale (mitigated with pgbouncer)
2. Table bloat — MVCC dead tuples require autovacuum tuning
3. Simpler setups may be over-engineered — more configuration knobs than some use cases need
4. Less common in legacy PHP/WordPress stacks — MySQL has deeper roots in LAMP ecosystem
5. No built-in connection pooler — requires external tools like PgBouncer or pgcat

MySQL Strengths Summary

1. Simplicity — easier initial setup and configuration
2. Thread-per-connection — lighter connection overhead for many short-lived connections
3. WordPress/PHP ecosystem — default database for LAMP stack
4. Read performance — slightly faster for simple primary key lookups
5. Pluggable storage engines — can choose InnoDB, MyISAM, Memory, etc.
6. Group Replication — built-in high-availability clustering
7. Wide hosting support — virtually every web host offers MySQL

MySQL Weaknesses

1. SQL standards gaps — FULL OUTER JOIN, RETURNING, DISTINCT ON not supported
2. Boolean as TINYINT — no real boolean type
3. CHECK constraints — silently ignored before version 8.0.16
4. Limited extensibility — no custom types, operators, or index methods
5. No Row-Level Security — must be implemented at the application layer
6. Oracle ownership — GPL licensing and corporate governance concerns
7. Weaker optimizer — struggles with complex joins and subqueries compared to PostgreSQL
8. No materialized views — must be simulated manually
9. No native array or range types — limits data modeling flexibility

When to Choose PostgreSQL

• Complex data models with advanced types (JSON, arrays, geospatial)
• Applications requiring strict SQL compliance
• High-concurrency write workloads
• Projects needing Row-Level Security
• Analytics and data warehousing workloads
• Geospatial applications (PostGIS)
• Multi-model data (relational + document + time-series)

When to Choose MySQL

• Simple web applications with straightforward CRUD
• WordPress, Drupal, or PHP-based applications
• When working with legacy LAMP stacks
• Environments where MySQL hosting is already available
• Projects requiring the simplest possible setup

---

FAQ

Q: Can I use my SQL knowledge with other databases?

A: Yes! Most SQL skills transfer between systems with some syntax adjustments. PostgreSQL's compliance means more of your knowledge is portable.

Q: Is PostgreSQL the only free SQL database?

A: No — MySQL (Community Edition) and SQLite are other popular open-source options. But PostgreSQL has the most permissive license.

Q: Does PostgreSQL support all SQL features?

A: PostgreSQL is the most standards-compliant open-source database. It supports features that many commercial databases lack.

Q: Which should I learn first — SQL or PostgreSQL?

A: Learn standard SQL concepts first, then PostgreSQL-specific implementations. PostgreSQL is an excellent choice because its behavior closely matches the SQL standard.

Q: Can I migrate from MySQL to PostgreSQL?

A: Yes. Tools like pgloader automate most of the migration. Key areas to review: data type mapping, AUTO_INCREMENT → IDENTITY, and MySQL-specific functions.

Quick Comparison Quiz

1. Which of these is a database system? a) SELECT b) PostgreSQL c) WHERE d) JOIN

2. True/False: All PostgreSQL queries will work in MySQL

3. Which clause is PostgreSQL-specific? a) WHERE b) RETURNING c) GROUP BY d) HAVING

4. Which feature does PostgreSQL have that MySQL does not? a) Primary keys b) Row-Level Security c) Indexes d) Foreign keys

(Answers: 1-b, 2-False, 3-b, 4-b)

Key Takeaways

• SQL is the language; PostgreSQL is a database that implements and extends it
• PostgreSQL is the most SQL-standard-compliant open-source database
• PostgreSQL adds powerful features: RETURNING, JSONB, DISTINCT ON, arrays, row-level security
• PostgreSQL excels at complex queries, write concurrency, and extensibility
• MySQL excels at simplicity, PHP ecosystem integration, and lightweight read workloads
• Fundamental SQL skills transfer between databases — always consider your RDBMS's specific syntax

Common Pitfalls

Pitfall	Consequence	Prevention
Assuming MySQL syntax works in PostgreSQL	Queries fail (AUTO_INCREMENT, IFNULL, backtick quoting)	Use PostgreSQL-native syntax: IDENTITY, COALESCE, double quotes
Ignoring case-folding of identifiers	MyTable becomes mytable without quotes	Use lowercase identifiers or always quote with ""
Not using RETURNING	Extra SELECT queries waste round-trips	Always use RETURNING after INSERT/UPDATE/DELETE
Using SERIAL instead of IDENTITY	Legacy behavior, harder to manage	Prefer GENERATED ALWAYS AS IDENTITY

Quick Reference

-- PostgreSQL: Inspect your environment
\l                     -- list databases
\c your_database       -- connect to a database
\dt                    -- list tables
\d+ your_table         -- describe table structure
SELECT version();      -- check PostgreSQL version
SELECT * FROM your_table LIMIT 10;

What's Next

• Previous: PostgreSQL Core Concepts — Review the previous lesson to reinforce context.
• Section Overview — Return to this section index and choose another related lesson.