K
K
kr_ilya2020-08-06 12:34:12
PostgreSQL
kr_ilya, 2020-08-06 12:34:12

How to get all data from a row in a given query?

Have a request

WITH RECURSIVE
    r AS (
        SELECT array[]::bigint[] AS res,min(id) AS min, max(id)-min(id) AS range FROM items
      UNION ALL
        SELECT res||ARRAY(SELECT id FROM items WHERE id IN (SELECT (min+range*random())::int FROM generate_series(1,${custom.numRows})) AND NOT id=ANY(res)), min, range
        FROM r
        WHERE
          coalesce(array_length(res,1),0)<${custom.numRows}
    )
    SELECT * FROM (
      SELECT res FROM r ORDER BY array_length(res,1) DESC NULLS LAST LIMIT 1
    ) AS t LIMIT ${custom.numRows};


custom.numRows = 1 - Number of rows.

As a result, I get the id values ​​in an array. There may be more than one, depending on the number of lines.
{ res: [ '4146' ] }

How can I get not just id values, but all row data?

Tried
WITH RECURSIVE
    r AS (
        SELECT array[]::bigint[] AS res,min(id) AS min, max(id)-min(id) AS range FROM items
      UNION ALL
        SELECT res||ARRAY(SELECT id FROM items WHERE id IN (SELECT (min+range*random())::int FROM generate_series(1,${custom.numRows})) AND NOT id=ANY(res)), min, range
        FROM r
        WHERE
          coalesce(array_length(res,1),0)<${custom.numRows}
    )
    SELECT * FROM items WHERE id IN (
      SELECT res FROM r ORDER BY array_length(res,1) DESC NULLS LAST LIMIT 1
    ) LIMIT ${custom.numRows}


In response, I get the error
error: operator does not exist: bigint = bigint[].

Another question:
How to add a condition for the selection?
For example, the items table has a cost column. How to specify that its value was > 1000?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question