Get size of a table

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

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

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

User, 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 ist not user postgres may update his password using this function, the new passwort is returned a the result of the execution:

SELECT rotate_password();

Max Connections allowed

Show current value and source of configuration

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

Set Max Connections

ALTER system SET max_connections = 250;

Kill Postgres Connections

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'

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

Convert Hex value to Bit String

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

Produces 11111111

Get configured Server Timezone

SELECT current_setting('timezone') AS configured_timezone;

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;

Kubernetes Setup via Postgres Helm Chart

Install 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 Passwort as ENV

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

Post 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

JSON Datatype, Inserting JSON, Querying JSON

Creating a table with the JSON datatype

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

The JSON to be inserted

{
  "title": "Some document",
  "author": {
    "firstName": "Fanny",
    "lastName": "Fisher"
  }
}

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, →> return 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:

Postgres Inline Data or 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)

Query for array row contains valueA or valueB

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

Postgres Expand Array to Set of Rows

An example:

-- Select UUID and Delivery Date from Dataset
-- Calculate the import date to begin at 00:00:00 and the
-- delivery date to start next day at 12:00:00
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|

Select all rows of first query that are not part of the second query with EXCEPT

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'

Create new table and copy structure and data of another table

CREATE TABLE mynewtable
  (LIKE myoldtable INCLUDING ALL);

Explorative Queries

Show running Queries

SELECT query FROM pg_stat_activity WHERE state = 'active';

show indexes in specific schema

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

find unused indizes in specific schema

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

Clear Screen Workaround

\! clear

Run local Postgres Database with Docker

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

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

Table Partitions

Select Tables with partitioning

also filtering out some tables we don’t want to see …​

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

Upserts

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

Ranges and Multiranges in Postgres

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 junge 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 with JDBC and JDBCObject

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

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

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

Lateral Joins

Allows to combine each row from table with the result of a lateral 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          |
+--+-----+--------------------+---+-------------------+

Another example expands the JSONB Array of hobbies:

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

Analyze RDS Logs with pgbadger

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

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

{
    "DescribeDBLogFiles": [
        {
            "LogFileName": "error/postgres.log",
            "LastWritten": 1720443677790,
            "Size": 963
        },
        {
            "LogFileName": "error/postgresql.log.2024-07-06-06",
            "LastWritten": 1720249200734,
            "Size": 7700843
        },
        {
            "LogFileName": "error/postgresql.log.2024-07-06-07",
            "LastWritten": 1720252800007,
            "Size": 7717431
        },
        {
            "LogFileName": "error/postgresql.log.2024-07-06-08",
            "LastWritten": 1720256399250,
            "Size": 7689466
        },
        {
            "LogFileName": "error/postgresql.log.2024-07-06-09",
            "LastWritten": 1720259998404,
            "Size": 7711202
        },
        {
            "LogFileName": "error/postgresql.log.2024-07-06-10",
            "LastWritten": 1720263600737,
            "Size": 7688732
        }
    ]
}
Filter out just the log file names
aws rds describe-db-log-files --db-instance-identifier my-dev-db | jq '.DescribeDBLogFiles[].LogFileName'
// or better
aws rds describe-db-log-files --db-instance-identifier my-dev-db --query 'DescribeDBLogFiles[].LogFileName' --output text

"error/postgresql.log.2024-07-08-10"
"error/postgresql.log.2024-07-08-11"
"error/postgresql.log.2024-07-08-12"
"error/postgresql.log.2024-07-08-13"
"error/postgresql.log.2024-07-08-14"
"error/postgresql.log.2024-07-08-15"
...
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

Bash Script to 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

Postgres Not To Do’s

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