PSQL not executing SQL command - Powershell

Question

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 ',';"

Answer 1

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);"

Answer 2

Mind you I am not a PostgreSQL user by any means...but it looks like you are trying to run an interactive tool through a PowerShell script that I don't think is going to work.

The most common method of connecting to databases with PowerShell is using .NET classes to make a connection, then run the query, pull the output into a PowerShell object, and then put that data in whatever format supported by PowerShell.

One thing you can try is put your call to the psql as one command. I am not sure if this works but I would think you should be able to do something like this:

Set-Location 'E:\Program Files\PostgreSQL\9.1\bin\';
$env:PGPASSWORD = 'mypwd';
$results = & psql -U postgres -w -d myDB -c 'SELECT * FROM mytable;'
$results | 
   Export-Csv -path 'C:\Users\e\Desktop\test1.csv' -NoClobber -Delimiter ";"

Regarding no column header in your CSV file

One note if you actually want a header row or column labels for your data then you are going to have to specify the columns in your query. Using SELECT * gives PowerShell no idea what property names to apply.

So if I just do as you did the first row of your CSV file will be data. If you change it to be the columns in the table like this:

SELECT col1, col2, col3 FROM mytable;

Then your CSV file should contain the column names in the first row. The other option would be to do this in your PowerShell command but it is much cleaner to do this in your SQL query.