From: | Andreas Tille <tillea(at)rki(dot)de> |
---|---|
To: | PostgreSQL SQL <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Separating data sets in a table |
Date: | 2002-08-26 09:46:12 |
Message-ID: | Pine.LNX.4.44.0208261124090.27720-100000@wr-linux02.rki.ivbb.bund.de |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 26 Aug 2002, Oliver Elphick wrote:
> Create a temporary table (no constraints)
>
> CREATE TEMP TABLE temptable AS
> (SELECT * FROM tablename LIMIT 1);
> DELETE FROM temptable;
>
> Copy all data into the temporary table
>
> COPY temptable FROM 'filepath';
Up to this point I have no problems. The difference is that I have to
mark the invalid data sets by a flag which represents a "reason" why
the data were invalid. That's why I use an additional flag in the
table and I do not use a temporary table because I have to store the
"histrory" of invalid data (to bother the provider of the data to
fix it).
> Select from the temporary table all items that satisfy the
> constraints, insert them into the real table and delete them from
> the temporary table:
>
> BEGIN;
> INSERT INTO tablename (SELECT * FROM temptable WHERE ...);
> DELETE FROM temptable WHERE ...;
> COMMIT;
>
> All good data should now be in place. The temporary table should
> now contain only those items that do not satisfy the constraints for
> the real table.
This was in my first atempt here.
The problem I have is that I need a JOIN to a further table and
I've got errors from the parser which let me guess that joins are not
allowed in INSERT statements ... at least I do not know how to do it
right if it should be possible. That's why I had the idea just to
set a certain flag and then do the insert of all data sets where flag = OK.
Kind regards
Andreas.
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Elphick | 2002-08-26 10:31:38 | Re: Separating data sets in a table |
Previous Message | Gaetano Mendola | 2002-08-26 09:29:37 | unsubscribe |