TRUNCATE TABLE "TB_CELL_XYZ_AVERAGE";--清空表
copy "TB_CELL_XYZ_AVERAGE" from 'D:\\data\\average.txt' USING DELIMITERS '|';--将文件load进average表
insert into "TB_HIS_AVERAGE"("CELLCI","LAC","START_TIME","END_TIME","SERVICE_TYPE","VOLUME","NOTE")
select "CELLCI","LAC",to_timestamp("START_TIME",'yyyy-mm-dd hh24:mi:ss'),to_timestamp("END_TIME",'yyyy-mm-dd hh24:mi:ss'),"NET_TYPE","VOLUME","COMMUNITY" from "TB_CELL_XYZ_AVERAGE"; --将average表数据转移到历史表
delete from "TB_HIS_AVERAGE" where (age("START_TIME")) > interval '7 days'; --删除7天前的数据
\q;--退出psql
以上是我在psql窗口执行通过的sql命令,我需要将它放到windows脚本 bat里面执行,尝试以以下方式保存为.bat
a.bat:
psql.bat -h localhost -p 5432 postgres "postgres" UTF8;
--sql命令跟后面
但总是不成功,求指教怎样写,谢谢!
copy "TB_CELL_XYZ_AVERAGE" from 'D:\\data\\average.txt' USING DELIMITERS '|';--将文件load进average表
insert into "TB_HIS_AVERAGE"("CELLCI","LAC","START_TIME","END_TIME","SERVICE_TYPE","VOLUME","NOTE")
select "CELLCI","LAC",to_timestamp("START_TIME",'yyyy-mm-dd hh24:mi:ss'),to_timestamp("END_TIME",'yyyy-mm-dd hh24:mi:ss'),"NET_TYPE","VOLUME","COMMUNITY" from "TB_CELL_XYZ_AVERAGE"; --将average表数据转移到历史表
delete from "TB_HIS_AVERAGE" where (age("START_TIME")) > interval '7 days'; --删除7天前的数据
\q;--退出psql
以上是我在psql窗口执行通过的sql命令,我需要将它放到windows脚本 bat里面执行,尝试以以下方式保存为.bat
a.bat:
psql.bat -h localhost -p 5432 postgres "postgres" UTF8;
--sql命令跟后面
但总是不成功,求指教怎样写,谢谢!
BAT文件内容:
PSql -Upostgres -c "set client_encoding=utf8;select * from tbl_repos" db_storelib>d:\6789.txt
这句是什么意思呢?
要执行SQL语句,要加-c,双引号中的是SQL语句 OR 命令
db_storelib:数据库名将db_storelib中表tbl_repos内容以UTF的字符集导出到d:\6789.txt中,http://www.pgsqldb.org/pgsqldoc-cvs/app-psql.html
看看PSQL帮助
psql.bat -h localhost -p 5432 postgres "postgres" -d sde UTF8 -c
"TRUNCATE TABLE "TB_CELL_XYZ_AVERAGE";
copy "TB_CELL_XYZ_AVERAGE" from 'D:\\data\\average.txt' USING DELIMITERS '|';
insert into "TB_HIS_AVERAGE"("CELLCI","LAC","START_TIME","END_TIME","SERVICE_TYPE","VOLUME","NOTE")
select "CELLCI","LAC",to_timestamp("START_TIME",'yyyy-mm-dd hh24:mi:ss'),to_timestamp("END_TIME",'yyyy-mm-dd hh24:mi:ss'),"NET_TYPE","VOLUME","COMMUNITY" from "TB_CELL_XYZ_AVERAGE";
delete from "TB_HIS_AVERAGE" where (age("START_TIME")) > interval '7 days';
\q;"
psql.bat -h localhost -p 5432 postgres "postgres" UTF8 -c
"\c sde;
TRUNCATE TABLE "TB_CELL_XYZ_AVERAGE";
copy "TB_CELL_XYZ_AVERAGE" from 'D:\\data\\average.txt' USING DELIMITERS '|';
insert into "TB_HIS_AVERAGE"("CELLCI","LAC","START_TIME","END_TIME","SERVICE_TYPE","VOLUME","NOTE")
select "CELLCI","LAC",to_timestamp("START_TIME",'yyyy-mm-dd hh24:mi:ss'),to_timestamp("END_TIME",'yyyy-mm-dd hh24:mi:ss'),"NET_TYPE","VOLUME","COMMUNITY" from "TB_CELL_XYZ_AVERAGE";
delete from "TB_HIS_AVERAGE" where (age("START_TIME")) > interval '7 days';
\q;"求助呀
我一执行脚本就到psql控制台了,并且sql也没执行,楼上能给个QQ联系方式吗?我刚用postgreSQL几天,想把以前的oracle转移到postgreSQL,帮帮忙
copy tb_cell_xyz_average from 'D:\\data\\average.txt' USING DELIMITERS '|';
commit;
insert into tb_his_average(cellci,lac,start_time,end_time,service_type,volume,note)
select cellci,lac,to_timestamp(start_time,'yyyy-mm-dd hh24:mi:ss'),to_timestamp(end_time,'yyyy-mm-dd hh24:mi:ss'),net_type,volume,community from tb_cell_xyz_average ;
commit;
delete from tb_his_average where (age(start_time)) > interval '7 days';
commit;
\q"我现在给这个表改成小写的了,但是还是到这个地方好像没走了
PSql -h localhost -p 5432 sde -Upostgres -c "TRUNCATE TABLE tb_cell_xyz_average"
能否通过
去掉你的BAT
PSQL
.BATC:\Documents and Settings\Administrator>PSql -h localhost -p 5432 -Upostgres sde
-c "TRUNCATE TABLE tb_cell_xyz_average"
PSql: warning: extra command-line argument "-c" ignored
PSql: warning: extra command-line argument "TRUNCATE TABLE tb_cell_xyz_average"
ignored
Password for user postgres:
Welcome to PSql 8.3.8, the PostgreSQL interactive terminal.Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quitsde=#
D:\postgresql91\bin>PSql -Upostgres -d数据库名 -c "select * from "^"TT^""
ID | BH
----+----
(0 行记录)D:\postgresql91\bin>不用输入密码:
在BAT中
set PGPASSWORD=你的密码
TRUNCATE TABLED:\postgresql91\bin>
bat脚本内容:
PSql -Upostgres -dsde -c "TRUNCATE TABLE tb_cell_xyz_average;"
PSql -Upostgres -dsde -c "copy tb_cell_xyz_average from 'D:\\data\\average.txt' USING DELIMITERS '|';"
PSql -Upostgres -dsde -c "insert into tb_his_average(cellci,lac,start_time,end_time,service_type,volume,note) select cellci,lac,to_timestamp(start_time,'yyyy-mm-dd hh24:mi:ss'),to_timestamp(end_time,'yyyy-mm-dd hh24:mi:ss'),net_type,volume,community from tb_cell_xyz_average ;"
PSql -Upostgres -dsde -c "delete from tb_his_average where (age(start_time)) > interval '7 days';"