Select

There are many tokens to tweak queries returning sets or aggregators.

Table Expressions

These always have a FROM clause.

SELECT
FROM
WHERE

LATERAL

I really battled with using jsonb_each(utility) until I discovered

SELECT key, value
FROM tictactoe_states, LATERAL jsonb_each(utility)
WHERE id < 10;

LATERAL is primarily useful when the cross-referenced column is necessary for computing the row(s) to be joined. A common application is providing an argument value for a set-returning function.

It turns out LATERAL is implied, so the above can be abbreviated to:

SELECT key, value
FROM tictactoe_states, jsonb_each(utility)
WHERE id < 10;

Implicitly stating LATERAL is good style.

The LATERAL key word is unnecessary in this example, but we use it for clarity.

The order of the tables after FROM is important because I initially tried

SELECT key, value
FROM jsonb_each(utility), tictactoe_states
WHERE id < 10;

Which resulted in

ERROR:  column "utility" does not exist
LINE 2: FROM jsonb_each(utility), tictactoe_states

Something that also wasted a couple of hours was learning settof record returning functions such as jsonb_each have to be in the FROM part of the query because

SELECT jsonb_each(utility) FROM tictactoe_states WHERE id < 10;

results in

 jsonb_each 
------------
 (o,50)
 (x,50)
 (o,50)
 (x,50)
 (o,50)
 (x,50)
 (o,50)
 (x,50)
 (o,50)
 (x,50)
 (o,50)
 (x,50)
 (o,50)
 (x,50)
 (o,50)
 (x,50)
 (o,50)
 (x,50)
(18 rows)

and I’ve yet to figure out how to retrieve elements from these records.

Aggregate Functions

Aggregate functions are not allowed in WHERE