pg创建dblink访问另外的库下的表
pg版本:10
[postgres@localhost ~]$ psql
psql.bin (10.15)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
db_test | hxl | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/hxl +
| | | | | hxl=CTc/hxl
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=# select user;
user
----------
postgres
(1 row)
创建dblink扩展
postgres=# create extension dblink;
查看扩展
postgres=# select * from pg_extension;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
---------+----------+--------------+----------------+------------+-----------+--------------
plpgsql | 10 | 11 | f | 1.0 | |
dblink | 10 | 2200 | t | 1.2 | |
(2 rows)
创建dblink
postgres=# select dblink_connect('test_dblink1','dbname=db_test host=localhost port=5432 user=hxl password=postgres');
这里的test_dblink1是dblink名
postgres=# select * from dblink('test_dblink1','select id,name1 from tb_test limit 10') as t1(id int,name1 varchar);
-- The End --