CODEWITHSUNDEEP
postgresqlruby-on-rails-4activerecord
Tom Prats
Tom Prats

Reputation: 7921

Undefined column when searching 2 columns concatenated into 1 in Postgresql

Problem

For some reason I'm getting the error:

PG::UndefinedColumn: ERROR:  column "full_name" does not exist

For the following query:

User.select("first_name || ' ' || last_name as full_name").where("full_name = ?", "Jon Snow")

Which converts into the sql query:

SELECT first_name || ' ' || last_name as full_name FROM \"users\" WHERE (full_name = 'Jon Snow')

Goal

My intention is to be able to query by a users full name, but I don't want to split it out because this is used in a much larger query.

Attempts

I've tried a many variations of using/not using the table name (users) around first_name, last_name, or full_name and also tried adding/removing quotes/parenthesis from various locations. I've found that if I query without a where, and then check what full_name is, it actually gathers it correctly. Concat also seems to yield the same error.

Possible Solution

Instead of naming it, it looks like if I move the concat into the where call, it should work:

User.where("CONCAT(first_name, ' ', last_name) = ?", "Jon Snow")

SQL:

"SELECT \"users\".* FROM \"users\" WHERE (CONCAT(first_name, ' ', last_name) = 'Jon Snow')

It doesn't seem as convenient as using a named variable but maybe you just can't do that.

Upvotes: 1

Views: 492

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324551

If you want to reference aliases from the SELECT clause in the WHERE clause you need to do so using a subquery in FROM, e.g.

SELECT *
FROM (
  SELECT
    *,
    concat(first_name, ' ', last_name) AS full_name
  FROM some_table
  WHERE ...otherpredicates...
) x
WHERE full_name = 'Jim Bob';

Upvotes: 2

Related Questions