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.

There is a PostgreSQL Transition Guide available at postgresql-transition-guide/guide: The PostgresSQL transition guide which should help people understand the benefits of PostgreSQL compared to commercial solutions.

If you would like an SQL formatter for PostreSQL then darold/pgFormatter: A PostgreSQL SQL syntax beautifier that can work as a console program or as a CGI works well, can be used from Visual Studio Code or there is a demo at pgFormatter.

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.

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+)

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/

Migrating to PostgreSQL

If you have an Oracle or MySQL database then Ora2Pg : Migrates Oracle to PostgreSQL is a handy tool for migrating to PostgreSQL.