Postgres select using lateral

Question

I'm after running a query with a subquery (perhaps inefficient) that using a regular expression pattern it can return how many instances of a characters are included with a given record.

In doing so, I came across something like:

SELECT t."page",
 product_tree_length,
 Count(*), 
 FROM "Table" t
 ,
 LATERAL (SELECT (LENGTH((regexp_matches(t."page", '^.+\/c\/(.+?)(?=\/\?|\?|\/$).*$', 'g'))[1]) - LENGTH(REPLACE(((regexp_matches(t."page", '^.+\/c\/(.+?)(?=\/\?|\?|\/$).*$', 'g'))[1]),'/','')))) AS "product_tree_length"
 where t.page like '%\/c/%'
 GROUP BY 1, product_tree_length;

However, in selecting the product_tree_length in the main query, the returned data type is a record and not an integer as expected. If I try to cast the value, PostgreSQL raise an exception that the operation cannot be completed.

Any idea on what I am doing wrong?

Thanks

• user1822: Please show us the complete query (edit your question, do not post code in comments)
• Andrea Moro: @a_horse_with_no_name query added above. Not sure if it really helps but worth a try.

Answer 1

Well product_tree_length is a table alias, not a column alias. So if you use that alias in the SELECT list, obviously you get a record.

You have two options to properly define an alias for the column

Solution one: use a column alias inside the derived table:

select t.page
 product_tree_length.len,
 count(*)
from "Table" t
 cross join lateral (
 SELECT length(....) as len
 ) AS product_tree_length
...

Solution two: define a column alias as part of the table alias:

select t.page
 product_tree_length.len,
 count(*)
from "Table" t
 cross join lateral (
 SELECT length(....)
 ) AS product_tree_length(len)
...

• Andrea Moro: Thanks. Any advice on which is the best in term of performance?
• user1822: There is no difference in performance.
• Andrea Moro: Thanks, your help was very appreciated. One more question. I noticed you used the CROSS JOIN as opposed me doing the FROM TABLE, LATERAL ... was that a mistake? Sorry to ask, but PostgreSQL is not my primary database.
• user1822: from a,b is the same as from a cross join b - I prefer the explicit use of CROSS JOIN to document that this is indeed what I intended. The cross join resulting from from a,b could have been an accident. (the use of lateral doesn't really change that)
• Andrea Moro: Makes sense. Thanks

Answer 2

SELECT t."page",
 Count(*), 
 (LENGTH((regexp_matches(t."page", '^.+\/c\/(.+?)(?=\/\?|\?|\/$).*$', 'g'))[1]) - LENGTH(REPLACE(((regexp_matches(t."page", '^.+\/c\/(.+?)(?=\/\?|\?|\/$).*$', 'g'))[1]),'/',''))) as product_tree_length FROM "Table" t where t.page like '%\/c/%' GROUP BY 1,3;

I think you don't need a lateral join here if you just wanted the length