powershell - How to export PostgreSQL query result into csv file using windows batch script (without using "COPY")

Question

I want to export PostgreSQL query result into csv file using windows batch script (without using "COPY" because it is creating permission issue). I am currently using below script which executing psql script and writing in file in PIPE("|") delimited format.

@echo off setlocal set PGPASSWORD='MyPassword' "psql.exe" -h myIP -p myPORT -U myUSER -d myDB -t -o "D:Testmyfile.csv" -f D:RaihanTestmy_sql_script.sql

SQL file(my_sql_script.sql): SELECT ID,NAME from MYTABLE

The code is writing file (myfile.csv) as pipe delimiter like below with one extra space after each pipe (Which is another issue I am also facing)

 1| Raihan
 2| Kalu
 3| Param

I can easily replace the pipe and space with coma (,) by using below script but my problem is it is tacking lots of time as it is converting line by line.

FOR /F "usebackq tokens=1-2 delims=| " %%I in (D:Testmyfile.csv) DO echo %%I,%%J >> D:Testmyfile_2.csv

Converting millions of data is not possible with this above script on time.

EDIT: I have used below code

COPY (select * from some_table) TO 'D:/tmp/some_file.csv' CSV HEADER;

which is giving error

psql:D:/Raihan/Test/test_sql.sql:2: ERROR: must be superuser to COPY to or from a file HINT: Anyone can COPY to stdout or from stdin. psql's copy command also works for anyone. Press any key to continue . . .

Note: my DB server is in another server from where I am running the batch file. It will also solve my issue if I could run /COPY without being a superuser.

question from:https://stackoverflow.com/questions/65872753/how-to-export-postgresql-query-result-into-csv-file-using-windows-batch-script

Answer 1

Waitting for answers