PostgreSQL Fundamentals

What PostgreSQL is, the words its own docs assume you already know, and how a connection is scoped

Constraints enforceda bad write is rejected, not stored
database → schema → tablethe containment chain everything sits inside
One connection, one databaseno plain SQL crosses that boundary
A strong defaultfor data that needs durability and integrity

1.PostgreSQL as a relational database management system

PostgreSQL is a server that stores data in typed tables and enforces the rules you declare on every write — a NULL, or an order for a nonexistent customer, gets refused. It protects correctness, not just accepts SQL.

Key features

PostgreSQL rejects writes that violate a declared constraint, at write time
Every column has a type — the server enforces it, not just your application
Structure is declared once, in the schema, and applies to every future row
MongoDB and other document stores accept a write app-side validation permits
MySQL is also relational, but differs in defaults, extensibility and SQL conformance

PostgreSQL next to three other stores

PostgreSQL next to three other stores
System	Data model	Enforces structure at write time?
PostgreSQL	relational — typed tables, rows, foreign keys	yes, by default
MySQL	relational — typed tables, rows, foreign keys	yes, by default
MongoDB	document — JSON-like documents, flexible shape	only if you add validation rules
SQL Server	relational — typed tables, rows, foreign keys	yes, by default

A write passes through the rules before it becomes a row

INSERT arrives

a candidate row

constraints checked

types, NOT NULL, CHECK, foreign keys

row stored

only if every rule passed

Command

sql

-- The schema is declared once...
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

-- ...and every later write is checked against it.
INSERT INTO customers (email) VALUES ('a@example.com');   -- accepted
INSERT INTO customers (email) VALUES (NULL);               -- rejected: NOT NULL

PostgreSQL enforces the constraints you declare on every write — it refuses bad data rather than accepting and storing whatever SQL sends it.

2.Core PostgreSQL terminology

Fourteen words the roadmap uses without redefining. A database holds schemas, a schema holds tables (and views, sequences, functions...), a table holds rows made of typed columns — roles, extensions and tablespaces sit outside that chain.

Key features

database → schema → table → row/column is the containment chain
A view is a saved query; a materialized view is a saved query PLUS its cached result
A sequence generates ordered numbers, commonly used to fill an ID column
A trigger runs a function automatically in response to a table event
A role is PostgreSQL's single concept for both "user" and "group"

Fourteen core terms

Fourteen core terms
Term	What it is
database	a named collection of schemas; one connection targets exactly one
schema	a namespace inside a database that groups tables and other objects
table	a named collection of rows, each with the same typed columns
row	one record in a table
column	one named, typed field every row in a table has
view	a saved SELECT query, queried like a table but computed on every read
materialized view	a view whose result is stored on disk, refreshed on demand
sequence	an object that generates a strictly increasing sequence of numbers
function	a stored, named piece of logic that returns a value
procedure	a stored, named piece of logic invoked with CALL, not required to return a value
trigger	a function bound to fire automatically on a table event (INSERT, UPDATE, DELETE)
extension	a packaged bundle of extra types, functions or operators, added to a database
role	an account or a group of privileges — PostgreSQL has one concept for both
tablespace	a named location on disk where the objects assigned to it are physically stored

The containment chain

Database

one connection targets one database

Schema

a namespace inside the database

Table

rows of typed columns

Row / Column

one record; one typed field

Command

sql

CREATE SCHEMA sales;
CREATE TABLE sales.orders (id INT, total NUMERIC);   -- table, inside a schema
CREATE VIEW sales.big_orders AS SELECT * FROM sales.orders WHERE total > 1000;
CREATE SEQUENCE sales.order_seq;                     -- generates ordered numbers
CREATE ROLE analyst LOGIN;                           -- an account, or a group

database → schema → table → row/column is the containment chain. Role, extension and tablespace answer a different question and sit outside it.

3.Server, database, schema and connection

A PostgreSQL server (a "cluster") can host several databases at once, but one connection always targets exactly one of them for its whole session — reaching a second database needs a second connection, not a different table name.

Key features

One PostgreSQL server process (a "cluster") can host many databases
One connection targets exactly one database — never several at once
A schema is a namespace inside a database; every database starts with "public"
Postgres is a strong default system of record for structured, relational data
A cache, a search index, or a wide time-series store are often a better fit elsewhere

Command

sql

-- psql connects to exactly one database per invocation
psql -h localhost -U app_user -d storefront

-- inside that connection, schema-qualify to be explicit
SELECT * FROM public.orders;
SELECT * FROM reporting.daily_totals;

-- to reach a DIFFERENT database on the same server, open a new connection
psql -h localhost -U app_user -d analytics

A connection is scoped to exactly one database for its whole session — schema-qualifying crosses a schema, nothing in plain SQL crosses a database.

Spotted a mistake on this page? Tell us.

Read Sheet