View on GitHub

Introduction to PostgreSQL



Basic Commands

Connect to a database from the command-line use

$ psql --user $USER --database $YOUR_DB

In the psql shell, you have access to a series of special commands.

\c dbname
Connect to given database.

\d name Describe table, view, sequence, or index.

List schemas available in current database.
\dt schema.
List relations in schema.
\h sql_command
Get help for given SQL command.
List availables databases.
Get help for available psql commands (\ commands)
\! shell_command
Execute the given command in the operating system’s shell.


In order to avoid typing too many parameters when launching psql from the command line, you can configure default values. By all means, I strongly recommend not to store your password without second thoughts. For storing defaults, there are three options. The first is the use of environment variables.

export PGHOST=localhost
export PGPORT=5433
export PGUSER=...
export PGPASSWORD=...
export PGDATABASE=...

Alternatively, you could use a .pgpass file using the following format.


Finally, you can use the .pg_service.conf file which uses an ini like format.



Self Joins

Reasons for joining a table onto itself include


The USING keyword allows to join two tables on a set of columns that share the exact same names. It is used as a shorthand notation for the slightly more verbose and flexible ON.

The syntax is

-- ...
JOIN other_table USING (col1)

where col1 is the column on which you want to join.


The ‘NATURAL keyword is essentially a shorthand for USING`. It joins two subsequent tables together on all the columns which have the same names in both tables. Hence

The syntax is

-- ...
NATURAL JOIN other_table