PostgreSQL from zero: install, psql, tables, queries, JSON, views, functions and administration.
The World's Most Advanced Open Source Database PostgreSQL, often just called Postgres, is a powerful open source relational database that has been developed by a global community ...
Installing on Ubuntu / Debian On Debian-family systems the postgres OS user owns the database cluster. Connect as that user first. Setting a Password and Creating a User ...
What is psql? psql is the interactive terminal client for PostgreSQL. It runs SQL and provides handy meta-commands that start with a backslash. Useful Meta-Commands Running a File ...
Numeric, Text, Date and More PostgreSQL has one of the richest type systems of any relational database. Numeric Types SERIAL is a convenient auto-incrementing integer (internally ...
Building a Table What Each Piece Means SERIAL : auto-incrementing ID served by a sequence. NOT NULL : value required. UNIQUE : no duplicates in the column. DEFAULT : value when ...
Inserting Rows Returning Values A PostgreSQL specialty: INSERT, UPDATE and DELETE can hand back the rows they touched. Selecting Data Upsert With ON CONFLICT Deleting Key Points ...
Joining Tables Join Types INNER JOIN: only matching rows on both sides. LEFT JOIN: all rows from the left table plus matches. RIGHT JOIN: all rows from the right table plus ...
Beyond Group By Window functions compute values across a set of rows related to the current row, without collapsing them into a single row like GROUP BY does. Ranking LAG and LEAD ...
Storing Documents in PostgreSQL Querying JSON -> returns a JSON value. ->> returns text. #> and #>> navigate a path such as payload #>> '{'metadata', 'ip'}' . Indexing JSONB A GIN ...
Views Are Saved Queries Views hide complexity, centralise logic and restrict what users can see. Materialized Views Cache Results A materialized view stores its result on disk. ...
SQL Functions PL/pgSQL PL/pgSQL is PostgreSQL's procedural language: variables, loops, conditions and exceptions all inside the database. Calculating with Loops Key Points SQL ...
Why Indexes Speed Things Up Without an index, PostgreSQL scans the whole table to find rows. An index is an ordered structure that lets it jump straight to the matches. Creating ...
Logical Backup With pg_dump Restoring Physical Backup With pg_basebackup pg_basebackup copies the whole data directory and is the foundation of streaming replication. Transactions ...