为何通过information_schema查到ids_table存在，但查询该表时提示不存在？

Problem Context #

I ran this query to pull a list of tables in my database:

SELECT table_name from information_schema.tables

And got this result:

But when I tried to query the table directly with:

SELECT * from ids_table

I hit this error:

Error: Failed to prepare query: ERROR: relation "ids_table" does not exist LINE 1: SELECT * from ids_table

Possible Causes & Fixes #

Let's break down the most common reasons this happens (especially in PostgreSQL, since the error message matches its syntax):

• The table lives in a different schema (not in your current search path)
  By default, information_schema.tables returns tables from all schemas in the database. If ids_table is stored in a non-default schema like analytics or archive, your current session's search_path might not include that schema. When you run an unqualified query like SELECT * from ids_table, PostgreSQL only looks in schemas listed in your search path.

  • Verify the table's schema: Run this to find where it lives:

    SELECT table_schema, table_name FROM information_schema.tables WHERE table_name = 'ids_table';
    

  • Fix it: Either qualify the table name with its schema (e.g., SELECT * from analytics.ids_table;) or add the schema to your search path:

    SET search_path TO public, analytics; -- replace "analytics" with your actual schema name
    

• Case sensitivity issues (table was created with double quotes)
  PostgreSQL treats unquoted identifiers (like table names) as case-insensitive—it auto-converts them to lowercase. But if the table was created with double quotes (e.g., CREATE TABLE "Ids_Table" (...)), its case is preserved exactly. The information_schema.tables result will show the precise case, but if you copied it as lowercase ids_table and query without quotes, PostgreSQL will look for a lowercase table that doesn't exist.

  • Check the exact table name: Run this to see the case-sensitive name:

    SELECT table_name FROM information_schema.tables WHERE table_name ILIKE 'ids_table';
    

  • Fix it: Query using double quotes around the exact, case-sensitive name:

    SELECT * from "Ids_Table"; -- match the case from the query result
    

• The table is a temporary table from another session
  Temporary tables are only visible in the database session where they were created. If you ran the first information_schema query in a different session where ids_table was a temp table, that table would no longer exist when you run the second query in your current session.

  • Check if it's a temp table: Run this to confirm:

    SELECT table_name, is_temporary FROM information_schema.tables WHERE table_name = 'ids_table';
    

  • Fix it: If it was a temp table, recreate it in your current session or switch to using a persistent table instead.

• Hidden whitespace or special characters in the table name
  It's possible the actual table name has trailing spaces, tabs, or invisible special characters (e.g., ids_table or ids_table\u0000). When you copied the name from information_schema, you might have missed these, leading to a mismatch when you query.

  • Check for hidden characters: Use this to see the exact length and hex representation of the name:

    SELECT table_name, length(table_name), encode(table_name::bytea, 'hex') FROM information_schema.tables WHERE table_name LIKE 'ids_table%';
    

  • Fix it: Either rename the table to remove special characters, or query using the exact name (including whitespace) wrapped in double quotes:

    SELECT * from "ids_table "; -- include the trailing space if present
    

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