postgres跨数据库查询并导出

文章目录

• Linux下执行
• 大象工具连接CDS库执行如下代码：

Linux下执行

touch /home/postgres/pgsql/test.csv
chown postgres.postgres  /home/postgres/pgsql/test.csv

大象工具连接CDS库执行如下代码：

create temp table tbl_info as 	
select s.usr_dir_code, s.camera_code, t.dev_name, s.interval_day
	from 
		(select usr_dir_code, substring(usr_dir_code,1,post-1) as camera_code , interval_day  from (select usr_dir_code,  case when position('#' in usr_dir_code)=0 then 1 else position('#' in usr_dir_code) end  as post, to_date (newest_time,'yyyy-mm-dd') - to_date (oldest_time,'yyyy-mm-dd') as interval_day   from test ) temp ) s
		left join 
		( select dev_code, dev_name FROM dblink('dbname=imos host=<具体的ip地址> port=5432 user=postgres password=<具体密码>','_select dev_code, dev_name from tbl_device') AS t1(dev_code varchar, dev_name varchar) ) t
	on (s.camera_code=t.dev_code);
	
set client_encoding to 'GBK';
\copy (select usr_dir_code,cameracode,dev_name,interval_day  from tbl_info ) to '/home/postgres/pgsql/test.csv' csv header ;
set client_encoding to 'UTF-8';