How to reference the column alias of a concatenation?

Question

I'm trying to select a concatenation as an alias then reference that alias in a WHERE clause, but I can't get it working. Here's an example:

select concat(area_code, "-", phone_triad, "-", phone_quad) as phone_number, first_name, last_name
from info_table 
where phone_number in (<LIST OF NUMBERS>);

Answer 1

Like has been commented, you cannot refer to output column names in the WHERE clause. Only to input column names. You'd have to repeat the expression. See:

• GROUP BY + CASE statement

But there is more.

Strings are single-quoted: 'string'. See:

• Insert text with single quotes in PostgreSQL

If your tables are not trivially small you'll want to improve performance with indexes. Your predicates have to be "sargable". For this, you have two basic options:

1. Expression index

CREATE INDEX ON info_table ((area_code || '-' || phone_triad || '-' || phone_quad));

SELECT area_code || '-' || phone_triad || '-' || phone_quad AS phone_number
     , first_name, last_name
FROM   info_table
WHERE  area_code || '-' || phone_triad || '-' || phone_quad IN (<list OF numbers>);

Note the expression area_code || '-' || phone_triad || '-' || phone_quad instead of your original concat() expression. Why? See:

• Combine two columns and add into one new column
• String concatenation using operator "||" or format() function

2. Split up input

And work with basic indexes on one or more parts of the number:

WITH nr AS (
   SELECT phone_number
        , split_part(phone_number, '-', 1) AS area_code
        , split_part(phone_number, '-', 2) AS phone_triad
        , split_part(phone_number, '-', 3) AS phone_quad
   FROM  (
      VALUES 
        ('123-456-789')  -- your input here
      , ('223-456-789')
      ) input(phone_number)
   )
SELECT nr.phone_number, i.first_name, i.last_name
FROM   nr
JOIN   info_table i USING (area_code, phone_triad, phone_quad);

See:

• Split comma separated column data into additional columns

To keep input numbers that are not found in the result, use LEFT JOIN instead of JOIN. Then null values are filled in for first_name & last_name - which is distinguishable from actual column values if at least one of your columns is defined NOT NULL.

Either way, you have to handle null values, empty strings and leading and trailing white space properly.