How to fix ERROR: schema "schema_name" does not exist in PostgreSQL

# Connect to your database
psql -h localhost -U postgres -d mydatabase

-- List all schemas
\dn

-- Or query the catalog
SELECT schema_name FROM information_schema.schemata;

-- List tables in public schema
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

-- List tables in a custom schema
SELECT table_name FROM information_schema.tables WHERE table_schema = 'myschema';

CREATE SCHEMA myschema;

CREATE SCHEMA IF NOT EXISTS myschema;

CREATE SCHEMA myschema AUTHORIZATION myuser;

psql -h localhost -U postgres -d mydatabase -c "CREATE SCHEMA IF NOT EXISTS app AUTHORIZATION appuser;"

\dn
-- Should show your new schema in the list

-- Correct: schema.table format
SELECT * FROM myschema.mytable;

-- For mixed-case schema names (must be quoted)
SELECT * FROM "MySchema".mytable;

-- Insert data
INSERT INTO myschema.mytable (col1, col2) VALUES (1, 'test');

-- Update data
UPDATE myschema.mytable SET col1 = 2 WHERE id = 1;

-- Create foreign key with schema
ALTER TABLE myschema.orders
ADD FOREIGN KEY (customer_id) REFERENCES myschema.customers(id);

-- For the current session only
SET search_path TO myschema, public;

-- Then you can omit the schema prefix
SELECT * FROM mytable;  -- Now searches myschema.mytable first, then public.mytable

SHOW search_path;
-- Default is: "$user", public

# Edit the config file
sudo nano /etc/postgresql/16/main/postgresql.conf

# Find and modify the search_path parameter
search_path = 'myschema, public'

# Restart PostgreSQL for changes to take effect
sudo systemctl restart postgresql

-- Create schema with mixed case (actually creates "myschema")
CREATE SCHEMA MySchema;

-- This fails - looking for "myschema"
SELECT * FROM MySchema.mytable;  -- ERROR: schema "myschema" does not exist

-- Create schema with explicit mixed-case (must use quotes)
CREATE SCHEMA "MySchema";

-- Reference with quotes (required)
SELECT * FROM "MySchema".mytable;  -- Works!

-- Or convert to lowercase for convenience
CREATE SCHEMA myschema;
SELECT * FROM myschema.mytable;  -- Works!

CREATE SCHEMA myapp_schema;
CREATE TABLE myapp_schema.users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255)
);

-- No quoting needed
SELECT * FROM myapp_schema.users;

-- See exact schema names (with their true casing)
SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE '%schema%';

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'mydatabase',
  user: 'postgres',
  password: 'password',
});

// Option 1: Qualify all table names
const result = await pool.query('SELECT * FROM myschema.users');

// Option 2: Set search path when getting a connection
const client = await pool.connect();
await client.query('SET search_path TO myschema, public');
const result = await client.query('SELECT * FROM users');
client.release();

// In schema.prisma, set the schema
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["myschema"]  // Specify the schema
}

model User {
  id    Int     @id @default(autoincrement())
  name  String

  @@schema("myschema")  // Explicitly map to schema
}

DATABASE_URL="postgresql://user:password@localhost:5432/mydatabase?schema=myschema"

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydatabase',
        'USER': 'postgres',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': '5432',
        'OPTIONS': {
            'options': '-c search_path=myschema,public'
        }
    }
}

from sqlalchemy import create_engine, event
from sqlalchemy.pool import StaticPool

engine = create_engine(
    'postgresql://user:password@localhost/mydatabase',
    poolclass=StaticPool
)

# Set search path on connection
@event.listens_for(engine, "connect")
def receive_connect(dbapi_conn, connection_record):
    dbapi_conn.cursor().execute("SET search_path TO myschema,public")

# Create V1__CreateSchema.sql
CREATE SCHEMA IF NOT EXISTS myapp;

# Then create tables in subsequent migrations
# V2__CreateTables.sql
CREATE TABLE myapp.users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255)
);

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog>
  <changeSet id="1" author="dev">
    <sql>CREATE SCHEMA IF NOT EXISTS myapp</sql>
  </changeSet>

  <changeSet id="2" author="dev">
    <createTable schemaName="myapp" tableName="users">
      <column name="id" type="SERIAL">
        <constraints primaryKey="true"/>
      </column>
      <column name="name" type="VARCHAR(255)"/>
    </createTable>
  </changeSet>
</databaseChangeLog>

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["myapp"]
}

npx prisma migrate dev --name create_schema_and_tables

# init-schema.sql
CREATE SCHEMA IF NOT EXISTS myapp;
CREATE TABLE IF NOT EXISTS myapp.users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255)
);

FROM postgres:16
COPY init-schema.sql /docker-entrypoint-initdb.d/