我有一个名为: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的等价物。
其他回答
用你想要导出的集合创建一个表，然后使用命令行实用程序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不转储模式。
正如下面所评论的，在表中创建视图而不是表将避免在需要新的导出时创建表。
对于我的用例，我可以简单地管道到grep。
pg_dump -U user_name --data-only --column-inserts -t nyummy.cimory | grep "tokyo" > tokyo.sql
你有没有试过在pgadmin执行查询“EXECUTE query WRITE RESULT TO FILE”选项
它只输出数据，其他的试试
pg_dump -t view_name DB_name > db.sql
-t option used for ==>只转储匹配表的表(或视图或序列)，参考
SQL Workbench有这样一个特性。
运行查询后，右键单击查询结果并选择“Copy Data As SQL > SQL Insert”
这是一种简单快捷的方法，可以用pgAdmin手动将表导出到脚本中，无需额外安装:
右键单击目标表并选择“备份”。 选择备份文件的存放路径。格式选择“普通”。 打开底部的“转储选项#2”选项卡，并勾选“使用列插入”。 单击backup -按钮。 如果你用文本阅读器(例如notepad++)打开结果文件，你会得到一个创建整个表格的脚本。从那里您可以简单地复制生成的insert语句。
该方法还可以使用@Clodoaldo Neto的回答中演示的生成export_table的技术。
