PostgreSQL Copying Data (tries to copy data that do not qualify WHERE)

When I try to copy data from my temporary table Tperson it tries to copy all the data, and some data do not have a matching PersonID in Customer.PersonID.

DROP TABLE IF EXISTS Tperson;

COPY Customer (CustomerID,PersonID) FROM 'C:\Users\DieCriminal\Desktop\data\Customer.txt' CSV HEADER;

CREATE TEMPORARY TABLE Tperson(PersonID integer,Title text,FirstName text,LastName text);
COPY Tperson FROM 'C:\Users\DieCriminal\Desktop\data\Person.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;

So it ends up like this:

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,)).

Also here are the Tables/Types: (some table/types will be altered after data loading)

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);

I want it to only copy data that have matching personIDs in both tables.That's what I thought WHERE clause would do but that's not the case. So any answer or any suggestion/tip about my code is welcomed.

Answers


SO it seems I am stupid and tired of reading right now. I forgot that INSERT applies to new rows, and I need UPDATE because I need to alter existing rows.

This worked fine:

DROP TABLE IF EXISTS Tperson;

COPY Customer (CustomerID,PersonID) FROM 'C:\Users\DieCriminal\Desktop\data\Customer.txt' CSV HEADER;

CREATE TEMPORARY TABLE Tperson(PersonID integer,Title text,FirstName text,LastName text);
COPY Tperson FROM 'C:\Users\DieCriminal\Desktop\data\Person.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;

Credits to: a_horse_with_no_name , he basicly solved my problem.Thanks.


Need Your Help

SQL sp_help_operator

sql sp-help-operator

Anyone know what group I need to belong to show up in the sp_help_operator list?

html onload not running javascript

javascript html function onload

I'm writing a game in javascript, and I had the world generator running fine, but when I created a startup function to run multiple things at once, my code doesn't run at all anymore. Can anyone se...