[SOLVED] Error while creating a table joining two tables in pgadmin

Error while creating a table joining two tables in pgadmin

I have two tables one is customer data, and the other is geography data which has a common field geography key. I joined the two tables using the following query:

SELECT c.*, g.*
FROM customer AS c
LEFT JOIN geography AS g
ON c.geographykey = g.geographykey;

I got the output.

But, trying to create a table using joined data is giving an error. sharing the query and error message below:

CREATE TABLE customer_geo AS
    (SELECT c.*, g.*
    FROM customer AS c
    LEFT JOIN geography AS g
    ON c.geographykey = g.geographykey);

ERROR: column "geographykey" specified more than once

SQL state: 42701 Please help by pointing out what I am doing wrong. I am a beginner.

Solution

Postgres allows duplicate column names in the result of a SELECT query. (Though some clients have a problem with that, too.)

But it's not possible to create a table with duplicate column names.

Either spell out columns in the SELECT list to omit duplicates. That's also more robust against future changes in the source tables. (If that matters.)

Or just remove duplicate columns used for joining (geographykey in your case) with the USING clause. Your SELECT list has to change, too. SELECT c.*, g.* would still bring in two copies.

CREATE TABLE customer_geo AS
SELECT *
FROM   customer  c
LEFT   JOIN geography g USING (geographykey);