0 
db1="community-service-data"
db2="ecomm-data-test"

drop_db="DROP DATABASE ${db1}"
sudo -u postgres -S /bin/bash -c "psql -U postgres -d postgres -c \"${drop_db}\""
drop_db="DROP DATABASE \"ecomm-data-test\";"
sudo -u postgres -S /bin/bash -c "psql -U postgres -d postgres -c \"${drop_db}\""

error

ERROR:  syntax error at or near "-"
LINE 1: DROP DATABASE community-service-data
                               ^

I have db names with hyphen in them. I am dropping these databases from a bash script. It keeps complaining about the '-' in the variable. I tried quoting but it doesn't work. I even tried drop_db="DROP DATABASE \"${db1}\"" If I do in quotes in the psql command line directly then it works.

how can I do it correctly.

Thanks

update:

drop_db=(DROP DATABASE "${db1}")

error:

ERROR:  syntax error at end of input
LINE 1: DROP
            ^
Improve this question
14
  • 1
    Please also include the command that works Commented Jul 5, 2024 at 11:26
  • 1
    Please add all relevant information to the question body. Comments are hard to read, easy to miss and can be deleted without warning. The error seems to be from postgres, by the way, not the shell. do you perhaps need to do some special quoting in postgres, like `my_table`? Commented Jul 5, 2024 at 11:57
  • 1
    If not, can you try drop_db=(DROP DATABASE "${db1}") and then sudo -u postgres -S /bin/bash -c "psql -U postgres -d postgres -c '${drop_db[@]}'", does that work? Commented Jul 5, 2024 at 12:00
  • 1
    @CodeWeed: Why are you calling bash to call psql instead of calling psql directly? Feels like some quoting is likely being eaten up in this layering. Commented Jul 5, 2024 at 12:11
  • 1
    right, right, and that's what Chris was getting at. You're right, @ilkkachu, I'm thinking of shell tokenization but it is psql that's relevant here. Commented Jul 5, 2024 at 12:46

2 Answers 2

Reset to default
5

The complexity here is that Postgres requires the database name to be in double quotes. You're also calling an extra layer of shell. Try this instead:

db1="community-service-data"
db2="ecomm-data-test"

drop_db="DROP DATABASE \"$db1\""

then

sudo -u postgres -S psql -U postgres -d postgres -c "$drop_db"

or

sudo -u postgres -S psql -U postgres -d postgres -c "DROP DATABASE \"$db2\""

You say that the fragment is part of a larger script. If a large part of the script is supposed to be run under sudo -u postgres then create a separate function in your script that's called only if a flag is provided, and then catch that to run it alone. For example, under sudo -u postgres /path/to/my/script -P, where -P is the flag that calls your function

Improve this answer
1
  • The first one worked thank you. I think it was the way I call the bash and sending the command in two layers. first to bash => psql => command string. Commented Jul 5, 2024 at 12:43
4

There's no need for sudo or bash here. postgresql comes with a standalone dropdb executable to remove databases, and more advanced shells like zsh can switch users and do stuff by themselves:

#! /bin/zsh -
for db (community-service-data ecomm-data-test) USERNAME=postgres dropdb $db

dropdb will construct the right SQL command whatever characters the db name contains¹.

Just enclosing the value inside " doesn't work for arbitrary values. Obviously, it doesn't work for values containing ", but it could also fail upon encoding clashes.

If using psql, it's better to pass values via psql variables:

db='--my "cool😎" db--'
USERNAME=postgres dropdb -- "$db"

Can also be written (still in zsh):

db='--my "cool😎" db--'
USERNAME=postgres psql -v db=$db <<< 'drop database :"db"'

¹ Though you have to make sure it's encoded using the same charset as used by dropdb; see the PGCLIENTENCODING variable to change it if need be.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.