Sample Header Ad - 728x90

Help with creating postgres databases with - in the name

0 votes
1 answer
145 views
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';"
}
Asked by Mr-F (101 rep)
Sep 7, 2024, 11:27 PM
Last activity: Sep 11, 2024, 04:47 PM