TransWikia.com

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

Database Administrators Asked by G. Iannello on August 12, 2020

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.

One Answer

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:

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:

Answered by Erwin Brandstetter on August 12, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP