R function dbGetQuery returns 1x1 table with name from function dbConnect

Question

I have following problem in R.

I connected to database using RPostgres package function dbConnect:

dbConnect(m, dbname=dbname, host=host, port=port, user=example, password=password)

The name of user is 'example' The name of one of the tables in database is also 'example'.

While running: dbGetQuery(db, "SELECT * from example") instead of getting the table, I get data frame 1x1 with only one value : example (the same as user value in dbConnect function)

It seems like the command dbGetQuery(db, "SELECT * from example") returns value of user from dbConnect(m, dbname=dbname, host=host, port=port, user=example, password=password) instead of returning table from database.

Do you have any ideas how to solve this problem?

Have you passed the connection object to the get command? So, conn <- dbConnect(m, dbname=dbname, host=host, port=port, user=example, password=password) and then dbGetQuery(conn, "SELECT * from example"). — 9314197
Is it to do with schemas? What happens if you do select * from schema.example? — 9314197
And does the problem only happen with the table called example or does it happen with all tables? — 9314197
Only or this one, and this solution select * from schema.example that you wrote works, thank you :) — Agnieszka

9314197 9314197 · Accepted Answer · 2020-06-19T13:03:10

Adding my previous comment as an answer: It looks like dbGetQuery() was getting confused between the username and the table name. Adding the schema name to the query works.

conn <- dbConnect(m, dbname=dbname, host=host, port=port, user=example, password=password)
dbGetQuery(conn, "SELECT * from schema.example")

R function dbGetQuery returns 1x1 table with name from function dbConnect

1 Answers