0

I want the lexemes of a tsvector array placed in a column, one per row. I can first unnest the array and then the resulting tsvectors with:

SELECT (unnest(unnest(my_array))).lexeme
FROM my_table
WHERE id = 1;

which gives:

lexeme  
----------
foo
bar
baz
...

I noticed, however, that if I try to do the same in the FROM clause:

SELECT lexeme
FROM unnest(
    (SELECT unnest(my_array) FROM my_table WHERE id = 1)
);

I get:

ERROR: more than one row returned by a subquery used as an expression

So, I end up doing this:

SELECT lexeme
FROM (
    SELECT (unnest(col1)).lexeme
    FROM (
        SELECT unnest(my_array) 
        FROM my_table 
        WHERE id = 1 
    ) AS t(col1)
) AS t2;

And I get the original result. But this is verbose. So, I'd like to know:

  1. Why does unnest() require a single input row in the FROM clause, but doesn't in the SELECT clause?

  2. Is there a more concise, less convoluted way than my code, to get the column of lexemes in the FROM clause?

  3. Is there a difference in performance between unnesting in the SELECT clause, versus doing it in the FROM clause?

3
  • 1
    Possibly relevant stackoverflow.com/a/23004157/14868997. You should favour two LATERAL unnest clauses over using them in the select Sep 5, 2021 at 5:00
  • @Charlieface It's very relevant. It's the answer to question 2: SELECT lexeme FROM my_table, unnest(array_column) AS vector, unnest(vector) WHERE product_id = 1; If you post it as an answer, I will mark it accepted. Many thanks. Now only questions 1 and 3 remain for the sake of clarification.
    – ARX
    Sep 5, 2021 at 15:48
  • @ARX: question #1 is not really related to unnest. Consider select (select 1 union select 2) which would fail with the same error message (more than one row returned by a subquery used as an expression) Sep 5, 2021 at 16:31

1 Answer 1

2

You're not really supposed to use set-returning operators in the select, even though PostgreSQL allows it.

It's much better to place them in the from and use a lateral join

SELECT lexeme
FROM my_table t,
  lateral unnest(array_column) AS vector,
  lateral unnest(vector)
WHERE t.product_id = 1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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