Postgres JOIN on timestamp fails

Question

Trying to do a simple FULL OUTER JOIN on a timestamp and it is outputing the full cartesian product instead of matching identical dates. What is wrong here?

SQL Fiddle with example data

CREATE TABLE A (
    id INT,
    time TIMESTAMP
);

CREATE TABLE B (
     id INT,
     time TIMESTAMP
);

Query:

SELECT A.Id AS a_id, A.Time AS a_time, B.Id AS b_id, B.Time AS b_time 
FROM A
FULL OUTER JOIN B ON A.Time = B.Time

-- This works:
-- SELECT A.id, A.time, B.id, B.time
-- FROM A
-- FULL OUTER JOIN B ON A.id = B.id

A full outer join produces a cartesian product by definition. Maybe you mean inner join? — Scott Marlowe
– Scott Marlowe, Commented Nov 7, 2017 at 21:25

Juan Carlos Oropeza · Accepted Answer · 2017-11-07 20:41:23Z

2

You are using the wrong parameters on TO_DATE() on your INSERTS easy to test if you do

SELECT * FROM A; 
SELECT * FROM B;

Instead of

 TO_DATE('01-01-2002', '%d-%m-%Y')

Should be:

 TO_DATE('01-01-2002', '%DD-%MM-%Y')

SQL DEMO

edited Nov 7, 2017 at 20:41

answered Nov 7, 2017 at 20:36

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sebastien · Accepted Answer · 2017-11-07 20:46:26Z

1

In your sql fiddle all your inserted dates are the same because your date pattern is wrong. Try using TO_DATE('01-01-2002', 'DD-MM-YYYY') instead of TO_DATE('01-01-2002', '%d-%m-%y')

answered Nov 7, 2017 at 20:46

Sebastien

6,7105 gold badges31 silver badges39 bronze badges

Collectives™ on Stack Overflow

Postgres JOIN on timestamp fails

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related