Mysql to Posgresql query conversion

Question

Please help to create postgresql query equal to mysql query

LOAD DATA LOCAL INFILE 'file.txt' REPLACE INTO TABLE newtable  TERMINATED BY ',' IGNORE 1 LINES;

Answer 1

There is no equivalent feature in PostgreSQL - at least in the current 9.3 or any prior version.

You must do this in a few steps:

• CREATE TEMPORARY TABLE ...
• COPY into the temp table
• Do an UPDATE ... FROM followed by an INSERT INTO ... WHERE NOT EXISTS (...) to merge data
• DROP the temp table

Search for "postgresql bulk upsert" or "postgresql copy upsert".

Answer 2

you might be looking for COPY

COPY will be run by the PostgreSQL backend (user "postgres"). The backend user requires permissions to read & write to the data file in order to copy from/to it. You need to use an absolute pathname with COPY. \COPY on the other hand, runs under the current $USER, and with that users environment. And \COPY can handle relative pathnames. The psql \COPY is accordingly much easier to use if it handles what you need.