PostgreSQL 9.2 正式版发布！

〇〇

还是不支持文件空列

est_en=# copy region from 'C:/region.tbl' WITH DELIMITER AS '|';
错误:  最后期望字段后有额外数据

〇〇

〇〇 发表于 2012-9-12 21:57
还是不支持文件空列

est_en=# copy region from 'C:/region.tbl' WITH DELIMITER AS '|';

windows没找到管道和字符串行处理的办法。
除了自己写脚本/程序处理，还可以通过postgresql本身。
方法如下：
1.创建临时表
2.加载文件到临时表
3.从临时表输出文件
4.加载文件到原始表

〇〇

步骤：
test_en=# create table temp(a varchar(1000));
CREATE TABLE
时间：25.971 ms
test_en=# copy temp from 'C:/Users/db2admin/Downloads/Tpc-h_windows/region.tbl';
COPY 5
时间：9.040 ms
test_en=# select * from temp;
                                                               a

------------------------------------------------------------------------------------------------------------------------
-------
0|AFRICA|lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are accordin
g to |
1|AMERICA|hs use ironic, even requests. s|
2|ASIA|ges. thinly even pinto beans ca|
3|EUROPE|ly final courts cajole furiously final excuse|
4|MIDDLE EAST|uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furio
usl|
(5 行记录)


时间：0.456 ms

〇〇

test_en=# select substr(a,1,length(a)-1) from temp;
                                                            substr

------------------------------------------------------------------------------------------------------------------------
------
0|AFRICA|lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are accordin
g to
1|AMERICA|hs use ironic, even requests. s
2|ASIA|ges. thinly even pinto beans ca
3|EUROPE|ly final courts cajole furiously final excuse
4|MIDDLE EAST|uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furio
usl
(5 行记录)


时间：0.894 ms

〇〇

test_en=# copy (select substr(a,1,length(a)-1) from temp) to 'C:/Users/db2admin/Downloads/Tpc-h_windows/temp.txt';
COPY 5
时间：0.694 ms
test_en=# copy region from 'C:/Users/db2admin/Downloads/Tpc-h_windows/temp.txt' WITH DELIMITER AS '|';
COPY 5
时间：12.021 ms
test_en=# select * from region;
r_regionkey |          r_name           |                                                      r_comment

-------------+---------------------------+------------------------------------------------------------------------------
---------------------------------------
           0 | AFRICA                    | lar deposits. blithely final packages cajole. regular waters are final reques
ts. regular accounts are according to
           1 | AMERICA                   | hs use ironic, even requests. s
           2 | ASIA                      | ges. thinly even pinto beans ca
           3 | EUROPE                    | ly final courts cajole furiously final excuse
           4 | MIDDLE EAST               | uickly special accounts cajole carefully blithely close requests. carefully f
inal asymptotes haggle furiousl
(5 行记录)


时间：0.429 ms

〇〇

这个方法对所有数据库都通用，真是高级

〇〇

问题就是导入太慢，不实用
导出还是挺快的
test_en=# truncate table temp;
TRUNCATE TABLE
时间：17.400 ms
test_en=# copy temp from 'C:/Users/db2admin/Downloads/Tpc-h_windows/lineitem.tbl';
COPY 59986052
时间：3905138.205 ms
test_en=# copy (select substr(a,1,length(a)-1) from temp) to 'C:/Users/db2admin/Downloads/Tpc-h_windows/lineitemtemp.txt
'
test_en-# ;
COPY 59986052
时间：681285.384 ms
test_en=#

〇〇

同样的步骤用c程序作,只是导出的时间
#include <stdio.h>
int main(int argc,char* argv[])
{
char s[1000];
FILE *in=fopen(argv[1],"r");
FILE *out=fopen(argv[2],"w");
while(1)
{
fgets(s,999,in);
if (feof(in))
break;
s[strlen(s)-2]='\0';
fprintf(out,"%s\n",s);
}
fclose(in);
fclose(out);
return 0;
}

C:\Users\db2admin\pgsql>cl -O2 fconv.c

C:\Users\db2admin\pgsql>ffc C:/Users/db2admin/Downloads/Tpc-h_windows/lineitem.t
bl lineitem.txt

C:\Users\db2admin\pgsql>time  0<cr
当前时间: 22:22:36.86
输入新时间:

C:\Users\db2admin\pgsql>fconv C:/Users/db2admin/Downloads/Tpc-h_windows/lineitem
.tbl lineitem.txt

C:\Users\db2admin\pgsql>time  0<cr
当前时间: 22:32:46.06
输入新时间:

myth8860

〇〇 发表于 2012-9-14 16:47
同样的步骤用c程序作,只是导出的时间
#include
int main(int argc,char* argv[])

〇〇

奇怪：
postgresql.conf
shared_buffers = 32MB                        # min 128kB

test_en=# select name, setting from pg_settings where name like 'share%';
           name           | setting
--------------------------+---------
shared_buffers           | 4096