分类： 数据库

PostgreSQL跨库联查

--导入支持(windows下使用，其他环境不支持)

create extension dblink

--方法一（缺陷：默认端口号5432）

select * from
dblink ( 'host=$IP port=$端口号 dbname=$数据库名 user=$用户名 password=$密码','$sql') as a(
$表结构（类似于建表命令 字段名 字段类型，....）
)

--方法二

--建立连接
SELECT dblink_connect('$连接名(自定义)','host=$IP dbname=$数据库名 user=$用户名 password=$密码');
--查看连接
select dblink_get_connections();

--断开所有连接
select dblink_disconnect();

--断开指定连接
select dblink_disconnect('$连接名');

--查询(如创建连接时没有定义连接名，则此处可省略)
SELECT * FROM dblink('$连接名','$sql')AS a ($表结构);

PostgreSQL查询数据库中所有表的行数

--联合两层schema并比较对应表的行数是否相等(需在vacuum后才准确，否则会有增删浮动)

SELECT a.relname,a.reltuples merge,b.reltuples center,a.reltuples=b.reltuples FROM (
SELECT relname, reltuples
FROM pg_class r JOIN pg_namespace n
ON (relnamespace = n.oid)
WHERE relkind = 'r' AND n.nspname = 'merge'
) a FULL JOIN (
SELECT relname, reltuples
FROM pg_class r JOIN pg_namespace n
ON (relnamespace = n.oid)
WHERE relkind = 'r' AND n.nspname = 'center'
) b ON a.relname=b.relname

postgresql根据结果集中某字段限定该字段相同值只获取20条

SELECT
T .company_id,
T .business_description_cn,
T .business_scope_cn,
T .company_tag_cn,
T .industry_id,
T .industry,
T .company_name_cn
FROM
(
SELECT
company_id,
business_description_cn,
business_scope_cn,
company_tag_cn,
industry_id,
config."dictionary".name_cn AS industry,
company_name_cn,
ROW_NUMBER() OVER(PARTITION BY industry_id) AS ROW
FROM
company_profile
LEFT JOIN config."dictionary" ON company_profile.industry_id = config."dictionary". ID
WHERE
industry_id IS NOT NULL
AND company_status_id LIKE '2%'
) T WHERE ROW <= 20

postgresql创建简单搜索视图

--创建search_company
CREATE MATERIALIZED VIEW search_company AS SELECT DISTINCT a.company_id,
a.ticker,
a.company_status_id,
a.company_name_cn,
a.company_name_en,
a.company_shortname_cn,
a.company_shortname_en,
b.brand_name,
a.year_founded,
c.name_cn AS company_status_name_cn,
c.name_en AS company_status_name_en
FROM ((company_profile a
LEFT JOIN brand_info b ON (((a.company_id)::text = (b.company_id)::text)))
LEFT JOIN config.dictionary c ON (((a.company_status_id)::text = (c.id)::text)))
WHERE a.company_status_id!='3.3'
ORDER BY a.ticker;

--导入自定义函数(扩展包)
CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION zhcfg (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION zhcfg ADD MAPPING FOR n,v,a,i,e,l WITH simple;
create extension pg_trgm;
create or replace function record_to_text(anyelement) returns text as $$
select $1::text;
$$ language sql strict immutable;

--创建全文索引
create index idx_search_company_gist on search_company using gist (record_to_text(search_company) gist_trgm_ops);
create index idx_search_company_gin on search_company using gin (record_to_text(search_company) gin_trgm_ops);

--检测索引是否正常
/*+ BitmapScan(search_company idx_search_company_gin) */ select * from search_company where record_to_text(search_company) ~ '经纬' and company_status_id like'3%' limit 99;

同步同一个表中的相关字段

--创建函数

CREATE OR REPLACE FUNCTION updateItemDDL(center_show varchar,item_ddl VARCHAR,table_name varchar,item varchar)
RETURNS
void as $$
UPDATE schema_table set center_show=$1,item_ddl=$2 WHERE "table_name"=$3 AND item=$4 AND "version"='v2';
$$ LANGUAGE 'sql' VOLATILE;

--执行函数

SELECT updateItemDDL(center_show,item_ddl,"table_name",item) from schema_table WHERE "version"='v1' AND item_ddl!='';

查询是否执行成功

SELECT "table_name",item,center_show,item_ddl from schema_table WHERE "version"='v2' AND item_ddl!=''