PostgreSQL Commands

Common PostgreSQL commands

This article has basic commands that are very helpful when setting up a PostgreSQL database. The first command switches you the PostgreSQL user:

$ubuntu@home: sudo -u postgres psql postgres

List Databases:

$ubuntu@home: postgres=# \l

List roles

$ubuntu@home: postgres=# \du

Create a role

$ubuntu@home: postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD 'password1' CREATEDB;

Create role with multiple privileges

$ubuntu@home: postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD
$ubuntu@home: postgres=# 'password1' CREATEDB CREATEROLE REPLICATION SUPERUSER;

Alter a role

$ubuntu@home: postgres=# ALTER ROLE demorole1 CREATEROLE CREATEDB REPLICATION SUPERUSER;

Drop a role

$ubuntu@home: postgres=# DROP ROLE demorole1;

Create a database

$ubuntu@home: postgres=# CREATE DATABASE demodb1 WITH OWNER demorole1 ENCODING 'UTF8';

Grant privileges to new user

$ubuntu@home: postgres=# GRANT ALL PRIVILEGES ON DATABASE demodb1 TO demorole1;

Drop a database

$ubuntu@home: postgres=# DROP DATABASE demodb1;

Connect to a database

$ubuntu@home: postgres=# \c <databasename>

List tables in connected database

$ubuntu@home: postgres=# \dt

List columns on table

$ubuntu@home: postgres=# \d <tablename>

Backup a database

$ubuntu@home: $ pg_dump <databasename> ><outfile>