4

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?

Manfred Singer
  • 169
  • 13
  • I think it's a good idea to add a few lines of the actual csv file. – Davide Alberani Sep 20 '17 at 07: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 Sep 20 '17 at 07: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 Sep 20 '17 at 07: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 Sep 20 '17 at 07: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 Sep 20 '17 at 08:04
  • hm - and what is on the server side in error log?.. – Vao Tsun Sep 20 '17 at 08:05
  • It seems to be, that there is no entry produced in C:\Programs\PostgreSQL\9.6\data\pg_log. The values are stored in the database correctly. The problem is, that the NPGSQL command in my visual basic program throughs that error. – Manfred Singer Sep 20 '17 at 08:17
  • if directory is empty it does not mean you have no errors, it means you don't collect errors I'd say. The error I'm looking for is not related to `COPY` - I thought you might find communication (protocol) error to help explain exception – Vao Tsun Sep 20 '17 at 08:25
  • I think "copy" and "\copy" are different commands. Please take a look at the answers and comments here : https://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgresql-table?rq=1 – ralf.w. Sep 20 '17 at 16:17
  • The error message indicates a bug inside Npgsql, can you please open an issue at http://github.com/npgsql/npgsql with the full schema, copy command and csv file (i.e. a full repro) triggering the error? – Shay Rojansky Sep 20 '17 at 18:17
  • Hi Shay. Thanks for your Response. I opened an issue. Please follow this link: https://github.com/npgsql/npgsql/issues/1673 – Manfred Singer Sep 21 '17 at 07:50

1 Answers1

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.

Shay Rojansky
  • 15,357
  • 2
  • 40
  • 69
  • 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. – Manfred Singer Sep 22 '17 at 06:16
  • 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](https://www.postgresql.org/docs/current/static/sql-copy.html) explain the difference between `FROM STDIN` and from a file present on the server. – Shay Rojansky Sep 23 '17 at 13:48