Run Redshift SQL Script File using psql Variable Substitution

PostgreSQL or psql allows the variable substitution using -v option in Redshift sql query files. The variable substitution is very important when you are calling the Redshift sql scripts from shell or any other scripting language. You can pass the values to query that you are calling using -v option. In this article, we will see how to run Redshift sql script file using psql variable substitution. We also see some of the working examples that uses the psql variable substitution method.

Run Redshift SQL Script File using psql Variable Substitution

Psql supports a variable substitution using -v option at a command line. You can set and use that variable within the script.

In case if you need to set up system variables to access Redshift without password prompt.

Follow my other post:

• Access Redshift using psql without Password Prompt – Use psql System Variables
• Redshift SET ON_ERROR_STOP using psql and Examples

Set value to a variable that you want to pass to Redshift sql query or substitute the value of variable in SQL query at run time. Below is the sample syntax that you can use to run Redshift sql script file using psql variable substitution.

$psql -v kind_val= ‘Action’ -f ‘sample_script.sql’

Above command is the sample example to set variable ‘kind_val’ to value ‘Action’. Also you can have multiple variable each with -v option in single psql command.

Run Redshift SQL Script File using psql Variable Substitution Example

Below is the working example to run Redshift SQL script file using psql variable substitution:

https://gist.github.com/6836895024bc0e5aa6ef43ba464415fd

Redshift psql client substitute the variable value at the runtime. This feature allows you to execute the sql queries by passing specific value at runtime. You can use variable substitution only using PostgreSQL client.

Here is the guide to connect Redshift to psql:

• Steps to connect Redshift using PostgreSQL – psql
• Psql Invalid Value for Parameter client_encoding – Redshift