Programmer’s Reference
Learn PostgreSQL queries, psql, transactions, and logical backups.
PostgreSQL SQL and psql commands are separate from terminal commands. Use a disposable demo database and a provisioned demo user. Query examples assume a demo sites table (id, name, enabled) and checks table (id, site_id, result); schema-change examples use separate demo_sites fixtures.
Select a term for its definition, syntax, example, and common mistakes.
Filter individual rows before grouping.
SELECT id, name FROM sites WHERE enabled = TRUE;Practical useUse this construct in your own practice project.
Common mistakesUse IS NULL for null checks, not = NULL.
Source: www.postgresql.org documentation
Combine or negate conditions.
SELECT id, name FROM sites
WHERE enabled = TRUE AND (name = 'Demo' OR name = 'Example');Practical useUse this construct in your own practice project.
Common mistakesAND binds more tightly than OR; parentheses make intent clear.
Source: www.postgresql.org documentation
Sort the result by one or more expressions.
SELECT id, name FROM sites ORDER BY name ASC, id DESC;Practical useUse this construct in your own practice project.
Common mistakesWithout ORDER BY, returned row order is not guaranteed.
Source: www.postgresql.org documentation
Limit rows and skip an initial number of rows.
SELECT id, name FROM sites ORDER BY id LIMIT 10 OFFSET 20;Practical useUse this construct in your own practice project.
Common mistakesUse a deterministic order; large offsets can be expensive.
Source: www.postgresql.org documentation
Remove duplicate result rows.
SELECT DISTINCT result FROM checks;Practical useUse this construct in your own practice project.
Common mistakesDISTINCT applies to the selected row, not only the first column.
Source: www.postgresql.org documentation
Represent and test missing or unknown values.
SELECT id, name FROM sites WHERE name IS NOT NULL;Practical useUse this construct in your own practice project.
Common mistakesNULL comparisons use three-valued logic; = NULL does not match nulls.
Source: www.postgresql.org documentation
Return a value selected by conditions.
SELECT name, CASE WHEN enabled = TRUE THEN 'Ready' ELSE 'Disabled' END AS state
FROM sites;Practical useUse this construct in your own practice project.
Common mistakesCASE is an expression, not a procedural goto or if statement.
Source: www.postgresql.org documentation
Group rows and filter groups using aggregate conditions.
SELECT result, COUNT(*) AS total
FROM checks GROUP BY result HAVING COUNT(*) > 1;Practical useUse this construct in your own practice project.
Common mistakesWHERE filters rows before grouping; HAVING filters groups afterward.
Source: www.postgresql.org documentation
Match text using percent for any sequence and underscore for one character.
SELECT id, name FROM sites WHERE name LIKE 'Demo%';Practical useUse this construct in your own practice project.
Common mistakesCase sensitivity depends on dialect and collation; parameterize external patterns.
Source: www.postgresql.org documentation
Open a local demo database using the PostgreSQL interactive client.
psql -U demo_user -d demo_dbPractical useConnect to an isolated application development database.
Common mistakesLocal socket authentication may use peer authentication rather than a password. Do not change server authentication rules just to run an example.
Source: www.postgresql.org documentation
Inspect the current connection, list tables, describe a table, and quit. Backslash commands belong in psql, not Bash.
\conninfo
\dt
\d sites
\qPractical useConfirm the database before inspecting demo inventory.
Common mistakesDo not add SQL semicolons to psql meta-commands. \dt lists visible tables according to the search path.
Source: www.postgresql.org documentation
Filter a native boolean column, sort results, and limit the output.
SELECT id, name
FROM sites
WHERE enabled IS TRUE
ORDER BY name, id
LIMIT 10;Practical useList enabled demo records.
Common mistakesUse IS NULL for null tests. PostgreSQL booleans differ from MySQL TINYINT aliases.
Source: www.postgresql.org documentation
Define an automatically generated identifier and required fields.
CREATE TABLE demo_sites (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
enabled BOOLEAN NOT NULL DEFAULT TRUE
);Practical useDefine a portable-looking inventory with PostgreSQL-specific identity syntax.
Common mistakesAUTO_INCREMENT is not PostgreSQL syntax. Quote identifiers only when necessary; unquoted names fold to lowercase.
Source: www.postgresql.org documentation
Show a query plan without executing the SELECT, then define an index on a demo foreign-key column.
⚠ Caution: Index creation changes the schema and may block writes.
EXPLAIN SELECT * FROM checks WHERE site_id = 7;
CREATE INDEX demo_checks_site_id_idx ON checks (site_id);Practical useEvaluate a slow inventory lookup on staging.
Common mistakesEXPLAIN ANALYZE runs the statement. A normal CREATE INDEX can block writes; plan live maintenance separately.
Source: www.postgresql.org documentation (1) · www.postgresql.org documentation (2)
Insert a record and return its generated ID in one statement.
⚠ Caution: Adds a database row.
INSERT INTO demo_sites (name)
VALUES ('Demo')
RETURNING id;Practical useCapture a new demo site ID.
Common mistakesDo not assume the ID sequence is gapless. Bind external values through the application’s database client.
Source: www.postgresql.org documentation
Group a change in a transaction and discard it at the end of this example.
⚠ Caution: Updates rows temporarily and can block concurrent work until rollback.
BEGIN;
SELECT id, name FROM demo_sites WHERE id = 7;
UPDATE demo_sites SET enabled = FALSE WHERE id = 7;
ROLLBACK;Practical usePractice a project change without keeping it.
Common mistakesTransactions keep locks until completion. After a statement error, roll back before continuing.
Source: www.postgresql.org documentation
Delete a selected record, show the affected identifier, then roll back the practice transaction.
⚠ Caution: Deletes data if committed; locks persist until transaction completion.
BEGIN;
DELETE FROM demo_sites WHERE id = 7 RETURNING id;
ROLLBACK;Practical usePractice deleting a demo inventory item.
Common mistakesNo WHERE means every row. A future COMMIT makes the deletion permanent; back up and preview first.
Source: www.postgresql.org documentation
Include sites with no checks and count matching check IDs.
SELECT s.id, s.name, COUNT(c.id) AS checks
FROM sites AS s
LEFT JOIN checks AS c ON c.site_id = s.id
GROUP BY s.id, s.name
ORDER BY s.name;Practical useReport demo sites that have no check history.
Common mistakesCOUNT(*) would count the preserved outer row even when there is no check. A WHERE filter on the right table can remove unmatched rows.
Source: www.postgresql.org documentation
Make a custom-format dump and restore into a separate existing empty demo database.
⚠ Caution: Dump can replace the output file; restore creates objects and writes data in the target database.
pg_dump -U demo_user -d demo_db -Fc -f demo-db.dump
pg_restore -U demo_user -d demo_restore --no-owner demo-db.dumpPractical useTest recovery of a disposable staging database.
Common mistakesUse compatible tool/server versions. Confirm the restore target; do not add --clean to a live database. Role and global-object backups are separate.
Source: www.postgresql.org documentation (1) · www.postgresql.org documentation (2)