CODEWITHSUNDEEP
pythonjsonpostgresql-9.2psycopg2
Gregory Arenius
Gregory Arenius

Reputation: 3204

How do I return json in a psycopg2 query?

I am using psycopg2 (version 2.5.4) to query a PostgreSQL database (version 9.2.7). One of the columns I'm querying is a json type which psycopg2 is documented as being able to handle. However, I'm receiving the following error:

psycopg2.ProgrammingError: could not identify an equality operator for type json

I'm not performing any equality operations on the column in question, just returning it with a select statement. The query is simple and fairly straight forward:

SELECT DISTINCT me.measure_id, me.choices  
FROM measures ME  
WHERE TRUE AND me.measure_id IN (3)  
ORDER BY me.measure_id;

me.choices is the only column of type JSON in the table. I've searched extensively and found nothing and can't think of a way forward. Any advice will be appreciated.

Upvotes: 1

Views: 1624

Answers (1)

Erin Call
Erin Call

Reputation: 1784

select distinct requires that each full row be distinct. So when you say select DISTINCT me.measure_id, me.choices you're asking postgresql to perform equality operations on choices to see if two rows are the same.

Assuming measure_id is the primary key for measures you can drop the distinct. Otherwise you could use distinct on to grab just one row for a measure_id.

Upvotes: 1

Related Questions