Re: [SQL] COPY FROM query
Hi,
For what it's worth, WindowsXP (at least) seems to currently support
forward slashes at the OS level, so this Postgres behavior isn't as odd
as it might seem. If you enclose your paths with double quote (")
marks, Windows will even accept Unix style paths for some instructions
on the command line:
dir "/temp"
Feeding Windows API calls with forward slashes seems to work with
everything I've tried so far, so using them in Postgres seems perfectly
smart, and reasonable..
Hope that helps,
Steve
At 11:03 AM 2/12/2007, [EMAIL PROTECTED] wrote:
Date: Mon, 12 Feb 2007 09:09:08 +0900
From: Paul Lambert <[EMAIL PROTECTED]>
To: Joe <[EMAIL PROTECTED]>
Cc: pgsql-sql@postgresql.org
Subject: Re: COPY FROM query.
Message-ID: <[EMAIL PROTECTED]>
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
Re: [SQL] COPY FROM query.
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] COPY FROM query.
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] COPY FROM query.
"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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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
Re: [SQL] COPY FROM query.
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
Re: [SQL] COPY FROM query.
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
