tablas - update en postgresql
¿Cómo actualizar las filas seleccionadas con los valores de un archivo CSV en Postgres? (1)
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.
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 ):
COPYnombrar 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: