我有一个名为: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,但我找不到这样做的选项。
用你想要导出的集合创建一个表,然后使用命令行实用程序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这样的客户端程序在客户端本地读写文件。如果两者都运行在同一台机器上,这并不重要,但对于远程连接来说却很重要。
还有psql的\copy命令:
执行前端(客户端)复制。这是一个运行
SQL COPY命令,但不是服务器读取或
写入指定的文件,PSQL读取或写入文件并进行路由
服务器与本地文件系统之间的数据。这意味着
文件可访问性和权限是本地用户的权限,而不是服务器的权限,并且不需要SQL超级用户权限。
语法与上面相同。只需将COPY替换为\ COPY。
我刚做了一个快速手术。它只适用于单行,因此我创建了一个临时视图,只选择我想要的行,然后替换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。在DBeaver客户端执行sql命令。
但同样可以从命令提示符运行,使用以下脚本:
/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 =文件/路径