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

\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 If you wish to wrap your table or column names, you would need to use double quotes (") instead of the ` character.

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 NO ACTION for both update and delete actions (resulting in the same behavioiur as RESTRICT). 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
);

Creating Tables With Enums

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE person (
    name text,
    current_mood mood
);

Creating Tables With Unique Column

CREATE TABLE users (
    name varchar(255) NOT NULL,
    username varchar(255) UNIQUE NOT NULL
);

This will automatically create an index on the unique column.

Creating Combined Unique Column

CREATE TABLE myTable (
    id uuid PRIMARY KEY,
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    UNIQUE (col1, col2)
)

Creating Tables With Indexes

You cannot specify an INDEX in the create table definition. However, if you specify a column as unique, then an index is automatically created for that column. If you have a column that is not unique but also needs to be indexed for quick selections, then you can add an index after the table has been created with:

CREATE INDEX on tableName ("column_name");

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

Describe Table

If you want to show a table, use:

\d+ tablename

Example output:

                                            Table "public.users"
  Column  |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
----------+------------------------+-----------+----------+---------+----------+--------------+-------------
 name     | character varying(255) |           | not null |         | extended |              | 
 username | character varying(255) |           | not null |         | extended |              | 
Indexes:
    "users_username_key" UNIQUE CONSTRAINT, btree (username)

... or you can use the following to get just the basic information:

\d tablename

                        Table "public.users"
  Column  |          Type          | Collation | Nullable | Default 
----------+------------------------+-----------+----------+---------
 name     | character varying(255) |           | not null | 
 username | character varying(255) |           | not null | 
Indexes:
    "users_username_key" UNIQUE CONSTRAINT, btree (username)

Unfortunately, there is no SHOW CREATE TABLE tablename.

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';

Working With JSON

If you use the JSON data type for a column, there are special operations you can do with it. For example, I created a server_dump column that holds the json_encode of the PHP $_SERVER superglobal and can then select the requestor's IP address from this with:

SELECT server_dump->'REMOTE_ADDR' FROM api_requests;

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)

Scrolling Results - Turn Off Paging

If there are a lot of results, PostgreSQL will implement scrolling by default. To scroll down through the results, one presses spacebar. However, if like me, you find this annoying, you can turn off paging with:

\pset pager off

References

• Stack Overflow - In Postgresql, Force unique on combination of two columns
• postgresqltutorial.com - PostgreSQL Foreign Key
• Stack Overflow - Show tables in PostgreSQL
• Why unsigned integer is not available in PostgreSQL?
• How to configure postgresql for the first time?
• Stack Overflow - How do you create a read-only user in PostgreSQL?
• Stack Overflow - Amazon RDS PostgreSQL: how to create user?
• Stack Overflow - Check Postgres access for a user
• PostgreSQL Docs - Enumerated Types
• PostgreSQL: Can you create an index in the CREATE TABLE definition?
• Stack Overflow - How to be able to vertically scroll terminal to see all data from a large table
• PostgreSQLDocs - JSON Functions and Operators