将PostgreSQL表中的特定行导出为INSERT 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脚本文件。但是，我只想导出城市等于“tokyo”的记录/数据(假设城市数据都是小写的)。

怎么做呢?

解决方案是使用免费的GUI工具还是命令行并不重要(尽管GUI工具解决方案更好)。我尝试过pgAdmin III，但我找不到这样做的选项。

当前回答

我试着以不同的方式写一个基于@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，没有时区的时间戳，等等)。

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

ps:我在WEB上找到了一个mysql的等价物。

2018-05-24 13:15:07

其他回答

用你想要导出的集合创建一个表，然后使用命令行实用程序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不转储模式。

正如下面所评论的，在表中创建视图而不是表将避免在需要新的导出时创建表。

2012-10-10 09:31:59

只能使用COPY命令导出数据:

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

您可以导出整个表，只导出选定的列，或者如所示导出查询结果。不需要显式地创建表。

您将获得一个纯文本格式的文件，其中每行有一个表(而不是INSERT命令)。比INSERT命令更小更快。

要将相同的表导入到匹配结构的另一个Postgres表中(列顺序相同，数据类型兼容!):

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

COPY在服务器本地读写文件，不像pg_dump或psql这样的客户端程序在客户端本地读写文件。如果两者都运行在同一台机器上，这并不重要，但对于远程连接来说却很重要。

还有psql的\copy命令:

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

语法与上面相同。只需将COPY替换为\ COPY。

2012-10-10 17:16:45

只是添加上一种简单的方式，但手工方法。

1)使用PGADMIN 4，查询数据后，下载csv格式的数据。

在任何记事本中打开CSV，并在在线CSV到SQL转换器中复制和粘贴数据。例如:https://www.convertcsv.com/csv-to-sql.htm。可以设置目标表的名称。输出是sql insert scripts。将INSERT脚本复制回目标db查询窗口中的PGADMIN4。

2022-11-06 18:32:18

SQL Workbench有这样一个特性。

运行查询后，右键单击查询结果并选择“Copy Data As SQL > SQL Insert”

2013-11-27 12:58:12