Search code examples
rpostgresqlpsqlsqldfrpostgresql

Querying mixed case columns in SQL with R

I have a mixed case column in my_table that can only be queried using double quotes in psql. For example:

select "mixedCase" from my_table limit 5; would be the correct way to write the query in psql, and this returns records successfully

However, I am unable to replicate this query in R:

I have tried the following:

  1. dbGetQuery(con, "SELECT '\"mixedCase\"' from my_table limit 5;")

which throws: RS-DBI driver warning: (unrecognized PostgreSQL field type unknown (id:705) in column 0)

  1. dbGetQuery(con, "SELECT 'mixedCase' from my_table limit 5;")

which throws: RS-DBI driver warning: (unrecognized PostgreSQL field type unknown (id:705) in column 0)

  1. dbGetQuery(con, "SELECT "mixedCase" from my_table limit 5;") which throws Error: unexpected symbol in "dbGetQuery(con, "SELECT "mixedCase"

What is the solution for mixed case columns with the RPostgreSQL package?

Solution

  • You seem to understand the problem, yet you never actually tried just using the literal correct query in R. Just escape the double quotes in the query string and it should work:

    dbGetQuery(con, "SELECT \"mixedCase\" from my_table limit 5;")

    Your first two attempts would have failed because you are passing in mixedCase as a string literal, not as a column name. And the third attempt would fail on the R side because you are passing in a broken string/code.