CODEWITHSUNDEEP
sqlpostgresql
Jov
Jov

Reputation: 93

ERROR: column "owner_name" specified more than once

I'm trying to create a new table, using A table that doesn't contain account_id in B table. However, I encountered an error:

ERROR: column "owner_name" specified more than once

postgres query:

Create table dallas_50000_2 
AS SELECT * from "2018_texas_county_dallas_individuals_person" A 
LEFT JOIN dallas_50000_copy B 
ON A.account_id = B.account_id 
WHERE B.account_id IS NULL;

Upvotes: 4

Views: 16828

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31736

You should either explicitly quote all the column names of at least one table that has the common columns (eg:- owner_name and account_id ) and specify them only once or give a separate alias for the common column names. Otherwise it becomes ambiguous whose column to be used for the target table's columns.

Create table dallas_50000_2 
AS SELECT A.* , B.col1 , B.col2 --other columns that are not common to A
from "2018_texas_county_dallas_individuals_person" A 
LEFT JOIN dallas_50000_copy B 
ON A.account_id = B.account_id 
WHERE B.account_id IS NULL;

OR

Create table dallas_50000_2 
AS SELECT A.account_id as a_account_id, A.owner_name as A_owner_name, 
B.col1 , B.col2,B.owner_name as B_owner_name
from "2018_texas_county_dallas_individuals_person" A 
LEFT JOIN dallas_50000_copy B 
ON A.account_id = B.account_id 
WHERE B.account_id IS NULL;

Upvotes: 4

Related Questions