Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
    
No, there won't be any duplicates. Thanks for the tip! –  Yanick Rochon Aug 8 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:

share|improve this answer
    
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 :) –  Yanick Rochon Aug 8 at 17:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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