Postgres Won't Accept Table Alias Before Column Name

Postgres won't accept table alias before column name

The problem is that you include the table alias in SET clause, in the columns. See the documentation of UPDATE in Postgres docs:

column

The name of a column in table. The column name can be qualified with a subfield name or array subscript, if needed. Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid.

This is valid in Postgres:

update GREETING Greeting 
set 
    NAME='World', 
    PHRASE='Hello World!' 
where Greeting.ID=5 ;

Why do I get an error querying from column alias?

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

2. Use CTE

WITH data s (
  SELECT first_name || ' ' || last_name AS fullname
  FROM actor
)
SELECT *
FROM data
WHERE fullname BETWEEN :conditio1 AND :conditio2

3. Use subquery

SELECT *
FROM (
  SELECT first_name || ' ' || last_name AS fullname
  FROM actor
) tmp
WHERE tmp.fullname BETWEEN :conditio1 AND :conditio2

How do you SELECT attributes of a custom type via a table alias?

You need to put the parentheses around the combination of alias and column:

SELECT id, (x.some_test).some_text, (x.some_test).some_number
FROM test_table x;

---

Related Topics