Hello,
I want to solve the following problem:
CREATE TABLE Ref ( Id int ) ;
CREATE TABLE Import ( Id int,
Other varchar(42),
Flag int,
Ts timestamp ) ;
CREATE TABLE Data ( Id int,
Other varchar(42) ) ;
The table Import will be filled by a COPY FROM statement and contains
no checks for referential integrity. The columns Id and Other have to
be moved to the table Data if the table Ref contains the Id. If not
Flag should get a certain value that something went wrong. Moreover
Import should only contain one representation of a dataset with equal
Id and Other column and I would like to store the newest one (this
is the reason for the timestamp).
I tried to do the following approach:
CREATE LOCAL TEMPORARY TABLE ImportOK () INHERITS (Import) ;
INSERT INTO ImportOK SELECT * FROM Import i
INNER JOIN Ref r ON i.Id = r.Id;
DELETE FROM Import WHERE Id IN (SELECT Id FROM ImportOK) ;
The idea was that the latest statement should get rid of all valid
data sets from Import. The valid datasets now could be moved to Data
and I could afterwards check Import for duplicated data sets.
Unfortunately the latest statement is so terribly slow that I can't
imagine that there is a better way to do this.
It seems like a very beginner question but I have no real clue how
to do this right. Probably the solution has to be done completely
different.
Thanks for your patience
Andreas.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
