Databases/PostgreSQL

PostgreSQL, often simply Postgres, is an w:object-relational database management system (ORDBMS). PostgreSQL is w:ACID-compliant and transactional. PostgreSQL has updatable [w:[View (SQL)|views]] and w:materialized views, triggers, w:foreign keys; supports functions and stored procedures, and other expandability

Installing PostgreSQL edit

MacOS edit

brew install postgres

Linux, BSD variants edit

The easiest way to install PostgreSQL in these operating systems is to use their respective built-in package managers - for instance, the pkg command in DragonFlyBSD or apt in Debian and its derivatives. Be advised that there will be different variants of PostgreSQL available for install at a given point, specially regarding version numbers and client/server side. You should decide beforehand which version suits your needs for a particular application. Furthermore, you need to make sure that you have sufficient privileges in the system to install software (properly configured sudo/doas access, for example) or your install will fail.

Basic Commands edit

Binaries: psql, pg_dump.

Connect to a PostgreSQL database: psql -h localhost -U postgres -d somedb^[1]
Connect to a PostgreSQL database with a timeout of 5 seconds: env PGCONNECT_TIMEOUT=5 psql -h localhost -U postgres -d somedb^[2]
Show tables: \dt or SELECT * FROM pg_catalog.pg_tables;^[3]
Describe employees table: \d employees
Show/List schemas: select nspname from pg_catalog.pg_namespace;^[4]
Create backup: use pg_dump ^[5] and ~/.pgpass file for automating login.

Releases (yearly) edit

PostgreSQL release a new version approximately every year around October: https://en.wikipedia.org/wiki/PostgreSQL#Release_history

October 2018 PostgreSQL 11.0 https://www.postgresql.org/about/news/1894/, https://www.postgresql.org/docs/release/11.0/
October 2019 PostgreSQL 12.0 https://www.postgresql.org/about/news/1976/, https://www.postgresql.org/docs/release/12.0/