postgres constantly gives "schema does not exist" errors

Question

I managed to create a table in a schema FOO.

Whenever I then try and do anything, like even a basic select, I just get:

ERROR: schema "FOO" does not exist
SQL state: 3F000
Character: 15

I am running the select in the same edit window as I created (using pgAdmin4). I get the same error when I try and create a view call FOO.Info. Yet when I try and create a new table in FOO it works.

What's going on? I am using the same syntax to refer to the table in the select as the create.

# worked fine
CREATE TABLE "FOO"."Events"
(
...

# all these have the error
select * from "FOO"."Events";
select * from FOO.Events;
select * from Foo.Events;

postgres=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 foo    | postgres
 public | postgres
(2 rows)

[edit] your question and add the `create schema` statement and the statement that generates the error. — a_horse_with_no_name, May 19 '17 at 07:18
@a_horse_with_no_name I created the schema using the GUI tool. I added the CREATE statement that works, then the select statement that dont work. — aaa90210, May 19 '17 at 07:25
`select * from "FOO"."Events";` should work - if you use double quotes on creation you should use them on all operations further — Vao Tsun, May 19 '17 at 07:25
`"FOO"` is not the same as `foo`: https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS — a_horse_with_no_name, May 19 '17 at 07:29
If `CREATE TABLE "FOO"."Events"` worked "fine" then `select * from "FOO"."Events";` **will** work. — a_horse_with_no_name, May 19 '17 at 07:29
You could try and find this schema name like that: `select oid::regnamespace from pg_namespace where nspname ilike '%foo%'`. It will double quote result if necessary. — Łukasz Kamiński, May 19 '17 at 07:30
schema name is "foo" as in my answer. `CREATE TABLE "FOO"."Events"` will fail. unless you run it in gui which perfors it own convertion. if so - put it aside and never use again :) — Vao Tsun, May 19 '17 at 07:30
The create table worked fine. The select and create view did not. — aaa90210, May 19 '17 at 08:41
A good additional note is If you have multiple instances of PG Admin open and you modify the search path then you should disconnect from the PG DB Server, and reconnect to the server, DB and the associated schema. — Rex Charles, Oct 02 '18 at 18:31
Another thing to check: Make sure you're running the query against the right database. :) — Eric Eskildsen, Mar 02 '20 at 14:41

score 5 · Answer 1 · answered May 19 '17 at 07:18

5

I believe you created it as

create schema FOO;

which creates schema "foo", not "FOO"

And then you reference it as

select * from "FOO".table_name

so it is not found

answered May 19 '17 at 07:18

Vao Tsun

42,665
8
85
115

1

no ive tried all case combination, and as I said, when I refer to the schema in a "create table" statement in exactly the same way, it works. – aaa90210 May 19 '17 at 07:18
1

ah. cool. so please put working and not working statements with error to the original post?.. – Vao Tsun May 19 '17 at 07:20

postgres constantly gives "schema does not exist" errors

1 Answers1