PostgreSQL Reference

Contents

• macOS
  • Install Location
• Debian
  • Install Location / Data Directory / Data Area
• Tools
  • createdb
  • initdb
  • pg_config
  • psql
    • Meta-Commands
• Environment Variables
• Commands
• Resources
• Frequently Asked Questions (FAQs)
  • Why does INSERT return lines INSERT 0 1?
  • Why is it called “Postgres”? Where did the name “Postgres” come from? Where did the name “PostgreSQL” originate?
• Database Management System (DBMS) Terminology

macOS

Install Location

/Library/PostgreSQL/16/

Debian

Install Location / Data Directory / Data Area

/var/lib/postgresql/

Tools

From postgresql-common:

• pg_conftool
• pg_createcluster
• pg_ctlcluster
• pg_dropcluster
• pg_lsclusters
• pg_renamecluster
• pg_upgradecluster
• pg_virtualenv

createdb

Creates a new PostgreSQL database. The database user who executes this command becomes the owner of the new database, unless -O/--owner is specified.

createdb [connection-option...] [option...] [dbname [description]]

createdb --username postgres --echo mydb
Password:
SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE mydb;

initdb

Creates a new PostgreSQL database cluster. Use this to create the directories where cluster data will live, etc.

From the initdb(1) man page:

initdb must be run as the user that will own the server process, because the server needs to have access to the files and directories that initdb creates. Since the server cannot be run as root, you must not run initdb as root either. (It will in fact refuse to do so.)

pg_config

pg_config provides information about the installed version of PostgreSQL.

pg_config [OPTION]...

With no arguments, all known items are shown.

psql

PostgreSQL interactive terminal. Reads from ~/.psqlrc on startup, if it exists.

Connect to the local cluster as user postgres:

psql --username postgres

Connect to the local cluster as user postgres, connecting to database mydb:

psql --username postgres --dbname mydb

Meta-Commands

List tables in mydb:

mydb=# \dt
          List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | cities  | table | postgres
 public | weather | table | postgres
(2 rows)

List tables in mydb with additional detail:

mydb=# \dt+
                                      List of relations
 Schema |  Name   | Type  |  Owner   | Persistence | Access method |    Size    | Description
--------+---------+-------+----------+-------------+---------------+------------+-------------
 public | cities  | table | postgres | permanent   | heap          | 8192 bytes |
 public | weather | table | postgres | permanent   | heap          | 8192 bytes |
(2 rows)

Environment Variables

Commands

Resources

• Official Postgres Wiki
• Official Postgres Frequently Asked Questions (FAQ)
• Don’t Do This A short list of common mistakes.
• THE DESIGN OF POSTGRES (PDF) by Michael Stonebraker and Lawrence A. Rowe
• The POSTGRES Data Model (PDF) by Lawrence A. Rowe and Michael Stonebraker
• THE DESIGN OF THE POSTGRES STORAGE SYSTEM by Michael Stonebraker

Frequently Asked Questions (FAQs)

Why does INSERT return lines INSERT 0 1?

This is a command tag of the form:

INSERT oid count

Where oid refers to the deprecated object identifier and count refers to the number of rows inserted. See more detail in the INSERT documentation.

Why is it called “Postgres”? Where did the name “Postgres” come from? Where did the name “PostgreSQL” originate?

Sequence:

• Ingres: INteractive Graphics REtrieval System.
• Postgres, or POST inGRES
• Postgres95, which was the cleaned up ANSI C version released to the web
• PostgreSQL, reflecting the connection / support of SQL

Database Management System (DBMS) Terminology

atomic

either happens completely or not at all

catalog

database; used in the SQL standard

cluster

the collection of databases managed by a single PostgreSQL server instance

dump

generate a file with SQL commands that, when fed back into the server, will recreate the database in the same state as it was at the time of the dump

durability

a guarantee of the recording of committed transactions even if the server crashes or loses power

function, window function

performs a calculation across a set of table rows that are somehow related to the current row

join

Queries that access multiple tables (or multiple instances of the same table) at one time

partition

splitting a single table into smaller tables

qualify

adding more specificity to a field or query; e.g. using SELECT tbl1.foo instead of SELECT foo; or adding a clause to a query (like WHERE) that restricts the results

query

how to retrieve data from a table

the process of retrieving, or the command to retrieve, data from a database

relation

essentially a mathematical term for a table

table

a named collection of rows

table expression

an expression that computes a table

transaction

bundles multiple steps into a single, all-or-nothing operation

transaction block

a group of statements surrounded by BEGIN and COMMIT

update

modifying data that is already present in the database

xact

short for “transaction”