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 ᐧ
Re: Can't quote_literal with COPY FROM PROGRAM
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 wrote: > > > On Dec 31, 2018, at 10: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 > "'; > > 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 > > ᐧ
Re: Can't quote_literal with COPY FROM PROGRAM
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 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.kla...@aklaver.com >
