2023-03-09

PostgreSQL Cheat Sheet

Introduction

This article introduces the PostgreSQL cheat sheet that consists of frequently used commands and queries that can be used as a quick reference when operating PostgreSQL.

Commands Used on Terminal

I detail the commonly used commands in a terminal (or command-line interface) for interacting with a PostgreSQL database.

Starting the Server

To start the PostgreSQL server, use the pg_ctl start command followed by the -D option to specify the directory where the data is stored.

bash

$ pg_ctl start -D /usr/local/var/postgres

Stopping the Server

To stop the PostgreSQL server, use the pg_ctl stop command, again followed by the -D option to specify the directory of the data to be stopped.

bash

$ pg_ctl stop -D /usr/local/var/postgres

Verifying if the Server is Running

To verify if the PostgreSQL server is running, you can use the ps aux | grep postgres command which lists all the current processes related to PostgreSQL.

bash

$ ps aux | grep postgres

Database Connection

To connect to a PostgreSQL database, use the psql command with the -d (database name), -U (username), and -h (hostname) options. If these options are unspecified, they default to the login username and localhost.

bash

$ psql -d database -U user -h host

Displaying List of Databases

To display a list of databases, use the psql -l command:

bash

$ psql -l

Displaying PostgreSQL Version

To check the version of PostgreSQL that you're currently using, run the psql -V command:

bash

$ psql -V

PostgreSQL Commands

I'll focus on the most commonly used commands within PostgreSQL's command-line interface.

Disconnect

If you want to disconnect from the PostgreSQL interface, use the \q or \! commands.

postgres=# \q
postgres=# \!

User

User management is an integral part of database administration. Here, we look at the commands related to user operations.

To display a list of users in PostgreSQL, use the \du command.

postgres=# \du

PostgreSQL manages database access permissions using the concept of roles. To list all roles, you can use the following query.

postgres=# SELECT rolname FROM pg_roles;

Database

Database operations are routine tasks in PostgreSQL. Here, we look at the commands related to these operations.

To display information about the connected database, use the \conninfo command.

postgres=# \conninfo

To list all databases, use the \l command.

postgres=# \l

To connect to a different database, use the \c <database_name> command.

postgres=# \c <database_name>

To show the currently connected database, use the SELECT current_database(); command.

postgres=# SELECT current_database();

To create a new database, use the CREATE DATABASE <database_name>; command.

postgres=# CREATE DATABASE <database_name>;

Table

Table-related operations form the core of managing a PostgreSQL database.

To get the description of a table, which includes the column names and their data types, use the \d <table> command.

postgres=# \d <table>

To get a detailed description of a table, including additional information like storage statistics and comments on columns, use the \d+ <table> command.

postgres=# \d+ <table>

To list all tables from the current schema, use the \dt command.

postgres=# \dt

To list tables from all schemas, use the \dt *.* command.

postgres=# \dt *.*

To list tables for a specific schema, use the \dt <schema>.* command.

postgres=# \dt <schema>.*

To list the access privileges of all tables, use the \dp command.

postgres=# \dp

Schema

A schema is a namespace: it contains named objects (tables, data types, functions, and operators) whose names can duplicate those of other objects existing in other schemas. PostgreSQL uses schemas to help several users share one database without interfering with each other.

To list all schemas in the current database, use the \dn command.

postgres=# \dn

View

A view can be thought of as a virtual table, essentially a stored query, which takes the output of a query and treats it as a table. Thus, a view is an object that is derived from one or more existing base tables.

To list all views in the current database, use the \dv command.

postgres=# \dv

Import/Export CSV

PostgreSQL provides powerful mechanisms to import and export data in various formats like CSV.

To export a table into a CSV file, you can use the \copy command. Here are some examples:

postgres=# \copy table TO '<path>' CSV
postgres=# \copy table(col1,col1) TO '<path>' CSV
postgres=# \copy (SELECT...) TO '<path>' CSV

To import data from a CSV file into a table, you can also use the \copy command. Here are some examples:

postgres=# \copy table FROM '<path>' CSV
postgres=# \copy table(col1,col1) FROM '<path>' CSV

History

Keeping track of previously executed commands can be very useful for repeating tasks or understanding the actions taken in a session. In PostgreSQL, you can use the \s command to display the command-line history.