Calling a table from Heroku Postgresql

[iFeanyi]

Hello,

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.

[andresrcs]

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

Trouble sending PostgreSQL query in R Studio

Hello R people, Please, I am trying to query a table that I created in PostgreSQL using the pgAdmin web interface. I created an empty table called "Actors", and attempted to write data into that table from R Studio. dbWriteTable(con,"Actors",value = voice,overwrite = TRUE) It appeared to be successful, because when I ran dbReadTable(conn = con,"Actors") I did get my populated table just as it is in the csv file. However, when I try to query that table, I get an error message. Here is my code: aquery <- dbSendQuery(conn = con,"SELECT movie,actor,character FROM Actors WHERE movie = Aladdin") Here is the error message: Error: Failed to prepare query: ERROR: relatio…

[iFeanyi]

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.