Take the 2-minute tour ×

Just trying out Postgresql for the first time, coming from MySQL. In our Rails application we have a couple of locations with SQL like so:

SELECT * FROM `currency_codes` ORDER BY FIELD(code, 'GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD') DESC, name ASC

It didn't take long to discover that this is not supported/allowed in Postgresql.

Does anyone know how to simulate this behaviour in Postgres or do we have to pull to sorting out into the code?



share|improve this question
It might be useful to explain what you want to achieve. As hard to imagine as it is - not everybody knows MySQL :) –  user80168 Aug 21 '09 at 9:14
Great point depesz! Basically a custom sort order is what we are after. The FIELD function allows you to create custom set to do your sorting with. –  Peer Allan Aug 21 '09 at 11:47

8 Answers 8

up vote 24 down vote accepted

Ah, gahooa was so close:

SELECT * FROM currency_codes
    WHEN code='USD' THEN 1
    WHEN code='CAD' THEN 2
    WHEN code='AUD' THEN 3
    WHEN code='BBD' THEN 4
    WHEN code='EUR' THEN 5
    WHEN code='GBP' THEN 6
    ELSE 7
share|improve this answer
Oops!! a bit of late evening dyslexia... You have my vote! –  gahooa Aug 21 '09 at 15:53
This method does not work when you are using DISTINCT. Any other ideas for this scenario? –  Corey Jul 25 '11 at 16:47

Update, fleshing out terrific suggestion by @Tometzky.

This ought to give you a MySQL FIELD()-alike function under pg 8.4:

-- SELECT FIELD(varnames, 'foo', 'bar', 'baz')
CREATE FUNCTION field(anyelement, VARIADIC anyarray) RETURNS numeric AS $$
     ( SELECT i FROM generate_subscripts($2, 1) gs(i)
       WHERE $2[i] = $1 ),

Mea culpa, but I cannot verify the above on 8.4 right now; however, I can work backwards to a "morally" equivalent version that works on the 8.1 instance in front of me:

-- SELECT FIELD(varname, ARRAY['foo', 'bar', 'baz'])
CREATE OR REPLACE FUNCTION field(anyelement, anyarray) RETURNS numeric AS $$
              FROM generate_series(1, array_upper($2, 1)) gs(i)
              WHERE $2[i] = $1),

More awkwardly, you still can portably use a (possibly derived) table of currency code rankings, like so:

pg=> select cc.* from currency_codes cc
     left join
       (select 'GBP' as code, 0 as rank union all
        select 'EUR', 1 union all
        select 'BBD', 2 union all
        select 'AUD', 3 union all
        select 'CAD', 4 union all
        select 'USD', 5) cc_weights
     on cc.code = cc_weights.code
     order by rank desc, name asc;
 code |           name
 USD  | USA bits
 CAD  | Canadian maple tokens
 AUD  | Australian diwallarangoos
 BBD  | Barbadian tridents
 EUR  | Euro chits
 GBP  | British haypennies
(6 rows)
share|improve this answer

This is I think the simplest way:

create temporary table test (id serial, field text);
insert into test(field) values
  ('GBP'), ('EUR'), ('BBD'), ('AUD'), ('CAD'), ('USD'),
select * from test
order by field!='GBP', field!='EUR', field!='BBD',
  field!='AUD', field!='CAD', field!='USD';
 id | field 
  1 | GBP
  7 | GBP
  2 | EUR
  8 | EUR
  3 | BBD
  9 | BBD
  4 | AUD
 10 | AUD
  5 | CAD
 11 | CAD
  6 | USD
 12 | USD
(12 rows)

In PostgreSQL 8.4 you can also use a function with variable number of arguments (variadic function) to port field function.

share|improve this answer
+1 for order-by-bang, and for VARIADIC suggestion, which I'll try to implement. –  pilcrow Aug 21 '09 at 14:57

Actually the version for postgres 8.1 as another advantage.

When calling a postgres function you cannot pass more than 100 parameters to it, so your ordering can be done at maximum on 99 elements.

Using the function using an array as second argument instead of having a variadic argument just remove this limit.

share|improve this answer

You can do this...

   ..., code
      WHEN code='GBP' THEN 1
      WHEN code='EUR' THEN 2
      WHEN code='BBD' THEN 3
      ELSE 4

But why are you hardcoding these into the query -- wouldn't a supporting table be more appropriate?


Edit: flipped it around as per comments

share|improve this answer
@gahooa, I think you've the sense of "code" reversed -- the code is the three alpha abbreviation, which the OP desires to sort in a non-alpha fashion. –  pilcrow Aug 21 '09 at 2:26
exactly right pilcrow –  Peer Allan Aug 21 '09 at 3:52
I wish I could take credit for why the SQL is the way it is, we are working on a refactor, but I admit I am looking for the quick fix right now –  Peer Allan Aug 21 '09 at 3:53

Just define the FIELD function and use it. It's easy enough to implement. The following should work in 8.4, as it has unnest and window functions like row_number:

    SELECT row_number() OVER () AS n, x FROM unnest($2) x
) numbered WHERE numbered.x = $1;

You can also define another copy with the signature:

CREATE OR REPLACE FUNCTION field(anyelement, VARIADIC anyarray) RETURNS bigint AS $$

and the same body if you want to support field() for any data type.

share|improve this answer
I think this is the best answer, but when I try to create the function, pgadmin says - "return type mismatch in function declared to return bigint, Function's final statement must be select/insert" ideas? –  chrismarx Jun 28 at 14:46
@chrismarx Pg version? Select version() –  Craig Ringer Jun 28 at 15:29
also unnest doesn't seem to be a registered function, "PostgreSQL 9.3.4 on x86_64-apple-darwin13.1.0, compiled by Apple LLVM version 5.1 (clang-503.0.38) (based on LLVM 3.4svn), 64-bit" –  chrismarx Jun 28 at 16:39
That sounds pretty borked. unnest has been around since 8.4... Show \df unnest please –  Craig Ringer Jun 30 at 3:10

If you'll run this often, add a new column and a pre-insert/update trigger. Then you set the value in the new column based on this trigger and order by this field. You can even add an index on this field.

share|improve this answer
Triggers are bad, mmkay? Avoid if at all possible! –  Will Sheppard Feb 6 '13 at 12:54

sort in mysql:

> ids = [11,31,29]
=> [11, 31, 29]
> User.where(id: ids).order("field(id, #{ids.join(',')})")

in postgres:

def self.order_by_ids(ids)
  order_by = ["case"]
  ids.each_with_index.map do |id, index|
    order_by << "WHEN id='#{id}' THEN #{index}"
  order_by << "end"
  order(order_by.join(" "))

User.where(:id => [3,2,1]).order_by_ids([3,2,1]).map(&:id) 
#=> [3,2,1]
share|improve this answer

Your Answer


