sqlpostgresqlcommandexecutecase-sensitive

PostgreSQL, case sensitive EXECUTE

The EXECUTE statement does not recognize upper case letters on the database name.

Running the below query on a database named "ABcdeFG"

DO
$c$
BEGIN
EXECUTE 'ALTER DATABASE ' || current_database() || ' SET client_encoding = $$LATIN1$$';
END;
$c$;

returns the following error:

"ERROR:  database "abcdefg" does not exist
CONTEXT:  SQL statement "ALTER DATABASE ABcdeFG SET client_encoding = $$LATIN1$$"
PL/pgSQL function inline_code_block line 3 at EXECUTE

********** Error **********

ERROR: database "abcdefg" does not exist
SQL state: 3D000
Context: SQL statement "ALTER DATABASE ABcdeFG SET client_encoding = $$LATIN1$$"
PL/pgSQL function inline_code_block line 3 at EXECUTE"

Is there a way for this command to be executed considering the upper and lower case letters in the database name, as informed?

Solution

  • You can use the quote_ident function to quote the database name, which will preserve the case.

    DO
$c$
BEGIN
EXECUTE 'ALTER DATABASE ' || quote_ident(current_database()) || ' SET 
client_encoding = $$LATIN1$$';
END;
$c$;