I know that the order of returned records is not guaranteed with the IN statement for Pstgres. For example if I do this:

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)

I may get this result:


However, I want the records returned to respect the in statement input, so I found few solutions online, such as:

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)
ORDER BY POSITION(id::text in '(13589, 16674, 13588)')


SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)
ORDER BY id = 13589 desc,
         id = 16674 desc,
         id = 13588 desc;

I wonder if there is a nicer way to do this, or better yet more efficient?

  • "Nicer" is quite subjective; would you consider using ORDER BY CASE WHEN ... THEN ... as nicer ? As to efficiency, I'm pretty sure optimizer will generate the same plan. – a1ex07 Feb 22 '14 at 5:18

WITH ORDINALITY in Postgres 9.4+

You will be most interested in the new feature WITH ORDINALITY in the upcoming version 9.4 (devel status as of writing). Per documentation:

When a function in the FROM clause is suffixed by WITH ORDINALITY, a bigint column is appended to the output which starts from 1 and increments by 1 for each row of the function's output. This is most useful in the case of set returning functions such as unnest().

FROM   unnest('{13589, 16674, 13588}'::int[]) WITH ORDINALITY AS r(some_id, rn)

array or set?

x IN (set) statements are rewritten internally in Postgres to x = ANY (array), which is equivalent:

SELECT users.id FROM users WHERE users.id = ANY ('{13589, 16674, 13588}')

You can see for yourself with EXPLAIN.

Postgres 9.3 or earlier

For now, to preserve the order of elements, you could:

   SELECT x.arr[x.rn] AS id, x.rn
   FROM   (
      SELECT arr, generate_subscripts(arr, 1) AS rn
      FROM  (SELECT '{13589, 16674, 13588}'::int[]) t(arr)
      ) x
   ) y
JOIN   users u USING (id)

-> SQLfiddle

More details under this closely related question:
How to preserve the original order of elements in an unnested array?

  • thanks for you answer. I get this error when I run the query for postgres 9.3 and earlier ERROR: missing FROM-clause entry for table "x" LINE 1: SELECT x.id – Gandalf StormCrow Feb 24 '14 at 17:11
  • @G: sorry, y.id, not x.id. Added a fiddle. – Erwin Brandstetter Feb 24 '14 at 17:13
  • @GandalfStormCrow: Not awesome? – Erwin Brandstetter Feb 24 '14 at 17:21
  • I meant to say thats awesome, I'm not sure where not came from, maybe someone is doing prank on me, didn't lock my computer and this answer was on the screen – Gandalf StormCrow Feb 24 '14 at 20:10
  • @GandalfStormCrow: I see. :) – Erwin Brandstetter Feb 24 '14 at 20:27

If you want to rewrite the condition slightly you could do something like this:

with id_list (id, sort_order) as (
      (13589, 1), 
      (16674, 2),
      (13588, 3)
select u.id
from users u
  join id_list il on il.id = u.id
order by il.sort_order;

You can also put the values clause directly into the join:

select u.id
from users u
  join (
      (13589, 1), 
      (16674, 2),
      (13588, 3)
  ) as il(id, sort_order) on il.id = u.id
order by il.sort_order;
  • 2
    @ErwinBrandstetter's method has the nice advantage that it can use an array of integers as a bind variable from a host language. – Colin 't Hart Feb 22 '14 at 16:01
  • @Colin'tHart: yes, absolutely. I just wanted to show a different way of doing this. – a_horse_with_no_name Feb 24 '14 at 17:18

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.