SELECT UNION from two views of the same table

Question

(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

SELECT * 
  FROM (SELECT id, name, qty, unit_name, unit_qty, ..., null
          FROM inv_items_vw
         UNION
        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?

score 7 · Accepted Answer · answered Aug 08 '14 at 15:45

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! – Yanick Rochon Aug 08 '14 at 16:44 — Yanick Rochon, Aug 08 '14 at 16:44

score 1 · Answer 2 · edited Apr 13 '17 at 12:42

@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.

SELECT UNION from two views of the same table

2 Answers2