How do I send an array of strings to PG from PHP without the former becoming confused?

Question

I want to do this, but for more than one string:

SELECT * FROM table WHERE label <> $1

That selects all records from table where the label column is different from a single string. It works.

But now I want to do the same, but instead of just checking a single string, I have an array of strings in PHP, [ 'ignore string 1', 'ignore string 2', 'ignore string 3' ], which I wish to have excluded.

If I just send an array of strings to that same query for the $1, it gives errors. I've tried a lot of things now. I even attempted:

SELECT * FROM table WHERE label NOT IN $1

But that doesn't work for an application-provided array of strings.

Do I simply need to do ::array to convert it or something? I couldn't get it to work either.

Yes, it's most common to have a different table in the database with "ignore phrases", but in this case, I need to provide a list of such strings as an array from the application.

Answer 1

Use instead:

SELECT * FROM table WHERE label <> ALL ($1);

Then you can pass an actual array for $1 - while (NOT) IN expects a list of values. See:

• How to use ANY instead of IN in a WHERE clause with Rails?

If NULL values can be involved on either side, either query is problematic. You may want the form:

SELECT * FROM table WHERE (label = ANY ($1) IS NOT TRUE);

See:

• Why does my SELECT query not return null values?