(Sorry the the vague title, if anyone want to re-edit, be my guest!)

I have these tables and views for a factory inventory module :

+----------------------------+    +--------------------------+
| inv_items                  |    | inv_items_stock          |
+----------------------------+    +--------------------------+
| id bigserial (PK)          |    | item_id (FK) (inv_items) |
| name character varying(32) |    | qty integer              |
| is_group boolean           |    | ...                      |
| ...                        |    +--------------------------+

+---------------------------+     +---------------------------------+
| inv_items_group           |     | inv_items_production            |
+---------------------------+     +---------------------------------+
| group_id (FK) (inv_items) |     | item_id (FK) (inv_items)        |
| item_id (FK) (inv_items)  |     | unit_name character varying(16) |
+---------------------------+     | unit_qty number(12,4)           |

+----------------------------------+   +------------------------------+
| inv_items_vw                     |   | inv_items_groups_vw          |      
+----------------------------------+   +------------------------------+
| id (inv_items)                   |   | id (inv_items)               |
| name (inv_items)                 |   | name (inv_items)             |
| qty (inv_items_stock)            |   | ...                          |
| unit_name (inv_items_production) |   | items (json[]) (inv_item_vw) |
| unit_qty (inv_items_production)  |   +------------------------------+
| ...                              |

Now, for another view, I need to fetch 1 row from a given item_id and return either from inv_items_vw or inv_items_groups_vw, something like

  FROM (SELECT id, name, qty, unit_name, unit_qty, ..., null
          FROM inv_items_vw
        SELECT id, name, null, null, null, ..., items
          FROM inv_items_groups_vw) item
 WHERE item.id = @id

But that one last query returns ERROR: could not identify an equality operator for type json.

I understand the error, and why I get this, nevertheless, I need this query :)

How can I fix this and return the proper row from either views?

2 Answers 2


A UNION by default will filter out duplicate rows (essentially adding an implicit DISTINCT) which means comparing the rows output to filter them.

If you have duplicate rows that you need filtering out then you may need to cast the JSON type into something else (can you cast it to a string type? - I'm not familiar with postgres and JSON support).

If you do not have duplicate rows to worry about (or you in fact want to keep them) then use UNION ALL instead of a plain UNION and the comparisons will not be necessary. In fact you are likely to find that UNION ALL is more efficient, it is better to use UNION ALL at all times except when you explicitly need duplicate row removal.

  • No, there won't be any duplicates. Thanks for the tip! Aug 8, 2014 at 16:44

@David already gave excellent advice concerning UNION ALL.

As to the immediate reason of the error: the data type json has no equality operator (=) defined, because equality is extremely hard to establish for json in text representation. There is an infinite number of different text representations for the same json value.

However, this is different for jsonb in the upcoming release Postgres 9.4. 'b' is for binary, because values are converted to a binary format. Consequently, there is also an equality operator making UNION queries possible - though that would be the wrong tool in your case anyway.

Related answer with more:

  • Yeah, I have read about 9.4 changes, and also the possible future implementation of foreign key arrays in future versions, etc. etc. This is why I tagged 9.3 :) Aug 8, 2014 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.