How to set PostgreSQL script variables

Let’s say you want to set a variable ROOT_DIR that is used within another script. You would do this:

\set ROOT_DIR /path/to/directory

And to reference the variable:

\echo 'ROOT_DIR => ' :ROOT_DIR

Now, everywhere you might hardcode /path/to/directory in your scripts like this:

\i /path/to/directory/my_script_customer_ABC.sql

You can substitute it with the ROOT_DIR script variable:

\i :ROOT_DIR/my_script_customer_ABC.sql

And you can combine multiple variables:

\set ROOT_DIR /path/to/directory
\i :ROOT_DIR/my_script_customer_:CUSTOMER_ID.sql


Hope you enjoyed this quick PostgreSQL tip! If you did, click the Like button.

Follow me on Twitter also @jstevenperry