0

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

  • 1
    Please show us the complete query (edit your question, do not post code in comments) – a_horse_with_no_name Jul 1 at 18:44
  • @a_horse_with_no_name query added above. Not sure if it really helps but worth a try. – Andrea Moro Jul 2 at 8:28
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)
...
  • Thanks. Any advice on which is the best in term of performance? – Andrea Moro Jul 2 at 8:49
  • There is no difference in performance. – a_horse_with_no_name Jul 2 at 8:50
  • 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. – Andrea Moro Jul 2 at 8:56
  • 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) – a_horse_with_no_name Jul 2 at 8:57
  • Makes sense. Thanks – Andrea Moro Jul 2 at 13:34
0
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.