PostgreSQL 16 Feature : Sub-Query Aliases and its usage in Code Conversion.

Coming from an Oracle background and primarily involved in migrating customers to PostgreSQL, one feature that excited me was the elimination of the need for aliases in the FROM clause for sub-queries or value constructors.

In PostgreSQL 16, it is now possible to define sub-queries without the need for aliases.

In contrast to the previous version where aliases were mandatory, failure to provide them would result in query errors.

In the migration process from Oracle to PostgreSQL, many conversion tools, such as ora2pg, automatically handle the variations in how aliases are supported across the two database engines. These tools add appropriate aliases whenever necessary to ensure a smooth transition.

Below is a sample conversion using ora2pg. The tool added “alias0” in PostgreSQL to support the mandatory use of aliases in PostgreSQL versions prior to 16.

Oracle	PostgreSQL(Pre 16)
create view vw_sample_alias as (select * from (select sysdate from dual));	CREATE OR REPLACE VIEW vw_sample_alias (sysdate) AS (select LOCALTIMESTAMP FROM (select LOCALTIMESTAMP ) alias0);

Ora2pg – Conversion -Sub-Query without aliases

The advantages of PostgreSQL 16 features, specifically in supporting sub-queries without aliases, become apparent in scenarios where SQL is dynamically built or embedded within application code logic during migrations. Handling such cases can be cumbersome, often encountered during functional testing, and typically resolved within the testing phase.

Now, as we begin utilizing unnamed aliases in PostgreSQL, we will observe them in the Explain plan as follows.

explain select * from (select id , a  from demo1 order by id limit 10) ,
 (select b from demo2 order by b limit 10) where id=100000 and id = b
postgres-# ;

                                            QUERY PLAN                                             
----------------------------------------------------------------------------------------
 Nested Loop  (cost=112.12..112.73 rows=1 width=16)
   ->  Subquery Scan on unnamed_subquery  (cost=0.29..0.74 rows=1 width=12)
         Filter: (unnamed_subquery.id = 100000)
         ->  Limit  (cost=0.29..0.62 rows=10 width=12)
               ->  Index Scan using demo1_pkey on demo1  (cost=0.29..3259.31 rows=100001 width=12)
   ->  Subquery Scan on unnamed_subquery_1  (cost=111.83..111.98 rows=1 width=4)
         Filter: (unnamed_subquery_1.b = 100000)
         ->  Limit  (cost=111.83..111.85 rows=10 width=4)
               ->  Sort  (cost=111.83..119.33 rows=3000 width=4)
                     Sort Key: demo2.b
                     ->  Seq Scan on demo2  (cost=0.00..47.00 rows=3000 width=4)

Internally, a sub-query defined without aliases is referred to as “unnamed_subquery” within the parsed SQL.

When we employ unnamed sub-queries within Views, the “unnamed_subquery” will also be stored as part of the View definitions. Below is a sample of the view definition as seen in the EDITOR set and pg_dump.

Overall, PostgreSQL 16 introduces several exciting features worth exploring. In this ongoing blog series, we will continue to examine release features that prove valuable during the conversion process to PostgreSQL from other database engines.

If you really enjoyed the blog and are exploring courses on Oracle to PostgreSQL Migration, please check out !