+2 votes
in Databases by (11.2k points)

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)

);

1 Answer

0 votes
by (16.9k points)

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)
);

...