Postgresql tables exist, but getting "relation does not exist" when querying

Case sensitive comrades and schema setup

In PostgreSQL, case makes a difference. Ensure the table name is in the correct case. Remember that PostgreSQL treats unquoted identifiers as lowercase. Here's the surprise:

-- Don't take it lightly, PostgreSQL does!
SELECT * FROM LIGHTbulb;


On the schema front, if no schema is specified, PostgreSQL checks the public schema by default:

-- If table is in public schema
SELECT * FROM your_table; 

-- If table is in different schema
SELECT * FROM other_schema.your_table;

-- Change the default search_path for the session
SET search_path TO other_schema;


Checking on citizenship and permissions

For a community to function smoothly, every member should have the right permissions. Similarly, the user should have permissions for the table:

-- Check if table's got the right passport (exists in schema)
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name = 'your_table';

-- Verify permissions
\dt your_schema.your_table


If the user has forgotten their passport (table exists, but permissions are inadequate), renew it:

-- Let's get the user across the border
GRANT SELECT ON your_table TO your_user;


The magic of search_path

Without the magic word (search_path), the unqualified table names go unnoticed. Make sure search_path includes all needed schemas:

SHOW search_path;


To set search_path for the future, use:

ALTER ROLE your_user SET search_path TO your_schema, public;


Schema usage: Creating your own playground

Creating a concentrated playground (private schema) helps you manage your toys (data) better:

-- Frame up a new playground
CREATE SCHEMA private;

-- Set the ground rules
SET search_path TO private;


Keep the public schema to a minimum to prevent unwanted toy theft (data exposure)!

When identifiers play hide and seek

PostgreSQL's case sensitivity can spark a game of hide and seek with identifiers:

-- Mixed case identifier (let gaming begin), requires quotes
SELECT * FROM "MixedCaseTable";


Remember, no quotes = lowercase:

CREATE TABLE "SensitiveTable" ...
SELECT * FROM sensitivetable; -- Where is "SensitiveTable"?! Found hide and seek champion! 🏆


How to find hidden treasure with information_schema

information_schema.columns allows you to find hidden columns in clear daylight:

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'your_table' AND table_schema = 'your_schema';


This helps in cases where a column seems to be playing a game of invisibility.