Array Contains (@>) Operation

[Sougat Chakraborty]

I need to execute an array contains (@>) query in PostgreSQL and need to check if all elements in an Erlang list is present in the array column in a database table. Guess, we can convert the Erlang list ([<<"one">>, <<"two">>]) to Postgres array type format ('{one, two}') and execute squery. Wondering if there is a better way of achieving this in epgsql?

[Sergey Prokhorov]

Smth like

10> epgsql:equery(<<"db_master:0">>, "SELECT ARRAY[0,1,2,3,4] @> $1", [ [1, 3] ]).
{ok,[{column,<<"?column?">>,bool,1,-1,1}],[{true}]}
11> epgsql:equery(<<"db_master:0">>, "SELECT ARRAY[0,1,2,3,4] @> $1", [ [1, 5] ]).
{ok,[{column,<<"?column?">>,bool,1,-1,1}],[{false}]}

четверг, 17 марта 2016 г., 10:38:58 UTC+3 пользователь Sougat Chakraborty написал:

[Sergey Prokhorov]

Sorry... corect version is

10> epgsql:equery(Connection, "SELECT ARRAY[0,1,2,3,4] @> $1", [ [1, 3] ]).


{ok,[{column,<<"?column?">>,bool,1,-1,1}],[{true}]}


11> epgsql:equery(Connection, "SELECT ARRAY[0,1,2,3,4] @> $1", [ [1, 5] ]).


{ok,[{column,<<"?column?">>,bool,1,-1,1}],[{false}]}

пятница, 18 марта 2016 г., 12:49:39 UTC+3 пользователь Sergey Prokhorov написал:

[Sergey Prokhorov]

And for array of strings:

12> epgsql:equery(Connection, "SELECT ARRAY['one','two','three'] @> $1", [[<<"two">>, <<"three">>]]).

{ok,[{column,<<"?column?">>,bool,1,-1,1}],[{true}]}

13> epgsql:equery(Connection, "SELECT ARRAY['one','two','three'] @> $1", [[<<"two">>, <<"three">>, <<"five">>]]).

{ok,[{column,<<"?column?">>,bool,1,-1,1}],[{false}]}

пятница, 18 марта 2016 г., 12:50:33 UTC+3 пользователь Sergey Prokhorov написал:

[Marc Worrell]

Just wondering, would array support make it possible to use something like:

“select foo from bar where id in ($1)”

Where $1 is then an array?

Currently I am joining and escaping terms to come up with “in (1,2,3)”.

Which is feels very silly.

- Marc

--
You received this message because you are subscribed to the Google Groups "Erlang epgsql Postgres driver use and development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to epgsql+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Sergey Prokhorov]

Maybe

SELECT * FROM tab WHERE id = ANY(ARRAY[2, 3, 4])

or

SELECT * FROM tab WHERE id IN (SELECT(unnest(ARRAY[2, 3, 4])))

But I don't know anything about performance of this

See also https://coderwall.com/p/jmtskw/use-in-instead-of-any-in-postgresql


пятница, 18 марта 2016 г., 15:40:35 UTC+3 пользователь Marc Worrell написал:

[Marc Worrell]

Interesting, thanks!

Of course, this needs array support in our epgsql driver.

- M

[Sergey Prokhorov]

Both of them works with epgsql for me (2'nd variant with explicit typecasting):

epgsql:equery(C, "SELECT id FROM forwarding WHERE id = ANY($1)", [[1000, 1001, 1002]]).

epgsql:equery(C, "SELECT id FROM forwarding WHERE id IN (SELECT(unnest($1::int[])))", [[1000, 1001, 1002]]).

пятница, 18 марта 2016 г., 16:26:04 UTC+3 пользователь Marc Worrell написал:

[Marc Worrell]

Ha, I think I missed some essential update here :)

This is great, thanks!

- Marc

[David Welton]

You might want to track the 'devel' branch, as its where a lot of
these things land.

David N. Welton

http://www.welton.it/davidw/

http://www.dedasys.com/