英文:
Missing table in PostgreSQL database
问题
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?
数据库连接成功，成功写入表格并读取，但是dbGetQuery命令出现错误，报告Error: Failed to prepare query: ERROR: relation "table" does not exist LINE 1: SELECT DISTINCT ym.s.date FROM table. 而dbListTables命令返回character(0)，dbExistsTable(conn = con, name ="table") 返回[1] FALSE，这表示表格不在数据库中。请告诉我这是为什么以及如何解决这个问题？
这个问题没有解决办法。
英文:
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
答案1
得分: 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
英文:
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
</details>
# 答案2
**得分**: 0
以下是翻译好的部分：
```r
首先尝试每个：
dbGetQuery(con, 'select * from "abc"."ABC_01"')
dbGetQuery(con, 'select * from "ABC"."ABC_01"')
我的探索路径：
DBI::dbExecute(db, "create schema ABC")
dbWriteTable(db, Id(schema="ABC", table="ABC_01"), mtcars, create=TRUE, append=FALSE)
# 错误：nanodbc/nanodbc.cpp:1691: 3F000: 错误：模式 "ABC" 不存在;
# 在执行查询时出错 错误：模式 "ABC" 不存在;
# 在执行查询时出错
dbWriteTable(db, Id(schema="abc", table="ABC_01"), mtcars, create=TRUE, append=FALSE)
### (无错误)
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
第一个提示：通常情况下，数据库在我们没有明确说明大小写时会忽略大小写。在这种情况下，虽然我“认为”我创建了模式“ABC”，但 PostgreSQL 会悄悄地将其变成小写。
接着：
dbGetQuery(db, "select * from ABC_01 limit 2")
# 错误：nanodbc/nanodbc.cpp:1752: 42P01: 错误：关系 "abc_01" 不存在;
# 在准备参数时出错 错误：关系 "abc_01" 不存在;
# 在准备参数时出错
# <SQL> 'select * from ABC_01 limit 2'
dbGetQuery(db, 'select * from ABC.ABC_01 limit 2')
# 错误：nanodbc/nanodbc.cpp:1752: 42P01: 错误：关系 "abc.abc_01" 不存在;
# 在准备参数时出错 错误：关系 "abc.abc_01" 不存在;
# 在准备参数时出错
# <SQL> 'select * from ABC.ABC_01 limit 2'
dbGetQuery(db, 'select * from abc.ABC_01 limit 2')
# 错误：nanodbc/nanodbc.cpp:1752: 42P01: 错误：关系 "abc.abc_01" 不存在;
# 在准备参数时出错 错误：关系 "abc.abc_01" 不存在;
# 在准备参数时出错
# <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
更进一步：
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")
# 错误：nanodbc/nanodbc.cpp:1752: 42P01: 错误：关系 "abc.abc_01" 不存在;
# 在准备参数时出错 错误：关系 "abc.abc_01" 不存在;
# 在准备参数时出错
# <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
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
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论