R DBI::dbGetQuery where clause interprets string as a column name

RStudio, packages DBI and odbc, connection to Amazon Redshift. I need the records of retirees from a leavers table. If I use a where clause like this:

leavers <- dbGetQuery(con, 
  'select distinct
	"employee number",
	"leaving reason"
		
from leaverstable where "employee number" = 12345')

This works. But if I swap the where clause to where "leaving reason" = Retirement' or where "leaving reason" = "Retirement"', I get the error message [SQLState 42703] ERROR: column "retirement" does not exist. I also tried having instead of where, but I get the same.

(As an aside: if I swap the single quotes and speech marks, i.e. the speech marks are on the outside and single quotes on the inside ('employee number'), then it does not work even with the first example.)

I can pull in the whole table and then filter out the records other than Retirement in R, but I am pulling in 3 million records for the sake of a couple of thousand. I know very little about SQL, so apologies if this is something trivial, but could someone help, please?

Quoted identifiers and quoted string literals are different between R and SQL.

| Lang | Identifier    | String Literal                 |
|------|---------------|--------------------------------|
| R    | `Some Column` | 'Some string' or "Some string" |
| SQL  | "Some Column" | 'Some string'                  |

In R, we typically only use backticks on column names when they contain a space, start with a number, or violate any others of R's "normal name rules". We can always use them, as in mtcars$`cyl` (equivalent to mtcars$cyl), but when there's no need to, it is not usually done.

Try

leavers <- dbGetQuery(con, 
  'select distinct
    "employee number",
    "leaving reason"
        
from leaverstable where "employee number" = 12345
  or "leaving reason" = \'Retirement\'
')

(Notice we need to escape the quotes, not a big deal.)

Better yet though is to use bound parameters. Among other things, they provide safety from inadvertent SQL Injection (a slight stretch of the term). Namely,

leavers <- dbGetQuery(con, 
  'select distinct
    "employee number",
    "leaving reason"     
from leaverstable where "employee number" = ?
  or "leaving reason" = ?
', params = list(12345, "Retirement"))

where the double-quotes used here is in R-land, not SQL-land, because from R we are passing a simple string. Here, we can use "Retirement" or 'Retirement', they will be the same, because what happens is that this string is passed efficiently internally as a string to SQL, which will interpret it correctly as a string literal because it was told it's a string and it doesn't need to spend time parsing it out of the SQL query. (There are other advantages to using bound-parameters, see parameterized queries.)

The use of ? as place-holders for the parameter to bind does change between DBMSes; for instance, ODBC always (I believe) uses ?, regardless of the DBMS type; postgres uses $1 (and $2, ...); sqlite uses ?, ?1, or :name; and SQL Server (native client) uses ?.