Paul, This is a really valuable idea. It will work in some situations for me. But in other situations I do not know if table will have a key of type int[] or string[] or even mixed. That’s why I’d wish to use JSON arrays and customize sort ordering.
Anyway I appreciate you shared this approach! Regards, Anthony Ananich http://ananich.pro On Jul 27, 2016, at 18:00, Paul Jungwirth <p...@illuminatedcomputing.com> wrote: > On 07/27/2016 07:44 AM, Vick Khera wrote: >> On Wed, Jul 27, 2016 at 3:28 AM, Anton Ananich <anton.anan...@gmail.com> >> wrote: >>> In my situation this order is invalid. Obviously, year 2016 should go after >>> 2014, like that: >> >> I think you expect JSONB to sort differently than it does. I cannot >> imagine what a "natural" ordering of arbitrary JSON objects is. > > FWIW, Postgres arrays do sort in the way he's expecting: > > paul=# create table t (id integer, v integer[]); > CREATE TABLE > paul=# insert into t values (1, array[2014]), (2, array[2014, 1]), (3, > array[2016]); > INSERT 0 3 > paul=# select * from t order by v; > id | v > ----+---------- > 1 | {2014} > 2 | {2014,1} > 3 | {2016} > (3 rows) > > So maybe convert to an array before sorting? > > Paul > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
