pgsql创建外部表

• 安装postgres_fdw

CREATE EXTENSION postgres_fdw;

• 创建远程服务

CREATE SERVER adminServer
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
	host '192.168.123.32',
	port '15432',
	dbname 'cosmagnx_sys'
);

• 创建用户映射

CREATE USER MAPPING FOR current_user
SERVER adminServer
OPTIONS (
	user 'postgres',
	password 'cosmagn-erp..2024'
);

• 创建外部表

CREATE FOREIGN TABLE toc_custom_xq (
    id bigint NOT NULL,
	nickname character varying COLLATE pg_catalog."default",
	password character varying COLLATE pg_catalog."default",
	salt character varying COLLATE pg_catalog."default",
	phone character varying COLLATE pg_catalog."default",
	avatar character varying COLLATE pg_catalog."default",
	wx_openid character varying COLLATE pg_catalog."default",
	sex bpchar COLLATE pg_catalog."default",
	create_by character varying COLLATE pg_catalog."default",
	create_time timestamp without time zone,
	update_by character varying COLLATE pg_catalog."default",
	update_time timestamp without time zone,
	del_flag bpchar COLLATE pg_catalog."default" DEFAULT '0'::bpchar,
	tenant_id bigint,
	lock_flag bpchar COLLATE pg_catalog."default",
	user_code character varying COLLATE pg_catalog."default" NOT NULL,
	address character varying COLLATE pg_catalog."default",
	language character varying COLLATE pg_catalog."default",
	status bpchar COLLATE pg_catalog."default" DEFAULT '0'::bpchar,
	is_member bpchar COLLATE pg_catalog."default"
)
SERVER adminServer
OPTIONS (schema_name 'public', table_name 'toc_custom_xq');

如果需要级联删除外部表的命令如下：

DROP SERVER adminServer CASCADE;