registros - manual de postgresql 10 en español pdf

Exportar filas específicas de una tabla PostgreSQL como INSERT secuencia de comandos SQL

Question

Tengo un esquema de base de datos llamado: nyummy y una tabla llamada cimory :

create table nyummy.cimory (
  id numeric(10,0) not null,
  name character varying(60) not null,
  city character varying(50) not null,
  CONSTRAINT cimory_pkey PRIMARY KEY (id)
);

Quiero exportar los cimory tabla cimory como insertar el archivo de script SQL. Sin embargo, solo deseo exportar registros / datos donde la ciudad es igual a ''tokyo'' (suponga que los datos de la ciudad son todos minúsculos).

¿Cómo hacerlo?

No importa si la solución está en herramientas de GUI gratuitas o en línea de comandos (aunque la solución de herramientas GUI es mejor). Probé pgAdmin III, pero no puedo encontrar una opción para hacer esto.

Answer 1

Cree una tabla con el conjunto que desea exportar y luego use la utilidad de línea de comando pg_dump para exportar a un archivo:

create table export_table as 
select id, name, city
from nyummy.cimory
where city = ''tokio''

$ pg_dump --table=export_table --data-only --column-inserts my_database > data.sql

--column-inserts se --column-inserts como comandos de inserción con nombres de columna.

--data-only no volcar esquema.

Como se comenta a continuación, la creación de una vista en lugar de una tabla obviará la creación de la tabla siempre que sea necesaria una nueva exportación.

Answer 2

Esta es una manera fácil y rápida de exportar una tabla a una secuencia de comandos con pgAdmin manualmente sin instalaciones adicionales :

1. Haga clic derecho en la tabla de destino y seleccione "Copia de seguridad".
2. Seleccione una ruta de archivo para almacenar la copia de seguridad. Como Formato elige "Normal".
3. Abra la pestaña "Opciones de volcado # 2" en la parte inferior y marque "Usar inserciones de columna".
4. Haga clic en el botón Copia de seguridad.
5. Si abre el archivo resultante con un lector de texto (por ejemplo, notepad ++) obtendrá un script para crear la tabla completa. Desde allí, simplemente puede copiar las instrucciones INSERT generadas.

Este método también funciona con la técnica de hacer un export_table como se demuestra en la respuesta de @Clodoaldo Neto.

Answer 3

Para mi caso de uso, pude simplemente pipetear grep.

pg_dump -U user_name --data-only --column-inserts -t nyummy.cimory | grep "tokyo" > tokyo.sql

Answer 4

Para una exportación de solo datos use COPY .
Obtiene un archivo con una fila de tabla por línea como texto sin formato (no INSERT comandos), es más pequeño y más rápido:

COPY (SELECT * FROM nyummy.cimory WHERE city = ''tokio'') TO ''/path/to/file.csv'';

Importe el mismo a otra tabla de la misma estructura en cualquier lugar con:

COPY other_tbl FROM ''/path/to/file.csv'';

COPY escribe y lee archivos locales en el servidor , a diferencia de programas de cliente como pg_dump o psql que leen y escriben archivos locales al cliente . Si ambos se ejecutan en la misma máquina, no importa mucho, pero sí para las conexiones remotas.

También está el comando /copy de psql que:

Realiza una copia de frontend (cliente). Esta es una operación que ejecuta un comando de COPY 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.

Answer 5

Puede hacer una vista de la tabla con registros específicos y luego volcar el archivo sql

CREATE VIEW foo AS
SELECT id,name,city FROM nyummy.cimory WHERE city = ''tokyo''

Answer 6

Simplemente cambié un procedimiento rápido para hacer esto. Solo funciona para una sola fila, así que creo una vista temporal que solo selecciona la fila que quiero, y luego reemplazo el pg_temp.temp_view con la tabla actual en la que quiero insertar.

CREATE OR REPLACE FUNCTION dv_util.gen_insert_statement(IN p_schema text, IN p_table text)
  RETURNS text AS
$BODY$
DECLARE
    selquery text; 
    valquery text; 
    selvalue text; 
    colvalue text; 
    colrec record;
BEGIN

    selquery := ''INSERT INTO '' ||  quote_ident(p_schema) || ''.'' || quote_ident(p_table);

    selquery := selquery || ''('';

    valquery := '' VALUES ('';
    FOR colrec IN SELECT table_schema, table_name, column_name, data_type
                  FROM information_schema.columns 
                  WHERE table_name = p_table and table_schema = p_schema 
                  ORDER BY ordinal_position 
    LOOP
      selquery := selquery || quote_ident(colrec.column_name) || '','';

      selvalue := 
        ''SELECT CASE WHEN '' || quote_ident(colrec.column_name) || '' IS NULL'' || 
                   '' THEN ''''NULL'''''' || 
                   '' ELSE '''''''' || quote_literal(''|| quote_ident(colrec.column_name) || '')::text || '''''''''' || 
                   '' END'' || 
        '' FROM ''||quote_ident(p_schema)||''.''||quote_ident(p_table);
      EXECUTE selvalue INTO colvalue;
      valquery := valquery || colvalue || '','';
    END LOOP;
    -- Replace the last , with a )
    selquery := substring(selquery,1,length(selquery)-1) || '')'';
    valquery := substring(valquery,1,length(valquery)-1) || '')'';

    selquery := selquery || valquery;

RETURN selquery;
END
$BODY$
  LANGUAGE plpgsql VOLATILE;

Invocado así:

SELECT distinct dv_util.gen_insert_statement(''pg_temp_'' || sess_id::text,''my_data'') 
from pg_stat_activity 
where procpid = pg_backend_pid()

No he probado esto contra ataques de inyección, por favor avíseme si la llamada quote_literal no es suficiente para eso.

También solo funciona para columnas que pueden simplemente enviarse a :: text y viceversa.

También esto es para Greenplum, pero no puedo pensar en una razón por la que no funcionaría en Postgres, CMIIW.

Answer 7

intenté en pgadmin ejecutar la consulta con la opción " EXECUTE QUERY WRITE RESULT TO FILE "

solo exporta los datos, de lo contrario intenta como

pg_dump -t view_name DB_name > db.sql

-t opción utilizada para ==> tablas solo de volcado (o vistas o secuencias) que coinciden con la tabla, refer

Answer 8

SQL Workbench tiene esa característica.

Después de ejecutar una consulta, haga clic derecho en los resultados de la consulta y seleccione "Copiar datos como SQL> Insertar SQL"