Обсуждение: Can't quote_literal with COPY FROM PROGRAM
Hi,
This command, which generates a JSON object as output, has some escaped data with backslashes: (see line 91 here: https://pastebin.com/D4it8ybS)
C:\\Portable\\curl\\curl.exe -k "https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted"'
I use the COPY command to pull it into a temp table like so:
COPY temp_maps_api from program 'C:\\Portable\\curl\\curl.exe -k "https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted"';
However copy eats those backslashes. I need to use quote_literal() but that's a syntax error. For some reason the COPY command doesn't allow for ESCAPE to work with programs, only CSV.
I tried using WITH BINARY but I get the error message: "COPY file signature not recognized"
Does anyone know how to make COPY FROM PROGRAM take the output literally?
Thanks,
Mark
Can you pipe the curl output through sed s,\\,\\\\,gHi,This command, which generates a JSON object as output, has some escaped data with backslashes: (see line 91 here: https://pastebin.com/D4it8ybS)C:\\Portable\\curl\\curl.exe -k "https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted"'I use the COPY command to pull it into a temp table like so:COPY temp_maps_api from program 'C:\\Portable\\curl\\curl.exe -k "https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted"';However copy eats those backslashes. I need to use quote_literal() but that's a syntax error. For some reason the COPY command doesn't allow for ESCAPE to work with programs, only CSV.I tried using WITH BINARY but I get the error message: "COPY file signature not recognized"Does anyone know how to make COPY FROM PROGRAM take the output literally?Thanks,Markᐧ
Thanks Rob,
Since I'm on Windows and Windows batch sucks I just ended up doing the JSON parsing with node.js
To be honest this whole affair with COPY FROM program seems like a bug to me though.
On Mon, Dec 31, 2018 at 1:59 PM Rob Sargent <robjsargent@gmail.com> wrote:
Can you pipe the curl output through sed s,\\,\\\\,gHi,This command, which generates a JSON object as output, has some escaped data with backslashes: (see line 91 here: https://pastebin.com/D4it8ybS)C:\\Portable\\curl\\curl.exe -k "https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted"'I use the COPY command to pull it into a temp table like so:COPY temp_maps_api from program 'C:\\Portable\\curl\\curl.exe -k "https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted"';However copy eats those backslashes. I need to use quote_literal() but that's a syntax error. For some reason the COPY command doesn't allow for ESCAPE to work with programs, only CSV.I tried using WITH BINARY but I get the error message: "COPY file signature not recognized"Does anyone know how to make COPY FROM PROGRAM take the output literally?Thanks,Markᐧ
On 12/31/18 9:36 AM, Mark Mikulec wrote: > Hi, > > This command, which generates a JSON object as output, has some escaped > data with backslashes: (see line 91 here: https://pastebin.com/D4it8ybS) > > C:\\Portable\\curl\\curl.exe -k > "https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted"' > > I use the COPY command to pull it into a temp table like so: > > COPY temp_maps_api from program 'C:\\Portable\\curl\\curl.exe -k > "https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted"'; So temp_maps_api has a single JSON field? > > However copy eats those backslashes. I need to use quote_literal() but > that's a syntax error. For some reason the COPY command doesn't allow > for ESCAPE to work with programs, only CSV. > > I tried using WITH BINARY but I get the error message: "COPY file > signature not recognized" > > Does anyone know how to make COPY FROM PROGRAM take the output literally? > > Thanks, > Mark > ᐧ -- Adrian Klaver adrian.klaver@aklaver.com
On Monday, December 31, 2018, Mark Mikulec <mark@mikutech.com> wrote:
Does anyone know how to make COPY FROM PROGRAM take the output literally?
Not that I can think of. I’d avoid COPY FROM PROGRAM and move the logic to psql. Roughly: \set varname `cmd`; select :’varname’;
David J.
I changed it to be just the single float value I needed to extract out of the JSON object, but originally it was a text column that held the entire JSON object.
On Mon, Dec 31, 2018 at 3:52 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 12/31/18 9:36 AM, Mark Mikulec wrote:
> Hi,
>
> This command, which generates a JSON object as output, has some escaped
> data with backslashes: (see line 91 here: https://pastebin.com/D4it8ybS)
>
> C:\\Portable\\curl\\curl.exe -k
> "https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted"'
>
> I use the COPY command to pull it into a temp table like so:
>
> COPY temp_maps_api from program 'C:\\Portable\\curl\\curl.exe -k
> "https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted"';
So temp_maps_api has a single JSON field?
>
> However copy eats those backslashes. I need to use quote_literal() but
> that's a syntax error. For some reason the COPY command doesn't allow
> for ESCAPE to work with programs, only CSV.
>
> I tried using WITH BINARY but I get the error message: "COPY file
> signature not recognized"
>
> Does anyone know how to make COPY FROM PROGRAM take the output literally?
>
> Thanks,
> Mark
> ᐧ
--
Adrian Klaver
adrian.klaver@aklaver.com
On 12/31/18 12:58 PM, Mark Mikulec wrote: > I changed it to be just the single float value I needed to extract out > of the JSON object, but originally it was a text column that held the > entire JSON object. Might want to look at: https://www.postgresql.org/docs/10/datatype-json.html Might handle the escaping better. > ᐧ > > On Mon, Dec 31, 2018 at 3:52 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 12/31/18 9:36 AM, Mark Mikulec wrote: > > Hi, > > > > This command, which generates a JSON object as output, has some > escaped > > data with backslashes: (see line 91 here: > https://pastebin.com/D4it8ybS) > > > > C:\\Portable\\curl\\curl.exe -k > > > "https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted"' > > > > I use the COPY command to pull it into a temp table like so: > > > > COPY temp_maps_api from program 'C:\\Portable\\curl\\curl.exe -k > > > "https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted"'; > > So temp_maps_api has a single JSON field? > > > > > However copy eats those backslashes. I need to use > quote_literal() but > > that's a syntax error. For some reason the COPY command doesn't > allow > > for ESCAPE to work with programs, only CSV. > > > > I tried using WITH BINARY but I get the error message: "COPY file > > signature not recognized" > > > > Does anyone know how to make COPY FROM PROGRAM take the output > literally? > > > > Thanks, > > Mark > > ᐧ > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
>>>>> "Mark" == Mark Mikulec <mark@mikutech.com> writes: Mark> To be honest this whole affair with COPY FROM program seems like Mark> a bug to me though. Perhaps you're misunderstanding what COPY FROM PROGRAM is actually for. Its purpose is to do exactly what COPY does, that is to say, take as input a file in either PG's tab-delimited format or in CSV format, break it into records and fields, and insert the data into a table. Note that JSON is not a supported input format for COPY, though of course JSON _values_ can appear as data within a field inside either the tab-delimited or CSV formats. COPY FROM PROGRAM simply does COPY but with the input (whether in tab or CSV format) taken from the output of the program rather than a file. In tab-delimited format, the delimiter can be changed to something other than a tab, but the escape character is fixed as \ and the characters NL, CR, \, and the delimiter character are required to be escaped. Thus, any literal \ in the data MUST be escaped as \\ before passing the data to COPY in this mode. In CSV mode, CSV quoting and escaping rules are followed. It's not COPY's job to read a single datum, whether in JSON format or anything else. -- Andrew (irc:RhodiumToad)