Postgres by Example

PostgreSQL is a powerful, open-source relational database. Please read the official documentation to learn more.

Postgres by Example is a hands-on introduction to PostgreSQL using annotated SQL examples. Check out the first example or browse the full list below.

Prerequisites: PostgreSQL installed and the server running. Examples assume you can connect with psql; the default database is postgres unless noted. Start the server with your system's service manager or pg_ctl start as needed.

Unless stated otherwise, examples target current stable PostgreSQL. Use the latest version you can if something isn't working.

Table of Contents

Getting Started

• First Query
• psql Basics

Querying

• SELECT Basics
• WHERE
• ORDER BY
• SELECT from a Table
• LIMIT and OFFSET
• DISTINCT
• NULLs
• Expressions

Data Types

• Numeric Types
• Text Types
• Boolean and Dates
• UUID and JSONB

DDL

• CREATE TABLE
• Column Types and Constraints
• ALTER TABLE and DROP
• Primary Keys and Unique
• NOT NULL and DEFAULT

DML

• INSERT
• UPDATE
• DELETE
• RETURNING

Joins and Sets

• INNER and LEFT JOIN
• RIGHT and FULL JOIN
• Self-Join
• UNION, INTERSECT, EXCEPT

Aggregation and Grouping

• COUNT, SUM, AVG
• GROUP BY
• HAVING

Subqueries

• Scalar and IN Subqueries
• EXISTS and Derived Tables

Functions and Operators

• String and Numeric Functions
• Date Functions and COALESCE
• CASE

Indexes

• CREATE INDEX
• When to Index

Transactions

• BEGIN, COMMIT, ROLLBACK
• Savepoints

Views

• CREATE VIEW

Security

• Roles and GRANT

Extras

• psql Meta-commands
• COPY

Licensed under CC BY 4.0.

by Dariush Abbasi | source