Can't quote_literal with COPY FROM PROGRAM

On Dec 31, 2018, at 10:36 AM, Mark Mikulec <mark@mikutech.com> 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&quot;&#39;

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&quot;&#39;;

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,\\,\\\\,g

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:

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&quot;&#39;

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&quot;&#39;;

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 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&quot;&#39;

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&quot;&#39;;

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)