How to copy CSV data to PostgreSQL using PowerShell

Question

I'm new to PowerShell. I'm able to establish connection to my PostgreSQL server and perform operation of deleting data from existing table. What I'm not able to perform is the copy statement. Below is my code.

$DBConnectionString = "Driver={PostgreSQL UNICODE(x64)};Server=mysvr;Port=5420;Database=mydb;Uid=myuser;Pwd=mypwd;Options='autocommit=off';"
$DBConn = New-Object System.Data.Odbc.OdbcConnection;
$DBConn.ConnectionString = $DBConnectionString;
$DBConn.Open();

$DeleteexistingdataDml3 = "delete from table1;delete from table2;"
$DBCmd = $DBConn.CreateCommand();
$DBCmd.CommandText = $DeleteexistingdataDml3;
$DBCmd.ExecuteReader();

I'm getting error while performing the copy statement.

$Copydatatotable = "\copy table1 FROM '\\filedolder\table1data.csv' DELIMITER AS '|' CSV NULL AS '';
                    \copy table2 FROM '\\filedolder\table2data.csv' DELIMITER AS '|' CSV NULL AS '';"
$DBCmd = $DBConn.CreateCommand();
$DBCmd.CommandText = $Copydatatotable;
$DBCmd.ExecuteReader();

Error:

Exception calling "ExecuteReader" with "0" argument(s): "ERROR [42601] ERROR: syntax error at or near "";

Thank you.

Can you do the same thing in Postgres itself? If so, what is different? — Walter Mitty, Jun 03 '21 at 18:38
Copy statement works fine in command prompt, since it is using PSQL utility. — Subhahan Syed, Jun 03 '21 at 19:25

Bacon Bits · Answer 1 · 2021-06-03T17:35:49.860

My understanding is that \COPY is a client command. I expect that means that it's understood by psql only. A generic ODBC provider won't understand it.

If you use the Npgsql .Net client, you may be able to use the PosgresSQL COPY command with it, but the \COPY command is for the PostgreSQL client psql only.

If you must use the ODBC connection, your best bet is likely to build an INSERT command with parameters and inserting each row of your CSV file one row at a time. In general, that would look similar to this answer inside a loop, but using Powershell instead of C#, of course.

How to copy CSV data to PostgreSQL using PowerShell

1 Answers1