Introduction to PostgreSQL
Connect to a database from the command-line use
$ psql --user $USER --database $YOUR_DB
psql shell, you have access to a series of special commands.
- Connect to given database.
Describe table, view, sequence, or index.
- List schemas available in current database.
- List relations in schema.
- Get help for given SQL command.
- List availables databases.
- Get help for available psql commands (
- 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.
[example] host=localhost port=5432 dbname=... user=... password=...
Reasons for joining a table onto itself include
- a table includes a hierarchy (eg. employees reporting to other employees)
- finding similarities in a column (eg. employees with the same birthday)
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
The syntax is
-- ... JOIN other_table USING (col1)
col1 is the column on which you want to join.
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
- order matters
- be careful if multiple column names are identical
The syntax is
-- ... NATURAL JOIN other_table