Thread: COPY FROM query.

COPY FROM query.

From

Paul Lambert

Date:

11 February 2007, 22:20:28

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

Re: COPY FROM query.

From

Paul Lambert

Date:

11 February 2007, 22:28:59

An INSERT INTO will fix my problem with needing the extra copy from and 
copy to.

I'm still curious as to why i was being told I couldn't specify a 
relative path though.

P.

-- 
Paul Lambert
Database Administrator
AutoLedgers

Re: COPY FROM query.

From

Joe

Date:

11 February 2007, 22:55:24

Hi Paul,

On Mon, 2007-02-12 at 08:19 +0900, Paul Lambert wrote:
> 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)

I believe that on Windows you need to use double backslashes, i.e., 'c:\
\autodrs_appraisal_new.txt', although the regular slash may also work,
i.e., 'c:/autodrs_appraisal_new.txt'.

Joe

Re: COPY FROM query.

From

Paul Lambert

Date:

11 February 2007, 23:10:18

Joe wrote:
> Hi Paul,
> 
> On Mon, 2007-02-12 at 08:19 +0900, Paul Lambert wrote:
>> 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)
> 
> I believe that on Windows you need to use double backslashes, i.e., 'c:\
> \autodrs_appraisal_new.txt', although the regular slash may also work,
> i.e., 'c:/autodrs_appraisal_new.txt'.
> 
> Joe
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
> 
> 

If this is the case, it is strange that the first copy statement works 
as that is also only using a single backslash.

Having said that, if I switch it to a forward slash it works without 
error... odd, given that weenblows standard is backslash.

Thanks for the help though.

-- 
Paul Lambert
Database Administrator
AutoLedgers

Re: COPY FROM query.

From

"Phillip Smith"

Date:

11 February 2007, 23:47:40

"Having said that, if I switch it to a forward slash it works without
error... odd, given that weenblows standard is backslash."

Yes, but PostgreSQL uses a back-slash as an escape character, which needs to
be used to escape itself at an application level before the O/S gets to deal
with it :)

-p

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Paul Lambert
Sent: Monday, 12 February 2007 11:09
To: Joe
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] COPY FROM query.

Joe wrote:
> Hi Paul,
>
> On Mon, 2007-02-12 at 08:19 +0900, Paul Lambert wrote:
>> 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)
>
> I believe that on Windows you need to use double backslashes, i.e., 'c:\
> \autodrs_appraisal_new.txt', although the regular slash may also work,
> i.e., 'c:/autodrs_appraisal_new.txt'.
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>

If this is the case, it is strange that the first copy statement works
as that is also only using a single backslash.

Having said that, if I switch it to a forward slash it works without
error... odd, given that weenblows standard is backslash.

Thanks for the help though.

--
Paul Lambert
Database Administrator
AutoLedgers

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: COPY FROM query.

From

Paul Lambert

Date:

11 February 2007, 23:52:51

Phillip Smith wrote:
> "Having said that, if I switch it to a forward slash it works without 
> error... odd, given that weenblows standard is backslash."
> 
> Yes, but PostgreSQL uses a back-slash as an escape character, which needs to
> be used to escape itself at an application level before the O/S gets to deal
> with it :)
> 
> -p
> 
> 

OK, that makes sense, cheers for the help.

-- 
Paul Lambert
Database Administrator
AutoLedgers

Re: COPY FROM query.

From

Richard Huxton

Date:

12 February 2007, 07:31:55

Paul Lambert wrote:
> Phillip Smith wrote:
>> "Having said that, if I switch it to a forward slash it works without 
>> error... odd, given that weenblows standard is backslash."
>>
>> Yes, but PostgreSQL uses a back-slash as an escape character, which 
>> needs to
>> be used to escape itself at an application level before the O/S gets 
>> to deal
>> with it :)
> 
> OK, that makes sense, cheers for the help.

Almost correct - PG is moving over to a more SQL standards-based 
interpretation of what should be done with backslashes in strings. The 
old behaviour will still be available by prefixing a string with E - 
E'like this'.

See manuals for details.

--   Richard Huxton  Archonet Ltd