CODEWITHSUNDEEP
sqlamazon-redshift
Michi
Michi

Reputation: 5481

UNION ALL and fill date column with NULL

DB-Fiddle

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    last_order DATE,
    customer VARCHAR(255)
);

INSERT INTO sales
(last_order, customer)
VALUES 
('2020-04-10', 'user_01'),
('2020-06-15', 'user_02'),
('2020-08-26', 'user_03');



CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    first_order DATE,
    customer VARCHAR(255)
);

INSERT INTO customers
(first_order, customer)
VALUES 
('2020-03-10', 'user_01'),
('2020-05-15', 'user_02'),
('2020-01-17', 'user_03');

Expected Result:

last_order   |   first_order     |
-------------|-------------------|----
2020-04-10   |                   |
2020-06-15   |                   |
2020-08-26   |                   |
             |    2020-03-10     |
             |    2020-05-15     |
             |    2020-01-17     |
             |                   |

In table sales I have the date of the last_order and in table customers I have the date of the first_order.
Now I want to UNION ALL both tables and therefore need to fill the column last_order and - respectively - the column first_order with NULL to keep the structure equal.

In postgresSQL I am using this query which works without any issue:

SELECT
s.last_order AS last_order,
NULL AS first_order
FROM sales s

UNION ALL

SELECT
NULL AS last_order,
c.first_order AS first_order
FROM customers c

However, when I apply this query to amazon-redshift I get error:

Invalid operation: UNION types date and text cannot be matched

How do I need to modify the query in amazon-redshift to achieve the expected result?

Upvotes: 1

Views: 3592

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

An amusing way to do this is:

select s.last_order, c.first_order
from sales s full join
     customers c
     on 1 = 0;  -- never true!

Upvotes: 2

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

As suggested by @Serg please try to cast null into date like below:

Query:

 SELECT
 s.last_order AS last_order,
 NULL::date AS first_order
 FROM sales s
 
 UNION ALL
 
 SELECT
 NULL::date AS last_order,
 c.first_order AS first_order
 FROM customers c

Output:

last_order first_order
2020-04-10 null
2020-06-15 null
2020-08-26 null
null 2020-03-10
null 2020-05-15
null 2020-01-17

db<>fiddle here

Upvotes: 0

Related Questions