0

I'm getting an error querying from the column alias and don't understand why. In the example below, if I run a query from the actual column, no problem. I concatenate first_name and last_name columns into a fullname column alias and then get the output.

SELECT first_name ||' '|| last_name AS fullname
FROM actor;

Output:

enter image description here

Now, if I create a column alias, I get the error. In this example, I'm concatenating first_name and last_name into a fullname column alias, and then query the names between value1 and value2.

SELECT first_name ||' '|| last_name AS fullname
FROM actor;
WHERE fullname BETWEEN 'Zero Cage' AND 'Fred Costner';

Output:

enter image description here

Thanks in advance for your taking the time to help!

Robin Sage
  • 969
  • 1
  • 8
  • 24
  • As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Nov 02 '21 at 05:37
  • Is the semicolon after 'actor' a syntax error? This might well explain the error message. – No'am Newman Nov 02 '21 at 06:11
  • @No'amNewman Nah, it's correct. Apparently PostgreSQL doesn't support text with the BETWEEN statement. – Robin Sage Nov 02 '21 at 07:32
  • @RobinSage: that has nothing to do with the BETWEEN operator and string comparison –  Nov 02 '21 at 08:10
  • @a_horse_with_no_name What do you mean? – Robin Sage Nov 02 '21 at 08:28
  • You wrote "*Apparently PostgreSQL doesn't support text with the BETWEEN*" - the error you get has nothing to do with using the BETWEEN operator with `text` values. –  Nov 02 '21 at 08:43
  • @a_horse_with_no_name I tried using it with text, but it won't work. It seems to me that BETWEEN is limited to numbers and date in PostgreSQL, but it seems to work fine in SQL. Am I mistaken? If so, could you clarify? – Robin Sage Nov 02 '21 at 09:47
  • Postgres uses SQL as its query language so "*work fine in SQL*" doesn't really make sense. But BETWEEN works just fine with `text` or `varchar` values https://dbfiddle.uk/?rdbms=postgres_13&fiddle=972e2b17a0b3b356ae6e2e44f4ad6e60 –  Nov 02 '21 at 10:09
  • @a_horse_with_no_name Strange, I couldn't get it to work at all on pgAdmin. I'll look into that. Thanks for helping! – Robin Sage Nov 02 '21 at 10:20

1 Answers1

2

In postgres document:

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

That's according to the SQL standard and may not be very intuitive. The (historic) reason behind this is the sequence of events in a SELECT query. WHERE and HAVING are resolved before column aliases are considered, while GROUP BY and ORDER BY happen later, after column aliases have been applied. Also note that conflicts between input and output names are resolved differently in ORDER BY and GROUP BY - another historic oddity (with a reason behind it, but potentially confusing nonetheless).

You can use one of the below manners:

  1. Use full both column name
SELECT first_name || ' ' || last_name AS fullname
FROM actor
WHERE first_name || ' ' || last_name BETWEEN :conditio1 AND :conditio2
  1. Use CTE
WITH data s (
  SELECT first_name || ' ' || last_name AS fullname
  FROM actor
)
SELECT *
FROM data
WHERE fullname BETWEEN :conditio1 AND :conditio2
  1. Use subquery
SELECT *
FROM (
  SELECT first_name || ' ' || last_name AS fullname
  FROM actor
) tmp
WHERE tmp.fullname BETWEEN :conditio1 AND :conditio2
Pooya
  • 2,968
  • 2
  • 12
  • 18
  • Hey, thanks for your reply. I had tried the first method before, as you suggested, but it returns no results. There is no error, just empty. I'm flabbergasted. Any idea why that is? – Robin Sage Nov 02 '21 at 05:44
  • I'm not sure if I want to mess with methods 2 and 3 as you mentioned because I haven't gotten that far in SQL. Just gettin started. – Robin Sage Nov 02 '21 at 05:46
  • In my opinion, your problem cus using `BETWEEN`, condition key BETWEEN use for `number` and `date` value and you can't use in text or varchar column. and when used this keyboard shows the empty output. [Sample test](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=b5d920a63af10039fa93a42456294a40) – Pooya Nov 02 '21 at 05:52
  • The tutorial says it's possible to use it with text, but clearly it is not. I just tried running: SELECT first_name FROM actor WHERE first_name BETWEEN 'Zero' AND 'Fred' It still won't work. So the problem is not the column alias after all. Thank you Pooya. – Robin Sage Nov 02 '21 at 06:00
  • @RobinSage: unless you've changed the order of comparison, 'Zero' generally comes *after* 'Fred', so BETWEEN 'Zero' AND 'Fred' is doomed to fail. BETWEEN 'Fred' AND 'Zero' has a better chance of succeeding. – No'am Newman Nov 03 '21 at 05:39
  • @No'amNewman Awesome. I'm going to review it all and see where I fumbled. Thank you for your input! – Robin Sage Nov 03 '21 at 06:59