Ruby PG conn.exec_params SQL structure

Question

I'm getting an error on a simple statement through PG:

require 'pg'
conn = PG.connect( dbname: 'myDB' )
@res = conn.exec_params( 'SELECT count(id) FROM users WHERE username = $1 AND status = "active"', ['johnny5'] )

The error:

/Users/rich/app.rb:14:in `exec_params': ERROR:  column "active" does not exist (PG::UndefinedColumn)
LINE 1: ...unt(id) FROM users WHERE username = $1 AND status = "active"
                                                               ^

"active" is a field value, not a column.

My question: I have fixed this by entering the value "active" as another placeholder. Are quoted values in the SQL not permitted? I assumed that quoted aspects of the SQL would have been fine.

Answer 1

String literals in SQL use sigle quotes, double quotes are for identifiers (such as table and column names). So, when you mention "active", the database complains that there is no such column.

The solution is to use a placeholder:

@res = conn.exec_params(
  %q{SELECT count(id) FROM users WHERE username = $1 AND status = $2},
  ['johnny5', 'active']
)

or use single quotes inside the SQL:

@res = conn.exec_params(
  %q{SELECT count(id) FROM users WHERE username = $1 AND status = 'active'},
  ['johnny5']
)

Switching from '...' to %q{...} for your SQL string literal makes the internal quoting problems a bit easier to deal with.