1

I have this query in Postgres 9.4:

select id from question where id = any(
    array_cat(
        ARRAY[0,579489,579482,579453,561983,561990,562083]::integer[], 
        (select array(
            select id from question where id not in 
                (0,579489,579482,579453,561983,561990,562083)
            and status in (1, -1) 
            and created_at > 1426131436 order by id desc offset 0 limit 10 )
        )::integer[]
    )
)

It returns:

   id
--------
 561983
 561990
 562083
 579453
 579482
 579489
 580541
 580542
 580543
 580544
 580545
 580546
 580547
 580548
 580549
 580550
(16 rows)

But it's not in the right order. I need the result ordered according to the result of the sub array:

array_cat(
        ARRAY[0,579489,579482,579453,561983,561990,562083]::integer[], 
        (select array(
            select id from question where id not in 
                (0,579489,579482,579453,561983,561990,562083)
            and status in (1, -1) 
            and created_at > 1426131436 order by id desc offset 0 limit 10 )
        )::integer[]
    )

How can I do that?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
jianfeng
  • 2,440
  • 4
  • 21
  • 28

2 Answers2

3

Basics:

Since you are using Postgres 9.4 you can use the new WITH ORDINALITY:

WITH t AS (
   SELECT *
   FROM   unnest('{0,579489,579482,579453,561983,561990,562083}'::int[])
                  WITH ORDINALITY AS t(id, rn)
   )
(
SELECT id
FROM   question
JOIN   t USING (id)
ORDER  BY t.rn
)
UNION ALL
(
SELECT id
FROM   question
LEFT   JOIN t USING (id)
WHERE  t.id IS NULL
AND    status IN (1, -1) 
AND    created_at > 1426131436
ORDER  BY id DESC
LIMIT  10
);

Explain

  1. Since you are using the same array twice I prepend the query with a CTE where you provide your array once. unnest() it immediately WITH ORDINALITY get row numbers (rn) according to order of array elements.

  2. Instead of stuffing your subquery into an array and transforming it back, use it directly. Much cheaper. The sort order is derived from the id directly.

  3. Instead of excluding IDs from the given array with NOT IN (which can be tricky with NULL values) use LEFT JOIN / IS NULL:

  4. Just append the two parts with UNION ALL. Parentheses are required to have separate ORDER BY on each leg of the UNION ALL query:

  5. The JOIN to question in the final SELECT is now redundant, I stripped it away.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I didnt know about that, it seems to be perfect :) – mathieu Apr 11 '15 at 05:10
  • @mathieu: I took it one step further. Should be very fast now. – Erwin Brandstetter Apr 11 '15 at 05:19
  • I dont really need the answer as i'm not the author of the question, but i find your way to achieve this interessant... Postgres offers so many possibilities it's hard to know them all :) – mathieu Apr 11 '15 at 05:22
  • @ErwinBrandstetter it work. And i think it is perfect in 9.4 , thanks a lot. – jianfeng Apr 11 '15 at 05:26
  • do some change to that : select q.id from question q join ( select * from unnest( array_cat( ARRAY[0,579489,579482,579453,561983,561990,562083]::integer[], (select array( select id from question where id not in (0,579489,579482,579453,561983,561990,562083) and status in (1, -1) and created_at > 1426131436 order by id desc offset 0 limit 10 ) )::integer[] ) ) WITH ORDINALITY as ids(id, rn) ) as tmp on q.id = tmp.id order by tmp.rn – jianfeng Apr 11 '15 at 05:26
0
ORDER BY idx(your_array, your_element)

or

ORDER BY your_array # your_element

intarray

select id from question where id = any(
    array_cat(
        ARRAY[0,579489,579482,579453,561983,561990,562083]::integer[], 
        (select array(
            select id from question where id not in 
                (0,579489,579482,579453,561983,561990,562083) and status in (1, -1) 
                and created_at > 1426131436 order by id desc offset 0 limit 10 )
        )::integer[]
    ) 
) ORDER BY array_cat(
    ARRAY[0,579489,579482,579453,561983,561990,562083]::integer[], 
    (select array(
        select id from question where id not in 
            (0,579489,579482,579453,561983,561990,562083) and status in (1, -1) 
            and created_at > 1426131436 order by id desc offset 0 limit 10 )
    )::integer[]
) # id
mathieu
  • 477
  • 3
  • 9