Missing table in PostgreSQL database

Question

I was able to connect to the database, write the table, read it, but the dbGetQuery command gives an error Error: Failed to prepare query: ERROR: relation "table" does not exist LINE 1: SELECT DISTINCT ym.s.date FROM table. And the dbListTables command returns character(0) and dbExistsTable(conn = con, name ="table") [1] FALSE. That is, if the table is not in the database. Can you please tell me why this is happening and how to solve this problem?

there is no solution to the problem

Answer 1

General problem so I will give you some hint.

Try to understand your PSQL structure database using the following:

\l - Display database
\c - Connect to database
\dn - List schemas
\dt - List tables inside public schemas
\dt schema1. - List tables inside particular schemas. For eg: 'schema1'.

Most of the cases you are not using the correct schema

Answer 2

Up front, try each of these:

dbGetQuery(con, 'select * from "abc"."ABC_01"')
dbGetQuery(con, 'select * from "ABC"."ABC_01"')

My path of exploration:

DBI::dbExecute(db, "create schema ABC")
dbWriteTable(db, Id(schema="ABC",table="ABC_01"), mtcars, create=TRUE, append=FALSE)
# Error: nanodbc/nanodbc.cpp:1691: 3F000: ERROR: schema "ABC" does not exist;
# Error while executing the query   RROR: schema "ABC" does not exist;
# Error while executing the query 
dbWriteTable(db, Id(schema="abc",table="ABC_01"), mtcars, create=TRUE, append=FALSE)
### (no error)
dbGetQuery(db, "select distinct table_schema, table_name from information_schema.columns where table_name ilike '%abc%'")
#   table_schema table_name
# 1          abc     ABC_01

First hint: often databases disregard the case when we are not explicit about it. In this case, while I think I created schema ABC, postgres silently downcased it.

From there,

dbGetQuery(db, "select * from ABC_01 limit 2")
# Error: nanodbc/nanodbc.cpp:1752: 42P01: ERROR: relation "abc_01" does not exist;
# Error while preparing parameters   RROR: relation "abc_01" does not exist;
# Error while preparing parameters 
# <SQL> 'select * from ABC_01 limit 2'
dbGetQuery(db, 'select * from ABC.ABC_01 limit 2')
# Error: nanodbc/nanodbc.cpp:1752: 42P01: ERROR: relation "abc.abc_01" does not exist;
# Error while preparing parameters   RROR: relation "abc.abc_01" does not exist;
# Error while preparing parameters 
# <SQL> 'select * from ABC.ABC_01 limit 2'
dbGetQuery(db, 'select * from abc.ABC_01 limit 2')
# Error: nanodbc/nanodbc.cpp:1752: 42P01: ERROR: relation "abc.abc_01" does not exist;
# Error while preparing parameters   RROR: relation "abc.abc_01" does not exist;
# Error while preparing parameters 
# <SQL> 'select * from abc.ABC_01 limit 2'

dbGetQuery(db, 'select * from "abc"."ABC_01" limit 2')
#       row_names mpg cyl disp  hp drat    wt  qsec vs am gear carb
# 1     Mazda RX4  21   6  160 110  3.9 2.620 16.46  0  1    4    4
# 2 Mazda RX4 Wag  21   6  160 110  3.9 2.875 17.02  0  1    4    4

Further:

dbExecute(db, 'drop table "ABC"."ABC_01"')
dbExecute(db, 'drop schema "ABC"')
dbExecute(db, 'create schema "ABC"')
dbWriteTable(db, Id(schema="ABC",table="ABC_01"), mtcars, create=TRUE, append=FALSE)
dbGetQuery(db, "select * from ABC.ABC_01 limit 2")
# Error: nanodbc/nanodbc.cpp:1752: 42P01: ERROR: relation "abc.abc_01" does not exist;
# Error while preparing parameters   RROR: relation "abc.abc_01" does not exist;
# Error while preparing parameters 
# <SQL> 'select * from ABC.ABC_01 limit 2'
dbGetQuery(db, 'select * from "ABC"."ABC_01" limit 2')
#       row_names mpg cyl disp  hp drat    wt  qsec vs am gear carb
# 1     Mazda RX4  21   6  160 110  3.9 2.620 16.46  0  1    4    4
# 2 Mazda RX4 Wag  21   6  160 110  3.9 2.875 17.02  0  1    4    4

dbGetQuery(db, "select distinct table_schema, table_name from information_schema.columns where table_name ilike '%abc%'")
#   table_schema table_name
# 1          ABC     ABC_01