PostgreSQL’s New LATERAL Join Type
blog.heapanalytics.com
PostgreSQL’s New LATERAL Join Type
1–10 of 42 posts
Re: PostgreSQL’s New LATERAL Join Type
#2LATERAL is awesome. It makes a lot of queries that required sub-select joins much simpler to write and later read.
It's also great for set returning functions. Even cooler, you don't need to explicitly specify the LATERAL keyword. The query planner will add it for you automatically:
-- NOTE: WITH clause is just to fake a table with data:
WITH foo AS (
SELECT 'a' AS name
, 2 AS quantity
UNION ALL
SELECT 'b' AS name
, 4 AS quantity)
SELECT t.*
, x
FROM foo t
-- No need to say "LATERAL" here as it's added automatically
, generate_series(1,quantity) x;
name | quantity | x
------+----------+---
a | 2 | 1
a | 2 | 2
b | 4 | 1
b | 4 | 2
b | 4 | 3
b | 4 | 4
(6 rows)Re: PostgreSQL’s New LATERAL Join Type
#3cross join lateral also works with json arrays. Select your json array then CROSS JOIN LATERAL with a jsonb_to_recordset() to take your json array into individual columns.
Re: PostgreSQL’s New LATERAL Join Type
#4I'm a huge believer in postgres' json store, but anything above the basic queries can get a bit messy. Lateral joins really clean up aggregate data queries on json columns. E.g.
SELECT id, COUNT(keys)
FROM users,
LATERAL json_object_keys(login_history) keys
GROUP BY id;Re: PostgreSQL’s New LATERAL Join Type
#5seems similar to CROSS APPLY and OUTER APPLY from mssql http://technet.microsoft.com/en-us/library/ms175156%28v=sql....
Re: PostgreSQL’s New LATERAL Join Type
#6LATERAL is awesome. It makes a lot of queries that required sub-select joins much simpler to write and later read. It's also great for set returning functions. Even cooler, you don't need to explicitly specify the LATERAL keyword. The query planner will add it for you automatically: -- NOTE: WITH clause is just to fake a table with data: WITH foo AS ( SELECT 'a' AS name , 2 AS quantity UNION ALL SELECT 'b' AS name ,…
Thanks for the example. It seems to be the same as MS SQL Server's CROSS APPLY / OUTER APPLY?
Anyway, good that Postgres has it too, now. There are several Postgres features I'd love in SQL Server, like range types...
Re: PostgreSQL’s New LATERAL Join Type
#7How does this relate to WITH RECURSIVE (CTEs)?
Re: PostgreSQL’s New LATERAL Join Type
#8LATERAL is awesome. It makes a lot of queries that required sub-select joins much simpler to write and later read. It's also great for set returning functions. Even cooler, you don't need to explicitly specify the LATERAL keyword. The query planner will add it for you automatically: -- NOTE: WITH clause is just to fake a table with data: WITH foo AS ( SELECT 'a' AS name , 2 AS quantity UNION ALL SELECT 'b' AS name ,…
Am I right in thinking that this does not increase Postgres's expressive power but allows more concise implementation?
Re: PostgreSQL’s New LATERAL Join Type
#9> Without lateral joins, we would need to resort to PL/pgSQL to do this analysis. Or, if our data set were small, we could get away with complex, inefficient queries.
I don't buy the performance benefit over derived tables with properly indexed fields for this example. However, I'd definitely use this more so with functions.
Re: PostgreSQL’s New LATERAL Join Type
#10LATERAL is awesome. It makes a lot of queries that required sub-select joins much simpler to write and later read. It's also great for set returning functions. Even cooler, you don't need to explicitly specify the LATERAL keyword. The query planner will add it for you automatically: -- NOTE: WITH clause is just to fake a table with data: WITH foo AS ( SELECT 'a' AS name , 2 AS quantity UNION ALL SELECT 'b' AS name ,…
Am I right in thinking that this does not increase Postgres's expressive power but allows more concise implementation?
No, you can write queries that are not really possible to express without it. Basically, it allows you to execute a table-valued function for each row in an earlier query.
For example, in SQL Server I find a common use of CROSS APPLY (which appears to be the same thing) is where the "table-valued function" is a SELECT with a WHERE clause referencing the earlier query, an ORDER BY, and a TOP (=LIMIT) 1. (In fact, this is exactly the example given in the article.) It allows you to do things like "for each row in table A, join the last row in table B where NaturalKey(A) = NaturalKey(B) and Value1(A) is greater than or equal to Value2(B)".