Postgresql – PSQL not executing SQL command – Powershell

postgresqlpowershellpsql

I have a simple script that should execute a select statement and output it to a CSV. My problem is that the script connects to the database and hangs waiting for my sql instructions (which are on the script and should execute automatically). Any help would be greatly appreciated.

Set-Location 'E:\Program Files\PostgreSQL\9.1\bin\';
$env:PGPASSWORD = 'mypwd';
.\psql --% -U postgres -w myDB 
.\psql --% -c "copy {'SELECT * FROM myTable';} TO    'C:\Users\e\Desktop\test1.csv' CSV DELIMITER ',';"

enter image description here

Best Answer

Set-Location 'E:\Program Files\PostgreSQL\9.1\bin\';
$env:PGPASSWORD = 'mypwd';
.\psql --% -U postgres -w myDB 
.\psql --% -c "copy {'SELECT * FROM myTable';} TO    'C:\Users\e\Desktop\test1.csv' CSV DELIMITER ',';"

You invoke psql twice here. The first enters interctive mode as no stdin was supplied, no -f filename parameter was supplied, and there's no -c command string.

So the script never gets to the second psql invocation. Put the parameters on one line.

Additionally:

  • You want WITH (CSV, HEADER) not CSV DELIMITER ','
  • {'SELECT * FROM myTable';} is complete nonsense. Simply use COPY mytable TO ... or if you need a subquery, use COPY (SELECT * FROM mytable) TO ...
  • If you're trying to do a line continuation, use a backtick ` at the end of the line.
  • Unless you intend to use server-side paths, with the file permissions of the PostgreSQL server, you probably want \copy not COPY

(I'm assuming that --% stops Powershell from looking for Powershell flags and options in the rest of the command; I haven't seen it before).

I think you're looking for something more like:

Set-Location 'E:\Program Files\PostgreSQL\9.1\bin\';
$env:PGPASSWORD = 'mypwd';
.\psql --% -U postgres -w myDB -c "\copy (SELECT * FROM myTable) TO    'C:\Users\e\Desktop\test1.csv' WITH (FORMAT CSV, HEADER);"
Related Question