How to Fix PostgreSQL Error: Database Does Not Exist

The "Database does not exist" error occurs when attempting to connect to or query a PostgreSQL database that hasn't been created. This is one of the most common errors for new PostgreSQL users and typically happens during initial setup or when deploying applications.

Impact

This error prevents any database operations from occurring, causing complete application failure. It can block deployments, prevent development work, and halt production services if databases are accidentally dropped or not properly initialized.

Common Causes

1. Database has not been created yet
2. Typo in database name
3. Wrong database cluster being accessed
4. Database was dropped or deleted
5. Application configuration pointing to wrong database name
6. Default database name assumption (e.g., trying to connect to a database named after username)
7. Case sensitivity issues in database names

Troubleshooting and Resolution Steps

1. List all existing databases:

   -- Connect to default postgres database
   sudo -u postgres psql
   
   -- List databases
   \l
   
   -- Or use SQL query
   SELECT datname FROM pg_database ORDER BY datname;
   

2. Create the missing database:

   -- Basic database creation
   CREATE DATABASE mydb;
   
   -- Create database with specific owner
   CREATE DATABASE mydb OWNER myuser;
   
   -- Create database with encoding and locale
   CREATE DATABASE mydb
       OWNER myuser
       ENCODING 'UTF8'
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       TEMPLATE template0;
   

3. Create database from command line:

   # Using createdb utility
   sudo -u postgres createdb mydb
   
   # With specific owner
   sudo -u postgres createdb -O myuser mydb
   
   # With template
   sudo -u postgres createdb -T template0 mydb
   

4. Verify database name in connection string:

   # psql connection
   psql -h localhost -U myuser -d mydb
   
   # Connection string
   postgresql://myuser:password@localhost:5432/mydb
   
   # Environment variable
   export DATABASE_URL="postgresql://myuser:password@localhost:5432/mydb"
   

5. Check application configuration files:

   # Example: config.yml
   database:
     host: localhost
     port: 5432
     name: mydb        # Ensure this matches created database
     user: myuser
     password: secure_password
   

6. Create database from backup:

   # Create database
   createdb mydb
   
   # Restore from SQL dump
   psql mydb < backup.sql
   
   # Or restore from custom format
   pg_restore -d mydb backup.dump
   

7. Check database name case sensitivity:

   -- Unquoted names are converted to lowercase
   CREATE DATABASE MyDB;  -- Creates "mydb"
   
   -- Quoted names preserve case
   CREATE DATABASE "MyDB";  -- Creates "MyDB"
   
   -- Check actual database names
   SELECT datname FROM pg_database WHERE datname ILIKE '%mydb%';
   

8. Grant connection privileges:

   -- Grant connect privilege
   GRANT CONNECT ON DATABASE mydb TO myuser;
   
   -- Revoke public access if needed
   REVOKE CONNECT ON DATABASE mydb FROM PUBLIC;
   

9. Verify you're connecting to correct PostgreSQL cluster:

   # List PostgreSQL clusters
   pg_lsclusters
   
   # Check running clusters
   sudo systemctl status postgresql@15-main
   

10. Set up database initialization script:

    #!/bin/bash
    # init_db.sh
    
    DB_NAME="mydb"
    DB_USER="myuser"
    DB_PASS="secure_password"
    
    # Create user if not exists
    sudo -u postgres psql -c "CREATE ROLE $DB_USER WITH LOGIN PASSWORD '$DB_PASS';" 2>/dev/null
    
    # Create database if not exists
    sudo -u postgres psql -c "CREATE DATABASE $DB_NAME OWNER $DB_USER;" 2>/dev/null
    
    echo "Database setup complete"
    

Additional Information

• Every PostgreSQL installation comes with three default databases: postgres, template0, and template1
• The postgres database is meant for administrative tasks
• template1 is the default template for new databases
• template0 is a pristine copy that should never be modified
• Database names are case-insensitive unless quoted during creation
• Use database templates to standardize new database creation
• Implement automated database initialization in deployment scripts

Frequently Asked Questions

Q: What database should I connect to if I just installed PostgreSQL?
A: Connect to the default postgres database: psql -U postgres -d postgres

Q: Can I rename a database?
A: Yes, use ALTER DATABASE old_name RENAME TO new_name; Note: no users can be connected to the database during the rename.

Q: How do I create a database with a specific character encoding?
A: Use CREATE DATABASE mydb ENCODING 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TEMPLATE template0;

Q: What's the difference between template0 and template1?
A: template1 is the default template that can be modified to add default objects to all new databases. template0 is a pristine template that should never be modified and is used when you need a clean database with specific encoding.

Q: How do I check the owner of a database?
A: Use \l in psql or query: SELECT datname, pg_catalog.pg_get_userbyid(datdba) AS owner FROM pg_database;

Q: Can I create a database from an application?
A: Yes, but the connecting user needs the CREATEDB privilege:

import psycopg2
conn = psycopg2.connect("postgresql://postgres@localhost/postgres")
conn.autocommit = True
cursor = conn.cursor()
cursor.execute("CREATE DATABASE mydb")

Q: Why can't I drop a database while connected to it?
A: PostgreSQL doesn't allow dropping the current database. Connect to a different database (like postgres) first, then drop the target database.