0

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?

iskandarblue
  • 7,208
  • 15
  • 60
  • 130
  • Can you not just use single quotes in your R syntax, like `dbGetQuery(con, 'SELECT "mixedCase" from my_table limit 5;')`? R allows either single or double quotes for strings, and you don't have to escape doubles if you use singles to start the string. – Marius Jul 23 '18 at 01:23

1 Answers1

4

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360