CODEWITHSUNDEEP
postgresqlpsql
Vivek S.
Vivek S.

Reputation: 21895

How to Drop/Create Database name that has upper-case letter?

for example my Postgresql database name is Ajp

i cannot drop/create the db with this name ie. Ajp

my scripts for drop is

cd /D D:\\Apps
cd RSTAR PGSQL DOTCONNECT
cd bin
cd Debug
cd PG
psql -U postgres  -d postgres -c "DROP DATABASE  Ajp "

while executing this i got this error

D:\Apps\RSTAR PGSQL DOTCONNECT\bin\Debug\PG>psql -U postgres -d postgres -c "DR OP DATABASE Ajp " ERROR: database "ajp" does not exist

i tried psql -U postgres -d postgres -c "DROP DATABASE 'Ajp' " (db name within quotes)

again got error

ERROR: syntax error at or near "'Ajp'" LINE 1: DROP DATABASE 'Ajp'

how fix this problem ?? (please don't comment that change your dbname to lower-case)

using : "PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 32-bit"

the solution for this problem is below

Upvotes: 33

Views: 28848

Answers (4)

Evandro Giachetto
Evandro Giachetto

Reputation: 1

have you tried this?

psql -U postgres  -d postgres -c "DROP DATABASE  ""Ajp"" "

Upvotes: 0

sutyak
sutyak

Reputation: 45

I'm sure someone will come across this question using PowerShell, which also requires the backslash and double quote. Both need escaped with a tick. (thanks pozs)

(Reading from an Xml config file)

$PSScriptRoot = Split-Path $MyInvocation.MyCommand.Path -Parent
[xml]$config = Get-Content -Path "$PSScriptRoot\Installer\Config\install.config.xml"
$securePass = Read-Host "Enter password for postgres account" -AsSecureString
$bstr = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($securePass)
$pgPass = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($bstr)
$env:PGPASSWORD = $pgPass
# Skip to here for usage:
$dropDbCmd = "DROP DATABASE IF EXISTS `\`"" + $config.Settings.dbName + "`\`";"
.\psql.exe -c $dropDbCmd -d "postgres" -p $config.Settings.dbPort -U "postgres"

Upvotes: 1

Roushan Jha
Roushan Jha

Reputation: 27

It's old question but, it may help someone else. Try this:

echo "CREATE DATABASE \\"DataBaseName\\" ;" | psql --host=xxxx.postgres.database.azure.com --port=5432 --username=xxxx@xxxx --dbname=postgres

Upvotes: 0

user330315
user330315

Reputation:

SQL identifiers that are case-sensitive need to be enclosed in double quotes:

DROP DATABASE  "Ajp";

Not sure if they are properly preserved on Windows if you pass them through the commandline though.

You might need to put that into a SQL script and pass the script name to psql.

For details on valid identifiers please see the manual: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Upvotes: 56

Related Questions