Programster's Blog

Tutorials focusing on Linux, programming, and open-source

PostgreSQL Cheatsheet

Related Posts

Table of Contents

  1. Basics
    1. Installation
    2. Login As Master/Root User
    3. Login As Normal User
    4. List / Show Databases
    5. Switch Database
    6. Show / List Tables
    7. Describe Table
    8. Show / List Sequences
    9. Exit / Logout
    10. Quotation Marks
  2. Output Formatting
    1. Flip Rows And Columns
    2. Scrolling Results - Turn Off Paging
    3. No Wrap
    4. Output Results To File
    5. Clear Screen
  3. User Management
    1. Create User
    2. Create Role
    3. Change User Password
    4. Allow User To Login
    5. Grant Read/Write Access
    6. Grant Readonly Access
    7. Grant Ability To Create Databases
    8. Grant Access To Public Schema
    9. List Roles / List Users / Check Privileges
    10. Rename User / Role
    11. Mass Change Ownership
    12. Change Database Owner
    13. Change Table Owner
    14. Delete User / Role
  4. General Administration
    1. Show Running Queries
    2. List User Connections
    3. Show Settings / Variables
    4. Kill User Connection
    5. Backup Database
    6. Import / Restore Database
  5. Extensions
    1. List Extensions
    2. Install Extension
  6. Databases
    1. Create Database
    2. Drop Database
  7. Create Table
    1. Basic Create Table Example
    2. Create Table With Foreign Keys
    3. Create Table With Enums
    4. Create Table With Unique Column
    5. Create Table With Combined Unique Key
  8. Editing Tables
    1. Rename Table
    2. Add Column
    3. Edit / Alter / Modify Column
    4. Rename Column
    5. Drop Column
  9. Indexes / Constraints / Checks
    1. Create Index
    2. Add Combined Unique Key Constraint
    3. Add Foreign Key Constraint
    4. Add Check Constraint
    5. Create Table With Check Constraint
    6. Remove / Drop Constraint
  10. Enums / Types
    1. List Types
    2. Describe Type / Enum
    3. Create Type (Enum)
    4. Drop Type (Enum)
  11. Selecting Data
    1. Select Distinct Values
    2. Select Distinct Combinations
    3. Select First Row Of Distinct Value
  12. Inserting Data
  13. Updating Data
  14. Deleting Data
  15. Schemas
    1. Create Schema
    2. Show Current Schema
    3. Switch / Set Schema
    4. Rename Schema
    5. Delete / Drop Schema
    6. Set Search Path
    7. Setting Search Path Changes Current Schema
    8. Show Search Path
  16. Working With JSON
  17. Working With Time
  18. Convert Unix Timestamp to Timestamp
  19. Convert Unix Timestamp to Date
  20. Convert Timestamp to Unix Timestamp
  21. Selecting Between Dates
  22. Grouping Dates By Month
  23. Comments
    1. Table Comments
    2. Column Comments
    3. SQL Comments
  24. PostGIS
    1. Latitude and Longitude Order
    2. Spatial Reference Identifier (SRID)
    3. Inserting Geometries
  25. Misc
    1. Passwordless Login
    2. Quotation Marks
    3. Output of Nulls
    4. Get Table Stats
  26. 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';

Show / List Sequences

\ds

Alternatively, if you need to use an SQL statement, becuase you need to run this from something like a PHP script, you would run:

SELECT sequence_schema, sequence_name 
FROM information_schema.sequences 
ORDER BY sequence_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.

Clear Screen  

If you want to clear the screen of previous results so that the next query and result will appear from the top of the screen, then do the following:

\! clear

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.

Grant Ability To Create Databases

The command below will give the user named username the ability to create their own databases.

ALTER USER username CREATEDB;

Grant Access To Public Schema

PostgreSQL 15 changes things so that the public schema does not have CREATE privileges granted by default anymore. Thus, if you create a user, and they need to operate on a database in the public schema, then you may need to grant them the ability to work in the public schema. Pick one of the following that is appropriate for your use case:

GRANT USAGE ON SCHEMA public TO my_database_user;

GRANT CREATE ON SCHEMA public TO my_database_user;

GRANT USAGE, CREATE ON SCHEMA public TO my_database_user;

GRANT ALL ON SCHEMA public TO my_database_user;

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

Rename User / Role

ALTER ROLE "old_role_name" RENAME TO "new_role_name";

Change Database Owner

ALTER DATABASE my_database_name OWNER TO "new_owner_name";

Changing the database owner does not implicitly change the ownership of its tables.

Change Table Owner

ALTER TABLE my_table_name OWNER TO new_owner_name;

Mass Change Ownership

If you want to mass change ownership of items from one role to another, then you can do the following:

REASSIGN OWNED BY "original_owner" TO "new_owner";

Delete User / Role

DELETE USER "my_users_name"

This will fail if the user is the owner of anything, and will output all the things that user owns.

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 \
  --password \
  -d $DATABASE \
  -f $FILEPATH

You can add --compress=9 to have pg_dump compress the output at the most-compressed level as if having been run through gzip. The default is effectively 0. 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 \
  --password \
  --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.

Don't use the --create or --clean switches if you wish to import the dump file later to a different database name! E.g. if you are creating a copy of the database, instead of creating a backup of a host for later restoration.

Extensions

List Extensions

CLI Command

If you are using the pgsql CLI, then one can simply run:

\dx

Below is the example output:

                           List of installed extensions
    Name    | Version |   Schema   |                  Description                  
------------+---------+------------+-----------------------------------------------
 btree_gist | 1.6     | public     | support for indexing common datatypes in GiST
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

You do not need the superuser privilege to execute this.

SQL Query Form

However, if you need to run a traditional query (such as if executing PHP code), then you would need the following:

SELECT * FROM pg_extension;

Example output:

  oid  |  extname   | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+------------+----------+--------------+----------------+------------+-----------+--------------
 14703 | plpgsql    |       10 |           11 | f              | 1.0        |           | 
 35190 | btree_gist |       10 |         2200 | t              | 1.6        |           | 
(2 rows)

You do not need the superuser privilege to execute this.

Install Extension

The following command will create the extension if it doesn't already exist:

CREATE EXTENSION IF NOT EXISTS extension_name_here.

You need the superuser privilege to execute this, even if the extension is already installed!

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

Add Check Constraint  

If you need to add a check constraint to an existing table:

ALTER TABLE my_table
ADD CONSTRAINT time_all_or_nothing
CHECK ((time_start IS NOT NULL AND time_end IS NOT NULL) OR (time_start IS NULL AND time_end IS NULL))

Create Table With Check Constraint  

If you want to create a table with a check constraint in the definition, rather than having to add it afterwards, here is an example whereby we have a table of events, and an event can be all day, in which case it does not have a start or end time, or it can be part of a day, in which case it must have both a start and an end time.

CREATE TABLE events (
    id uuid PRIMARY KEY,
    date_start date NOT NULL,
    date_end date NOT NULL,
    time_start time,
    time_end time,
    name varchar(255) NOT NULL,
    CHECK ((time_start IS NOT NULL AND time_end IS NOT NULL) OR (time_start IS NULL AND time_end IS NULL))
);

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

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  

List Types  

If you wish to list out all the user created types / enums (not the tables), then run the following psql command:

\dt

Describe Type / Enum  

If you wish to find out about a specific type, such as find out the possible values for an enum, then do the following:

\dT+ my_custom_type

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;

Selecting Data

Select Distinct Values {#select-distinct-values)

The following query will output only the unique/distinct values for the myColumnName column in a table.

SELECT DISTINCT myColumnName
FROM my_table_name

Select Distinct Combinations

The following query will output only the unique/distinct combination of column1, column2 values in a table.

SELECT DISTINCT column1, column2
FROM my_table_name

Select First Row Of Distinct Value

The query below will order a table by column3, and select the first row for each distinct value of column1. E.g. there may be multiple column2 values for a single value in column1, but it will only show the first of them when ordered by column3.

SELECT 
  DISTINCT ON(column1) column1_alias
  column2
FROM my_table_name
ORDER BY column3

One does not have to have an ORDER BY clause, but you almost certainly want one in order to achieve repeatable results due to how the DISTINCT ON clause will only return the first row for each unique value of column1

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  

Convert Unix Timestamp to Timestamp  

Use the to_timestamp function.

SELECT to_timestamp(my_unix_timestamp_column) as my_timestamp FROM my_table;

Convert Unix Timestamp to Date  

To convert a unix timestamp (epoch) to a date (such as to group by a day of the year):

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

Convert Timestamp to Unix Timestamp (Epoch)  

The following will convert the created_at field from a timestamp with timezone field, to a Unix timestamp.

SELECT 
  EXTRACT(EPOCH FROM created_at)::integer as created_at 
FROM my_table;

You do not have to have the ::integer at the end. This simply converts it from a double precision to an integer.

Selecting Between Dates  

If you have a table full of rows that have dates / timestamps / etc, and you want to select the rows between (and including) two dates/times, then one can just use the BETWEEN keyword like so:

SELECT * FROM my_table 
WHERE my_date_field BETWEEN '2019-01-01' AND '2022-01-01'

Grouping Dates By Month  

The following example will convert the table's my_date_field into the corresponding month using the DATE_TRUNC function. We can then group by that date and order by it. This is particularly useful for stats/reporting.

SELECT 
    DATE_TRUNC('month', my_date_field)::date as month,
    fome_field
FROM my_table
GROUP BY month
ORDER BY month ASC;

Comments

Table Comments

You can add comments to a table for others to read later. E.g.

COMMENT ON TABLE mytable IS 'This table is for...';

Column Comments

You can retrospectively add a comment to a table column like so:

COMMENT ON COLUMN my_table.column_name IS 'This is my comment';

If you want to specify the comment at the point of creating the table, then you could do:

CREATE TABLE something (
    id serial NOT NULL,
    column1 varchar(255) IS 'comment describing column here',
    PRIMARY KEY (id)
);

SQL Comments

You can add a comment to a line using -- like so:

-- comment goes here  creating the programster user.
CREATE USER programster WITH PASSWORD 'thisismypassword';

Multi-line Comments

You can add a multiline comment anywhere in SQL by using /* and */ which signify the start and end of the comment.

/* 
  * multiline comment here
  * creating the programster user.  
  */
CREATE USER programster WITH PASSWORD 'thisismypassword';

... or alternatively:

CREATE TABLE something (
    id serial NOT NULL,
    column1 varchar(255), /* comment here - deliberately not setting NOT NULL for some reason */
    PRIMARY KEY (id)
);

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

Passwordless Login

Sometimes one needs to be able to run commands from a script, and thus one cannot perform the interactive password input. Thus, one needs to be able to "provide" the password in a secure manner (specifying --password=something doesn't work). There are two main ways to do this as outlined below:

PGPASS File

Create a .pgpass file in your home directory with the connection details of your databases like so:

HOST:PORT:DB_NAME:USERNAME:PASSWORD

e.g.

localhost:5432:postgres:myadmin:Str0ngP@ssw0rd

Then you should be able to log in without entering a password, if you provide the same three following attributes that match up: * database name * host * user

This will note work if you add --password to your psql command, so don't forget to not include that.

One can specify wildcards in your .pgpass file. E.g.

*:*:*:postgres:myadmin:Str0ngP@ssw0rd

If you don't have a home directory, or if you need to specify a custom location, you can set the location of your .pgpass file by setting an environment variable like so:

export PGPASSFILE='/custom/path/to/.pgpass'

Finally, your .pgpass file will only work if you set its permissions to 600 (like your SSH keys).

sudo chmod 600 .pgpass

Environment Variable

You can temporarily set your password by setting the PGPASSWORD environment variable like so:

export PGPASSWORD

This will note work if you add --password to your psql command, so don't forget to not include that.

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 don't 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: 3rd September 2023
First published: 16th August 2018