tablas - update en postgresql

¿Cómo actualizar las filas seleccionadas con los valores de un archivo CSV en Postgres?

Question

Estoy usando Postgres y me gustaría hacer una gran consulta de actualización que pueda obtener de un archivo CSV, digamos que obtuve una tabla que tiene (id, banana, apple) .

Me gustaría ejecutar una actualización que cambie las Bananas y no las Manzanas, cada nueva Banana y su ID estaría en un archivo CSV.

Traté de mirar el sitio de Postgres, pero los ejemplos me están matando.

Answer 1

COPY el archivo a una tabla temporal y actualizo la tabla desde allí. Podría verse así:

CREATE TEMP TABLE tmp_x (id int, apple text, banana text); -- but see 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; -- else it is dropped at end of session automatically

Si la tabla importada coincide con la tabla para actualizarse exactamente, esto puede ser conveniente:

CREATE TEMP TABLE tmp_x AS SELECT * FROM tbl LIMIT 0;

Crea una tabla temporal vacía que coincide con la estructura de la tabla existente, sin restricciones.

Privilegios

SQL COPY requiere privilegios de superusuario para esto. ( El manual ):

COPY nombrar un archivo o comando solo se permite a los superusuarios de la base de datos, ya que permite leer o escribir cualquier archivo al que el servidor tenga privilegios de acceso.

El meta-comando /copy psql funciona para cualquier función de db. El manual:

Realiza una copia de frontend (cliente). Esta es una operación que ejecuta un comando de COPIA SQL, pero en lugar de que el servidor lea o escriba el archivo especificado, psql lee o escribe el archivo y enruta los datos entre el servidor y el sistema de archivos local. Esto significa que la accesibilidad y los privilegios de los archivos son los del usuario local, no del servidor, y no se requieren privilegios de superusuario SQL.

El alcance de las tablas temporales está limitado a una única sesión de una única función, por lo que lo anterior debe ejecutarse en la misma sesión psql:

CREATE TEMP TABLE ...;
/copy tmp_x FROM ''/absolute/path/to/file'' (FORMAT csv);
UPDATE ...;

Si está scripting esto en un comando bash, asegúrese de envolverlo todo en una sola llamada psql. Me gusta:

echo ''CREATE TEMP TABLE tmp_x ...; /copy tmp_x FROM ...; UPDATE ...;'' | psql

Normalmente, necesita el meta-comando // para alternar entre los comandos meta psql y los comandos de SQL en psql, pero /copy es una excepción a esta regla. El manual nuevamente:

las reglas de análisis especiales se aplican al /copy . A diferencia de la mayoría de los otros metacomandos, el resto de la línea siempre se toma como los argumentos de /copy , y ni la interpolación de variables ni la expansión de las comillas inversas se realizan en los argumentos.

Grandes mesas

Si la tabla de importación es grande, puede ser temp_buffers incrementar temp_buffers temporalmente para la sesión (lo primero en la sesión):

SET temp_buffers = ''500MB'';  -- example value

Agregue un índice a la tabla temporal:

CREATE INDEX tmp_x_id_idx ON tmp_x(id);

Y ejecute ANALYZE manualmente, ya que las tablas temporales no están cubiertas por autovacuum / auto-analysis.

ANALYZE tmp_x;

Respuestas relacionadas:

• La mejor manera de eliminar millones de filas por ID
• ¿Cómo puedo insertar datos comunes en una tabla temporal de esquemas dispares?
• ¿Cómo eliminar entradas duplicadas?