JMM’s notes on

PostgreSQL

Command line

The command line application is “psql”. To connect to a specific database use psql --dbname=somedb

Dump/Restore

Simple dump:

pg_dump somedb > somedb.sql

You may need to be the postgres user to do this (or whoever is an administrator).

Then to restore:

psql -d newdb -f somedb.sql

To dump a schema:

pg_dump --create --clean --schema-only somedb > somedb-schema.sql

To dump just a single table (possibly not recommended, as it doesn’t copy dependencies):

pg_dump --format=plain --verbose --table=public.some_table --dbname=yourdb --data-only > somefile.sql

Copying data, but doing nothing if rows conflict (I’m doing this as a poor man’s replication):

pg_dump --format=plain --table=public.table1 --table=public.table2 --dbname=yourdb --data-only --inserts --on-conflict-do-nothing > somefile.sql

Greatest N per group

This is such a common request that it has its own greatest-n-per-group tag on DBA Stackexchange.

I like the recursive CTE with LATERAL join answer from Erwin Brandstetter. Any answer by this guy is interesting, sometimes I read through his StackExchange answers because they’re insightful.

If you’re doing this query often enough, it might be better to do a MATERIALIZED VIEW or set triggers to automatically update a separate table.

Emacs

In a sql-mode buffer you can add the following to set login options:

-- Local Variables:
-- sql-product: postgres
-- sql-user: "someuser"
-- sql-database: "somedb"
-- sql-server: "localhost"
-- End:

Then use M-x sql-product-interactive or C-c TAB to start a process.

Miscellaneous queries

Overlapping dates

Say you have a table of events that have a start_date and end_date, where these dates are equivalent for single-day events (like a birthday). How do you find dates that overlap?

SELECT *
FROM events
WHERE daterange(start_date, end_date, '[]') && daterange(date '2024-02-22', date '2024-02-24', '[]');

Note that just doing OVERLAPS won’t work for this query if the start_date and end_date are date '2024-02-24'. Using daterange('2024-02-24', '2024-02-24', '[]') gives a range of [2024-02-24,2024-02-25).

See PostgreSQL documentation on Range Types for more info.

Misc

Clojure

What to put in a deps.edn:

org.postgresql/postgresql {:mvn/version "42.3.0"}

jdbc.next and connection pooling

Here’s an example of using next.jdbc and HikariCP.

(ns io.jmm.example-nextjdbc
  (:require [next.jdbc :as jdbc]
            [next.jdbc.sql]
            [next.jdbc.connection :as connection])
  (:import (com.zaxxer.hikari HikariDataSource)))

(def db {:dbtype   "postgresql"
         :dbname   "somedb"
         ;; :user is required for next.jdbc without connection pooling
         :user     "whateveryourusernameis"
         ;; :username is required for HikariCP
         :username "whateveryourusernameis"
         :password "hopefullyyouhaveagoodpassword"})

(def ds (jdbc/get-datasource db))

(time (jdbc/execute! ds ["SELECT * FROM sometable WHERE x = ?" 100]))

(def ds2 (connection/->pool HikariDataSource db))

(time (jdbc/execute! ds2 ["SELECT * FROM sometable WHERE x = ?" 100])))

;; Multiple inserts
(jdbc/execute! ds ["CREATE TABLE sometest (id INT GENERATED ALWAYS AS IDENTITY, msg TEXT NOT NULL);"])

(next.jdbc.sql/insert-multi! ds "sometest" [{:msg "Hello"} {:msg "There"}])