COPY FROM query.

I'm attempting to copy from a table into a file using a select query
inside the copy.

The following is my command:

COPY (SELECT DISTINCT ON (dealer_id,appraisal_id) * FROM
appraisals_temp) TO 'c:\autodrs_appraisal_new.txt' WITH DELIMITER AS '^'
CSV HEADER;

I get the following returned:

WARNING: nonstandard use of escape in a string literal
LINE 1: ...ealer_id,appraisal_id) * FROM appraisals_temp) TO 'C:\autodr...
^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.

ERROR: relative path not allowed for COPY to file
SQL state: 42602

(The caret character is pointing to the M in FROM)

As far as I can see this looks to be structured the same as an example
in the manual:

COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO
'/usr1/proj/bray/sql/a_list_countries.copy';

(with the exception of me using a distinct on)

If I run the inner select statement by itself I get results returned,
but as soon as I wrap it with a copy from I get the above error.

As a secondary question, is there any way to get select into to put the
results in an existing table rather then having it create a new table?

To give you a better understanding of what I am doing, my current
problem is as follows.

I have a data extract running on a VMS system which generates a caret
delimited text file. This file may (due to database setup on the VMS
side which can't be fixed) contain entries which violate the duplicate
key constraints of my Postgres database (i.e. the same record appears in
the extracted text file twice) which will give me errors when I try to
copy the file into the table in Postgres. In order to get around this, I
am making a temporary table with no primary key, copying the data from
the file into this table, doing the above select distinct to get only
unique records and putting the result into the original table (which has
been truncated of course)
I can't find any way of selecting into an existing table, only selecting
into a new table, which means setting up the primary/foreign keys and
such again which I don't want to have to do each time I run the script.

The following is the script I'm using:

DROP TABLE appraisals_temp;
CREATE TABLE appraisals_temp AS SELECT * FROM appraisals WHERE 1=0;
TRUNCATE TABLE appraisals;
COPY appraisals_temp FROM 'c:\autodrs_appraisal.txt' WITH DELIMITER AS
'^' CSV HEADER;
COPY (SELECT DISTINCT ON (dealer_id,appraisal_id) * FROM
appraisals_temp) TO 'c:\autodrs_appraisal_new.txt' WITH DELIMITER AS '^'
CSV HEADER;
COPY appraisals FROM 'c:\autodrs_appraisal_new.txt' WITH DELIMITER AS
'^' CSV HEADER;

I'd be greatful if anyone could explain why my copy to does not work,
also greatful if anyone can offer any suggestions on a better way to do
what I am doing (if such a way exists - which I'm sure it would)

Thanks in advance.

--
Paul Lambert
Database Administrator
AutoLedgers