CODEWITHSUNDEEP
sqlpostgresqlschema
Padagomez
Padagomez

Reputation: 1254

Differentiate a schema from a table?

In Postgresql, the query is getting an error for saying that schema "purchase_order" doesn't exist. 

from ((select a.item_no
    from stocka a
        join order_item oi
            on (oi.item_no = a.item_no)
        join purchase_order po
            on (po.order_no = oi.order_no)
    where po.location = 'LocationA'
    ) UNION ALL
    (select b.item_no
    from stockb b
        join order_item oi
            on (oi.item_no = b.item_no)
        join purchase_order po
            on (po.order_no = oi.order_no)
    where po.location = 'LocationB'
    ))

The Union is for the from clause

It is for some reason saying that purchase_order isn't a table, but a schema.

Upvotes: 0

Views: 57

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656666

The error you describe is not due to the code you posted, which should work - given the objects exist.

I only added an alias for the subquery: sub (required!), simplified with USING (optional), removed redundant parentheses and reformatted:

SELECT *
FROM  (
   SELECT a.item_no
   FROM   stocka a
   JOIN   order_item     oi USING (item_no)
   JOIN   purchase_order po USING (order_no)
   WHERE  po.location = 'LocationA'

   UNION ALL
   SELECT b.item_no
   FROM   stockb b
   JOIN   order_item     oi USING (item_no)
   JOIN   purchase_order po USING (order_no)
   WHERE  po.location = 'LocationB'
   ) sub;

Depending on db layout (table definitions?) and exact requirements this could possibly be simplified further.

Upvotes: 1

Related Questions