将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，但我找不到这样做的选项。

275233 次浏览
小开

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

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

你有没有试过在pgadmin中使用 " EXECUTE QUERY WRITE RESULT TO FILE " option执行查询

它只输出数据，其他的试试

pg_dump -t view_name DB_name > db.sql

-t选项用于==>只转储表(或视图或序列)匹配表，请参考

小开
最佳答案

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

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

小开

出口数据只能使用< >强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这样的客户端程序在客户端本地读写文件。如果两者都运行在同一台机器上，这并不重要，但对于远程连接来说却很重要。

还有\copy psql:

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

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

小开

SQL工作台有这样一个特性。

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

小开

对于我的用例，我可以简单地管道到grep。

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

这是手动获取无需额外安装的简单快捷方法:

  1. 右键单击目标表并选择“备份”。
  2. 选择备份文件的存放路径。格式选择“普通”。
  3. 打开底部的“转储选项#2”选项卡，并勾选“使用列插入”。
  4. 单击backup -按钮。
  5. 如果你用文本阅读器(例如notepad++)打开结果文件，你会得到一个创建整个表格的脚本。从那里您可以简单地复制生成的insert语句。

该方法还可以使用@Clodoaldo Neto的回答中演示的生成export_table的技术。

在目标表上点击右键，选择"备份 < / >

选择目标路径，并将格式更改为 < / >

打开标签&;转储选项#2 < / >

你可以从那里复制INSERT语句。 < / >

小开

我刚做了一个快速手术。它只适用于单行，因此我创建了一个临时视图，只选择我想要的行，然后替换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上工作的原因。

小开

我试图写一个过程这样做，基于@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的等价物。

小开

我需要一种不需要创建临时表(在生产环境中)就可以生成插入语句的方法。我确实看到了上面的pg_dump的一些有用的参数，但最终还是设计了一种方法

生成插入语句并转储到文件中

下面的陈述很有用，我觉得对那些在这里找到类似答案的人会很有用。

$ /usr/local/bin/pg_dump -h the_db_host_address -d the_db_name --table=schema_name.table_name --data-only --column-inserts -U postgres -p the_port_number -v -f /Users/the_user/folder_name/insrt_stmts_file_name.sql

下一件事是一个询问密码的提示符，在上面的情况下，我提供了postgres密码，但任何其他具有读取能力的用户都可以正常工作。

我运行的只是插入语句从文件命名: insrt_stmts_file_name.sql在DBeaver客户端

但同样可以从命令提示符运行，使用以下脚本:

/usr/local/bin/psql -h the_db_host_address -d the_db_name -v -U postgres -f /Users/the_user/folder_name/insrt_stmts_file_name.sql
pg_dump / psql标志的参考: -h = host -d = db name -v = verbose(它会随着进程输出) -U = db用户名 -f = file / path

小开

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

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

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

  2. 将INSERT脚本复制回目标db查询窗口中的PGADMIN4。


提交答案