Administration & Configuration

Connections

Show Max Connections (value and source)

SELECT setting, source, sourcefile, sourceline
FROM pg_settings
WHERE name = 'max_connections';

Set Max Connections

ALTER system SET max_connections = 250;

Kill Connections for a Database

SELECT pg_terminate_backend(pid)
FROM pg_catalog.pg_stat_activity
-- we don't want to kill our own connection
WHERE pid != pg_backend_pid()
-- we don't want to kill connections to other databases
AND datname = 'MYDATABASE'

Show Statement Timeout Settings for All Users

SELECT r.rolname,
       (SELECT unnest(s.setconfig) FROM pg_db_role_setting s WHERE s.setrole = r.oid AND s.setconfig::text LIKE '%statement_timeout%' LIMIT 1) AS statement_timeout
FROM pg_roles r
WHERE r.rolcanlogin
ORDER BY r.rolname;

Users, Roles & Passwords

Show Users and their Assigned Roles

SELECT
  member.rolname AS user,
  role.rolname   AS granted_role
FROM
  pg_auth_members m
JOIN
  pg_roles role   ON m.roleid = role.oid
JOIN
  pg_roles member ON m.member = member.oid

Show How Long User Passwords are Valid

SELECT rolname,
       rolvaliduntil
FROM pg_roles
WHERE rolname = 'your_username';

Update User Password

Set new password valid for 6 months
ALTER USER USERNAME WITH password 'PASSWORD' VALID UNTIL current_date + interval '6 month';

Function: Rotate a User’s Password

The following function allows users with time-restricted passwords (VALID UNTIL) that have no sufficient permissions to update the validity of their password also as the password itself to update their credentials with this function. The function is run with the permissions of its creator, so the creator must have sufficient permissions to alter roles.

If called, the script …​

  • …​ checks if the current session user is not in the blacklist of forbidden users (e.g. user postgres)

  • …​ generates a new password of 32 characters

  • …​ updates the validity of the new password to 6 months in the future

  • …​ returns the newly created password

rotate-password.sql
CREATE OR REPLACE FUNCTION rotate_password()
    RETURNS TEXT AS $$
DECLARE
    new_password TEXT;
    uppercase TEXT := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    lowercase TEXT := 'abcdefghijklmnopqrstuvwxyz';
    digits TEXT := '0123456789';
    special TEXT := '~!@#$%^&*-+=''|\(){}[]:;"''<>,?.';
    charset TEXT;
    forbidden_users TEXT[] := ARRAY['postgres'];
BEGIN
    IF (session_user = ANY(forbidden_users)) THEN
        RAISE EXCEPTION 'User % is not allowed to reset password (blacklisted)', session_user;
    END IF;

    charset := uppercase || lowercase || digits || special;

    new_password := '';
    new_password := new_password || substr(uppercase, floor(random() * length(uppercase) + 1)::int, 1);
    new_password := new_password || substr(lowercase, floor(random() * length(lowercase) + 1)::int, 1);
    new_password := new_password || substr(digits, floor(random() * length(digits) + 1)::int, 1);

    FOR i IN 1..29 LOOP
            new_password := new_password || substr(charset, floor(random() * length(charset) + 1)::int, 1);
        END LOOP;

    new_password := (
        SELECT string_agg(ch, '')
        FROM (
                 SELECT substr(new_password, i, 1) AS ch
                 FROM generate_series(1, 32) i
                 ORDER BY random()
             ) shuffled
    );

    EXECUTE format('ALTER USER %I WITH PASSWORD %L VALID UNTIL %L',
                   session_user,
                   new_password,
                   (now() + interval '6 months')::timestamp
            );

    RETURN new_password;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Now a user who is not user postgres may update his password using this function, the new password is returned as the result of the execution:

SELECT rotate_password();

Bash Script: Generate Postgres Users

  • generates CREATE USER statements for three environments (dev, release, prod)

  • generates 32-char passwords

  • password is valid for 6 months

#!/bin/bash

# Check for input
if [ -z "$1" ]; then
  echo "Usage: $0 \"username1,username2,username3\""
  echo "Example: $0 \"alice,bob,carol\""
  exit 1
fi

# Input: comma-separated usernames
input="$1"

# Convert to array
IFS=',' read -ra usernames <<< "$input"

# Fixed environments
environments=("dev" "release" "prd")

# Calculate date 6 months from now (format: 'Mon DD YYYY')
valid_until=$(date -d "+6 months" "+%b %d %Y")

# Generate SQL statements
for username in "${usernames[@]}"; do
  for env in "${environments[@]}"; do
    password=$(openssl rand -base64 32 | tr -dc 'A-Za-z0-9' | cut -c1-32)
    echo "CREATE USER ${username} WITH PASSWORD '${password}' VALID UNTIL '${valid_until}';"
  done
done

Table Ownership

Show Owner for a Table in a Designated Schema

SELECT tableowner
FROM pg_tables WHERE schemaname = 'SCHEMA' AND tablename = 'TABLE'

Server Info

Get Configured Server Timezone

SELECT current_setting('timezone') AS configured_timezone;

Show Running Queries

SELECT query FROM pg_stat_activity WHERE state = 'active';

Show the Definition of a Custom Stored Function

SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = 'THE_FUNCTION_NAME';

Querying Techniques

Set Operations

Select Unique Combination of Fields / Tuples

SELECT DISTINCT ON(field1, field2) field1, field2
FROM thetable

Select Rows Where a Combination of Fields is Not Unique

SELECT columnA, columnB, count(*) AS count
FROM thetable
GROUP BY columnA, columnB
HAVING count(*) > 1

EXCEPT: Rows in First Query Not in Second

Types and columns of both queries must match! 
select id
from (
 values
 ('A', 1),
 ('A', 2),
 ('A', 3),
 ('A', 4),
 ('B', 3),
 ('B', 4),
 ('B', 5),
 ('B', 6)
) as data(importkey, id)
where importkey = 'A'
except
select id
from (
 values
 ('A', 1),
 ('A', 2),
 ('A', 3),
 ('A', 4),
 ('B', 3),
 ('B', 4),
 ('B', 5),
 ('B', 6)
) as data(importkey, id)
where importkey = 'B'

Inline Data / Table Value Constructor

Input data without using a table:

select *
from (
 values
 ('A', 1),
 ('A', 2),
 ('A', 3),
 ('A', 4),
 ('B', 3),
 ('B', 4),
 ('B', 5),
 ('B', 6)
) as data(importkey, id)

Common Table Expressions (CTEs)

Materialize to avoid inlining

WITH xxx AS MATERIALIZED ( ... )

Update Columns That Are Foreign Keys

Given two tables with a foreign key constraint from table execution to table planned_execution:

Table planned_execution
CREATE TABLE planned_execution (
    planned_time timestamptz NOT NULL,
    someattr TEXT NOT NULL,
    CONSTRAINT planned_execution_pkey PRIMARY KEY (planned_time, someattr)
);
Table execution
CREATE TABLE execution (
    execution_time timestamptz NOT NULL,
    planned_time timestamptz NOT NULL,
    someattr TEXT NOT NULL,
    CONSTRAINT execution_pkey PRIMARY KEY (execution_time),
    CONSTRAINT planned_execution_fkey FOREIGN KEY (planned_time, someattr) REFERENCES planned_execution(planned_time, someattr)
);

We can now use a common table expression (CTE) to update both tables without receiving errors:

WITH new_planned_execution AS (
    UPDATE planned_execution
        SET someattr = '123'
        WHERE planned_time = '2025-01-05 02:00:00.000000 +01:00'
)
UPDATE execution
SET someattr = '123'
WHERE planned_time = '2025-01-05 02:00:00.000000 +01:00';

Lateral Joins

Allows to combine each row from a table with the result of a lateral subquery.

Join with Correlated Subquery

In the following example, we’re joining people’s reading preferences with matching book titles:

WITH persons AS (
    SELECT * FROM (VALUES
                       (1, 'Alice', 'Horror'),
                       (2, 'Bob', 'Poetry'),
                       (3, 'Sally', 'Romance')
                  ) AS person(id, name, preferred_book_genre)
),
     books AS (
         SELECT * FROM (VALUES
                            (101, 'The Call of Cthulhu', 'Horror'),
                            (102, 'Best of Poe', 'Poetry'),
                            (103, 'Sunset of Love', 'Romance'),
                            (104, 'Nyarlathothep', 'Horror'),
                            (105, 'The Raven', 'Poetry')
                       ) AS book(id, title, genre)
     ) -- everything before this marker is just there to provide some sample data
SELECT
    person.id,
    person.name,
    person.preferred_book_genre,
    book.id,
    book.title
FROM
    persons person
        CROSS JOIN LATERAL (
        SELECT
            id,
            title,
            genre
        FROM
            books
        WHERE
            books.genre = person.preferred_book_genre
        ) book;

Produces the following result:

+--+-----+--------------------+---+-------------------+
|id|name |preferred_book_genre|id |title              |
+--+-----+--------------------+---+-------------------+
|1 |Alice|Horror              |101|The Call of Cthulhu|
|2 |Bob  |Poetry              |102|Best of Poe        |
|3 |Sally|Romance             |103|Sunset of Love     |
|1 |Alice|Horror              |104|Nyarlathothe       |
|2 |Bob  |Poetry              |105|The Raven          |
+--+-----+--------------------+---+-------------------+

Expand JSONB Arrays with Lateral Join

with mytable (username, jsondata) as (
  values
    ('Sally', '{"hobbies":["swimming", "reading"]}'::jsonb),
    ('Tim', '{"hobbies":["diving", "coding", "cooking"]}'::jsonb),
    ('Maggie', '{"hobbies":["reading", "photography", "swimming"]}'::jsonb)
)
select t.username as username, hobby
from mytable t
cross join lateral jsonb_array_elements(t.jsondata -> 'hobbies') as hobby

This produces the following output:

username|hobby        |
--------+-------------+
Sally   |"swimming"   |
Sally   |"reading"    |
Tim     |"diving"     |
Tim     |"coding"     |
Tim     |"cooking"    |
Maggie  |"reading"    |
Maggie  |"photography"|
Maggie  |"swimming"   |

Upserts (INSERT ON CONFLICT)

Upsert means to either insert or update existing records (or do nothing) .. but this must be guaranteed

On Table Columns

on conflict with the values of col1 and col2, do nothing
INSERT INTO <table>
VALUES('something', 123)
ON CONFLICT ON (col1, col2)
DO NOTHING

On Table Constraints

on conflict with constraint unique_cols, update col2
INSERT INTO <table>
VALUES('something', 123)
ON CONFLICT ON CONSTRAINT unique_cols
DO UPDATE SET col2 = 'xoxo'

Resources

Data Types

Arrays

Search for Rows with Array Containing Value

Assuming, the field appointments has the type date[]

SELECT *
FROM mtable
WHERE appointments @> ARRAY['2023-09-19'::date]

also valid is this query:

SELECT *
FROM mtable
WHERE ARRAY['2023-09-19'::date] <@ appointments

Query for Array Containing Any of Multiple Values (Overlap)

In the following example, we’re querying for all rows where the date 2024-01-29 or 2024-01-30 are contained in the column dates of type date[]

select id, dates from (
 values
  (1, array['2024-01-29'::date,'2024-01-31'::date]),
  (2, array['2024-01-28'::date, '2024-01-30'::date]),
  (3, array['2024-01-27'::date, '2024-01-28'::date, '2024-01-31'::date])
) as mydata(id, dates)
where dates && '{2024-01-29,2024-01-30}'

yields the following result:

id|dates                  |
--+-----------------------+
 1|{2024-01-29,2024-01-31}|
 2|{2024-01-28,2024-01-30}|

Expand Array to Set of Rows (unnest)

select
  (id) as "import_id",
  (deliverydate + '00:00:00'::time) as "date_begin",
  (deliverydate + interval '1 day' + '12:00:00'::time) as "date_deliver"
from unnest(
'{91f8f3e5-9d99-4f2b-be2c-7573262440fd, f3f0b446-3336-4704-9abd-1c8fa05913b4, 866d1b46-545e-4572-ba7c-ab75052b08e5}':: uuid[],
'{2023-1-2, 2023-1-4, 2023-1-9}':: date[]
) as plan(id, deliverydate);

Produces the following result:

import_id                           |date_begin             |date_deliver           |
------------------------------------+-----------------------+-----------------------+
91f8f3e5-9d99-4f2b-be2c-7573262440fd|2023-01-02 00:00:00.000|2023-01-03 12:00:00.000|
f3f0b446-3336-4704-9abd-1c8fa05913b4|2023-01-04 00:00:00.000|2023-01-05 12:00:00.000|
866d1b46-545e-4572-ba7c-ab75052b08e5|2023-01-09 00:00:00.000|2023-01-10 12:00:00.000|

JSON / JSONB

Creating a Table with the JSON Datatype

CREATE TABLE documents
(
  title varchar NOT NULL CONSTRAINT documents_pk PRIMARY KEY,
  "metadata" json NOT NULL
);

Inserting JSON Data

INSERT INTO documents VALUES('Some document',to_json('{"title": "Some document", "author": {"firstName": "Fanny","lastName": "Fisher"}}'::json)

or as inline query:

select *
from (
 values
 ('Some document',to_json('{"title": "Some document", "author": {"firstName": "Fanny","lastName": "Fisher"}}'::json))
 ) as data(title,jsondata)

or as common table expression (CTE):

with data(title,jsondata) as (
 values
 ('Some document',to_json('{"title": "Some document", "author": {"firstName": "Fanny","lastName": "Fisher"}}'::json))
)
select d.jsondata #>> '{author, firstName}'
from data d

Querying JSON

e.g. we query for the document’s author’s first name:

JSON Query
SELECT d.metadata -> 'author' ->> 'firstName' AS firstName
FROM documents d
→ returns the node, →> returns a text value

A more elegant way for deeper nested JSON structures is using the path operator:

SELECT d.metadata #>> '{author, firstName}' AS firstName
FROM documents d

Querying with JSON Path

with data(jsondata) as (
 values
 (to_jsonb('{"data": [{"id":1},{"id":2},{"id":3} ]}'::jsonb))
)
select jsonb_path_query(jsondata, '$.data[*].id') as all_ids
from data d

yields:

all_ids|
-------+
1      |
2      |
3      |

Resources:

Ranges & Multiranges

Datemultirange Example (Hotel Bookings)

We’re using `multidaterange`s to model a hotel booking table where duration marks the date ranges where a customer has booked a room.

-- Sally has booked room 23 from 3rd june (inkl.) to 6th june (excl.) and 8th june to 10th june (both inkl.)
-- Tim has booked room 12 from 2nd june (inkl.) to 5th june (excl.) and 19th june to 22nd june (both inkl.)
with bookings(customer_name, room_number, duration) as (
 values
 ('Sally', 23, '{[2024-06-03,2024-06-06), [2024-06-08,2024-06-10]}'::datemultirange),
 ('Tim', 12, '{[2024-06-02,2024-06-05), [2024-06-19,2024-06-22]}'::datemultirange)
)
select *
from bookings
where duration @> '[2024-06-04, 2024-06-05]'::daterange

This produces the following outcome:

customer_name|room_number|duration                                         |
-------------+-----------+-------------------------------------------------+
Sally        |         23|{[2024-06-03,2024-06-06),[2024-06-08,2024-06-11)}|

Resources

Mapping Ranges with JDBC and PGobject

PGobject dateRange = new PGobject();
dateRange.setType("daterange");
dateRange.setValue("[2024-06-19, 2024-06-25]");

PreparedStatement stmt = conn.prepareStatement(sql);
// as a query parameter
stmt.setObject(1,dateRange);

// for mapping values from the resultset
PGobject daterangeValue = (PGobject) rs.getObject("daterange_col");

Domain Types

e.g. we have a composite key consisting of a user’s id and his locale, separated by a double-colon so that it might look like this: 12345:EN

We could create a domain type based on varchar and add a check for this pattern:

create domain userkey as varchar check (VALUE ~* '^\d+:[A-Z]+$')

Afterwards we may use this type:

create table userlogin (user userkey, loggedin timestamp);

Custom Composite Types

Postgres allows us to define a custom type that consists of multiple elements, e.g.:

Creating a composite type of two elements

CREATE TYPE pathkeytype AS (pathid int, pathversion int);

Replacing existing keys with the composite type

ALTER TABLE mytable ADD COLUMN pathkey pathkeytype GENERATED ALWAYS AS ((pathid, pathversion)::pathkeytype) STORED;

Date & Time

Duration Between Two Timestamps in Minutes

SELECT
 EXTRACT(
  EPOCH FROM ('2025-10-01T12:00'::timestamptz - '2025-10-01T08:00'::timestamptz)
) / 60 as duration_minute

Generate Date Series Between Two Dates

SELECT generate_series(
  timestamp '2025-03-29',
  timestamp '2025-03-31',
  '1 days'::interval,
  'Europe/Berlin'
) AS day;

or:

SELECT (generate_series(
  timestamp '2025-03-29',
  timestamp '2025-03-31',
  interval '1 day'
) AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Berlin')::timestamptz AS day;

Convert Hex Value to Bit String

select ('x'||'ff')::bit(8)::text

Produces 11111111

Indexes & Performance

Show Indexes and Their Size in a Schema

SELECT tablename, indexname, indexdef,
       pg_size_pretty(pg_relation_size(schemaname || '.' || indexname)) AS index_size
FROM pg_indexes
WHERE schemaname = 'SCHEMANAME'
ORDER BY pg_relation_size(schemaname || '.' || indexname) DESC;

Show Indexes in a Schema (Simple)

select tablename, indexname, indexdef
from pg_indexes
where schemaname = 'myschema'
order by tablename, indexname;

Find Unused Indexes

select *
from pg_stat_user_indexes
where schemaname = 'myschema'
and idx_scan=0

Get Size of a Table

SELECT pg_size_pretty(pg_total_relation_size('schemaname.tablename'));

Analyze Statistics for a Table Column

SELECT * from pg_stats
where tablename = 'TABLE_NAME'
and attname = 'COLUMN_NAME'

this produces something like this:

Name                  |Value                                                                                                                                                                                                                         |
----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
schemaname            |myschema                                                                                                                                                                                                                  |
tablename             |table_name                                                                                                                                                                                                                |
attname               |column_name                                                                                                                                                                                                                    |
inherited             |true                                                                                                                                                                                                                          |
null_frac             |0.0                                                                                                                                                                                                                           |
avg_width             |4                                                                                                                                                                                                                             |
n_distinct            |53.0                                                                                                                                                                                                                          |
most_common_vals      |{1,2,3,4,5,6,7,8,9,10,11,12,13,15,14,16,23,17,21,19,22,24}                                                                                                                                                                    |
most_common_freqs     |{0.8954,0.049566668,0.015266667,0.0083,0.006966667,0.0048666666,0.0036666666,0.0026666666,0.0024666667,0.0017,0.0011666666,0.0011333333,0.0009,0.00076667,0.0005,0.0005,0.00036667,0.00033333,0.00033333,0.0003,0.0003,0.0003}|
histogram_bounds      |{18,18,18,20,20,20,20,26,26,27,27,28,29,30,30,33,34,34,35,36,38,39,40,43,46,49,60,61,64,120,195}                                                                                                                              |
correlation           |0.8240362                                                                                                                                                                                                                     |
most_common_elems     |                                                                                                                                                                                                                              |
most_common_elem_freqs|NULL                                                                                                                                                                                                                          |
elem_count_histogram  |NULL                                                                                                                                                                                                                          |
range_length_histogram|                                                                                                                                                                                                                              |
range_empty_frac      |                                                                                                                                                                                                                              |
range_bounds_histogram|                                                                                                                                                                                                                              |

Table Management

Partitioning: Show Partitioned Tables

SELECT partrelid::regclass::text FROM pg_partitioned_table WHERE partrelid::regclass::text NOT IN ('tablethatdoesnotexist', 'notexistingtoo') order by 1

Create New Table and Copy Structure of Another

CREATE TABLE mynewtable
  (LIKE myoldtable INCLUDING ALL);

Search for Column Matching Special Characters

e.g. we have a field content of type varchar and we want to search for all rows where this field contains an underscore character _:

SELECT *
FROM your_table_name
WHERE content LIKE '%\_%' ESCAPE '\';

DevOps & Tooling

Docker: Run Local Postgres

docker run --name somename -p5432:5432 -e POSTGRES_PASSWORD=somefancypassword -d mydesiredusername postgres

Kubernetes / Helm

Install Postgres Helm Chart

We’re installing it as a release named my-postgres:

helm install my-postgres oci://registry-1.docker.io/bitnamicharts/postgresql

Get the Password as ENV

export POSTGRES_PASSWORD=$(kubectl get secret --namespace testns my-postgres-postgresql -o jsonpath="{.data.postgres-password}" | base64 -d)

Port-Forward and Connect to Database

kubectl port-forward --namespace testns svc/my-postgres-postgresql 5432:5432 &
PGPASSWORD="$POSTGRES_PASSWORD" psql --host 127.0.0.1 -U postgres -d postgres -p 5432

More Information

helm inspect all oci://registry-1.docker.io/bitnamicharts/postgresql

AWS RDS

Download Postgres Logs from AWS RDS

Get AWS RDS instances
aws rds describe-db-instances
Get available log files
aws rds describe-db-log-files --db-instance-identifier my-dev-db
Filter out just the log file names
aws rds describe-db-log-files --db-instance-identifier my-dev-db --query 'DescribeDBLogFiles[].LogFileName' --output text
Bash Script to download RDS Logs for a DB instance
#!/bin/bash
set -x

the_region="eu-central-1"
the_db_instance="my-dev-db"

for filename in $( aws rds describe-db-log-files --region $the_region --db-instance-identifier $the_db_instance | jq -r '.DescribeDBLogFiles[] | .LogFileName' )
do
aws rds download-db-log-file-portion --region $the_region --db-instance-identifier $the_db_instance --output text --no-paginate --log-file $filename  >> ./postgres_aws_rds_logs_$the_db_instance.log
done

Analyze RDS Logs with pgbadger

cat all.log | docker run -i --rm matsuu/pgbadger -f rds - -o - -x html > out.html

psql Tips

Clear Screen Workaround

\! clear

Fun

Generate Lotto Numbers

5 of 1 to 50:

SELECT
  (random() * 50 + 1)::int AS lotto_number
FROM
  generate_series(1, 5)
ORDER BY 1 ASC

Anti-Patterns

There is a comprehensive list in the Postgres Wiki: Don’t Do This!