Differentiate a schema from a table?

Question

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.

Also post the output of `SHOW search_path;` please - and (as always) your version of Postgres. Plus, a ***verbatim*** copy of the error message. — Erwin Brandstetter, May 21 '14 at 20:49

Erwin Brandstetter · Accepted Answer · 2014-05-22T02:38:56.080

1

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.

edited May 22 '14 at 02:38

answered May 21 '14 at 20:43

Erwin Brandstetter

605,456
145
1,078
1,228

Just a quick question, what does the sub; mean? – Padagomez May 21 '14 at 20:52
1

@Padagomez: `sub` is the alias for the subquery I mentioned - an arbitrary name. I added a link to the manual. – Erwin Brandstetter May 21 '14 at 20:53
Thank you. I don't need all the parenthesis separating the unions then? – Padagomez May 21 '14 at 21:00
1

@Padagomez: Nope. Those are only needed if you want to include `ORDER BY` or `LIMIT` in an individual `SELECT`, whioch would normally only apply to the whole of the `UNION` query. [Example with more info.](http://stackoverflow.com/questions/11828772/postgresql-combine-multiple-select-statements/11833128#11833128) – Erwin Brandstetter May 21 '14 at 21:03
For some reason it is still giving me the same error – Padagomez May 21 '14 at 21:16
Never mind, found it. It was a type, I didn't have an '=' sign in one of them – Padagomez May 22 '14 at 16:02

Differentiate a schema from a table?

1 Answers1