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;
Add a comment
|
2 Answers
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 ...COPYinto the temp table- Do an
UPDATE ... FROMfollowed by anINSERT INTO ... WHERE NOT EXISTS (...)to merge data DROPthe temp table
Search for "postgresql bulk upsert" or "postgresql copy upsert".
1 Comment
user2584292
Can you show me exact query in step3 Do an UPDATE ... FROM followed by an INSERT INTO ... WHERE NOT EXISTS (...) to merge data
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.
1 Comment
Praveen Prasannan
You're right. Replace is not there in postgresql copy: stackoverflow.com/a/772196/1029330