How to fix relation "table_name" does not exist in PostgreSQL

# Connect to PostgreSQL
psql -U username -d database_name -h localhost

# List all tables in current schema
\dt

# Or show tables in a specific schema
\dt schema_name.*

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'my_schema'
ORDER BY table_name;

-- Find tables matching a pattern (case-insensitive)
SELECT schemaname, tablename FROM pg_tables
WHERE tablename ILIKE 'users%';

schemaname | tablename
------------+-----------
public     | users
public     | users_audit

-- If created without quotes (case-insensitive)
CREATE TABLE users (...);
SELECT * FROM users;    -- works
SELECT * FROM USERS;    -- also works
SELECT * FROM Users;    -- also works

-- If created WITH quotes (case-sensitive)
CREATE TABLE "Users" (...);
SELECT * FROM "Users";  -- must match exact case
SELECT * FROM "users";  -- fails with 42P01

-- Query fails if table is in app_schema, not public
SELECT * FROM orders;

-- Correct: fully qualify the name
SELECT * FROM app_schema.orders;

-- View current search_path
SHOW search_path;

-- Set it for this session
SET search_path TO app_schema, public;

-- Now unqualified names search in app_schema first
SELECT * FROM orders;  -- finds app_schema.orders

ALTER ROLE your_user SET search_path TO app_schema, public;

# Check current connection in psql
\c

# Output shows:
# You are connected to database "mydb" as user "myuser" on host "localhost"

# Connect to a different database
\c different_database

# Or from command line
psql -U myuser -d correct_database -h 127.0.0.1 -p 5432

// Node.js with pg library
const client = new Client({
  host: 'localhost',
  port: 5432,
  database: 'correct_database',  // Verify this
  user: 'myuser',
  password: 'password',
});

// Prisma
// DATABASE_URL=postgresql://user:password@localhost:5432/correct_database

-- Check what permissions your user has on the table
\dp table_name

-- Grant SELECT permission
GRANT SELECT ON table_name TO your_user;

-- Grant all permissions
GRANT ALL PRIVILEGES ON table_name TO your_user;

-- If table is in a non-public schema, also grant schema usage
GRANT USAGE ON SCHEMA schema_name TO your_user;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO your_user;

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'table_name';

// .env or .env.local
DATABASE_URL=postgresql://user:password@localhost/db?schema=public

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url = env("DATABASE_URL")
  // schema defaults to "public" but can be overridden
}

# If using non-public schema
engine = create_engine(
    'postgresql://user:password@localhost/db',
    connect_args={'options': '-c search_path=my_schema'}
)

createConnection({
  type: 'postgres',
  host: 'localhost',
  database: 'mydb',
  schema: 'my_schema',  // Specify if not public
  entities: [],
});

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE app_schema.users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL
);

# Generate migration based on schema.prisma
npx prisma migrate dev --name add_users_table

# Apply pending migrations
npx prisma migrate deploy

# Create and run migration
alembic upgrade head