Get the most out of PostgreSQL using psql with must known features.

If you’re a PostgreSQL user, you’re likely familiar with psql – the interactive terminal that connects you to your Postgresql databases. psql tool is more than just a basic utility. In fact, I consider it to be an essential asset for anyone working with PostgreSQL databases. With an impressive range of features, tips, and tricks, psql is go to tool to talk with PostgreSQL. It never fails to impress and surprise you with its incredible capabilities.

I have compiled a list of the most useful psql features that I frequently use in my day-to-day interactions with PostgreSQL databases.

Integration with Code Editor.

With psql, we can integrate with our favorite code editor and use it to view and edit procedural code.

If we don’t specify functions, it will create empty function template.

export PSQL_EDITOR="code -w"
\ef

Change password using \password.

To avoid logging plain passwords in logs or command lines, use the \password option in psql to change passwords. It encrypts passwords with the encryption specified in the password_encryption flag.
We can use it to change current user or other user password.

Custom shortcut in psql.

Use the psql \set option to assign names to values and create custom abbreviations for frequently used commands. For instance, ‘eab’ for ‘explain (analyze, buffers).’

One option for retrieving ‘select * from table’ in psql is to use the table <<table_name>> option.

Monitor SQL output within interval and stop if req.

If you are looking for a way to lookout for a SQL within specified intervals, check out \watch option in psql.

If needed we can also auto stop it based on intentional exception.

Use watch in psql to run SQL repeatedly with an interval & stop it when a condition is met.
Example: Table is ! empty.
select case (select count(1) from test1) when 0 then 0 else (select 1)/(select 0) end.
Stop the watch with an intentional error. #psql #SQLtips #PostgreSQL

— Deepak Mahto (@mahtodeepak05) February 3, 2023

Below sample check for records existence within a table using \watch and stop when a new record is inserted.

Execute from query output buffers.

We can use \gexec to execute SQL queries that are embedded in the output of another query.

Echo all internals query use by psql.

With -E option, we can view all PostgreSQL metadata queries used by psql to get the output. It helps a lot to create your own custom shortcut using \set.

Each new PostgreSQL release introduces new improvements and enhancement for psql. If you’re curious to explore some of these interesting features, please take a look at the blog post below

Interesting PSQL features from PostgreSQL releases you don’t wanna miss.

Overall psql is a beast in terms of features, if interested for further read check out official documentation.