Databases/PostgreSQL

From Wikiversity
< Databases(Redirected from PostgreSQL)
Jump to navigation Jump to search

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 | edit source]

MacOS[edit | edit source]

  • brew install postgres

Linux, BSD variants[edit | edit source]

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 | edit source]

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 | edit source]

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

See also[edit | edit source]

  1. http://postgresguide.com/utilities/psql.html
  2. http://postgresguide.com/utilities/psql.html
  3. https://stackoverflow.com/questions/769683/show-tables-in-postgresql
  4. https://dba.stackexchange.com/questions/40045/how-do-i-list-all-schemas-in-postgresql
  5. https://www.postgresql.org/docs/current/static/app-pgdump.html