3

I'm using Postgres 9.5. I want to write an order by expression for a series of concatenated strings, but I'm not sure if that's possible or how to do it. I have cobbled together the below

myproject=> SELECT  "my_objects".* 
            FROM "my_objects" 
              left join addresses on my_objects.address_id = addresses.id 
            WHERE (my_objects.name ILIKE '%my_object%' 
              AND EXISTS (SELECT * 
                          FROM my_object_times 
                          WHERE my_object_times.my_object_id = my_objects.id)) 
            order by lower(addresses.city) || "," || addresses.state_id; 

ERROR:  column "," does not exist 
LINE 1: ...id = my_objects.id)) order by lower(addresses.city) || "," || add...

But as you can see there is an error complaining about syntax. Is what I'm tryihng to do possible and if so how do I write an order by clause based on a concatenation of columns?

  • don't forget to mark the answer as chosen if you're satisfied – Evan Carroll Feb 21 '17 at 16:29
4

There are two things I can say here,

  1. The solution: "," is different from ',' single-quotes is the literal quote mechanism. Double quotes means the column named , which is perfectly valid however horrible it would be in practice CREATE TABLE foo ("," int);

    -- Identifier quoting
    order by lower(addresses.city) || "," || addresses.state_id;
    -- Literal quoting
    order by lower(addresses.city) || ',' || addresses.state_id;
    
  2. The larger problem: why concatenate the strings? It can only ever slow this down or lose accuracy. You can order by more than one column; just do this..

    order by lower(addresses.city), addresses.state_id;
    
| improve this answer | |

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.