1

I'm running a simple query:

Listing.where('advertiserName LIKE ?', '%lowes%')

and I get the following error

Listing.where('advertiserName LIKE ?', '%lowes%')
D, [2021-09-13T00:26:42.656409 #4] DEBUG -- :   Listing Load (1.6ms)  SELECT "listings".* FROM "listings" WHERE (advertiserName LIKE '%lowes%') LIMIT $1  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR:  column "advertisername" does not exist)
LINE 1: SELECT "listings".* FROM "listings" WHERE (advertiserName LI...
                                                   ^
HINT:  Perhaps you meant to reference the column "listings.advertiserName".

4 attempts to solve

1

I double checked the column name exists:

Listing.column_names
=> ["id", "language", "jobId", "jobTitle", "jobClassification", "jobSubClassification", "advertiserName", "advertiserId",

2

I made sure I was using single quotes like PostgreSQL requires.

3

I ran a similar query on another table in the same app, and it seems to work just fine:

Metric.where('average_salary_by_language LIKE ?', '%Hadoop%')
# returns results

4

I did as suggested in the error message, and tried replacing advertiserName with listings.advertiserName:

Listing.where('listings.advertiserName LIKE ?', '%lowes%')
D, [2021-09-13T00:31:36.017492 #4] DEBUG -- :   Listing Load (1.7ms)  SELECT "listings".* FROM "listings" WHERE (listings.advertiserName LIKE '%lowes%') LIMIT $1  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR:  column listings.advertisername does not exist)
LINE 1: SELECT "listings".* FROM "listings" WHERE (listings.advertis...
                                                   ^
HINT:  Perhaps you meant to reference the column "listings.advertiserName".

Other ideas

I can see the error message says:

PG::UndefinedColumn: ERROR: column listings.advertisername does not exist

I know that advertisername doesn't exist - it's clearly advertiserName (with a capital 'N'), and that's what I'm using. Could rails/AR/postgres have a bug?

stevec
  • 41,291
  • 27
  • 223
  • 311
  • 1
    Not a bug, but that doesn't mean it works as you are clearly expecting, https://stackoverflow.com/questions/45253713/camelcase-column-in-postgresql-database-in-rails-activerecord#answer-45256592 – Rockwell Rice Sep 13 '21 at 00:44
  • 1
    @RockwellRice you nailed it. Note to self: ***never*** use camel case in db column names. This works `Listing.where('"advertiserName" LIKE ?', '%lowes%')` (single quotes because [postgres requires single quotes](https://stackoverflow.com/questions/14987634/rails-and-heroku-pgerror-column-does-not-exist-for-where-argument/14987636#14987636), and double quotes around the column containing camel case because [object names are case-insensitive in ANSI/postgres](https://stackoverflow.com/questions/45253713/camelcase-column-in-postgresql-database-in-rails-activerecord#answer-45256592)) – stevec Sep 13 '21 at 00:56
  • Have you tried `Listing.arel_table[:advertiserName].matches(Arel::Nodes.build_quoted("%lowes%"))` Not a huge fan of using String SQL and this should avoid that "requirement" – engineersmnky Sep 13 '21 at 14:18
  • @engineersmnky I gave it a try, but I can't seem to get any records as results, just some info like this: `=> # – stevec Sep 16 '21 at 05:54
  • @stevec I am not sure where that result is coming from. You should just be able to replace your where condition e.g. `Listing.where(Listing.arel_table[:advertiserName].matches(Arel::Nodes.build_quoted("%lowes%")))` – engineersmnky Sep 16 '21 at 12:03
  • Hi @max I wondered if you would consider reopening. The reason being, the duplicate has the same *solution*, **and** the same *cause* (excellent grounds for it being a duplicate), however, it has a different *symptom*! I realise that's a fine point of difference, but leaving this question open will allow someone experiencing this particular symptom (`PG::UndefinedColumn: ERROR:`) to work through possible causes and find the solution more quickly. – stevec Sep 18 '21 at 09:53
  • From [meta](https://meta.stackoverflow.com/questions/411592/are-questions-with-different-symptoms-same-cause-same-solution-duplicates#comment868256_411592): it is indeed considered a duplicate (I retracted my reopen vote). – stevec Sep 18 '21 at 10:34

0 Answers0