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

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.