AWS Aurora PostgreSQL使用psycopg2查询表提示relation不存在问题

Let's break down the possible issues and fixes for this frustrating problem—since you can see the tables in information_schema.tables but can't query them directly, there are a few less obvious things to check:

1. Case Sensitivity is Probably the Culprit #

PostgreSQL automatically converts unquoted table names to lowercase, but if your table was created with double quotes (e.g., CREATE TABLE "Table_Name"), you must use quotes when querying it. Skipping quotes makes PostgreSQL look for table_name (all lowercase), which doesn't exist.

• Quick test: Run SELECT * FROM "Table_Name"; in your psycopg2 code.
• Permanent fix: Rename the table to avoid case sensitivity headaches:

  ALTER TABLE "Table_Name" RENAME TO table_name;
  

2. Your psycopg2 Connection's search_path Might Be Wrong #

Even if you set a default schema at the database level, psycopg2 can override this setting, or your connection might not be including the target schema in its search path.

• First, check your current search path after connecting:

  cur.execute("SHOW search_path;")
  print(cur.fetchone())
  

• If your target schema isn't listed, fix it in one of two ways:
  • Add it to your connection string:

    conn = psycopg2.connect(
        dbname="your_db",
        user="your_user",
        password="your_pass",
        host="your_aurora_host",
        options="-c search_path=your_schema,public"
    )
    

  • Or set it after connecting:

    cur.execute("SET search_path TO your_schema,public;")
    

3. Double-Check the Table's Actual Schema Location #

It's easy to misread the table_schema value in information_schema.tables, especially if there are duplicate table names across different schemas.

• Run this query to get the exact schema and table name (covers both case-sensitive and lowercase names):

  SELECT table_schema, table_name 
  FROM information_schema.tables 
  WHERE table_name IN ('Table_Name', 'table_name');
  

• Then query using the fully qualified name (adjust quotes as needed):

  SELECT * FROM your_schema."Table_Name";
  

4. Verify Role Permissions (Beyond Basic SELECT) #

You might have SELECT on the table, but if your role lacks USAGE permission on the schema containing the table, PostgreSQL won't let you access it.

• Check if your role has schema usage access:

  SELECT has_schema_privilege('your_role', 'your_schema', 'USAGE');
  

• Check table select access (use quotes if needed):

  SELECT has_table_privilege('your_role', 'your_schema."Table_Name"', 'SELECT');
  

• If either returns f, grant the missing permissions:

  GRANT USAGE ON SCHEMA your_schema TO your_role;
  GRANT SELECT ON TABLE your_schema."Table_Name" TO your_role;
  

5. Aurora-Specific Replication/Caching Delays #

If you're connecting to an Aurora read replica, there might be a brief replication delay where the table exists on the primary but hasn't propagated to the replica yet.

• Try connecting directly to the Aurora primary instance and running your query.
• If that works, wait a minute or two for replication to catch up, or refresh the schema cache on the replica:

  SELECT pg_stat_clear_snapshot();
  

内容的提问来源于stack exchange，提问作者Erik Tavares