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 Looks like \connect is doing "DEZDAZ" and not finding the upper case version of the name. — Adrian Klaver
– Adrian Klaver, Commented Jan 18, 2022 at 20:31
that was it @Adrian Klaver. Please post it as answer, will accept it. — dossani
– dossani, Commented Jan 18, 2022 at 20:34

Adrian Klaver · Accepted Answer · 2022-01-18 20:39:45Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related