Skip to content
Tutorials Center
My learning

Topics

Lesson 94 of 125 ยท Database Systems

PostgreSQL Essentials

Move from SQLite to PostgreSQL. Learn psql basics, strict types, IDENTITY columns, RETURNING, arrays, JSONB, ILIKE, FILTER and DISTINCT ON, with SQLite equivalents.

PostgreSQL (often just "Postgres") is a free, open-source relational database server with a reputation for correctness, a rich type system and a huge ecosystem of extensions. It powers everything from small side projects to some of the largest applications on the web, and it consistently ranks among developers' favorite databases.

Almost everything you've learned in this course carries over directly: SELECT, joins, GROUP BY, CTEs, window functions, INSERT ... ON CONFLICT, transactions and indexes all work the same way or very nearly so. What changes is the setting (a server you connect to instead of a file you open) and a set of extra features that SQLite doesn't have or does differently.

This lesson is a practical tour of those differences. PostgreSQL code is shown read-only and labeled -- PostgreSQL. Wherever possible, a runnable SQLite example next to it shows the same idea, so you can still experiment here.

Installing PostgreSQL and connecting with psql

PostgreSQL runs as a server process. Your applications and tools connect to it over the network (port 5432 by default) with a user name and password. Common ways to get one running:

  • Installers for Windows and macOS from the PostgreSQL project, or Postgres.app on macOS.
  • Package managers: brew install postgresql@18 on macOS, sudo apt install postgresql on Debian/Ubuntu.
  • Docker, which is handy because it leaves nothing behind:
Terminal
docker run --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:18
docker exec -it pg psql -U postgres
  • Managed services (from all major cloud providers and specialized hosts) for production, so you don't have to run backups and upgrades yourself.

psql is the official command-line client. You type SQL statements ending in ;, plus meta-commands that start with a backslash and are handled by psql itself:

Command What it does
\l List databases
\c shop Connect to the database shop
\dt List tables in the current schema(s)
\d orders Describe a table: columns, types, indexes, constraints, triggers
\dn, \df, \du List schemas, functions, roles
\x Toggle expanded display (one column per line, great for wide rows)
\timing Show how long each statement takes
\i file.sql Run statements from a file
\copy Import or export CSV between your computer and a table
\h CREATE INDEX Show the syntax of an SQL command
\? / \q Help on meta-commands / quit

Graphical tools such as pgAdmin, DBeaver, DataGrip or your editor's database extension work too. See SQL Dialects and SQLite for a broader look at clients.

Databases, schemas and users

A PostgreSQL server (a cluster) holds many databases. Each database contains schemas, which are namespaces for tables, views and functions. New databases have a schema called public, and unqualified names are looked up along the search_path (by default your user's own schema, then public). A fully qualified name looks like sales.orders.

SQLite has nothing quite like this: a database is a file, and the closest thing to schemas is attaching another file with ATTACH DATABASE 'other.db' AS other. PostgreSQL also has real users and roles with permissions, covered in Users, Roles and Permissions.

Identifiers fold to lowercase. PostgreSQL lowercases unquoted names, so CREATE TABLE Orders creates orders, and SELECT * FROM ORDERS finds it. But a name created with double quotes, like "OrderItems", keeps its case and must be quoted forever after. Stick to unquoted snake_case names and you'll never notice.

Types are strict

The biggest day-to-day difference is typing. SQLite uses flexible type affinity: a column declared INTEGER will happily store the text 'twelve'. PostgreSQL checks every value against the column's type and rejects anything that doesn't fit. SQLite's STRICT tables give you a taste of that behavior. The last insert fails on purpose:

SQL Flexible versus strict typing

Press Run (or Ctrl + Enter) to see the result here.

In PostgreSQL the same mistake fails with an error like invalid input syntax for type integer: "twelve". You convert values explicitly with the standard CAST(x AS type) or PostgreSQL's shorthand x::type:

SQL
-- PostgreSQL
SELECT '2026-05-12'::date + 30;           -- a date 30 days later
SELECT CAST('19.90' AS numeric(10, 2));
SELECT 7 / 2, 7 / 2.0, 7::numeric / 2;    -- 3, 3.5000000000000000, 3.5000000000000000

Integer division truncates in both databases (7 / 2 is 3), but / with a numeric operand keeps the fraction. PostgreSQL's most important types, and their closest SQLite equivalents:

PostgreSQL Use it for SQLite equivalent
integer, bigint, smallint Whole numbers (4, 8, 2 bytes) INTEGER
numeric(p, s) Exact decimals: money, quantities No exact decimal; use integer cents or REAL
real, double precision Approximate floating point REAL
text, varchar(n) Strings (text is the idiomatic choice) TEXT
boolean true / false INTEGER 1 / 0
date, time, timestamp, timestamptz Dates and times (timestamptz for real-world instants) TEXT in ISO format, or Unix-time numbers
interval Durations: interval '3 days' Date modifiers: date(d, '+3 days')
uuid Universally unique ids TEXT or BLOB
jsonb, json JSON documents (jsonb is binary and indexable) TEXT with JSON functions, or BLOB with jsonb()
text[], integer[], ... Arrays of any type JSON arrays
bytea Binary data BLOB
Enums (CREATE TYPE mood AS ENUM (...)) A fixed list of labels TEXT with a CHECK

Use timestamptz for moments in time. Despite its name, timestamp with time zone doesn't store a time zone. It stores an exact instant (in UTC) and converts to the session's time zone when displayed. Plain timestamp stores a wall-clock reading with no zone, which causes trouble as soon as users, servers or daylight saving time disagree.

Auto-incrementing keys and RETURNING

SQLite gives every INTEGER PRIMARY KEY an automatic value. PostgreSQL uses identity columns (standard SQL, PostgreSQL 10+) or the older serial shorthand, both backed by a sequence:

SQL
-- PostgreSQL
CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Older style you'll see in existing code:
-- id serial PRIMARY KEY

GENERATED ALWAYS refuses manually supplied ids (unless you write OVERRIDING SYSTEM VALUE), while GENERATED BY DEFAULT accepts them. Prefer identity columns in new code: serial is just a shortcut that creates a separately owned sequence, which complicates permissions and copying tables. See Auto-Increment and Identity Columns for a cross-database comparison.

To get generated values back, PostgreSQL has long supported RETURNING, and SQLite added the same clause in version 3.35, so this part is identical:

SQL RETURNING works in both databases

Press Run (or Ctrl + Enter) to see the result here.

Notice that SQLite prints 1 and 0 for TRUE and FALSE: it accepts the keywords but stores integers. PostgreSQL has a real boolean type and prints t and f in psql, so you can write WHERE is_active or WHERE NOT is_active directly.

Text: case sensitivity, ILIKE and regular expressions

In PostgreSQL, LIKE is case-sensitive: 'Apple' LIKE 'app%' is false. For case-insensitive matching it adds ILIKE. SQLite's LIKE is case-insensitive for ASCII letters by default, so SQLite's LIKE behaves more like PostgreSQL's ILIKE.

SQL
-- PostgreSQL
SELECT name FROM products WHERE name ILIKE '%lamp%';    -- any case
SELECT name FROM products WHERE name ~ '^[A-Z]{3}-\d+$'; -- regex, case-sensitive
SELECT name FROM products WHERE name ~* 'kit$';          -- regex, any case
SELECT string_agg(name, ', ' ORDER BY name) FROM products;

PostgreSQL has full POSIX regular expressions (~, ~*, !~, regexp_replace, regexp_matches), while SQLite has no built-in regex operator. Both use || for concatenation and single quotes for strings. String aggregation is string_agg in PostgreSQL and group_concat in SQLite (newer SQLite versions also accept string_agg). More in String Functions and Pattern Matching with LIKE.

FILTER and DISTINCT ON

PostgreSQL pioneered two features that make reports shorter. The first, the standard FILTER clause on aggregates, is also supported by SQLite, so you can run it here. It counts or sums only the rows that match a condition, without a CASE expression:

SQL Aggregates with FILTER ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In PostgreSQL you'd group by date_trunc('month', ordered_at), which returns the first instant of each month as a real timestamp; strftime('%Y-%m', ...) is SQLite's text-based equivalent. See Pivoting Rows into Columns for more FILTER patterns.

The second feature, DISTINCT ON, is PostgreSQL-only. It keeps the first row of each group according to the ORDER BY, which answers "latest order per customer" in a single, readable statement:

SQL
-- PostgreSQL
SELECT DISTINCT ON (customer_id)
  customer_id, id AS order_id, ordered_at, status
FROM orders
ORDER BY customer_id, ordered_at DESC;

The portable equivalent, which works in SQLite and every other modern database, numbers the rows with a window function and keeps number 1:

SQL DISTINCT ON rewritten with ROW_NUMBER ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Arrays

PostgreSQL columns can hold arrays of any type. For small, simple lists that you always read together, such as a handful of tags, an array can save a junction table:

SQL
-- PostgreSQL
CREATE TABLE posts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  tags text[] NOT NULL DEFAULT '{}'
);

INSERT INTO posts (title, tags) VALUES
  ('Reading Query Plans', ARRAY['sql', 'performance']),
  ('Window Functions', '{sql,beginner}');

SELECT title FROM posts WHERE 'performance' = ANY (tags);   -- contains one value
SELECT title FROM posts WHERE tags @> ARRAY['sql', 'beginner']; -- contains all values
SELECT unnest(tags) AS tag, count(*) FROM posts GROUP BY tag;    -- one row per element

CREATE INDEX posts_tags_gin ON posts USING gin (tags);           -- speeds up @> and &&

Arrays are 1-based (tags[1] is the first element). SQLite has no array type, but JSON arrays and json_each cover the same ground:

SQL Arrays in SQLite with JSON

Press Run (or Ctrl + Enter) to see the result here.

Use arrays (or JSON lists) with care. Once you need to attach data to each element, enforce foreign keys, or query from the element's side ("all posts for each tag") often, a junction table is the better design.

JSONB

PostgreSQL's jsonb type stores JSON in a parsed binary form. It's fast to query, can be indexed with GIN indexes, and comes with a rich set of operators:

SQL
-- PostgreSQL
CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload jsonb NOT NULL
);

SELECT payload -> 'device' ->> 'os' FROM events;           -- -> returns jsonb, ->> returns text
SELECT * FROM events WHERE payload @> '{"type": "signup"}'; -- containment
SELECT * FROM events WHERE payload ? 'coupon';              -- does the key exist?
UPDATE events SET payload = jsonb_set(payload, '{seen}', 'true') WHERE id = 1;

CREATE INDEX events_payload_gin ON events USING gin (payload);  -- serves @> and ?

SQLite's JSON support uses the same -> and ->> operators (borrowed from PostgreSQL), plus functions for everything else. A @> containment test becomes an explicit comparison on the field, and an index on the extracted field replaces the GIN index (see Partial, Expression and Covering Indexes):

SQL JSONB-style queries in SQLite

Press Run (or Ctrl + Enter) to see the result here.

The Working with JSON lesson covers SQLite's side in depth.

Dates, intervals and generate_series

PostgreSQL's date handling is built on real types and intervals:

SQL
-- PostgreSQL
SELECT now();                                   -- current timestamptz
SELECT CURRENT_DATE - 30;                       -- the date 30 days ago
SELECT now() + interval '2 hours 30 minutes';
SELECT date_trunc('week', now());               -- start of the current week
SELECT EXTRACT(YEAR FROM ordered_at) FROM orders;
SELECT age(DATE '2026-05-12', DATE '1990-02-01');  -- 36 years 3 mons 11 days
SELECT d::date FROM generate_series(DATE '2026-01-01', DATE '2026-01-07', interval '1 day') AS d;

generate_series is a favorite for filling gaps in reports, for example listing every day of a month even when some days have no sales. SQLite's date() modifiers handle the arithmetic, and a recursive CTE replaces generate_series:

SQL generate_series as a recursive CTE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Days without orders show 0 instead of disappearing, which is exactly what a chart needs. See Date and Time Functions for SQLite's modifiers.

Upserts, MERGE and transactions

INSERT ... ON CONFLICT (column) DO UPDATE SET ... = EXCLUDED.... and DO NOTHING work the same way in PostgreSQL and SQLite; SQLite copied the syntax. PostgreSQL 15 added the standard MERGE statement for more complex synchronizations, as described in Upserts with ON CONFLICT.

Transactions are where a server database really differs from an embedded one:

  • Many writers at once. PostgreSQL uses MVCC (multi-version concurrency control): readers never block writers and writers never block readers, because each transaction sees a consistent snapshot. SQLite allows only one writer at a time.
  • Default isolation is READ COMMITTED. Each statement sees data committed before it started. You can ask for REPEATABLE READ or SERIALIZABLE. See Isolation Levels and Locking.
  • Transactional DDL. CREATE TABLE, ALTER TABLE and most other schema changes can be rolled back, which makes migrations safer. (SQLite has this too; MySQL doesn't.)
  • VACUUM. Because MVCC keeps old row versions around, PostgreSQL needs vacuuming to reclaim them. The autovacuum daemon does this automatically; you mostly need to know it exists and not to disable it.

Extensions

A big part of PostgreSQL's appeal is its extension system. CREATE EXTENSION adds new types, functions and index methods to a database. Well-known examples include pg_trgm (fuzzy text search and fast ILIKE '%...%'), PostGIS (geographic data), pgvector (vector similarity search for AI embeddings), and pg_stat_statements (which queries take the most time). Managed services usually offer a curated list of supported extensions.

SQLite to PostgreSQL cheat sheet

Task SQLite PostgreSQL
Auto-increment key id INTEGER PRIMARY KEY id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
Boolean INTEGER 0/1 (TRUE/FALSE accepted) boolean
Exact decimals integer cents numeric(12, 2)
Current time datetime('now') now() / CURRENT_TIMESTAMP
Add time date(d, '+1 month') d + interval '1 month'
Truncate to month strftime('%Y-%m-01', d) date_trunc('month', d)
Case-insensitive match LIKE ILIKE
Regex not built in ~, ~*, regexp_replace
String aggregation group_concat(x, ', ') string_agg(x, ', ')
First row per group ROW_NUMBER() subquery DISTINCT ON (or ROW_NUMBER())
Number series recursive CTE generate_series()
Lists JSON arrays + json_each arrays + unnest, ANY
JSON TEXT + ->, ->>, json_* jsonb + ->, ->>, @>, ?, GIN index
Cast CAST(x AS INTEGER) CAST(x AS integer) or x::integer
Describe a table PRAGMA table_info(t) \d t in psql
Show a plan EXPLAIN QUERY PLAN EXPLAIN / EXPLAIN ANALYZE

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In PostgreSQL, what does SELECT 'Apple' LIKE 'app%'; return?

    PostgreSQL's LIKE is case-sensitive, so 'Apple' doesn't match 'app%'. Use ILIKE for case-insensitive matching. SQLite's LIKE would return true here.
  2. 2. Which is the recommended way to declare an auto-incrementing primary key in modern PostgreSQL?

    Identity columns are standard SQL and the modern PostgreSQL choice. AUTOINCREMENT is SQLite, AUTO_INCREMENT is MySQL and IDENTITY(1,1) is SQL Server.
  3. 3. What does SELECT DISTINCT ON (customer_id) customer_id, ordered_at FROM orders ORDER BY customer_id, ordered_at DESC; return in PostgreSQL?

    DISTINCT ON keeps the first row of each customer_id group in ORDER BY order. Sorting by ordered_at DESC makes the newest order come first.
  4. 4. You insert the text 'twelve' into an integer column. What happens?

    SQLite's type affinity keeps values it can't convert, while PostgreSQL enforces the column type. SQLite's STRICT tables behave like PostgreSQL here.
  5. 5. Which PostgreSQL feature does a recursive CTE most often replace in SQLite?

    SQLite (in this environment) has no generate_series function, so a recursive CTE generates number and date series. RETURNING and ON CONFLICT exist in both; ILIKE is replaced by LIKE.

Summary

  • PostgreSQL is a client-server database: install it locally, with Docker or as a managed service, and connect with psql or a GUI. Learn the psql meta-commands \l, \c, \dt, \d, \x and \timing.
  • A server holds databases, databases hold schemas (public by default); unquoted identifiers fold to lowercase.
  • Types are strict: use CAST or ::, numeric for money, boolean, timestamptz for instants, and identity columns for keys.
  • RETURNING, ON CONFLICT, CTEs, window functions and FILTER work in both databases.
  • PostgreSQL extras: ILIKE and regex, DISTINCT ON, arrays, jsonb with GIN indexes, intervals, date_trunc, generate_series, MERGE and a rich extension ecosystem.
  • MVCC lets many readers and writers work at once; autovacuum cleans up old row versions.

Next: MySQL and MariaDB Essentials, the other hugely popular open-source database family.