postgres `order by` argument type

Question

What is the argument type for the order by clause in Postgresql?

I came across a very strange behaviour (using Postgresql 9.5). Namely, the query

select * from unnest(array[1,4,3,2]) as x order by 1;

produces 1,2,3,4 as expected. However the query

select * from unnest(array[1,4,3,2]) as x order by 1::int;

produces 1,4,3,2, which seems strange. Similarly, whenever I replace 1::int with whatever function (e.g. greatest(0,1)) or even case operator, the results are unordered (on the contrary to what I would expect).

So which type should an argument of order by have, and how do I get the expected behaviour?

What **is** the expected behaviour? Add some sample data and the expected output to your question — , Jan 30 '16 at 10:29

score 8 · Accepted Answer · 2016-01-30T10:25:24.913

8

This is expected (and documented) behaviour:

A sort_expression can also be the column label or number of an output column

So the expression:

order by 1

sorts by the first column of the result set (as defined by the SQL standard)

However the expression:

order by 1::int

sorts by the constant value 1, it's essentially the same as:

order by 'foo'

By using a constant value for the order by all rows have the same sort value and thus aren't really sorted.

To sort by an expression, just use that:

order by 
    case 
       when some_column = 'foo' then 1
       when some_column = 'bar' then 2
       else 3
    end

The above sorts the result based on the result of the case expression.

edited Jan 30 '16 at 10:25

answered Jan 30 '16 at 10:21

OK, but then how do I plug in e.g. case operator? – overdawn Jan 30 '16 at 10:24
@overdawn: see my edit, is that what you are looking for? – Jan 30 '16 at 10:25
I've tried `select * from unnest(array[1,4,3,2]) as x order by case when true then 1 end;` but it doesn't work (actually this is why I asked the question;)) – overdawn Jan 30 '16 at 10:27
1

`case when true then 1 end` does not make any sense, it's a really complicated way to obfuscate the constant number `1` What is the **real** problem you are trying solve? Maybe you should edit your question and add some sample data and the expected output based on that data. – Jan 30 '16 at 10:28
Actually I have a function with an integer argument which indicates the column to be used in the order by clause. But I don't see the problem with `case when true then 1 end` - it is just a (brutal) simplification of your example. And it doesn't work for me. If it worked, my problem would be solved. – overdawn Jan 30 '16 at 10:34
@overdawn You mean `order by my_func(column_number int)`? – Clodoaldo Neto Jan 30 '16 at 10:43
"case operator"? Suspect you meant a "case expression". The case expression `case when true then 1 end` is the equivalent of `case when 1 then 1 end` or `case when 'constant' then 'constant' end` in effect it leaves your result unsorted. `case when column >= value then 1 else 0 end` might be a more practical example perhaps. – Paul Maxwell Jan 30 '16 at 10:47
@Used_By_Already Something like this: create temp table my_data ( id serial, val text ); insert into my_data(id, val) values (default, 'a'), (default, 'c'), (default, 'd'), (default, 'b'); create function fetch_my_data(col int) returns setof my_data as $$ select * from my_data order by col $$ language sql; select * from fetch_my_data(2); – overdawn Jan 30 '16 at 10:48
ok I put that into http://sqlfiddle.com/#!15/94cf4/1 but I don't follow I'm afraid. What do you expect the result to be? (that's in your head but not mine) & What does that have to do with a case expression? – Paul Maxwell Jan 30 '16 at 11:06

krokodilko · Answer 2 · 2016-01-30T11:31:18.893

Actually I have a function with an integer argument which indicates the column to be used in the order by clause.

In a case when all columns are of the same type, this can work: :

SELECT ....
ORDER BY 
  CASE function_to_get_a_column_number()
    WHEN 1 THEN column1
    WHEN 2 THEN column2
    .....
    WHEN 1235 THEN column1235
END

If columns are of different types, you can try:

SELECT ....
ORDER BY 
  CASE function_to_get_a_column_number()
    WHEN 1 THEN column1::varchar
    WHEN 2 THEN column2::varchar
    .....
    WHEN 1235 THEN column1235::varchar
END

But these "workarounds" are horrible. You need some other approach than the function returning a column number. Maybe a dynamic SQL ?

score 0 · Answer 3 · edited May 23 '17 at 11:44

I would say that dynamic SQL (thanks @kordirko and the others for the hints) is the best solution to the problem I originally had in mind:

create temp table my_data (
  id serial,
  val text
);

insert into my_data(id, val)
values (default, 'a'), (default, 'c'), (default, 'd'), (default, 'b');

create function fetch_my_data(col text)
returns setof my_data as
$f$
begin
 return query execute $$
   select * from my_data
   order by $$|| quote_ident(col);
end
$f$ language plpgsql;

select * from fetch_my_data('val'); -- order by val
select * from fetch_my_data('id'); -- order by id

In the beginning I thought this could be achieved using case expression in the argument of the order by clause - the sort_expression. And here comes the tricky part which confused me: when sort_expression is a kind of identifier (name of a column or a number of a column), the corresponding column is used when ordering the results. But when sort_expression is some value, we actually order the results using that value itself (computed for each row). This is @a_horse_with_no_name's answer rephrased.

So when I queried ... order by 1::int, in a way I have assigned value 1 to each row and then tried to sort an array of ones, which clearly is useless.

There are some workarounds without dynamic queries, but they require writing more code and do not seem to have any significant advantages.

postgres `order by` argument type

3 Answers3