How to fix ERROR: invalid object name in PostgreSQL

-- List all tables in the current schema
\dt

-- List all views
\dv

-- List all schemas
\dn

-- List all functions
\df

-- List all indexes
\di

-- View table structure with exact column names and types
\d your_table_name

-- Search for a table by pattern
\dt public.*your_pattern*

CREATE TABLE "UserAccounts" (id BIGINT);  -- Name preserved as "UserAccounts"
CREATE SCHEMA "MySchema";                  -- Name preserved as "MySchema"

-- These work - exact match
SELECT * FROM "UserAccounts";
SELECT * FROM "MySchema".users;

-- These fail - PostgreSQL looks for lowercase
SELECT * FROM useraccounts;        -- ERROR: relation "useraccounts" does not exist
SELECT * FROM myschema.users;      -- ERROR: schema "myschema" does not exist

CREATE TABLE user_accounts (id BIGINT);  -- Always referenced as lowercase
SELECT * FROM user_accounts;              -- Always works

-- Wrong - typo in table name
SELECT * FROM user_acounts;     -- Missing 'c'
-- ERROR: relation "user_acounts" does not exist

-- Correct
SELECT * FROM user_accounts;

-- Wrong - typo in schema name
SELECT * FROM publiv.users;     -- 'publiv' instead of 'public'
-- ERROR: schema "publiv" does not exist

-- Correct
SELECT * FROM public.users;

-- Without schema (searches search_path, may not find object)
SELECT * FROM users;

-- With schema (explicit and reliable)
SELECT * FROM public.users;

-- Using a custom schema
SELECT * FROM my_app_schema.users;

-- Multiple schemas in one query
SELECT u.id, p.product_name
FROM public.users u
JOIN my_app_schema.products p ON u.id = p.user_id;

-- View current search_path
SHOW search_path;

-- Modify search_path for the current session
SET search_path TO public, my_app_schema;

-- Verify the change
SHOW search_path;

-- Check if a table still exists
SELECT EXISTS (
  SELECT 1 FROM information_schema.tables
  WHERE table_schema = 'public'
    AND table_name = 'users'
);

-- Check if an index still exists
SELECT EXISTS (
  SELECT 1 FROM information_schema.statistics
  WHERE table_schema = 'public'
    AND table_name = 'users'
    AND index_name = 'users_email_idx'
);

-- Check for recently dropped objects in logs
-- (if you have pg_stat_statements enabled)

-- Old query with dropped column
SELECT * FROM user_status;        -- ERROR: invalid object name

-- After schema change, use new name
SELECT * FROM user_status_codes;  -- Updated to new table name

-- List tables visible to the current user
\dt

-- Check if a specific object is visible
SELECT EXISTS (
  SELECT 1 FROM information_schema.tables
  WHERE table_schema = 'public'
    AND table_name = 'users'
    AND table_type = 'BASE TABLE'
);

-- View table privileges
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
  AND table_name = 'users';

-- Grant permissions if needed
GRANT SELECT ON public.users TO my_user;
GRANT USAGE ON SCHEMA public TO my_user;

-- Check current database
SELECT current_database();

-- List all databases
\l

-- Connect to a specific database
\c my_database

-- Reconnect with connection string
psql "postgresql://user:password@localhost:5432/my_database"