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