当我尝试从临时表Tperson复制数据时,它会尝试复制所有数据,并且某些数据在Customer.PersonID中没有匹配的PersonID。
DROP TABLE IF EXISTS Tperson;
COPY Customer (CustomerID,PersonID) FROM 'C:UsersDieCriminalDesktopdataCustomer.txt' CSV HEADER;
CREATE TEMPORARY TABLE Tperson(PersonID integer,Title text,FirstName text,LastName text);
COPY Tperson FROM 'C:UsersDieCriminalDesktopdataPerson.txt' CSV HEADER;
INSERT INTO Customer (Person.PersonID,Person.Title,Person.FirstName,Person.LastName)
SELECT Tperson.PersonID,Tperson.Title,Tperson.FirstName,Tperson.LastName
From Tperson,Customer
WHERE Customer.PersonID = Tperson.PersonID;
SELECT *
FROM Customer;
所以它最终是这样的:
ERROR: null value in column "customerid" violates not-null constraint
DETAIL: Failing row contains (null, null, (291,Mr.,Gustavo,Achong,)).
********** Error **********
ERROR: null value in column "customerid" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (null, null, (291,Mr.,Gustavo,Achong,)).
这里还有表/类型:(某些表/类型将在数据加载后更改)
CREATE TABLE Customer(
CustomerID integer,
PersonID integer,
Person PersonType,
PRIMARY KEY(CustomerID));
CREATE TYPE PersonType AS(
PersonID integer,
Title text,
FirstName text,
LastName text,
Address AddressType[]);
CREATE TYPE AddressType AS(
AddressID integer,
AddressLine1 text,
AddressLine2 text,
City text,
PostalCode text);
我希望它只复制两个表中具有匹配 personID 的数据。这就是我认为 WHERE 子句会做的事情,但事实并非如此。因此,欢迎对我的代码提出任何答案或任何建议/提示。
所以看来
我现在很愚蠢,厌倦了阅读。我忘记了插入适用于新行,我需要更新,因为我需要更改现有行。
这工作正常:
DROP TABLE IF EXISTS Tperson;
COPY Customer (CustomerID,PersonID) FROM 'C:UsersDieCriminalDesktopdataCustomer.txt' CSV HEADER;
CREATE TEMPORARY TABLE Tperson(PersonID integer,Title text,FirstName text,LastName text);
COPY Tperson FROM 'C:UsersDieCriminalDesktopdataPerson.txt' CSV HEADER;
UPDATE Customer
SET (Person.PersonID,Person.Title,Person.FirstName,Person.LastName) = (Tperson.PersonID,Tperson.Title,Tperson.FirstName,Tperson.LastName)
From Tperson
WHERE Customer.PersonID = Tperson.PersonID;
SELECT *
FROM Customer;
学分:a_horse_with_no_name,他基本上解决了我的问题。谢谢。