Programster's Blog

Tutorials focusing on Linux, programming, and open-source

PostgreSQL Cheatsheet

cheatsheet PostgreSQL

PostgreSQL - The RETURNING Clause

Basics
Output Formatting
User Management
General Administration
Databases
1. Create Database
2. Drop Database
Create Table
Editing Tables
Indexes / Constraints
Enums / Types
1. Create Type (Enum)
2. Drop Type (Enum)
Inserting Data
Updating Data
Deleting Data
Schemas
Working With JSON
Working With Time
PostGIS
Misc
References

Basics

Installation

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

Login As Master/Root User

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

sudo -u postgres psql

Login 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.

List / Show Databases

\list

Switch Database

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

\connect DBNAME

Show / List Tables

If you are using the psql cli tool:

\dt

This shows all the tables in the schemas in your search path (usually set to just public). The default schema is called public, but there may be others. To show all tables across all schemas, execute the following:

\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. However, if you are using PHP to try and retrieve information about the tables, then one can use the following to get details about the columns:

SELECT * FROM information_schema.columns
WHERE table_name = 'my_table_name';

.... and the following to get information about the foreign keys:

SELECT
  tc.table_schema, 
  tc.constraint_name, 
  tc.table_name, 
  kcu.column_name, 
  ccu.table_schema AS foreign_table_schema,
  ccu.table_name AS foreign_table_name,
  ccu.column_name AS foreign_column_name 
FROM 
  information_schema.table_constraints AS tc 
  JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
  JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
    AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' 
AND tc.table_name='my_table_name';

... and the following to get the table's primary key:

SELECT
  tc.table_schema, 
  tc.constraint_name, 
  tc.table_name, 
  kcu.column_name,
  tc.constraint_type
FROM 
  information_schema.table_constraints AS tc 
  JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
WHERE tc.constraint_type = 'PRIMARY KEY' 
AND tc.table_name='my_table_name';

Exit / Logout

\q

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

Output Formatting

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

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

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"

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.

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.

Change User Password

\password [user]

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

Allow User To Login

If you forgot to add the login part when creating a user, you can retrospectively change a role/user so they can log in.

ALTER ROLE "username" WITH LOGIN;

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 | {}

General Administration

Show Running Queries

SELECT * FROM pg_stat_activity;

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;

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;

Kill A Users Connection / Session

SELECT pg_terminate_backend($PROCESS_ID);

$PROCESS_ID is the pid output in listing connections.

Restore / Import 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.

Dump / Backup Database

pg_dump  \
  --host $HOST \
  --port "5432" \
  --username $USERNAME \
  --no-acl \
  --no-owner \
  --clean \
  --create \
  --file /path/to/dump/file.sql \
  $DB_NAME

--no-owner - Do not output commands to set ownership of objects to match the original database.
--no-acl - Prevent dumping of access privileges (grant/revoke commands).
--clean - Output commands to clean (drop) database objects prior to outputting the commands for creating them.
--create - Begin the output with a command to create the database itself and reconnect to the created database.

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;

Create Table

Basic Create a table Example

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.

Create Table With Combined Unique Key

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

Editing Tables

Rename Table

ALTER TABLE original_table_name 
RENAME TO new_table_name;

Add Column

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

Edit / Alter / 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;

Rename Column

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

Drop Column

ALTER TABLE my_table 
DROP COLUMN "my_column_name";

If you wish to drop multiple columns then you can do this like so:

ALTER TABLE my_table 
DROP COLUMN "my_first_column",
DROP COLUMN "my_second_column";

If the column is used by other views/procedures etc, then you may wish to cascade the removals:

ALTER TABLE my_table 
DROP COLUMN "my_first_column" CASCADE;

Indexes / Constraints

Create Index

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

Add Combined Unique Key Constraint

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

Add Foreign Key Constraint

If you need to update an existing table column, to turn it into a foreign key:

ALTER TABLE my_table
ADD CONSTRAINT my_constraint_name 
FOREIGN KEY (my_table_column) 
REFERENCES my_other_table (id);

Remove / Drop 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";

Enums / Types

Create Type (Enum)

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

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;

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

Schemas

In PostgreSQL, a schema is a namespace that contains named database objects such as tables, views, indexes, data types, functions, stored procedures and operators. A database can contain any number of schemas. A database will contain a default schema called public.

Create Schema

CREATE SCHEMA 'my_schema_name';

Show Current Schema

SELECT current_schema();

Switch / Set Schema

SET SCHEMA 'my_schema_name';

Please note: Setting the schema also sets your search path accordingly. E.g. the following commands will set the search_path to hello, public before then setting the schema to hello. When we output the search_path, one sees that the search path is set to just hello.

bob=# SET search_path TO hello,public;
SET
bob=# SET SCHEMA 'hello';
SET
bob=# SHOW search_path;
 search_path 
-------------
 hello
(1 row)

Rename Schema

ALTER SCHEMA 'my_schema_name'
RENAME TO 'new_schema_name';

If a schema with the new name already exists, then the operation will fail.

Delete / Drop Schema

DROP SCHEMA 'my_schema_name';

Set Search Path

Usually, users just refer to the table name, rather than prefixing with the schema name. E.g. myTableName instead of mySchemaName.myTableName. When referencing without the schema, this is referred to as an unqualified name. When PostgreSQL encounters an unqualified name, the system determines the table by following the search path. The default search path is just set to public, which is also the default schema.

Thus, if you wish to refer to tables in a schema using unqualified names, you can just include it in your search path like so:

SET search_path TO mySchema,public;

Order may be important! The system will use the first table it comes across in the search path. Thus, if you have two tables with the same name in different schemas, the one within the schema that was listed first will be the one that is used. It would be a good idea to ensure you don't have tables with the same name in the schemas listed in your search path.

Setting Search Path Changes Current Schema

If one sets the search path, one is also setting the current schema. The current schema will change to whatever is the first schema listed in the search path. For example, if one was to set the schema to hello, but then set the search path to 'public','hello', one would have changed the current schema back to public as shown below:

bob=# SET SCHEMA 'hello';
SET
bob=# SET search_path TO 'public','hello';
SET
bob=# SELECT current_schema();
 current_schema 
----------------
 public
(1 row)

Show Search Path

SHOW search_path;

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;

Working With Time

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;

PostGIS

The content within this section expects you to have installed the PostGIS extension in your PostgreSQL database, or be running the PostGIS Docker image.

Latitude and Longitude Order

PostGIS uses longitude, latitude ordering for everything, whilst services such as Google maps, and the EPSG:4326 standard use the latitude, longitude ordering.

Spatial Reference Identifier (SRID)

When looking at the documentation for many PostGIS functions, you will see an optional srid parameter. This specifies what coordinate system one is working within and one always needs to make sure that one is operating across matching coordinate systems. E.g. when trying to find if a point is within a shape, both the shape and the point need to be defined using the same coordinate system.

A common SRID that you will often see, and is a default in many of the PostGIS functions is 4326, which represents spatial data using longitude and latitude coordinates on the Earth's surface as defined in the WGS84 standard, which is also used for the Global Positioning System (GPS).

Inserting Geometries

The following snippet shows you how you can insert various geometric types into the database.

CREATE TABLE geometries (name varchar, geom geometry);

INSERT INTO geometries VALUES
  ('Point', 'POINT(0 0)'),
  ('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'),
  ('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
  ('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),
  ('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');

SELECT name, ST_AsText(geom) FROM geometries;

ST_Contains

The ST_Contains function checks whether the first geometry fully contains the second geometry specified. For example, the code below shows how to check if the geometry column of my_table contains the GPS coordinates of Big Ben.

$longitude = -0.1246
$latitude = 51.5007
$srid = 4326

$query = 
    "SELECT *
    FROM my_table
    WHERE ST_Contains(
        my_geometry_column, 
        ST_GeomFromText('POINT({$longitude} {$latitude})', {$srid})
    );";

Notice how there is no comma between the longitude and latitude when specifying the POINT coordinates.

ST_GeomFromGeoJSON

The ST_GeomFromGeoJSON function converts a GeoJSON string into a geometry object. This is incredibly useful for taking online GeoJSON data sources (such as this), and importing them into your database.

$geoJsonString = '{
       "type": "Polygon",
       "coordinates": [
            [ 
                [100.0, 0.0], 
                [101.0, 0.0], 
                [101.0, 1.0], 
                [100.0, 1.0], 
                [100.0, 0.0] 
            ]
        ]
    }';

$escapedGeoJsonString = pg_escape_literal($conn, $geoJsonString);

$query = "INSERT INTO my_table (name, geometry_column) VALUES (
    'myShape',
     (SELECT ST_GeomFromGeoJSON({$escapedGeoJsonString}))
);";

This gracefully handles all kinds of shapes as defined in the GeoJSON, not just the Polygon type in the example. E.g. this includes the MultiPolygon etc.

ST_Within

ST_Within is the inverse of contains, in that it checks if the first parameter is within the second, instead of checking if the second parameter contains the second. (order of the parameters).

Below is an example where I dynamically check if a table points latitude and logitude are within the provided GeoJSON polygon (used from a Leaflet codebase).

ST_Within(
    ST_SetSRID(ST_MakePoint(table_longitude_column, table_latitude_column), 4326),
    ST_GeomFromGeoJSON('{.....}')
);

Misc

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.

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

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

References

Last updated: 25th May 2022
First published: 16th August 2018