Postgresql array in array

[kalra19]

Want to generate below query using pypika for postgresql.

select name, employee_id from employee where phone_no && ARRAY['7377','3877','9277']::varchar[]

How to create above query using pypika? Is there any way we can do that?

[wd60622]

So there is an Array object which when used with PostgreSQLQuery will give the ARRAY[...] syntax

from pypika import Field, Array
from pypika.dialects import PostgreSQLQuery

query = (
    PostgreSQLQuery
    .from_("employee").select("name", "employee_id")
    .where(Array("7377", "3877", "9277"))
)

which gets you some of the way

SELECT "name","employee_id" 
FROM "employee" 
WHERE ARRAY['7377','3877','9277']

I suspect the && will have to be built out like this class and the ::varchar[] will have to be built off of the Term class

EDIT: Try out the ArithmeticExpression for the && and build out custom operation like in #779

[wd60622]

Hi @kalra19, were you able to find a solution or work around for this?

[kalra19]

No as of now, but for a work around solution, i have used sql string composition to generate the query.

FYR: https://www.psycopg.org/docs/sql.html