Links
- Homepage
- https://www.postgresql.org/
- Documentation
- https://www.postgresql.org/docs/
- PostgreSQL Wiki
-
https://wiki.postgresql.org/wiki/Main_Page
Check out the Don't Do This article.
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.
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.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])))