Using PostgreSQL with iTerm (and oh-my-zsh)

Context

I recently had to install PostgreSQL for a uni module. The primary front-end is a command-line program called psql.

psql is installed by default with PostgreSQL. I've been using it for a few hours now, and think it's a nifty, lightweight way of interfacing with Postgre. But I didn't like the fact that the default app ran in the default terminal, there's all sorts of weird text rendering issues, and I don't have the comfort of my own terminal setup.

My terminal setup is usually oh-my-zsh with iTerm2.

The text-rendering, tab support, intuitive copy/pasting and searching features make terminal usage much more comfortable, which is why I wanted psql to open in my usual iTerm2 window.

So, I initially found the psql binary in my Library (mine was at /Library/PostgreSQL/15/bin). I thought it would be a case of just adding that to my path and running psql in my terminal... While there may be a way to get this to work, I found that this was using my MacOS profile when signing into PostgreSQL, which I am unable to do (my module requires me to sign in with different credentials).

Guide

I found that the binary (mentioned above) shouldn't be the entry point, instead /scripts contains a shell script called runpsql.sh, which prompts you to enter the database, port, username and password, which it then passes to the binary. Perfect!

So... All you need to do is add this line to your .zshrc file (mine was at /Library/Users/{username}/.zshrc):

alias psql="sh /Library/PostgreSQL/15/scripts/runpsql.sh"

Of course, your path may differ, you should make sure you check the file is there before continuing.

Just restart your terminal, type psql, enter the details for host/port/username/password, and you should be looking at your PostgreSQL database.

If you need a quick test, try SELECT version();