首页 文章

将PostgreSQL表中的特定行导出为INSERT SQL脚本

提问于
浏览 次
134

我有一个名为： nyummy 的数据库模式和一个名为 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)
);

我想导出 cimory 表's data as insert SQL script file. However, I only want to export records/data where the city is equal to ' tokyo'（假设城市数据都是小写的） .

怎么做？

解决方案是否在免费的GUI工具或命令行中无关紧要（尽管GUI工具解决方案更好） . 我曾尝试过pgAdmin III，但我找不到这样做的选择 .

postgresql insert export sql-scripts postgresql-copy

9 回答

  • 215

    使用要导出的集创建表，然后使用命令行实用程序pg_dump导出到文件：

    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 将转储为带有列名的insert命令 .

    --data-only 不要转储架构 .

    如下所述，无论何时需要新的导出，创建视图而不是表都将避免创建表 .

    回复于
  • -2

    对于 data-only export 使用COPY .
    你得到的文件每行有一个表行作为纯文本（不是 INSERT 命令），它更小更快：

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

    Import 与具有相同结构的另一个表相同：

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

    COPY 写入和读取服务器本地的文件，不像客户端程序，如 pg_dump 或 psql ，它们读取和写入客户端本地的文件 . 如果两者都在同一台机器上运行，那么它并不重要，但它适用于远程连接 .

    还有\copy command of psql：

    执行前端（客户端）副本 . 这是一个运行SQL COPY命令的操作，但不是服务器读取或写入指定的文件，psql读取或写入文件并在服务器和本地文件系统之间路由数据 . 这意味着文件可访问性和权限是本地用户的权限，而不是服务器的权限，并且不需要SQL超级用户权限 .

    回复于
  • 2

    这是 easy and fast 方式 export a table to a script with pgAdmin 手动 without extra installations ：

    • 右键单击目标表并选择"Backup" .

    • 选择用于存储备份的文件路径 . 格式选择"Plain" .

    • 打开底部的标签"Dump Options #2"并检查"Use Column Inserts" .

    • 单击“备份”按钮 .

    • 如果使用文本阅读器（例如记事本）打开生成的文件，则会获得一个脚本来创建整个表 . 从那里你可以简单地复制生成的INSERT语句 .

    这个方法也适用于制作export_table的技术，如@Clodoaldo Neto的回答所示 .

    Click right on target table and choose "Backup"

    Choose a destination path and change the format to "Plain"

    Open the tab "Dump Options #2" at the bottom and check "Use Column Inserts"

    You can copy the INSERT Statements from there.

    回复于
  • 6

    SQL Workbench有这样的功能 .

    运行查询后，右键单击查询结果并选择“将数据复制为SQL> SQL插入”

    回复于
  • 0

    对于我的用例，我能够简单地管道grep .

    pg_dump -U user_name --data-only --column-inserts -t nyummy.cimory | grep "tokyo" > tokyo.sql
    回复于
  • 18

    您可以使用指定记录查看表，然后转储sql文件

    CREATE VIEW foo AS
SELECT id,name,city FROM nyummy.cimory WHERE city = 'tokyo'
    回复于
  • 9

    我刚刚敲了一个快速的程序来做这件事 . 它只适用于单行，因此我创建一个临时视图，只选择我想要的行，然后将pg_temp.temp_view替换为我想要插入的实际表 .

    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;

    这样调用：

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

    我没有对注射攻击进行测试，如果quote_literal调用不够，请告诉我 .

    此外，它仅适用于可以简单地转换为:: text并再次返回的列 .

    这也适用于Greenplum，但我想不出为什么它不适用于Postgres，CMIIW .

    回复于
  • 137

    我尝试以不同的方式编写一个基于@PhilHibbs代码的程序 . 请看看并测试 .

    CREATE OR REPLACE FUNCTION dump(IN p_schema text, IN p_table text, IN p_where text)
   RETURNS setof text AS
 $BODY$
 DECLARE
     dumpquery_0 text;
     dumpquery_1 text;
     selquery text;
     selvalue text;
     valrec record;
     colrec record;
 BEGIN

     -- ------ --
     -- GLOBAL --
     --   build base INSERT
     --   build SELECT array[ ... ]
     dumpquery_0 := 'INSERT INTO ' ||  quote_ident(p_schema) || '.' || quote_ident(p_table) || '(';
     selquery    := 'SELECT array[';

     <<label0>>
     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
         dumpquery_0 := dumpquery_0 || quote_ident(colrec.column_name) || ',';
         selquery    := selquery    || 'CAST(' || quote_ident(colrec.column_name) || ' AS TEXT),';
     END LOOP label0;

     dumpquery_0 := substring(dumpquery_0 ,1,length(dumpquery_0)-1) || ')';
     dumpquery_0 := dumpquery_0 || ' VALUES (';
     selquery    := substring(selquery    ,1,length(selquery)-1)    || '] AS MYARRAY';
     selquery    := selquery    || ' FROM ' ||quote_ident(p_schema)||'.'||quote_ident(p_table);
     selquery    := selquery    || ' WHERE '||p_where;
     -- GLOBAL --
     -- ------ --

     -- ----------- --
     -- SELECT LOOP --
     --   execute SELECT built and loop on each row
     <<label1>>
     FOR valrec IN  EXECUTE  selquery
     LOOP
         dumpquery_1 := '';
         IF not found THEN
             EXIT ;
         END IF;

         -- ----------- --
         -- LOOP ARRAY (EACH FIELDS) --
         <<label2>>
         FOREACH selvalue in ARRAY valrec.MYARRAY
         LOOP
             IF selvalue IS NULL
             THEN selvalue := 'NULL';
             ELSE selvalue := quote_literal(selvalue);
             END IF;
             dumpquery_1 := dumpquery_1 || selvalue || ',';
         END LOOP label2;
         dumpquery_1 := substring(dumpquery_1 ,1,length(dumpquery_1)-1) || ');';
         -- LOOP ARRAY (EACH FIELD) --
         -- ----------- --

         -- debug: RETURN NEXT dumpquery_0 || dumpquery_1 || ' --' || selquery;
         -- debug: RETURN NEXT selquery;
         RETURN NEXT dumpquery_0 || dumpquery_1;

     END LOOP label1 ;
     -- SELECT LOOP --
     -- ----------- --

 RETURN ;
 END
 $BODY$
   LANGUAGE plpgsql VOLATILE;

    然后 ：

    -- for a range
SELECT dump('public', 'my_table','my_id between 123456 and 123459'); 
-- for the entire table
SELECT dump('public', 'my_table','true');

    在我的postgres 9.1上测试，带有混合字段数据类型的表（text，double，int，没有时区的时间戳等） .

    这就是为什么需要TEXT类型的CAST . 我的测试正确运行大约9M行，看起来它在运行18分钟之前就失败了 .

    ps：我在WEB上找到了mysql的等价物 .

    回复于
  • 0

    你有没有尝试过使用 " EXECUTE QUERY WRITE RESULT TO FILE " 选项执行查询的pgadmin

    它只导出数据，否则尝试

    pg_dump -t view_name DB_name > db.sql

    -t选项用于==>仅转储匹配表的表（或视图或序列），refer

    回复于

相关问题