sql - 错误 : column "owner_name" specified more than once

Question

我正在尝试创建一个新表，使用在 B 表中不包含 account_id 的 A 表。但是，我遇到了一个错误:

ERROR: column "owner_name" specified more than once

postgres 查询:

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;

Answer 1

您应该明确引用至少一个具有公共(public)列(例如:- owner_name 和 account_id )的表的所有列名，并且只指定一次，或者为公共(public)列名提供单独的别名。否则，目标表的列将使用哪一列会变得不明确。

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;

或

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;