Programster's Blog

Tutorials focusing on Linux, programming, and open-source

PostgreSQL Cheatsheet

PostgreSQL

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

\q

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

GRANT ALL ON database $DATABASE_NAME TO $ROLE_OR_USER;

Check Role Privileges

\du

Change User Password

\password [user]

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

Databases

Create A Database

CREATE DATABASE my_database_name
ENCODING 'UTF8' 
LC_COLLATE = 'en_US.UTF-8'
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;

Tables

Create a table

CREATE TABLE broker (
    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

CREATE TABLE cities (
    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:

\dt

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

SELECT tablename FROM pg_catalog.pg_tables

Show Databases

\list

Inserting Data

CREATE TABLE cities (
    city     varchar(80) primary key,
    location point
);

INSERT INTO cities (city, location)
VALUES 
    ('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

DELETE FROM cities 
WHERE city='Houston';

Misc

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)