从PostgreSQL表的INSERT SQL脚本出口特定的行从PostgreSQL表的INSERT

我有一个名为数据库模式： 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表的数据插入SQL脚本文件。 不过，我只想导出记录/数据所在的城市等于“东京”（假设城市数据都是小写）。

怎么做？

不要紧，解决方案是否是在免费软件GUI工具或命令行（尽管GUI工具的解决方案是更好）。 我试过的pgAdmin III，但我无法找到一个选项来做到这一点。

Answer 1:

创建要导出，然后使用命令行实用程序告诉pg_dump导出到文件中设定一个表：

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

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

--column-inserts将倾的插入与列名的命令。

--data-only不转储模式。

正如下面评论说，创造的不是表将避免创建表每当一个新的出口是必要的视图。

Answer 2:

对于纯数据导出使用COPY 。
你得到每行一个表行纯文本（不是文件INSERT命令），它的体积更小，速度更快：

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

导入相同的相同结构的另一个表任何地方：

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

COPY写入和读取本地服务器上的文件，不像在客户端程序pg_dump或psql其读取和写入本地的客户端文件。 如果同时在同一台机器上运行，它并没有多大关系，但它进行远程连接。

也有\copy PSQL的命令是：

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

Answer 3:

这是一个表导出与pgAdmin的脚本手动，无需额外安装提供方便，快捷的方式：

1. 右键单击目标表，然后选择“备份”。
2. 选择存储备份文件路径。 作为格式选择“普通”。
3. 打开标签“转储选项＃2”在底部，并勾选“使用插入栏”。
4. 单击备份按钮。
5. 如果您打开生成的文件与文本阅读器（如记事本++）你会得到一个脚本来创建整个表。 从那里，你可以简单地复制生成的INSERT的陈述。

这种方法还适用于制造export_table在@Clodoaldo内托的回答表现出的技术。

Answer 4:

SQL工作台具有这样的功能。

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

Answer 5:

对于我的用例，我能够简单地管到grep。

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

Answer 6:

您可以查看该表与specifit记录，然后转储SQL文件

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

Answer 7:

我敲了一个快速的方法来做到这一点。 它仅适用于单排，所以我创建只选择我想要的行，然后替换，我要插入到实际表中的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_nullable不足以说。

此外，它仅适用于可简单地转换为文本::然后再返回列。

另外这是Greenplum的，但我想不出理由，为什么它不会对Postgres的，CMIIW工作。

Answer 8:

我试着写一个程序做，基于@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测试，与混合场数据类型的表（文字，双，INT，时间戳没有时区等）。

这就是为什么需要在文本类型的CAST。 我的测试运行正常约9M的线条，看起来它失败之前18分钟运行。

PS：我发现在WEB上的MySQL等效。

Answer 9:

具有u试图执行的pgadmin与查询" EXECUTE QUERY WRITE RESULT TO FILE "选项

其唯一的出口数据，否则尝试像

pg_dump -t view_name DB_name > db.sql

用于==> -t选项只转储表（或视图或序列）的匹配表中， 参考

文章来源: Export specific rows from a PostgreSQL table as INSERT SQL script