Postgresql – UNION queries with different number of columns

postgresqlunion

I have 2 queries on a table:

SELECT *, 1 as query_cr_0 FROM invoices LEFT OUTER JOIN customers ON customers.id = invoices.customer_id WHERE (customers.id IN (64, 66, 63, 62, 65))

SELECT *, 5 as query_cr_1 FROM invoices WHERE (invoices.state IN ('ready', 'out_for_delivery', 'delivered', 'closed')

I want to fetch all invoices that fulfill either of the queries.

My approach was to use UNION but LEFT OUTER JOIN in the first query prevents this:

ERROR: each UNION query must have the same number of columns

My second approach was to use something like this:

select invoices.id from invoices
where id in (SELECT invoices.id, 1 as query_cr_0 FROM invoices 
             LEFT OUTER JOIN customers ON customers.id = invoices.customer_id
             WHERE (customers.id IN (64, 66, 63, 62, 65)))
OR id in (SELECT invoices.id, 5 as query_cr_1 FROM invoices 
          WHERE (invoices.state IN ('ready', 'out_for_delivery', 'delivered', 'closed')))

But this throws an error:

subquery has too many columns

My purpose is to fetch the invoices with query_cr_0 and query_cr_1 value. For ex: An invoice might be satisfying both the conditions so in that case both query_cr_0 and query_cr_1 should be present. If an invoice satisfies only one of the queries corresponding query_cr must be present.

How to solve this issue?

Best Answer

You really should not use *. But if you want to use it anyway, write SELECT invoices.*, 1 to prevent the columns from the customers table from showing up.

Doing an outer join is pointless because any resulting rows that have NULL in customer.id would be filtered out by the WHERE.

And you do not actually need the join, because you have the customer ID value already in the invoices table:

SELECT ... FROM invoices WHERE customer_id IN (64, 66, 63, 62, 65)

Your second approach would work if you removed the second column (1 or 5) from the subqueries.

If you want the 1/5 values to show up in the result, then you must use your first approach (and you can use UNION ALL instead of UNION because there will not be duplicate rows).

Related Question