Java Runtime.exec fails to execute psql command

Question

What I am trying to do is to copy subset of DB table datas from X environment to local file storage. I am tryin to execute psql command with Java Runtime but it fails

this.psqlExportDataFile = "psql postgresql://dburl -o /Users/Shared/CopyDB/project.csv -c \"COPY (SELECT * FROM gls.project where id in (112371,148904,1652068)) TO STDOUT(FORMAT csv, DELIMITER ',', NULL 'null')'\"";

Process process = runtime.exec(psqlExportDataFile);
System.out.println(new String(toByteArray(process.getErrorStream())));

But it normally works when I am running it within shell.

The error output is:

ERROR:  unterminated quoted identifier at or near ""COPY"
LINE 1: "COPY
        ^

What could be the issue?

Answer 1

You incorrectly think that 'typing on the command line' (cmd.exe, or /bin/bash, or whatever your terminal is) is identical to 'starting a process'. Not so.

Your shell does lots of stuff. It takes that string you type and processes it in many, many ways. It results in running a command, sure (here, firing up psql.exe or /bin/psql or whatnot, and passing certain arguments to that process). But it's bash / cmd.exe that e.g. applies quotes, space-splitting, and all that jazz.

Java's process builder isn't cmd.exe and isn't /bin/bash. It does almost nothing except space-splitting. You want to take control of it, so stop using Runtime.exec() and use ProcessBuilder instead, and that whole 'quotes around an argument' thing isn't how ProcessBuilder works in the first place. It's bash/cmd that decided: To pass more than 1 arg, put spaces in between, and then solved the dilemma of "What if I want to pass a single argument that, itself, contains spaces" with: "Ah, well, then, um, put quotes around it?".

Java's processbuilder picked a different, simpler solution: Pass each argument as.. 1 argument:

new ProcessBuilder(
  "/bin/psql",
  "postgresql://dburl",
  "-o",
  "/Users/Shared/CopyDB/project.csv",
  "-c",
  "COPY (SELECT * FROM gls.project where id in (112371,148904,1652068)) TO STDOUT(FORMAT csv, DELIMITER ',', NULL 'null');");

and then run that/