Join two datasets by two columns in PostgreSQL

Question

I have a PostgreSQL query, where I create temp table by joining another temp table, and table from my database.

DROP TABLE IF EXISTS other_temp_table;
CREATE TEMP TABLE other_temp_table AS
  SELECT *
        FROM base.main_data
        WHERE  period_start_time::DATE >= '2017-06-20' AND period_start_time::DATE <= '2017-07-26';
------------------------------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS first_temp_table;
CREATE TEMP TABLE first_temp_table AS
SELECT *
    FROM _temp_table
LEFT JOIN base."UL_parameters"
        ON temp_table.base_col::INT = base."UL_parameters".base_col::INT
        and temp_table.sec_col::INT= base."UL_parameters".sec_col::INT;

Now, the problem is `ERROR: column "sec_col" specified more than once. But when I delete sec_col join condition and do just base_col, everything is ok. I think that I need to create an alias, but not sure how.

I don't know the PostgreSQL syntax, but DROP TABLE IF EXISTS _temp_table; CREATE TEMP TABLE _temp_table AS SELECT * FROM _temp_table looks strange to me — Raphael Müllner
– Raphael Müllner, Commented Sep 14, 2017 at 9:39
You try to join _temp_table but then refer to temp_table (without the first underscore) — verhie
– verhie, Commented Sep 14, 2017 at 9:46
@verhie Thanks, but it's not that a problem, I couldn't copy original syntax because it's under NDA and then I made mistake while typing. I'm not sure how to create alias for sec_col from base.UL_parameters — jovicbg
– jovicbg, Commented Sep 14, 2017 at 9:51

Raphael Müllner · Accepted Answer · 2017-09-14 09:48:02Z

2

I think that the problem is that there is a sec_col column in both joined tables. Try to replace select * with select column1, column2, ....

It's a good practice to avoid select * in general, because it can cause errors when the table definition changes.

answered Sep 14, 2017 at 9:48

Raphael Müllner

4383 silver badges7 bronze badges

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

1 Comment

jovicbg Over a year ago

Thank you very much, I have change order of tables while join and then select columns, not select *.

Collectives™ on Stack Overflow

Join two datasets by two columns in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related