PostgreSQL命令速查 – 扩展模块

扩展模块可以认为是第三方提供的一批函数,通过CREATE/DROP EXTENSION someone来安装或移除,通过\dx命令查看所有已安装的扩展模块。

更多扩展模块参考PostgreSQL官方文档

bloom

基于Bloom filters的索引方式。Bloom filters用于判断一个元素是不是在一个集合里,判定有则很可能有,判定无则一定无。优点是省空间低耗时,缺点是有一定误算率。

-- 创建bloom索引
CREATE INDEX bloom_idx ON user_info USING bloom (name, age, sex);

-- 查询索引大小
SELECT pg_size_pretty(pg_relation_size('bloom_idx'));

-- 查询
EXPLAIN ANALYZE SELECT * FROM user_info WHERE name = 'ran_101' AND age = 32;

btree_gin

基因倒排索引,查找速度快,适合静态数据。

CREATE INDEX gin_idx ON user_info USING GIN (jsonb_data);
CREATE INDEX gin_idx2 ON user_info USING GIN ((jsonb_data -> 'a'));

btree_gist

通用搜索树索引,更新快,适合动态数据,但可能会产生错误的匹配。

CREATE INDEX gist_idx ON user_info USING GiST (information);

citext

大小写不敏感的text类型,该类字段存入的文本在与其他文本比较时将无视大小写。

CREATE TABLE users (
    nick CITEXT PRIMARY KEY,
    pass TEXT   NOT NULL
);

INSERT INTO users VALUES ( 'larry',  md5(random()::text) );
INSERT INTO users VALUES ( 'Tom',    md5(random()::text) );
INSERT INTO users VALUES ( 'Damian', md5(random()::text) );
INSERT INTO users VALUES ( 'NEAL',   md5(random()::text) );
INSERT INTO users VALUES ( 'Bjørn',  md5(random()::text) );

SELECT * FROM users WHERE nick = 'Larry';

dblink

用于访问其他PostgreSQL数据库。

-- 直接在远程数据库执行查询语句
SELECT * FROM dblink('dbname=postgres password=postgres', 'SELECT name, age FROM user_info')
    AS t1 (user_name varchar(64), user_age int2)
    WHERE user_name LIKE 'ran_%';

-- 创建连接远程数据库的一个连接myconn
SELECT dblink_connect('myconn', 'dbname=postgres');

-- 通过myconn执行查询语句
SELECT * FROM dblink('myconn', 'SELECT name, age FROM user_info') AS t1 (user_name varchar(64), user_age int2);

-- 通过myconn执行更新类语句
SELECT dblink_exec('myconn', 'UPDATE user_info SET information = ''dblink info'' WHERE name = ''张三'' AND is_manager = FALSE;');

-- 获取一个游标mycur
SELECT dblink_open('myconn', 'mycur', 'SELECT name, age FROM user_info');

-- 通过mycur读取3条数据,并移动游标
SELECT * FROM dblink_fetch('myconn', 'mycur', 3) AS (user_name varchar(64), user_age int2);

-- 关闭游标mycur
SELECT dblink_close('myconn', 'mycur');

-- 执行异步查询
SELECT dblink_send_query('myconn', 'SELECT * FROM user_info');

-- 检查myconn上的异步查询状态,1表示未完,0表示完成。当完成时通过dblink_get_result获取异步结果不会阻塞
SELECT dblink_is_busy('myconn');

-- 获取异步查询结果
SELECT * FROM dblink_get_result('myconn') AS t1 (user_name varchar(64), user_age int2);

-- 终止正在进行的异步查询
SELECT dblink_cancel_query('myconn');

-- 关闭连接myconn
SELECT dblink_disconnect('myconn');

file_fdw

用于直接读取数据库以外的csv、txt等类型的文件,文件内容必须是可以被COPY FROM解析的格式,通常是通过\copy命令导出的数据文件。

-- 创建file_fdw服务
CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

-- 创建外部表
CREATE FOREIGN TABLE pglog (
    log_time timestamp(3) with time zone,
    user_name text,
    database_name text,
    process_id integer,
    connection_from text,
    session_id text,
    session_line_num bigint,
    command_tag text,
    session_start_time timestamp with time zone,
    virtual_transaction_id text,
    transaction_id bigint,
    error_severity text,
    sql_state_code text,
    message text,
    detail text,
    hint text,
    internal_query text,
    internal_query_pos integer,
    context text,
    query text,
    query_pos integer,
    location text,
    application_name text
) SERVER pglog
OPTIONS ( filename '/data/log/dn/pglog.csv', format 'csv' );

-- 查询
EXPLAIN SELECT * FROM pglog ORDER BY log_time LIMIT 10;

hstore

一种数据类型,用来在一个单一PostgreSQL 值中存储键值对。

intarray

用于操作一维整数数组,多维整数数组将被视作一维处理。

SELECT icount('{1,2,3}'::int[]) "元素个数",                                        -- 3
    sort(array[11,77,44], 'desc') "元素排序",                                      -- {77,44,11}
    uniq(sort('{1,2,3,2,1}'::int[])) "排序后元素去重",                             -- {1,2,3}
    idx(array[11,22,33,22,11], 22) "查找第一个匹配元素序号",                       -- 2 如果是0表示没有匹配上
    subarray('{1,2,3,2,1}'::int[], 2, 3) "获取子数组,第2个元素开始,长度3",       -- {2,3,2} 第三个参数缺省表示到末尾
    intset(42) "整数转数组";                                                       -- {42}

SELECT ARRAY[1,2,3,4] && ARRAY[2,3] "是否含有相同元素",                            -- t
    ARRAY[1,2,3,4] @> ARRAY[2,3] "是否包含",                                       -- t
    ARRAY[1,2,3,4] <@ ARRAY[2,3] "是否被包含",                                     -- f
    # ARRAY[1,2,3,4] "元素个数",                                                   -- 4
    ARRAY[1,2,3,4,2] # 2 "查找第一个匹配元素序号",                                 -- 2
    ARRAY[1,2,3,4] + 2 "增加元素到数组末尾",                                       -- {1,2,3,4,2}
    ARRAY[1,2,3,4] + ARRAY[2,3] "增加元素到数组末尾",                              -- {1,2,3,4,2,3}      |       |         | {1,2,3,4} | {1,2,3,4} | {2,3}
    ARRAY[1,2,3,4,2] - 2 "移除匹配元素",                                           -- {1,3,4}
    ARRAY[1,2,3,4] - ARRAY[2,3] "移除匹配元素",                                    -- {1,4}
    ARRAY[1,2,3,4] | 2 "并集",                                                     -- {1,2,3,4}
    ARRAY[1,2,3,4] | ARRAY[2,3] "并集",                                            -- {1,2,3,4}
    ARRAY[1,2,3,4] & ARRAY[2,3] "交集",                                            -- {2,3}
    ARRAY[1,2,3,4] @@ '1&(2|3)'::query_int "是否匹配query_int类型数据",            -- t  '1&(2|3)'表示包含1并且包含2或3
    '!1'::query_int ~~ ARRAY[1,2,3,4] "是否匹配query_int类型数据";                 -- f  '!1'表示不含1

pg_buffercache

用于实时查看shared buffer使用情况。

SELECT c.relname, count(*) AS buffers FROM pg_buffercache b INNER JOIN pg_class c
    ON b.relfilenode = pg_relation_filenode(c.oid) 
        AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
    GROUP BY c.relname
    ORDER BY 2 DESC
    LIMIT 10;

pgcrypto

提供密码函数。

pgrowlocks

查看行上的锁信息。

SELECT * FROM pgrowlocks('device_base');

pg_stat_statements

用于统计服务器上所有SQL的执行信息。开启首先需要在postgresql.conf中增加配置 shared_preload_libraries = ‘pg_stat_statements’

SELECT pg_stat_statements_reset();

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
    FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

pgstattuple

统计tuple(元组)和索引的状态,tuple可以认为是抽象的row(行)。

SELECT * FROM pgstattuple('op_common.user_base');

SELECT * FROM pgstatindex('user_base_pkey');

SELECT * FROM pgstattuple_approx('pg_catalog.pg_proc'::regclass);

pg_trgm

用于基于三元组匹配的字符串相似度计算。

-- 计算两个字符串的相似度,返回0到1的值,越大越相似
SELECT word_similarity('asdasqrqffasd','asdas1rqffasd');

-- 查看字符串的所有三元组
SELECT show_trgm('abcdefghijklmn');

postgres_fdw

用于直接读取远程PostgreSQL数据库的表数据。

-- 创建postgres_fdw服务
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');

-- 创建用户映射
CREATE USER MAPPING FOR local_user SERVER foreign_server OPTIONS (user 'foreign_user', password 'password');

-- 创建外部表
CREATE FOREIGN TABLE foreign_table (
    id integer NOT NULL,
    data text
) SERVER foreign_server OPTIONS (schema_name 'some_schema', table_name 'some_table');

-- 查询
EXPLAIN SELECT * FROM foreign_goods;

tablefunc

用于将具有特殊结构关系的表数据变换为二维表、层级等结构。

-- 二维表
CREATE TABLE exam (name text, course text, score int);
INSERT INTO exam VALUES 
    ('张三', '英语', 87), ('李四', '英语', 89), ('赵丽', '英语', 98), ('小明', '英语', 20),
    ('张三', '数学', 121), ('李四', '数学', 132), ('赵丽', '数学', 111),
    ('张三', '生化', 75), ('李四', '生化', 83), ('赵丽', '生化', 67);

-- crosstab(text source_sql),参数SQL语句必须返回3列(rowid, category, values),注意顺序和结果集字段类型
SELECT * FROM crosstab('SELECT name, course, score FROM exam ORDER BY 1, 2')
    AS rs (name text, 英语 int, 生化 int, 数学 int);

-- crosstab(text source_sql, text category_sql)
SELECT * FROM crosstab(
    'SELECT course, name, score FROM exam ORDER BY course',
    'SELECT DISTINCT name FROM exam ORDER BY 1;'
    )
    AS rs (course text, 小明 int, 张三 int, 李四 int, 赵丽 int);

-- 层级
CREATE TABLE bigtree (id int, parent_id int, name text);
INSERT INTO bigtree VALUES 
    (1, 0, 'root'), 
    (11, 1, 'one'), (12, 1, 'one'), (13, 1, 'one'), 
    (111, 11, 'two'), (112, 11, 'two'), (121, 12, 'two'), (122, 12, 'two'), (131, 13, 'two'), 
    (1121, 112, 'three'), (1211, 121, 'three'), (1212, 121, 'three'), (1312, 131, 'three'), (1312, 131, 'three');

-- connectby(text relname, text keyid_fld, text parent_keyid_fld
--          [, text orderby_fld ], text start_with, int max_depth
--          [, text branch_delim ])
SELECT * FROM connectby('bigtree', 'id', 'parent_id', '1', 10) AS t (id int, parent_id int, level int);

-- 按id排序,id=11的数据开始,0表示不限深度,branch用~连接
SELECT * FROM connectby('bigtree', 'id', 'parent_id', 'id', '11', 0, '~') AS t (id int, parent_id int, level int, branch text, pos int);

uuid-ossp

用于构造UUID。

CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();      -- 6097a234-88e5-408e-a4a5-60c7054dc1b1

xml2

以下需要在编译前的configure加上–with-libxml –with-libxslt。另外,部分xml相关函数并不需要开启xml2扩展模块即可使用。

SELECT xmlroot(xmlparse(document '<?xml version="1.1"?><content>abc</content>'), version '1.0', standalone yes);
SELECT xmlconcat('<?xml version="1.1"?><foo/>', '<?xml version="1.1" standalone="no"?><bar/>');
SELECT xmlconcat(
    xmlcomment('This is a comment!'),                                                                            -- <!--This is a comment!-->
    '<name/><stuff id=''idkey''>value</stuff>',
    xmlelement(name foo, xmlattributes('xyz' as attr1, -123 as attr2), 'hello', 'world'),                        -- <foo attr1="xyz" attr2="-123">helloworld</foo>
    xmlelement(name foo2, xmlelement(name yyy, 'z', 'z', 'z')),                                                  -- <foo2><yyy>zzz</yyy></foo2>
    xmlforest('abc' as bar1, 123 as bar2, xmlelement(name sub, xmlattributes(current_date as tm)) as bar3)       -- <bar1>abc</bar1><bar2>123</bar2><bar3><sub tm="2017-03-27"/></bar3>
);

-- XML转换,函数名都是xxx_to_yyy格式,第一个布尔型参数为是否输出NULL值<columnname xsi:nil="true"/>
SELECT table_to_xml('user_info', true, true, 'namespace01'),        -- 表数据转成xml
    database_to_xmlschema(true, true, 'namespace02'),               -- 数据库结构转成xml
    query_to_xml_and_xmlschema('SELECT id, name, sex, age, rank() OVER (PARTITION BY sex ORDER BY age) FROM user_info', false, false, 'namespace03'),   -- 查询结果的结构和数据转成xml
    schema_to_xml('myschema', false, false, 'namespace04');         -- 模式下的表数据转成xml

SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF '<towns><town>Toronto</town><town>Ottawa</town></towns>');  -- t
SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>', ARRAY[ARRAY['my', 'http://example.com']]);
SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></a>', ARRAY[ARRAY['mydefns', 'http://example.com']]);   -- 取xpath的值
SELECT xpath('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>', ARRAY[ARRAY['my', 'http://example.com']]);            -- 取xpath的值
Creative Commons License

本文基于署名-非商业性使用-相同方式共享 4.0许可协议发布,欢迎转载、使用、重新发布,但请保留文章署名wanghengbin(包含链接:https://wanghengbin.com),不得用于商业目的,基于本文修改后的作品请以相同的许可发布。

发表评论