Postgres weird PG::UndefinedColumn: ERROR: on value

Question

While executing a simple select - where operation using activerecord execute,

ActiveRecord::Base.connection.execute('select * from spree_variants where sku = "1SB-E4196-00";')

I got this error:

from /Users/abc/.rvm/gems/ruby-2.7.2@cboparts/gems/activerecord-6.0.3.5/lib/active_record/connection_adapters/postgresql/database_statements.rb:92:in `exec'
Caused by PG::UndefinedColumn: ERROR:  column "1SB-E4196-00" does not exist
LINE 1: select * from spree_variants where sku = "1SB-E4196-00";

Why it is considering "1SB-E4196-00" as a column but not SKU? The error seems misleading.

mu is too short · Accepted Answer · 2021-07-29 05:39:48Z

4

Because PostgreSQL expects strings to be bounded in single quotes. While double quotes have a different meaning:

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word.

That means if the following query should work:

ActiveRecord::Base.connection.execute(
  "select * from spree_variants where sku = '1SB-E4196-00';"
)

Btw you if you are using Rails and have a SpreeVariant model then you can see in the console how Rails formats and escapes the query like this:

puts SpreeVariant.where(sku: '1SB-E4196-00').to_sql

edited Jul 29, 2021 at 5:39

mu is too short

436k71 gold badges863 silver badges822 bronze badges

answered Jul 29, 2021 at 5:18

spickermann

108k9 gold badges115 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres weird PG::UndefinedColumn: ERROR: on value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related