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



Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.