5

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

0

2 Answers 2

6
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);"
0
1

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.

2
  • I modified the query to pull just the individual columns as you suggested. Unfortunately the issue persists. The Header is "Length" and the values are the number of characters instead of the actual data. So instead of display joe's email as it is on the database, it displays 53.
    – 530529
    Aug 11, 2015 at 21:10
  • Re column names you just have to specify WITH (FORMAT CSV, HEADER) instead of your CSV DELIMITER ','. Aug 11, 2015 at 23:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.