Migrating SQL statements to PostgreSQL

When moving from a database to PostgreSQL, it makes sense to take a look and figure out which database engine provides which kind of functionality. Moving the data and the structure itself is usually fairly easy. However, rewriting SQL might not be. Therefore, I decided to include a section that explicitly focuses on various advanced features of SQL and their availability in today's database engines.

Using lateral joins

In SQL, a lateral join can basically be seen as some sort of loop. It basically allows us to parameterize a join and execute everything inside the LATERAL clause more than once. Here is a simple example:

test=# SELECT * 
   FROM generate_series(1, 4) AS x, 
         LATERAL (SELECT array_agg(y) 
                  FROM generate_series(1, x) AS y 
                 ) AS z; 
 x  | array_agg 
----+----------- 
 1  | {1} 
 2  | {1,2} 
 3  | {1,2,3} 
 4  | {1,2,3,4} 
(4 rows)

The LATERAL clause will be called for each x. To the end user, it is...