Writing the content of a csv file to PostgreSQL with NPGSQL fails

Question

I am writing the Content of a csv file to a PostgreSQL table with help of NPGSQL in version 3.2.5.

The content of my csv is the following

id, value
1, 89
2, 286
3, 80
4, 107

I use the following command to write

Using writer = conn.BeginTextImport("COPY tbl_test (id,value) FROM 'C:/temp/test.csv' DELIMITER ',' CSV HEADER")

When I run my code, the values are written into my database, but the command is throwing the following error message:

Received unexpected backend message CompletedResponse. Please file a bug.

When I run the command directly in the SQL Shell everything works fine, so the problem seems to be produced by NPGSQL.

Here is my command which I use in the SQL Shell:

\COPY tbl_test(id,value) FROM 'C:/temp/test.csv' DELIMITER ',' CSV HEADER;

Has anybody else experience with this message?

I think it's a good idea to add a few lines of the actual csv file. — Davide Alberani
– Davide Alberani, Commented Sep 20, 2017 at 7:02
Default delimiter is tab in text format and comma in csv, so maybe you need to specify that it is semicolon instead: DELIMITER ';' — Łukasz Kamiński
– Łukasz Kamiński, Commented Sep 20, 2017 at 7:42
I tried this already with the same result but I have edited my question therefore. Even I changed the delimiter from ';' to ',' — Manfred Singer
– Manfred Singer, Commented Sep 20, 2017 at 7:44
comparing your shell command and NPGSQL I assume you use psql in case of shell and remote COPY in case of NPGSQL - do you have this path on your server?.. C:/temp/test.csv?.. — Vao Tsun
– Vao Tsun, Commented Sep 20, 2017 at 7:55
Yes, the path exists and even via the NPGSQL command the datas are successfully stored, but with the difference that the command throughs an exeption as described above after the commit. — Manfred Singer
– Manfred Singer, Commented Sep 20, 2017 at 8:04

Shay Rojansky · Accepted Answer · 2017-09-22 05:06:17Z

1

As answered in the github issue, You're using the API incorrectly.

If you have your CSV file on the client side (where the Npgsql app is running), then you should be using COPY tbl_test(value) FROM STDIN, not FROM c:\temp\test.csv. The latter is used when the csv file is on the PostgreSQL server. See the documentation.

If you simply want to import a file present on your server, just execute the COPY command as a regular SQL - create a command and execute it with ExecuteNonQuery. Don't use the BeginTextImport API.

answered Sep 22, 2017 at 5:06

Shay Rojansky

17k3 gold badges44 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Manfred Singer Over a year ago

Thanks for the tip. Without using the API function TextImport it works. I am only a little bit confused, because there is no hint in the documentation of NPGSQL. In the docs you use TextImport so this was the reason why I used it too.

Shay Rojansky Over a year ago

The sample code in the npgsql docs clearly shows an example with FROM STDIN, and with CSV data being written in C#. The PostgreSQL docs for COPY explain the difference between FROM STDIN and from a file present on the server.

Collectives™ on Stack Overflow

Writing the content of a csv file to PostgreSQL with NPGSQL fails

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related