There is a copy command that inserts data into tables from a file. Is there any analogue not to insert, but to change the record? Is it possible, for example, to use a trigger on insert, in which there will be a check for the existence of a record on the PK and produce an update - how do you think it is effective?

  • one
    I think this can be implemented through copy to a temporary table and already make changes from it through the subquery update table t set ... from (subquery for temptable) s where t.... = s.... - Alex Krass
  • Whether the trigger on the temporary table approaches for this purpose? or general update will work faster - vdsp
  • I do not see the point in the trigger, why is it there? Simply transfer data from the temporary table to the main one within a single transaction. - Alex Krass

1 answer 1

As advised in the comments, first make a copy in a temporary table, and only then insert it into the destination table.

First option:

In PostgreSQL since version 9.5, the INSERT has an optional ON CONFLICT INSERT . Example:

 -- создаем временную структуру таблицы назначения копируя 1 строку CREATE TABLE tmp_tbl AS (SELECT * FROM tbl LIMIT 1); -- чистим временную таблицу TRUNCATE TABLE tmp_tbl; -- копируем данные из файла COPY tmp_tbl FROM '/какой-то путь/какой-то файл'; -- вставляем/обновляем данные INSERT INTO tbl (col1, col2, col3, ...) VALUES (1, 'asdf', 'sdfds', ...), (2, 'cvb'', 'nhfg', ...), ... ON CONFLICT (col1) DO UPDATE SET col2 = EXCLUDED.col2, col3 = EXCLUDED.col3, ...; -- убиваем временную таблицу DROP TABLE tmp_tbl;

The second option:

For PostgreSQL versions lower than 9.5. We use a simple update and insert:

 -- создаем временную структуру таблицы назначения копируя 1 строку CREATE TABLE tmp_tbl AS (SELECT * FROM tbl LIMIT 1); -- чистим временную таблицу TRUNCATE TABLE tmp_tbl; -- копируем данные из файла COPY tmp_tbl FROM '/какой-то путь/какой-то файл'; -- обновляем существующие записи UPDATE tbl AS t1 SET col2 = t2.col2, col3 = t2.col3, ... FROM tmp_tbl AS t2 WHERE t2.col1 = t1.col1; -- вставляем новые записи INSERT INTO tbl (col1, col2, col3, ...) SELECT t1.col1, t1.col2, t1.col3, ... FROM tmp_tbl AS t1 LEFT JOIN tbl AS t2 ON t1.col1 = t2.col1 WHERE t2.col1 IS NULL; -- убиваем временную таблицу DROP TABLE tmp_tbl;
  • Is there a similar for the copy command? - vdsp
  • copy no such functionality. First, copy it into a temporary table, and then paste it into the main one. - vikolyada
  • Expanded the answer and added a second solution for younger versions. - vikolyada
  • Thanks, the second option would be solved like this: INSERT INTO .... EXCEPTION WHEN unique_violation THEN UPDATE ... - vdsp