PostgreSQL

We are heavy database users at Uniregisty, but I'm no professional Database Administrator.

Cool tricks

pgcli

pgcli is a major improvement over the stock pshell. Queries are auto-completed as you type and it's especially useful to complete complex joins.

Debugging stored procedures

If you're writing complex stored procedures - maybe involving the JSON(B) field types with a lot of new operators - it can be helpful to debug your stored procedures.

1. It might be easier to run a Stored Procedure / DB Function using the psql / pgcli shell than from your client code, but I have written Python tests just to test a specific SQL query and hooked up a "test watcher" to also watch the SQL files, replace the functions/procedures and run the corresponding tests.
2. The obvious way to do debug is with exception messages/notices. Depending on the error level this will either end the current transaction and your ORM will surface the error in your application (or the psql shell) or you will have to inspect your database's logs.