How do I build a PostgreSQL ORDER BY expression based on a concatenation of columns?

Question

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?

Answer 1

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;