将 PostgreSQL 表中的特定行导出为 INSERT SQL 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12815496/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export specific rows from a PostgreSQL table as INSERT SQL script
提问by null
I have a database schema named: nyummyand a table named cimory:
我有一个名为：的数据库架构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)
);
I want to export the cimorytable's data as insert SQL script file. However, I only want to export records/data where the city is equal to 'tokyo' (assume city data are all lowercase).
我想将cimory表的数据导出为插入 SQL 脚本文件。但是，我只想导出城市等于“东京”的记录/数据（假设城市数据都是小写的）。
How to do it?
怎么做？
It doesn't matter whether the solution is in freeware GUI tools or command line (although GUI tools solution is better). I had tried pgAdmin III, but I can't find an option to do this.
解决方案是在免费软件 GUI 工具中还是在命令行中都没有关系（尽管 GUI 工具解决方案更好）。我曾尝试过 pgAdmin III，但找不到执行此操作的选项。
回答by Clodoaldo Neto
Create a table with the set you want to export and then use the command line utility pg_dump to export to a file:
使用要导出的集合创建一个表，然后使用命令行实用程序 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-insertswill dump as insert commands with column names.
--column-inserts将转储为带有列名的插入命令。
--data-onlydo not dump schema.
--data-only不要转储模式。
As commented below, creating a view in instead of a table will obviate the table creation whenever a new export is necessary.
如下所述，每当需要新导出时，创建视图而不是表将避免创建表。
回答by Erwin Brandstetter
For a data-only exportuse COPY.
You get a file with one table row per line as plain text (not INSERTcommands), it's smaller and faster:
对于仅数据导出使用COPY.
你会得到一个每行一个表格行的文件作为纯文本（不是INSERT命令），它更小更快：
COPY (SELECT * FROM nyummy.cimory WHERE city = 'tokio') TO '/path/to/file.csv';
Importthe same to another table of the same structure anywhere with:
使用以下命令将相同的内容导入到具有相同结构的另一个表中：
COPY other_tbl FROM '/path/to/file.csv';
COPYwrites and read files local to the server, unlike client programs like pg_dumpor psqlwhich read and write files local to the client. If both run on the same machine, it doesn't matter much, but it does for remote connections.
COPY写入和读取文件服务器本地的，不像在客户端程序pg_dump或psql该读写文件的本地客户端。如果两者都在同一台机器上运行，则无关紧要，但对于远程连接却很重要。
There is also the \copycommand of psqlthat:
Performs a frontend (client) copy. This is an operation that runs an SQL
COPYcommand, but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system. This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required.
执行前端（客户端）复制。这是一个运行 SQL
COPY命令的操作，但不是服务器读取或写入指定文件，而是 psql 读取或写入文件并在服务器和本地文件系统之间路由数据。这意味着文件可访问性和权限是本地用户的，而不是服务器的，并且不需要 SQL 超级用户权限。
回答by Andi R
This is an easy and fastway to export a table to a script with pgAdminmanually without extra installations:
这是一种使用 pgAdmin手动将表导出到脚本的简单快捷的方法，无需额外安装：
- Right click on target table and select "Backup".
- Select a file path to store the backup. As Format choose "Plain".
- Open the tab "Dump Options #2" at the bottom and check "Use Column Inserts".
- Click the Backup-button.
- If you open the resulting file with a text reader (e.g. notepad++) you get a script to create the whole table. From there you can simply copy the generated INSERT-Statements.
- 右键单击目标表并选择“备份”。
- 选择一个文件路径来存储备份。作为格式选择“普通”。
- 打开底部的“转储选项#2”选项卡并选中“使用列插入”。
- 单击备份按钮。
- 如果您使用文本阅读器（例如记事本++）打开生成的文件，您将获得一个脚本来创建整个表格。从那里您可以简单地复制生成的 INSERT 语句。
This method also works with the technique of making an export_table as demonstrated in @Clodoaldo Neto's answer.
此方法也适用于制作 export_table 的技术，如@Clodoaldo Neto 的回答中所示。
回答by machinery
SQL Workbenchhas such a feature.
SQL Workbench就有这样的功能。
After running a query, right click on the query results and choose "Copy Data As SQL > SQL Insert"
运行查询后，右键单击查询结果并选择“Copy Data As SQL > SQL Insert”
回答by M.Vanderlee
For my use-case I was able to simply pipe to grep.
对于我的用例，我可以简单地通过管道传输到 grep。
pg_dump -U user_name --data-only --column-inserts -t nyummy.cimory | grep "tokyo" > tokyo.sql
回答by Vi Shen
I tried to write a procedure doing that, based on @PhilHibbs codes, on a different way. Please have a look and test.
我试图根据@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;
And then :
进而 ：
-- for a range
SELECT dump('public', 'my_table','my_id between 123456 and 123459');
-- for the entire table
SELECT dump('public', 'my_table','true');
tested on my postgres 9.1, with a table with mixed field datatype (text, double, int,timestamp without time zone, etc).
在我的 postgres 9.1 上测试，使用混合字段数据类型（文本、双精度、整数、无时区的时间戳等）的表。
That's why the CAST in TEXT type is needed. My test run correctly for about 9M lines, looks like it fail just before 18 minutes of running.
这就是为什么需要 TEXT 类型的 CAST。我的测试正确运行了大约 900 万行，看起来它在运行 18 分钟之前就失败了。
ps : I found an equivalent for mysql on the WEB.
ps：我在网上找到了mysql的等价物。
回答by Giorgi Peikrishvili
You can make view of the table with specifit records and then dump sql file
您可以使用特定记录查看表，然后转储 sql 文件
CREATE VIEW foo AS
SELECT id,name,city FROM nyummy.cimory WHERE city = 'tokyo'
回答by PhilHibbs
I just knocked up a quick procedure to do this. It only works for a single row, so I create a temporary view that just selects the row I want, and then replace the pg_temp.temp_view with the actual table that I want to insert into.
我只是提出了一个快速程序来做到这一点。它只适用于单行，所以我创建了一个临时视图，只选择我想要的行，然后用我想要插入的实际表替换 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;
Invoked thus:
如此调用：
SELECT distinct dv_util.gen_insert_statement('pg_temp_' || sess_id::text,'my_data')
from pg_stat_activity
where procpid = pg_backend_pid()
I haven't tested this against injection attacks, please let me know if the quote_literal call isn't sufficient for that.
我还没有针对注入攻击测试过这个，如果 quote_literal 调用还不够，请告诉我。
Also it only works for columns that can be simply cast to ::text and back again.
此外，它仅适用于可以简单地转换为 ::text 并再次返回的列。
Also this is for Greenplum but I can't think of a reason why it wouldn't work on Postgres, CMIIW.
这也是针对 Greenplum 的，但我想不出它在 Postgres、CMIIW 上不起作用的原因。
回答by solaimuruganv
have u tried in pgadmin executing query with " EXECUTE QUERY WRITE RESULT TO FILE " option
你有没有试过在 pgadmin 中使用" EXECUTE QUERY WRITE RESULT TO FILE " 选项 执行查询
its only export the data, else try like
它只导出数据，否则尝试像
pg_dump -t view_name DB_name > db.sql
-t option used for ==> Dump only tables (or views or sequences) matching table, refer
-t 选项用于 ==> 仅转储匹配表的表（或视图或序列），请参阅
