Create New Database and a Schema in new database using Sql Script in PostgreSQL

Question

I am trying to create a new database and a schema in the new database using Sql script as below. The database is PostgreSql 14.1

    DROP DATABASE IF EXISTS DEZDAZ;
    
    CREATE DATABASE DEZDAZ
        WITH 
        OWNER = postgres
        ENCODING = 'UTF8'
        TABLESPACE = pg_default
        CONNECTION LIMIT = -1;
      
        \connect DEZDAZ;

        CREATE SCHEMA DEZDAZ;

Keep getting below error:

DROP DATABASE
CREATE DATABASE
error: \connect: connection to server at "localhost" (::1), port 5432 failed: FATAL:  database "DEZDAZ" does not exist

I have followed the answer below: PostgreSQL: Create schema in specific database

Thanks for your help.

Try `\connect dezdaz`. You did not do `CREATE DATABASE "DEZDAZ"` so the database name is folded to lower case `dezdaz` per [Identifiers](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS) Looks like `\connect` is doing `"DEZDAZ"` and not finding the upper case version of the name. — Adrian Klaver, Jan 18 '22 at 20:31
that was it @Adrian Klaver. Please post it as answer, will accept it. — dossani, Jan 18 '22 at 20:34

score 5 · Accepted Answer · answered Jan 18 '22 at 20:39

Try:

\connect dezdaz.

You did not do CREATE DATABASE "DEZDAZ" so the database name is folded to lower case dezdaz per Identifiers. Looks like \connect is doing "DEZDAZ" and not finding the upper case version of the name. This is called out in src/bin/psql/command.c:

/*
 * Read and interpret an argument to the \connect slash command.
...

/*
         * Ideally we should treat the arguments as SQL identifiers.  But for
         * backwards compatibility with 7.2 and older pg_dump files, we have to
         * take unquoted arguments verbatim (don't downcase them). For now,
         * double-quoted arguments may be stripped of double quotes (as if SQL
         * identifiers).  By 7.4 or so, pg_dump files can be expected to
         * double-quote all mixed-case \connect arguments, and then we can get rid
         * of OT_SQLIDHACK.
         */

Create New Database and a Schema in new database using Sql Script in PostgreSQL

1 Answers1