Programster's Blog

Tutorials focusing on Linux, programming, and open-source

PostgreSQL Cheatsheet

If you need to connect to a remote server, Ubuntu 16.04 users can install the PostgreSQL client with:

sudo apt install postgresql-client-common postgresql-client-9.5

Logging In

Enter the CLI as Master/Root User

postgres is the master user, (and comes with their own database by default).

sudo -u postgres psql

Enter the CLI Utility As Normal User

psql \
  --user $USER \
  --password \
  -d $DATABASE \
  --host $HOST
  • If the user has their own database and you wish to connect to that, you can skip -D $DATABASE as you would automatically connect to that.
  • if you wish to connect to localhost, you can skip --host
  • If the user doesnt need a password, you can skip --password
  • If your current BASH user is the same name as the user in psql you wish to connect as, you can skip --user.

If you are having difficulty connecting to the local database (localhost), you might not have configured your PostgreSQL database for local connections like you probably want to.

CLI Utility

Exiting the CLI Utility


It is easier just to use the keyboard shortcut Ctrl + D.

Switching Database

Technically this command does not "switch" database, you are just closing one connection and opening another.

\connect DBNAME

User Management

CREATE USER programster WITH PASSWORD 'thisismypassword';

By default, when this user logs in, postgresql will try to connect them to the programster database, and they will have the ability to see and create tables in the other databases.

Create User With New Role

CREATE ROLE $MY_USER with password '$USER_PASSWORD' login;

Remove login if you don't want that to be loginable.

Grant User Access To Database


Check Role Privileges


Change User Password

\password [user]

This is most easily done by logging in as the admin user first with sudo -u postgres psql postgres


Create A Database

CREATE DATABASE my_database_name
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE template0;

If you don't need utf8 and are fine with LATIN encoding, then you can just use:

CREATE DATABASE my_database_name;

Delete/Drop Database

DROP DATABASE my_database_name;


Create a table

    id serial NOT NULL,
    name varchar(255) NOT NULL,
    redirect_url text NOT NULL,
    secret varchar(30) NOT NULL,
    modified_timestamp timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)

There is no AUTO_INCREMENT but a special serial and big_serial type. There is no unsigned type

Creating Tables With Foreign Keys

    city     varchar(80) primary key,
    location point

CREATE TABLE weather (
    city      varchar(80) references cities(city),
    temp_lo   int

By default, foreign keys will be set to use RESTRICT for both update and delete actions. You can manually specify what you want like so:

CREATE TABLE weather (
    city      varchar(80) references cities(city) ON UPDATE CASCADE ON DELETE RESTRICT,
    temp_lo   int

Show Tables

If you are using the psql cli tool:


If you are connecting using something like PHP, then you need to do:

SELECT tablename FROM pg_catalog.pg_tables

Show Databases


Inserting Data

    city     varchar(80) primary key,
    location point

INSERT INTO cities (city, location)
    ('Houston', '29.7604, -95.3698'),
    ('Dallas', '32.7767, -96.7970')

Pay close attention to the use of (or lack of ) various quotation marks.

Updating Data

UPDATE cities 
SET city='Washington' 
WHERE city='Houston';

Deleting Data

WHERE city='Houston';


Ouput of Nulls

Unlike MySQL which will show "null" as a value when showing data, like so:

mysql> select * from cities;
| city    | location |
| Houston |     NULL |
1 row in set (0.00 sec)

...PostgreSQL will just show emptiness like so:

postgres=# select * from cities;
    city    |      location      
 Dallas     | (32.7767,-96.797)
 Washington | (29.7604,-95.3698)
 London     | 
(3 rows)