0

I am using the DBI package with the RPostgreSQL driver to query from an amazon redshift database and am able to pull tables with simple queries without issue. By simple queries I mean something like this:

'SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 200'

When I use a WHERE statement on a field that has a type FLOAT8, again, no issue. Queries such as:

'SELECT * FROM transactions ORDER BY timestamp WHERE transaction_amt < 5 DESC LIMIT 200'

The issue arises when trying to use a WHERE statement with a field that has a type VARCHAR. A query such as:

'SELECT * FROM transactions ORDER BY timestamp WHERE type = 'Credit Card' DESC LIMIT 200'

Gives me an error that says:

Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  column "credit card" does not exist in transactions
)



My question is if there is some specific syntax when using WHERE statements with these packages or this specific database. Why would it tell me that the column 'credit card' doesn't exist?

When looking at the information about each field I did see that 'type' and many other character columns have a Len of -1, could that have something to do with it?

Konrad Rudolph
  • 530,221
  • 131
  • 937
  • 1,214
nerrez
  • 15
  • 3

1 Answers1

0

Never mind, as it turns out the error was cause by using single quotes on the outside and double quotes within the query. I switched it to using double quotes on the outside with single on the inside.

This:

"SELECT * FROM transactions ORDER BY timestamp WHERE type = 'Credit Card' DESC LIMIT 200"

Not this:

'SELECT * FROM transactions ORDER BY timestamp WHERE type = "Credit Card" DESC LIMIT 200'

Sometimes coding makes me feel dumb.

nerrez
  • 15
  • 3
  • 1
    R and python are both generally agnostic between single- and double-quotes, but not all languages share that property. SQL and the bash shell are two notable languages where they have very distinct interpretations. Glad you found it. – r2evans Aug 16 '21 at 18:21