3

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?

Improve this question
1
  • What is the expected behaviour? Add some sample data and the expected output to your question Commented Jan 30, 2016 at 10:29

3 Answers 3

Reset to default
9

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

OK, but then how do I plug in e.g. case operator?
@overdawn: see my edit, is that what you are looking for?
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;))
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.
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.
|
1

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 ?

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.