Learn
PostgreSQL
Tutorials and error guides with examples.
Quick Reference
Guides
- PostgreSQL arrays: syntax, operators, and indexingPostgreSQL arrays are 1-indexed and out-of-bounds access returns NULL rather than an error. Here is the syntax and the traps.
- PostgreSQL CTE: WITH queries explainedCTEs name a subquery so you can read it. Since PostgreSQL 12 they are inlined by default, so they are no longer an optimization fence.
- PostgreSQL timestamp vs timestamptzUse timestamptz. It is the same 8 bytes as timestamp and it does not throw away the offset.
- PostgreSQL ON CONFLICT: upsert syntax and examplesUpsert in PostgreSQL with INSERT ... ON CONFLICT DO UPDATE. Here is the syntax, the EXCLUDED table, and the traps.
- PostgreSQL SHOW TABLES equivalentThere is no SHOW TABLES in PostgreSQL. Use \dt in psql, or query information_schema.tables.
- PostgreSQL connect to database (and the USE equivalent)Switch databases with \c, connect with psql -d or a connection URI. There is no USE command in Postgres.
- PostgreSQL default portPostgreSQL's default port is 5432. Here's how to check it, change it, and connect.
- PostgreSQL default passwordThere is no default PostgreSQL password. Here's how to set one, reset it, and configure authentication.
- PostgreSQL COALESCECOALESCE returns the first non-NULL value from a list. Use it to substitute defaults for NULL columns.
- PostgreSQL CREATE USERCreate PostgreSQL users with the right privileges and access controls.
- PostgreSQL list databases (and the SHOW DATABASES equivalent)List all PostgreSQL databases with \l in psql or query pg_database directly. There is no SHOW DATABASES in Postgres.
- PostgreSQL data typesPostgreSQL's data types and when to use each one.
- PostgreSQL UPDATEUpdate single rows, multiple rows, and use RETURNING to get the changed data back.
- PostgreSQL CREATE DATABASECreate PostgreSQL databases from the command line, psql, or SQL with the right options for your setup.
- PostgreSQL INSERTInsert single rows, multiple rows, and handle conflicts with PostgreSQL's INSERT statement.
- PostgreSQL List TablesAll the ways to list tables in a PostgreSQL database.
- PostgreSQL CREATE TABLEEverything you need to know about creating tables in PostgreSQL.
- PostgreSQL VACUUM: Maintenance and OptimizationKeep your PostgreSQL database healthy and fast.
- PostgreSQL List UsersView all users, roles, and permissions in PostgreSQL.
- PostgreSQL CREATE INDEXSpeed up your queries by teaching PostgreSQL where to look.
Errors
- duplicate key value violates unique constraint
- violates foreign key constraint
- null value in column violates not-null constraint
- new row violates check constraint
- relation does not exist
- column does not exist
- syntax error at or near
- password authentication failed
- database does not exist
- could not connect to server