Search code examples
mysqlsqlpostgresqlsubquery

How to use an alias in a subquery in the SELECT clause ? postgreSQL

First of all, thank you for your time!

I have migrated my database from MariaDB to PostgreSQL and now some of my queries don't work anymore.

Here is an example :

SELECT DISTINCT table.column AS columnAlias,
          SUM(table2.column)/(SELECT COUNT(table.column) 
                              FROM table 
                              WHERE table.column= columnAlias) 
FROM ...

The problem is that this SQL query worked well when my database was a MariaDB database, but actually, I had to migrate it to a PostgreSQL one, and now it throws an

ERROR: 42703: column "columnAlias" does not exists.

I think the problem is that PostgreSQL doesn't accept the alias in a subquery, but have you an idea of how to fix that? or another way to make the job done?

Thank you for your time,

Guillaume.

Solution

  • You can use a lateral join:

    SELECT DISTINCT v.columnAlias,
                SUM(table2.column) / (SELECT COUNT(table.column)
                                       FROM table WHERE table.column= v.columnAlias)
FROM ... CROSS JOIN LATERAL
     (VALUES (table.column)) v(columnAlias)