Frequently Used PostgreSQL Commands

Very, very high-level quick-start guide to get started using PostgreSQL.

Installation

  • Debian: sudo apt install postgresql
  • Mac: brew install postgresql

Create Databases and Users

Create user:

  • sudo su postgres
  • create role Alice LOGIN CREATEDB

Create database

  • do it as Alice
  • createdb somedatabase
  • psql somedatabase

Password and Access Control

login a database as Alice

  • \password

Edit /etc/postgresql/9.5/main/pg_hba.conf for access control. The file is pretty self-explainatory.

Show Information

  • \d \dt list all tables
  • \d+ table_name show detailed table fields
                                 Table "public.pfx2as_files"
   Column   |            Type             | Modifiers | Storage  | Stats target | Description
------------+-----------------------------+-----------+----------+--------------+-------------
 time       | timestamp without time zone | not null  | plain    |              |
 swift_path | character varying(50)       | not null  | extended |              |
Indexes:
    "pfx2as_files_pkey" PRIMARY KEY, btree ("time")