PostgreSQL

This section is all about PostgreSQL: The world's most advanced open source database.

The "standard" client or admin utility is pgAdmin - PostgreSQL Tools which actually runs in a browser and is written in Python. It also includes the psql command line utility.

Command Line

There is a PostgreSQL command line utility called psql, this is documented at PostgreSQL: Documentation: psql.

Setting up Command Line

One very useful thing is setting up a password file, see PostgreSQL: Documentation: 12: 33.15. The Password File for details on this, which does work rather nicely, however be sure to read the part that describes setting the file permissions correctly.

Embedded PostgreSQL

Whilst working on a Java project, built using Maven, I needed to execute Integration Tests, this meant testing with PostgreSQL but without deploying it, and so I chose to use an Embedded PostgreSQL solution. At first I found some quite old solutions that had not been updated in a while, eventually I found GitHub - opentable/otj-pg-embedded: Java embedded PostgreSQL component for testing which has worked very nicely. However this was found to not run on Alpine Linux in Docker, so I moved to a fork at zonkyio/embedded-postgres: Java embedded PostgreSQL component for testing.

It is worth noting that at the time of writing PostgreSQL 14 has been release, but the default version for Embedded PostgreSQL is 10.18, which still has over 12 months of support left, but it quite old and missing various functions and features. If you read the ZonkyIO GitHub page you will see how to get version 13.x/

Handy Queries

One useful one, is this, which should return all the version and build information you need

SELECT version()

Some other useful functions are now() and gen_random_uuid() (Version 13+)