[Bug] PostgreSQL - Import CSV

Hello,

I'd like to report a bug with the "Import CSV File" tool. Seems like it uses MySQL commands even while importing into a PostgreSQL database.

I first tried importing server-side, and it produced the following query and error. Note that my schema is named "public" and the table is named "data_at_rest_20150713".

LOAD DATA LOW_PRIORITY LOCAL INFILE E'C:\\temp\\data_at_rest_20150712.csv' INTO TABLE "public"."data_at_rest_20150713" FIELDS TERMINATED BY E',' OPTIONALLY ENCLOSED BY E'"' ESCAPED BY E'"' LINES TERMINATED BY E'\r\n' IGNORE 1 LINES ("tools_internal_id", "run_date", "analyzed_date", "volume", "source", "category", "type", "feedname");
/* ERROR:  syntax error at or near "DATA"
LINE 1: LOAD DATA LOW_PRIORITY LOCAL INFILE E'C:\\temp\\data_at_rest...
^ */
/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 0 of 1 query: 0.000 sec. */

Looks like the equivalent method in Postgres is to use the COPY command:
https://wiki.postgresql.org/wiki/COPY

I translated the above MySQL into PostgreSQL syntax but didn't get much further:

COPY "public"."data_at_rest_20150713" ("tools_internal_id", "run_date", "analyzed_date", "volume", "source", "category", "type", "feedname") 
FROM E'C:\\temp\\data_at_rest_20150712.csv' WITH DELIMITER AS E',' CSV HEADER QUOTE AS E'"' ESCAPE AS E'"';
/* 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. */
/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 0 of 1 query: 0.000 sec. */

After some Googling seems like the preferred method is to use the utility \copy, not the command COPY. Haven't yet figured out how to make it work.

So moving on, I tried importing again, this time using "Client parses file contents" instead of "Server...". The original behavior was:

INSERT LOW_PRIORITY INTO "public"."data_at_rest_20150713" (<columns were here>) VALUES <values were here>
/* ERROR:  syntax error at or near "LOW_PRIORITY"
LINE 1: INSERT LOW_PRIORITY INTO "public"."data_at_rest_20150713" ("...
^ */

Removing "LOW_PRIORITY", however, works.

Although since the file was somewhat large, not all of the rows show up in the log, so for now it's a fairly limited operation (only 14 of my rows were displayed).

Thanks!

Thanks for checking that, and reporting possible alternatives. r4977 just disables features not supported by the active connection, namely
* the "low priority" checkbox
* the "server parses file contents (LOAD DATA)"
* the "INSERT IGNORE" and "REPLACE" options for handling duplicate rows

Also, it does not run the MySQL-only command "SHOW WARNINGS" after the import.

Would be nice if you do another check with the new build and see if I forgot something which is still active but not supported in PostgreSQL.

Looks good! I was able to import the file. One thing I noticed though:
Looks like the last field of the last row isn't imported (is NULL) if there's no newline; I was using \r\n as the line termination characters. Maybe EOF can be interpreted as a line terminator as well?

Thanks ansgar!

So, the last value in the very last row does not have a line terminator, and no field terminator?

Sounds reasonable to use the remainder as the last value here. Will check that.

Done in r4979: Does not ignore the very last value of the very last row, if it's not followed by a line or field terminator.

Thanks for reporting that! I guess this was hard to find, but critical as the data was not imported correctly in many cases.

Awesome, works now. Thanks!

I'm happy to help make the best SQL client even better!