Sample Header Ad - 728x90

Issues using a psql -v variable with jsonb_set

0 votes
1 answer
475 views
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
Asked by Luke Belbina (103 rep)
Aug 15, 2023, 12:31 AM
Last activity: Aug 16, 2023, 02:32 PM