Ora2Pg Migration – Subquery must have an alias

Following type of SQL’s can cause “subquery in FROM must have an alias” issue. They are like below,

ERROR: subquery in FROM must have an alias
Hint: For example, FROM (SELECT ...) [AS] foo.

It needs to be fixed to meet the SQL standards. There is no question asked about why PostgreSQL did not support 🙂 why should I create alias in all the cases 🙂

select * from (SELECT student_id, status from student where dep_id=1001 limit 2)

The correct query with alias are as follows,

select * from (SELECT student_id, status from student where dep_id=1001 limit 2)
 as std

For more reference refer following link,

https://www.postgresql.org/message-id/1487773980.3143.15.camel%40oopsware.de

Also in my experience tools like pganalyzer can come handy for analysing such queries failing at the server side. Good thing about this tool is it provide fix recommendation 🙂