Schema.Table in Postgres

Question

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?

blami · Accepted Answer · 2020-04-21T03:56:14.357

0

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

edited Apr 21 '20 at 03:56

answered Apr 21 '20 at 02:46

blami

6,588
2
23
31

I already tried to set the search_path. I ran show search_path and it says "public" - which is correct. But when I run the query it still fails. – anhtle Apr 21 '20 at 03:08
@anhtle I've seen your comment. There must be some mistake somewhere as public is nothing but ordinary schema. Can you post output of `SHOW search_path;` after setting it when it does not work? – blami Apr 21 '20 at 03:11
here's my result ````portfolio=# SHOW search_path; search_path ------------- public (1 row) ```` – anhtle Apr 21 '20 at 03:35
That looks good, now when you type `\dt` to display all tables what do you get? – blami Apr 21 '20 at 03:39
I suspect your problem isn't really search_path but use of quotes (they have special meaning in PostgreSQL, if not quoted all is converted to lowercase so I guess your table is called TRANSACTIONS and thus not seen. – blami Apr 21 '20 at 03:41
yes! indeed my table is called TRANSACTIONS. I created a new table called "transactions" and everything is okay. Thank you so much! – anhtle Apr 21 '20 at 03:56
I updated my answer to reflect that too. Glad it works! – blami Apr 21 '20 at 03:56

score 0 · Answer 2 · answered Apr 21 '20 at 03:42

Looks like you are having trouble with double quoting identifiers ("), which should be avoided if at all possible. If an identifier is double quoted it MUST ALWAYS be double quoted. Thus the the following statements are not the same: Select * from TRANSACTIONS; and Select * from "TRANSACTIONS";

Since public."TRANSACTIONS" works for you try double quoting without the schema:

select * from "TRANSACTIONS";

If that works then make sure to always double quote. Or better, before too far into it, rename it eliminating double quotes.

Schema.Table in Postgres

2 Answers2