如何基於列的串聯建構 PostgreSQL ORDER BY 表達式？

我正在使用 Postgres 9.5。我想為一系列連接的字元串編寫一個表達式順序，但我不確定這是否可能或如何去做。我拼湊了下面

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...

但是正如您所看到的，有一個抱怨語法的錯誤。我正在嘗試做的事情是否可行，如果可以，我如何根據列的串聯編寫 order by 子句？

在這裡我可以說兩點，

1. 解決方案：","與','單引號不同的是字面引用機制。雙引號表示命名的列,完全有效，但在實踐中會很糟糕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. 更大的問題：為什麼要連接字元串？它只會減慢速度或失去準確性。您可以按多列排序；就這樣做..

order by lower(addresses.city), addresses.state_id;

引用自：https://dba.stackexchange.com/questions/165042