PostgreSQL Cheatsheet

Related Posts

• PostgreSQL - The RETURNING Clause

Installing

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

Quotation Marks

Double quotes (") are used to denote column names, and single quotes (') are also only used to denote values. When working with UUIDs, its best to always wrap in single quotes.

UPDATE my_table 
SET "my_column" = 'f68b8ef1-ed46-42bd-9619-4ae37dae3eb3' 
WHERE "uuid"='97c681fc-d217-4bcb-970d-e54793d8fd94';

You dont always have to use quotes, but you will need to use them if you have a column name that clashes with a reserved word, such as when.

User Management

I use the word "user" to save confusion. Technically, PostgreSQL has no concept of "users", only "roles". More info.

Create User

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

Grant User Read-Only Access to Database

GRANT pg_read_all_data ON database $DATABASE_NAME TO $ROLE_OR_USER;

pg_read_all_data came in with PostgreSQL 14, so if you are using an older version of PostgreSQL, this will not work.

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

List Users / Show Users

\du

Example output:

                                    List of roles
 Role name  |                         Attributes                         | Member of 
------------+------------------------------------------------------------+-----------
 myusername |                                                            | {}
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Administration

List User Connections

If you want to see who has an open connection to the database, run the following query:

select 
    pid, 
    usename, 
    datname as database_name, 
    client_addr as client_address, 
    application_name,
    backend_start,
    state,
    state_change
from pg_stat_activity;

Kill A Users Connection / Session

SELECT pg_terminate_backend($PROCESS_ID);

$PROCESS_ID is the pid output in listing connections.

Databases

Create A Database

CREATE DATABASE my_database_name
ENCODING 'UTF8' 
LC_COLLATE = 'en_GB.UTF-8'
LC_CTYPE = 'en_GB.UTF-8'
TEMPLATE template0;

If you get a locale error, then run the following commands from your BASH shell:

sudo apt-get install language-pack-en
sudo locale-gen en_GB.UTF-8
sudo update-locale 
sudo service postgresql restart

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

Columns that are foreign keys are not automatically indexed. PostgreSQL only requires the column that is referenced is an index. It is probably a good idea to make your foreign key columns indexes, because otherwise it can slow down operations like CASCADE delete, as the database will have to scan the whole table to find the row to delete.

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

Add Column

ALTER TABLE my_table 
ADD COLUMN "new_column" 
varchar NOT NULL;

Edit/Modify Column

In the MySQL world, one would define everything about the column in one statement. In PostgreSQL one uses a step for each part. E.g. one wanted to change the type, set to not being null, and remove the default value, one would do:

ALTER TABLE my_table_name 
ALTER COLUMN my_column_name TYPE varchar(255),
ALTER COLUMN my_column_name SET NOT NULL,
ALTER COLUMN my_column_name DROP DEFAULT;

Drop/Remove Type (Enum)

If you want to remove a type (such as an Enum you created), then you would do it like so:

DROP TYPE my_type;

Rename Column

ALTER TABLE my_table 
RENAME COLUMN "original_name" 
TO "new_column_name";

Make Column(s) Unique

ALTER TABLE my_table
ADD CONSTRAINT constraint_name UNIQUE (column1, column2);

Removing Constraint

If you want to remove a constraint, such as an index or a foreign key, then you would do it like so:

ALTER TABLE my_table DROP CONSTRAINT "my_constraint_name";

Working With JSON

If you use the JSON or JSONB data types 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;

As a rule of thumb, use jsonb instead of json data type wherever possible. The json data type simply checks the text is valid json and stores it in plain text, rather than actually processing it. On the other hand, jsonb gets compressed to binary, and allows things like more advanced indexing. Info source 2.

PostgreSQL 14 added support for subscripting syntax, so you can now do:

SELECT server_dump['REMOTE_ADDR'] FROM api_requests;

... or for an example that goes two layers down:

UPDATE shirts
SET details['attributes']['color'] = '"neon blue"'
WHERE id = 123;

Misc

Show Running Queries

SELECT * FROM pg_stat_activity;

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

Dump Database

pg_dump  \
  --host $HOST \
  --port "5432" \
  --username $USERNAME \
  --file /path/to/dump/file.sql \
  $DB_NAME

Restore Database

To restore a dump taken using the way we created a pg_dump, you would just run the SQL file like so:

psql \
  --host $HOST \
  --port "5432" \
  --username $USERNAME \
  -d $DATABASE \
  -f $FILEPATH

There is a pg_restore command, but it wouldn't work on a plain SQL dump like we created.

Show Settings / Variables

If you want to see all the settings for postgresql, then run:

SHOW ALL;

If you want to see a specific setting then specify it instead of all, like:

SHOW max_connections;

No Wrap Output

If you have lots of columns in your tables and you don't want word wrapping in your output, you can run the following command inside PostgreSQL to use less as your pager, and tell it not to wrap lines.

\setenv PAGER 'less -S'

If you are using a PostgreSQL docker container it won't have less installed, so you would need to install less, and then set the environment variable like so:

PAGER="/usr/bin/less -S"

Getting Table Stats

The command below is a good way to get some stats about your tables. It can be quite useful when you want to see how "active" your tables are, or see how many "dead tuples" are taking up space and need deleting by the vacuum process. (Dead tuples are rows that have been deleted but have not been removed from disk yet by the vacuum process).

SELECT 
    relname as table_name,
    n_tup_ins as "inserts",
    n_tup_upd as "updates",
    n_tup_del as "deletes",
    n_live_tup as "live_tuples",
    n_dead_tup as "dead_tuples",
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze,
    vacuum_count,
    autovacuum_count,
    analyze_count,
    autoanalyze_count
FROM pg_stat_user_tables
ORDER BY table_name;

Converting Unix Timestamp Integer to Timestamp

Use the to_timestamp function.

SELECT to_timestamp(my_unix_timestamp_column) as my_timestamp FROM my_table;

To convert that to a date (such as to group by a day of the year) wrap it again like so:

SELECT DATE(to_timestamp(my_unix_timestamp_column)) as my_date FROM my_table;

Output Results To File

You can use \o as an output buffer that will output the results to a file or pipe. E.g.

db=>\o out.txt
db=>\dt
db=>\o

The second \o turns off the output.

You could put as many queries in there as you like.

Flip Rows And Columns

If you are trying to fetch just one row and the table has a lot of columns, you may find it easier if you flip the output so that each column becomes a newline. To do this use:

\x

References

• Crunchydata - Postgres 14: It's The Little Things
• postgresqltutorial.com - PostgreSQL List Users
• Stack Overflow - psql - save results of command to a file
• 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
• Stack Overflow - input file appears to be a text format dump. Please use psql
• Ask Ubuntu - Set PostgreSQL pager to less?