Postgres Column Alias Problem

postgres column alias problem

In PostgreSQL you can not use expression with an alias in order by. Only plain aliases work there. Your query should look like this:

   select distinct 
          l2.*, 
          l.user_id as l_user_id, 
          l.geopoint_id as l_geopoint_id 
     from locations l 
left join locations l2 on l.geopoint_id = l2.geopoint_id 
    where l.user_id = 8 
 order by l2.geopoint_id, l.user_id = l2.user_id desc;

I assume you mean that l2.user_id=l.user_id ought to go first.

This is relevant message on PostgreSQL-general mailing list. The following is in the documentation of ORDER BY clause:

Each expression can be the name or
ordinal number of an output
column (SELECT list item), or it
can be an arbitrary expression formed
from input-column values.

So no aliases when expression used.

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

Column alias is not recognized

Alas, that is true. Column aliases are not allowed. One solution is to repeat the expression:

SELECT EXTRACT(YEAR FROM rental_ts) as year,
       COUNT(DISTINCT rental_id)
FROM rental
GROUP BY year
HAVING EXTRACT(YEAR FROM rental_ts) = 2020;

A better solution is to filter before aggregating:

SELECT EXTRACT(YEAR FROM rental_ts) as year,
       COUNT(DISTINCT rental_id)
FROM rental
WHERE rental_ts >= '2020-01-01' AND rental_ts < '2021-01-01'
GROUP BY year;

This is better for two reasons. First, it is index (and partition) compatible. Second, it reduces the amount of data needed for the aggregation.

Postgresql - Select in Select Alias Problem

I think you are looking for lateral joins. Your example is too contrieved to really make sense, but the logic is:

select u.id, ub.*, b.*
from users u
cross join lateral (
    select array_agg(distinct ub.bonus_id) bonus_id
    from user_has_bonus ub
    where ub.user_id = u.id
) ub
cross join lateral (
    select ...
    from bonuses b
    where b.bonus_id = any(ub.bonus_id)
) b

As opposed to when using inline subqueries, you can refer to columns from one subquery within the next one - as shown in the where clause of the second subquery.

Note that you want to use any() rather than in to check if a value belongs to an array.

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 ;

DISTINCT ON (col) col_alias – alias not working

You can't give on (...) an alias because it's not expression that "returns" a name. It's like trying to give an IN condition an alias where a in (1,2,3) as foo

If in doubt, read the manual. It does not mention an alias there.

You can only define an alias for an expression in the actual select list, and the (...) is not part of the select list, but part of the distinct on expression.

I think it's just a matter of bad wording on that tutorial page, and should read:

select distinct on (column1) 
          column1 as column_1_alias,
          column2
from ....

postgres column allow space to column while making alias name

Postgres follows the SQL standard and uses double quotes, not backticks, to escape database object names (such as column or table names). Use this version:

query = 'SELECT species AS "flower name" FROM iris'
connection = db_connect(connection_data)
cursor = connection.cursor()
cursor.execute(query)

---

Related Topics