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

Try running a query to see what the value table names are: https://stackoverflow.com/questions/43720911/list-tables-within-a-postgres-schema-using-r Do you really have a table named "table"? — MrFlick, Apr 17 '23 at 13:56
I'll add it if it turns out to be important. I'm running version 15.00.02, using the default settings host=localhost, port=5433. And yet it doesn’t fit in my head how such a situation arises when there is a connection, writing and reading tables are possible, and working with queries is impossible — KOs, Apr 18 '23 at 11:11

score 1 · Answer 1 · answered Apr 17 '23 at 12:59

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

answered Apr 17 '23 at 12:59

jacorl

396
2
9

I'm just getting started with PostgreSQL. Tell me how to do it? – KOs Apr 18 '23 at 11:06
I was able to get a list of schemas with the \dn command. but when I request \dt, I get the answer relationship not found – KOs Apr 19 '23 at 14:45

r2evans · Answer 2 · 2023-04-19T12:37:14.723

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

For example, I specified the table name Table. In fact, the table has a different name. — KOs, Apr 18 '23 at 11:07
Then it seems clear to me you're either connecting to the wrong _database_ (perhaps the right _server_) or you don't have permissions for that "table". Talk to your DBA, it's likely they're the only ones who can know more than what you've told us. — r2evans, Apr 18 '23 at 11:29
the bottom line is that I created the database myself on my local computer — KOs, Apr 18 '23 at 12:37
I'll keep digging: (1) How did you create/instantiate the server? (2) How did you create the _database_ within the server? (3) How did you create the table? (4) How are you connecting? (All of these are requests for actual commands/code used, please.) — r2evans, Apr 18 '23 at 12:41
I would like to thank you for your help and your time! This is very valuable! 1. Installed PostgreSQL from the official site https://www.enterprisedb.com/downloads/postgres-postgresql-downloads. Created a username and password 2. pgAdmin4 has a username and password from the steps. 3. Installed DBeaver to work in the database. I also entered my username and password, installed the driver, and successfully checked the connection. — KOs, Apr 18 '23 at 13:56
4. Next, I created a new scheme by selecting the appropriate item in the context menu. 5. After processing the data in RSTudio, making a connection to the database using the function con = dbConnect(RPostgres::Postgres(),dbname="postgres", port=5433, user="postgres", password="password") and wrote the data.frame using the command dbWriteTable(con,Id(schema ="ABC",table = "ABC_01"),ABC_01,append=TRUE) to the database. 6. Checked in DBeaver that a new table named ABC_01 in schema ABC has been created. The requests are sent successfully and the result is correctly returned. — KOs, Apr 18 '23 at 13:56
Try using `ABC.ABC_01` instead of just the tablename, something like `"select * from ABC.ABC_01 limit 2"`. — r2evans, Apr 18 '23 at 16:27
I was able to get a list of schemas with the \dn command. but when I request \dt, I get the answer relationship not found — KOs, Apr 19 '23 at 14:45

Missing table in PostgreSQL database

2 Answers2