export - 从PostgreSQL表中导出特定的行作为INSERT SQL脚本





sql-scripts postgresql-copy (8)


对于我的使用情况,我能够简单地通过管道传输给grep。

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

我有一个名为: 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,但我找不到这样做的选项。




使用要导出的集创建表,然后使用命令行实用程序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将以列名作为插入命令转储。

--data-only不转储架构。

如下所述,创建一个视图而不是一个表将会避免创建表时创建一个新的导出是必要的。




我只是敲了一个快速程序来做到这一点。 它仅适用于单行,所以我创建了一个临时视图,它只选择我想要的行,然后用我想要插入的实际表替换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。




你有没有试过在pgadmin执行" EXECUTE QUERY WRITE RESULT TO FILE "选项来执行查询

它只能导出数据,否则试试

pg_dump -t view_name DB_name > db.sql

-t选项用于==>仅转储表(或视图或序列)匹配表, refer




SQL Workbench具有这样的功能。

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




这是一种使用pgAdmin手动将表格导出到脚本简便方法, 无需额外安装

  1. 右键单击目标表并选择“备份”。
  2. 选择一个文件路径来存储备份。 作为格式选择“平原”。
  3. 打开底部的“转储选项#2”选项卡并选中“使用列插入”。
  4. 点击备份按钮。
  5. 如果您使用文本阅读器打开生成的文件(例如记事本++),您将获得一个脚本来创建整个表格。 从那里你可以简单地复制生成的INSERT语句。

这种方法也适用于制作export_table的技巧,如@Clodoaldo Neto的答案所示。




对于纯数据输出使用COPY
你得到的文件每行只有一行表格(不是INSERT命令),它更小更快:

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

通过以下方式将其导入到具有相同结构的另一个表中:

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

与客户端程序(如读取和写入本地客户端的文件的pg_dumppsql不同, COPY写入和读取服务器 本地的文件。 如果两者都运行在同一台机器上,那么它并不重要,但它可以用于远程连接。

还有psql\copy命令

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




注意:此答案适用于SQL Server 2005.对于SQL Server 2008和更高版本,其他答案中有更好的方法。

你可以使用INSERT和SELECT UNION ALL

INSERT INTO MyTable  (FirstCol, SecondCol)
    SELECT  'First' ,1
    UNION ALL
SELECT  'Second' ,2
    UNION ALL
SELECT  'Third' ,3
...

尽管如此,对于小数据集来说,对于您的4条记录来说应该没问题。







postgresql insert export sql-scripts postgresql-copy