PostgreSQL Select Arrays with order

Question

I have a 'route' table describe as follows:

CREATE TABLE NEW_ROUTES (
    id        integer primary key,
    path      integer[],
    name      text
);

And I added some rows:

insert into NEW_ROUTES (id,path,name)
values (1, '{1,2,3,4}', 'path1');

insert into NEW_ROUTES (id,path,name)
values (2, '{1,2,3,4,5}', 'path2');

insert into NEW_ROUTES (id,path,name)
values (3, '{5,4,3,2}', 'path3');

insert into NEW_ROUTES (id,path,name)
values (4, '{2,3,1,7}', 'path4');

insert into NEW_ROUTES (id,path,name)
values (5, '{7,6,5,4,3}', 'path5');

insert into NEW_ROUTES (id,path,name)
values (6, '{1,2}', 'path6');

insert into NEW_ROUTES (id,path,name)
values (7, '{2,1}', 'path7');

insert into NEW_ROUTES (id,path,name)
values (8, '{2,7,5,1,9}', 'path8');

How can I select multiple rows, which have integers 1 and 2 and where 2 is placed before 1 (for example, {2,1} and {2,7,5,1,9} etc..).
Which type of index should i use (for example, GIN)?
Is there another and best way to solve this problem (for example, using string, json, tree or smth else)?

I will be appreciated if you help me.

And even if i add such row: insert into NEW_ROUTES (id,path,name) values (9, '{2,7,5,1,2,9}', 'path9') - i wanted to get this row. — Иван Волынчук
– Иван Волынчук, Commented Jun 3, 2016 at 11:27

gabba · Accepted Answer · 2016-06-03 12:03:21Z

1

Try to use idx function, that return index of element or 0 if it dos't contained.

select * from NEW_ROUTES where '{1}' &> path and '{2}' &> path and idx(path, 2) < idx(path, 1)

https://www.postgresql.org/docs/9.1/static/intarray.html

To use idx function You should enable 'intarray' module as described in https://www.postgrespro.ru/doc/contrib.html

GIN index is faster to search than a GiST index, but slower to build or update; so GIN is better suited for static data and GiST for often-updated data.
you could try to optimize this query if you store hash table with node id as a key, and index in path array as a value. To do it, You mast add another column and calculate index of each node in path one time

edited Jun 3, 2016 at 12:03

answered Jun 3, 2016 at 11:14

gabba

2,9002 gold badges31 silver badges49 bronze badges

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

6 Comments

Иван Волынчук Over a year ago

ERROR: function idx(integer[], integer) does not exist LINE 4: idx(path,2) < idx(path,1); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. ********** Error ********** ERROR: function idx(integer[], integer) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. Character: 32

Иван Волынчук Over a year ago

Is it possible to use not in procedure?

Иван Волынчук Over a year ago

How Can I know the order of rows if i will use var. 3?

gabba Over a year ago

1. You should enable 'intarray' module as described in postgrespro.ru/doc/contrib.html 3. To do it you mast add another column and calculate index of each node in path one time

Иван Волынчук Over a year ago

Thanks. Which type of index should i use if i choose first variant?

|

Collectives™ on Stack Overflow

PostgreSQL Select Arrays with order

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related