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

Questions

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

January 18, 2022

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.

>Solution :

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.
         */