postgresql: non-table expression in from-list?

user_3208297 0
Viewed 42

Let's say I have a table

id | description
-----------------
1  |  aaa        
2  |  bbb

I accidentally wrote

select * from the_table, upper(description)

and was very surprised to get the result

id | description | upper
-------------------------
1  |  aaa        | AAA
2  |  bbb        | BBB

How/why does this work? The documentation mentions that comma-separated entries in the from-clause must be table-like structures, which are then cross-join together. Here neither is true. Which part of the syntax & semantics allows this??

postgresql sql
2 Answers

This is tacitly handled like this:

select *
from the_table
   CROSS JOIN LATERAL upper(description) AS upper;

The reason:

  • having several FROM list entries amounts to a cross join, see the documentation:

    If more than one table reference is listed in the FROM clause, the tables are cross-joined (that is, the Cartesian product of their rows is formed).

  • a function call in the FROM list is treated as if there were LATERAL, as the documentation says:

    Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key word is optional

The missing puzzle piece here is that upper is no table function. That is true, but PostgreSQL is a bit fuzzy about this: any function can be used as a table function (so you can run SELECT * FROM sin(pi() / 2);), and in many contexts you can use table functions outside the FROM clause (like in SELECT generate_series(1, 10);).

The former is not documented, but the latter is, although it is not easy to find.

user_64643080
user_39842210
Related