Issues using a psql -v variable with jsonb_set

Question

Pretty obscure situation and a couple hours of trying different things with no luck.

I have a bash script which restores a production database backup locally. Part of the script runs some SQL to set things in the database.

One of those items is setting the correct slack_token in a slack_details table. Here's how I run the bash script:

$ ./restore-prod-database.sh -t "xoxb-1234567-1234....."

The script has a line which triggers running a .sql file like this:

psql -h $DATABASE_HOST -d $DATABASE_NAME -a -v slack_token=$SLACK_TOKEN -f restore-populate-slack-details.sql

Finally, the SQL does something like this:

UPDATE slack_details
SET installation = jsonb_set(installation, '{bot,token}', ('"' || :slack_token ||'"')::jsonb)
WHERE id = 1;

No matter what I try, whether its concatenating the :slack_token with quotes, passing it in with quotes, using single quotes and " I get the following error:

UPDATE slack_details SET installation = jsonb_set(installation, '{bot,token}', ('"' || :slack_token ||'"')::jsonb) WHERE id = 1; psql:restore-populate-slack-details.sql:61: ERROR: column "xoxb" does not exist LINE 2: ... = jsonb_set(installation, '{bot,token}', ('"' || xoxb-12345...

Quick note, installation is a jsonb column and the following code works fine, moment I use the -v variable it doesn't work:

UPDATE slack_details
SET installation = jsonb_set(installation, '{bot,token}', ('"' || '123456' ||'"')::jsonb)
WHERE id = 1; --- works fine

Answer 1

To inject the :slack_token variable as a literal in the SQL query, the proper syntax to use is :'slack_token'.

From the psql documentation in "SQL interpolation":

When a value is to be used as an SQL literal or identifier, it is safest to arrange for it to be quoted. To quote the value of a variable as an SQL literal, write a colon followed by the variable name in single quotes. To quote the value as an SQL identifier, write a colon followed by the variable name in double quotes. These constructs deal correctly with quotes and other special characters embedded within the variable value

The query could be written as:

UPDATE slack_details
SET installation = jsonb_set(installation, '{bot,token}', ('"' || :'slack_token' ||'"')::jsonb)
WHERE id = 1;

Besides, as commented below by @jjanes, the construction of the jsonb value should be simplified and made safer by using to_jsonb, otherwise the lack of quoting inside the token might lead to invalid json strings with certain values of :slack_token:

UPDATE slack_details
SET installation = jsonb_set(
 installation,
 '{bot,token}',
 to_jsonb(:'slack_token'::text)
) WHERE id = 1;

• jjanes: Yes. But to improve it further, I think he would want to use to_jsonb() not concatenating literal double quotes to the thing. to_jsonb(:'slack_token'::text) That way it will work correctly if the token happens to contain a literal double quote.
• Daniel Vérité: @jjanes: good point, answer updated.