Calling a table from Heroku Postgresql


Please, I have set up a PostgreSQL on Heroku, which I am trying to call from R Studio. I have used this line of code dbWriteTable(conn = conh,"apple",value = aapl,overwrite = TRUE) to write a table to the database called "apple" , which has columns Open, High, Low, and Close. When I call the table with the following basic sql query dbGetQuery(conn = conh, "SELECT * FROM apple") I get the entire table returned as an R object. But when I write something like dbGetQuery(conh,"SELECT Open, High,Low FROM apple WHERE Open > 70"), I get the following error message and hint, which does not solve the problem, in my console:

Error: Failed to prepare query: ERROR:  column "open" does not exist
LINE 1: SELECT Open,High,Low FROM apple WHERE Open > 70
HINT:  Perhaps you meant to reference the column "apple.Open".

Please if anybody else has had the same experience, kindly advise me please, thanks.

This is not related to R but to postgresql's sql dialect, it is not case sensitive by default so you have to double quote column names if they contain upper case letters.

dbGetQuery(conh, 'SELECT "Open", "High", "Low" FROM apple WHERE "Open" > 70')

when working with Postgresql is better not to use upper case letters to keep things simple.

Edit: Now that I recall, I already told you this before

You are right. You've actually shared that solution with me before, and I apologise for not recalling that. Nevertheless, thanks again for reminding me of it.

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.