PostgreSQL - ERROR: column "event_type" specified more than once

Question

When I run the following query, I get "ERROR:  column "event_type" specified more than once". If I remove "create temporary table" statement from the query, the query works fine. Why "create temporary table" is not working?

create temporary table bob as (

select X1.person_id, X1.event_type, X1.event_period, X2.event_type, X2.event_period

FROM mdcr2003_2015_mortality.events_of_interest X1, mdcr2003_2015_mortality.events_of_interest X2

WHERE X1.person_id = X2.person_id

AND X1.event_type = 'death'

AND upper(X1.event_period) <= lower(X2.event_period)

);

Answer 1

You cannot have two columns with the same name in your temporary table 'bob'. If you run the query without "create temporary table" statement, it will show the following columns: "  person_id  | event_type |      event_period       |      event_type      |      event_period". That's why you are getting the error. To fix the error, rename the columns using AS clause.

create temporary table bob as (
    SELECT DISTINCT X1.person_id, X1.event_type as event1, X1.event_period as event_period1, X2.event_type as event2, X2.event_period as event_period2
    FROM mdcr2003_2015_mortality.events_of_interest X1, mdcr2003_2015_mortality.events_of_interest X2
    WHERE X1.person_id = X2.person_id
    AND X1.event_type = 'death'
    AND upper(X1.event_period) < lower(X2.event_period)
);