PostgreSQL – SELECT UNION from Two Views of the Same Table

postgresqlpostgresql-9.3

(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?

Best Answer

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.

Related Question