{"id":8370,"date":"2023-09-12T06:34:19","date_gmt":"2023-09-12T11:34:19","guid":{"rendered":"https:\/\/bobbeaty.com\/wp\/?p=8370"},"modified":"2023-09-13T05:48:24","modified_gmt":"2023-09-13T10:48:24","slug":"nice-postgres-feature-lateral","status":"publish","type":"post","link":"https:\/\/bobbeaty.com\/wp\/archives\/8370","title":{"rendered":"Nice Postgres Feature: LATERAL<\/code>"},"content":{"rendered":"

\"PostgreSQL.jpg\"<\/p>\n

There are many times when you would like a sub-select query to be constrained on one of the values of the main query, but when you attempt to do that you get an error message about not being able to use the variable in this context. For example, this query:<\/p>\n

  SELECT<\/span> c.<\/span>id,<\/span> c.<\/span>company_name,<\/span> pb.<\/span>available,<\/span> pb.<\/span>current,<\/span>\n         date_trunc(<\/span>'second'<\/span>,<\/span> pb.<\/span>as_of)<\/span> AS<\/span> as_of,<\/span> pbs.*<\/span>\n    FROM<\/span> companies c,<\/span> plaid_tokens pt,<\/span> plaid_balances pb,<\/span>\n         (<\/span>SELECT<\/span> SUM<\/span>(<\/span>available)<\/span> AS<\/span> all_available,<\/span>\n                 SUM<\/span>(<\/span>CURRENT<\/span>)<\/span> AS<\/span> all_current\n            FROM<\/span> plaid_balances WHERE<\/span> company_id=<\/span>c.<\/span>id)<\/span> pbs\n   WHERE<\/span> pt.<\/span>id =<\/span> (<\/span>c.<\/span>additional_info->><\/span>'primaryPlaidAccount'<\/span>)<\/span>::uuid\n     AND<\/span> pt.<\/span>account_id =<\/span> pb.<\/span>account_id<\/pre>\n
where the goal is to have a sub-select gather the sum<\/tt> of the individual columns being pulled in the main query. It's a nice thing to have, but the inability to have c.id<\/tt> used in the sub-select really makes it difficult.<\/p>\n
Postgres has a nice feature in LATERAL<\/tt>, where is allows the sub-select to reference these fields by changing the order of evaluation of the sub-select and doesn't penalize the performance too much.<\/em><\/p>\n
  SELECT<\/span> c.<\/span>id,<\/span> c.<\/span>company_name,<\/span> pb.<\/span>available,<\/span> pb.<\/span>current,<\/span>\n         date_trunc(<\/span>'second'<\/span>,<\/span> pb.<\/span>as_of)<\/span> AS<\/span> as_of,<\/span> pbs.*<\/span>\n    FROM<\/span> companies c,<\/span> plaid_tokens pt,<\/span> plaid_balances pb,<\/span>\n         lateral (<\/span>SELECT<\/span> SUM<\/span>(<\/span>available)<\/span> AS<\/span> all_available,<\/span>\n                         SUM<\/span>(<\/span>CURRENT<\/span>)<\/span> AS<\/span> all_current\n                    FROM<\/span> plaid_balances WHERE<\/span> company_id=<\/span>c.<\/span>id)<\/span> pbs\n   WHERE<\/span> pt.<\/span>id =<\/span> (<\/span>c.<\/span>additional_info->><\/span>'primaryPlaidAccount'<\/span>)<\/span>::uuid\n     AND<\/span> pt.<\/span>account_id =<\/span> pb.<\/span>account_id<\/pre>\n
This is still quick, and it saves the machinations of having to calculate the sums in a temp table, or write a function to do this... it's just a nice little trick that they put in the language. Very considerate. \ud83d\ude42 <\/p>\n","protected":false},"excerpt":{"rendered":"
There are many times when you would like a sub-select query to be constrained on one of the values of the main query, but when you attempt to do that you get an error message about not being able to use the variable in this context. For example, this query: SELECT c.id, c.company_name, pb.available, pb.current, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,12],"tags":[],"class_list":["post-8370","post","type-post","status-publish","format-standard","hentry","category-coding","category-javascript-coding"],"_links":{"self":[{"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/posts\/8370","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/comments?post=8370"}],"version-history":[{"count":1,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/posts\/8370\/revisions"}],"predecessor-version":[{"id":8371,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/posts\/8370\/revisions\/8371"}],"wp:attachment":[{"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/media?parent=8370"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/categories?post=8370"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/tags?post=8370"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}