Обсуждение: Can't quote_literal with COPY FROM PROGRAM

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

Does anyone know how to make COPY FROM PROGRAM take the output literally?

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)