Search code examples
sqlpostgresqlquoted-identifier

Schema.Table in Postgres

In Postgres, when I run any query using only the table name, I receive the error below:

ERROR:  relation "transactions" does not exist
LINE 2: SELECT * FROM TRANSACTIONS
                      ^
SQL state: 42P01
Character: 16

To get around that I need to use "schema.table" format - which makes the queries very long and clunky. 

SELECT * FROM public."TRANSACTIONS"

I only have 1 schema - public. I have already tried to set the search_path to public but it doesn't help. Any suggestion?

Solution

  • You can set search path:

    SET search_path TO public;

    If it doesn't work check what is your search path after setting by:

    SHOW search_path;

    See documentation: https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH

    Also note that double quoting object names in PostgreSQL matters. Maybe your search_path is correct but table was created as double quoted "TRANSACTIONS". PostgreSQL converts only unquoted names to lowercase (in all statements), so if you type SELECT FROM TRANSACTIONS it will become SELECT FROM transactions which will correctly yield error that transactions relation doesn't exist (only TRANSACTIONS does). You can check your table name as seen by PostgreSQL by running \dt - display tables (that will also prove your search_path is set correctly).

    TLDR; you don't want to double quote anything unless you have good reason for that.

    See documentation on quoting here: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS