0

Using sqlalchemy expression language how can I query a postgresSQL CITEXT array?

SQL: 

SELECT id FROM message_list WHERE recipients @> ARRAY['user@example.com']::citext[];
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
sol
  • 15
  • 1
  • 5
  • https://stackoverflow.com/a/23545530/2681632 – Ilja Everilä Oct 13 '18 at 17:59
  • Is `recipients` itself a `citext[]` column? – Ilja Everilä Oct 13 '18 at 19:50
  • @Ilja Everilä Actually `message_list` is view. `Message_recipients` is another relation and address is `citext[]` column. Definition: `array_agg(message_recipient.address ORDER BY message_recipient.header_id) AS recipients,...` I was able to query using `messagelist.c.recipients.any('user@example.com')`. I wish I could make it work like: `messagelist.c.recipients.any('%user@example.com%')` – sol Oct 14 '18 at 12:38
  • `messagelist.c.recipients.contains(['user@...'])` would produce the `@>` expression, given that the view is correctly reflected/defined. If you want `LIKE ANY`, try the solution from here: https://stackoverflow.com/a/42573552/2681632 – Ilja Everilä Oct 14 '18 at 14:06
  • `... messagelist.c.recipients.like(any_(to))...` produces `sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does not exist: citext[] ~~ unknown LINE 3: ...st.subject ILIKE 'HI' AND message_list.recipients LIKE ANY (... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.` – sol Oct 14 '18 at 15:11
  • As for `...messagelist.c.recipients.contains['user@...']` - TypeError: 'method' object is not subscriptable – sol Oct 14 '18 at 15:13
  • 1
    Well, the latter is obviosly wrong: you're using subscript access on a method instead of calling it with a list argument. The former is not so clear and kindof underlines the fact that this question would benefit from a [mcve]. – Ilja Everilä Oct 14 '18 at 15:38

0 Answers0