Wie kann ich Zeilen in einer Postgres-Tabelle effizient aus einer CSV-Datei aktualisieren?

Postgres-Updates mit CSV-Daten optimieren

Die effiziente Aktualisierung von Postgres-Tabellen mit Daten aus CSV-Dateien ist für die Datenverwaltung von entscheidender Bedeutung. Nehmen wir an, Sie haben eine Tabelle (ID, Banane, Apfel) und eine CSV-Datei mit aktualisierten banana-Werten. Die Herausforderung besteht darin, nur die Spalte banana zu aktualisieren, ohne die Spalte apple zu ändern.

Nutzung von COPY und UPDATE für effiziente Updates

Der optimale Ansatz besteht darin, die CSV-Datei mit COPY in eine temporäre Tabelle zu importieren und dann einen UPDATE basierend auf der Spalte id auszuführen. Hier ist der Prozess:

<code class="language-sql">CREATE TEMP TABLE tmp_x (id int, apple text, banana text); -- Or see alternative below

COPY tmp_x FROM '/absolute/path/to/file' (FORMAT csv);

UPDATE tbl
SET banana = tmp_x.banana
FROM tmp_x
WHERE tbl.id = tmp_x.id;

DROP TABLE tmp_x; -- Alternatively, it's automatically dropped at the end of the session</code>

Für einen effizienteren Ansatz, wenn die temporäre Tabellenstruktur die Zieltabelle widerspiegelt:

<code class="language-sql">CREATE TEMP TABLE tmp_x AS SELECT * FROM tbl LIMIT 0;</code>

Dadurch wird eine leere temporäre Tabelle mit demselben Schema wie tbl erstellt, wobei Einschränkungen weggelassen werden.

Sicherheits- und Leistungsaspekte

Vor Postgres 11 waren COPYSuperuser-Rechte erforderlich. Postgres 11 und spätere Versionen bieten jedoch vordefinierte Rollen (wie pg_read_server_files und pg_write_server_files) für verbesserte Sicherheit.

Der psql-Metabefehl copy bietet eine weitere Lösung, indem er den COPY-Befehl lokal ausführt und so die Superuser-Berechtigungsanforderungen umgeht.

Bei großen CSV-Dateien ist die Optimierung der Leistung von entscheidender Bedeutung. Erwägen Sie eine vorübergehende Erhöhung des Parameters temp_buffers. Das Erstellen eines Index für die Spalte id der temporären Tabelle und das Ausführen von ANALYZE kann die Abfragegeschwindigkeit weiter verbessern.

Das obige ist der detaillierte Inhalt vonWie kann ich Zeilen in einer Postgres-Tabelle effizient aus einer CSV-Datei aktualisieren?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!