Compare Python list to table rows

Question

I have a Python list which looks like this:

[{'id': 5, 'field1': True}, {'id': 6, 'field1': False}]

It's just a list of dictionaries containing key/value pairs. I want to look up rows from a table in a Postgres database that match on id and differ on field1.

Say my table looks like:

id   field1
-----------
5    True
6    True

Only the second row should be in the result: it matches on id = 6 but differs on field1 = true

Is there any way to achieve this with SQL or would I need to loop through it manually? My use case involves a lot of rows, and this process would be repeated many times, so I'm trying to find the most efficient way to do it.

score 1 · Answer 1 · answered Jun 20 '22 at 23:58

1

There's a couple of ways to do this in PostgreSQL, using a WHERE IN clause:

SELECT *
FROM test
WHERE id IN (5, 6) AND (id, field1) NOT IN ((5, True), (6, False))

or a JOIN to a values table:

SELECT test.*
FROM test
JOIN (VALUES (5, True), (6, False)) AS v(id, field1)
ON test.id = v.id AND test.field1 != v.field1

In both cases for your sample data the result is

id  field1
6   t

With large amounts of data I would expect the JOIN query to be most efficient. You can build the VALUES part of the query using something like:

ll = [{'id': 5, 'field1': True}, {'id': 6, 'field1': False}]
','.join(str(tuple(d.values())) for d in ll)

which for your sample data gives:

(5, True),(6, False)

answered Jun 20 '22 at 23:58

Nick

138,499
22
57
95

Thanks for the great answer! Facing one issue tho: JOIN (VALUES ('0123456789', 5), ('5123456789', 3), ('9123456789', 5)) This is how it shows my sql query. However, when I try to execute it, I get the following error: django.db.utils.ProgrammingError: syntax error at or near "'(''0123456789'', 5), (''5123456789'', 3), (''9123456789'', 5)'" LINE 4: JOIN (VALUES '(''0123456789'', 5), (''5123456789'', 3), (''9... As you can see, I'm using Django and trying to embed the string of tuples with %s (prepared statements) – OhMad Jun 21 '22 at 10:00
You won’t be able to use the values string in a prepared query, you’ll need to instantiate each tuple separately in the query. Perhaps something like this https://stackoverflow.com/questions/2253494/passing-param-to-db-execute-for-where-in-int-list – Nick Jun 22 '22 at 01:27

score 1 · Answer 2 · answered Jun 21 '22 at 00:14

Your data looks like a JSON array, which you can pass as is:

SELECT t.*
FROM   jsonb_array_elements('[{"id": 5, "field1": true}, {"id": 6, "field1": false}]'::jsonb) j(obj)
JOIN   tbl t ON t.id     =  (obj->>'id')::int
            AND t.field1 <> (obj->>'field1')::bool

The same as prepared statement (Python has its own feature for this):

PREPARE qr1(json) AS 
SELECT t.*
FROM   json_array_elements($1) j(obj)
JOIN   tbl t ON t.id     =  (obj->>'id')::int
            AND t.field1 <> (obj->>'field1')::bool;

Then:

EXECUTE qr1('[{"id": 5, "field1": true}, {"id": 6, "field1": false}]');

db<>fiddle here

Be sure to have an index on tbl (id, field1).

Compare Python list to table rows

2 Answers2