How to fix ERROR: index 'indexname' does not exist in PostgreSQL

-- List all indexes for a specific table:
SELECT indexname, schemaname, tablename FROM pg_indexes WHERE tablename = 'users';

-- Find a specific index by partial name:
SELECT indexname, schemaname FROM pg_indexes WHERE indexname ILIKE '%email%';

-- Show index details including if it is unique or primary:
SELECT * FROM pg_indexes WHERE schemaname = 'public' ORDER BY tablename, indexname;

-- Safe index drop (does not error if index missing):
DROP INDEX IF EXISTS users_email_idx;

-- Drop with CASCADE to remove dependent objects:
DROP INDEX IF EXISTS users_email_idx CASCADE;

-- Safe index rename:
ALTER INDEX IF EXISTS old_index_name RENAME TO new_index_name;

-- Safe reindex:
REINDEX INDEX IF EXISTS users_email_idx;

-- This creates an index stored as "myindex" (lowercase):
CREATE INDEX myindex ON users(email);

-- This creates an index stored as "MyIndex" (preserves case):
CREATE INDEX "MyIndex" ON users(email);

-- To reference the quoted index, you MUST use quotes:
DROP INDEX IF EXISTS "MyIndex";  -- Works
DROP INDEX IF EXISTS MyIndex;     -- Fails (looks for "myindex")

-- Best practice: avoid quotes and use lowercase names:
CREATE INDEX users_email_idx ON users(email);
DROP INDEX IF EXISTS users_email_idx;  -- Always works

-- List indexes in a specific schema:
SELECT indexname FROM pg_indexes WHERE schemaname = 'myschema';

-- Drop an index from a non-public schema:
DROP INDEX IF EXISTS myschema.users_email_idx;

-- Rename an index from a specific schema:
ALTER INDEX IF EXISTS myschema.old_name RENAME TO new_name;

-- View current search path (schemas searched without qualification):
SHOW search_path;

-- Compare index lists between databases:
psql -U postgres -d prod_db -c "SELECT indexname FROM pg_indexes WHERE schemaname = 'public' ORDER BY indexname;" > prod_indexes.txt
psql -U postgres -d staging_db -c "SELECT indexname FROM pg_indexes WHERE schemaname = 'public' ORDER BY indexname;" > staging_indexes.txt
diff prod_indexes.txt staging_indexes.txt

-- Restore from backup if indexes were accidentally dropped:
pg_restore --dbname=mydb --data-only /path/to/backup.sql

// Example Prisma migration (prisma/migrations/xxx_safe_index_drop/migration.sql):
DROP INDEX IF EXISTS users_email_idx;
DROP INDEX IF EXISTS users_created_at_idx;
DROP INDEX IF EXISTS posts_user_id_idx;

CREATE INDEX users_username_idx ON public.users (username);
CREATE INDEX posts_published_idx ON public.posts (published_at DESC NULLS LAST);