Help with creating postgres databases with - in the name

Question

I'm currently trying to script some migration of databases on a server.

As part of this process I will need to create a new database with a similar name to the existing database.

Whilst I have this working for the majority of cases I have come across a problem when the name contains the character - in them e.g.

sample-database-name

My script will execute the following command

psql -c "CREATE DATABASE sample-database-name WITH TEMPLATE = template0;"

Which leads to the following error message

ERROR: syntax error at or near "-"
LINE 1: CREATE DATABASE sample-database-name WITH TEMPLATE = templat...

I believe this is due to the - in the name as others without this work fine, so is there a way to escape the the database name in the command to allow these names to be passed in from the script? Ideally I'm looking for a solution which I doesn't require me to check for problematic characters and simply make the name as escaped in some fashion.

UPDATE:

This is the function in the script that handles the database creation call

create_new_database() {
 local old_db=1ドル
 local new_db="${old_db}_new"
 psql -c "CREATE DATABASE $new_db WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';"
}

• Sahap Asci: You can also use createdb client application for creating database using shell.

Answer 1

You need ro escape the database name or simply don't use such characters

psql -c 'CREATE DATABASE "sample-database-name" WITH TEMPLATE = template0;"

Your command in bash would look like where you have to escape the double quotes

create_new_database() {
 local old_db=1ドル
 local new_db="${old_db}_new"
 psql -c "CREATE DATABASE \"$new_db\" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';"
}

• Mr-F: Thank you for this and yes this works from the command line, but doesn't actually solve the issue when running in a script. My apologies I should have provided the script function to give you full context (which I've updated the original post to include now). I tried psql -c 'CREATE DATABASE "$new_db" WITH TEMPLATE = template0 ENCODING = "UTF8" LC_COLLATE = "en_US.UTF-8" LC_CTYPE = "en_US.UTF-8";' but that didn't work. Finally yes I agree about the names, but that isn't something i have control over so can't change that.
• Rabban Keyak: @Mr-F psql -c 'CREATE DATABASE "'$new_db'" WITH TEMPLATE = template0 ENCODING = "UTF8" LC_COLLATE = "en_US.UTF-8" LC_CTYPE = "en_US.UTF-8";'
• nbk: @Mr-F i added you bash command with escaped double quotes