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.