psql \set an environment variable from the existing shell environment

Question

I want to set up a psql variable from an existing shell environment variable that I can use in later psql commands.

I found the \set meta-command, but I'm not able to make it use an existing environment variable in Linux:

user@localhost# MY_DB='test_database'
user@localhost# sudo -u postgres psql
\set dbname ${MY_DB}
\set 
...
dbname = '${MY_DB}'

whereas I would like dbname = 'test_database' on the last line so that I can use it or example like: \connect :dbname;

How could I achieve that with pg 15 on debian 11?

This is not helping me much because \set dbname `echo ${MY_DB}` is issuing an empty string.

This neither, because I'd like to set my psql variable only once in order to avoid redundancy and to reuse it in plenty of psql commands instead of setting it plenty of times with -v in each of those psql commands.

Answer 1

psql version 15 provides the \getenv metacommand to do this efficiently:

\getenv psql_var env_var
Gets the value of the environment variable env_var and assigns it to the psql variable psql_var. If env_var is not defined in the psql process's environment, psql_var is not changed. Example:

           => \getenv home HOME
           => \echo :home
           /home/postgres

Please note that it's not enough to define a variable in the shell from which psql is launched: the variable needs to be exported (see export with POSIX shells) to be accessible from child processes. This is why in your example, \set dbname `echo ${MY_DB}` produces an empty string. Whether you want to use the faster \getenv or echo in a subshell, you should do export MY_DB='test_database'.