15

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)
aaa90210
  • 11,295
  • 13
  • 51
  • 88
  • 1
    [edit] your question and add the `create schema` statement and the statement that generates the error. –  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
  • 1
    @VaoTsun it should, but it doesn't. – aaa90210 May 19 '17 at 07:26
  • 1
    please `psql` and `\dn` and put result to the post – Vao Tsun May 19 '17 at 07:26
  • `"FOO"` is not the same as `foo`: https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS –  May 19 '17 at 07:29
  • If `CREATE TABLE "FOO"."Events"` worked "fine" then `select * from "FOO"."Events";` **will** work. –  May 19 '17 at 07:29
  • @VaoTsun ok it is there – aaa90210 May 19 '17 at 07:30
  • 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

1 Answers1

9

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

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • 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