序列操作

创建序列

CREATE SEQUENCE sys_mdict_id_seq  --序列名称
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

将序列与字段绑定

alter table sys_mdict alter column id set default nextval('sys_mdict_id_seq');

修改序列值

alter sequence sys_resource_id_seq restart with 1000

select 相关

对JSON字段的in查询

select * from device   where types::jsonb @>'[2,1]'::jsonb;
'1' in(select json_array_elements_text(‘[1,2,4]'))

select base.*,(select json_array_elements_text(base.publishing_platform_list))
        from documentation as base
                where
                '1'::varchar in(select json_array_elements_text(base.publishing_platform_list))

时间操作

-- 本月第一天
select date_trunc('month',current_date)

-- 今天
current_date

-- 时间加减
interval ‘1 (year、month、day、hour、minute)’

-- 获取时间的某个单位
select extract(day from now());

-- 设置某个时间的年为 2019                
SELECT
    to_date(
        CONCAT (
            '2019',
            RIGHT ( to_char( now( ), 'yyyy-mm-dd' ), 6 ) 
        ),
    'yyyy-mm-dd' 
    )

字符操作

-- 截取字符串后几位
Right(’1232’,3)

表级备份

将查询结果输出到新表

方式1–新建表后插入查询结果

通过CREATE TABLE新建表films

通过INSERT将查询结果插入到新表

INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';

方式2–生成新表时同时填充查询结果

命令:CREATE TABLE AS:从一条查询的结果中定义一个新表

示例:创建一个只包含表films中最近的记录的新表films_recent：

CREATE TABLE films_recent AS
SELECT * FROM films WHERE date_prod >= '2002-01-01';

注意:若添加参数TEMP,则表示生成临时表

统计磁盘占用

统计数各据库占用磁盘大小

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20

统计数据库中各表占用磁盘大小

SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC

查看索引使用几率

-- MISSING INDICES
SELECT
    relname AS "Table name",
    usage_percentage "Indices usage overall scans"
FROM (
    SELECT
    relname,
    round(idx_scan * 100.0 / NULLIF(idx_scan + seq_scan, 0), 2) AS usage_percentage,
    (seq_scan + idx_scan) / avg(seq_scan + idx_scan) OVER () AS overall_percentage
    FROM pg_stat_user_tables tbl
) tbl
WHERE usage_percentage < 90
    AND overall_percentage > 0.01
ORDER BY usage_percentage DESC;

查看并删除PGSQL锁

SELECT locker.pid,  
        pc.relname,  
        locker.mode,  
        locker_act.application_name,  
        least(query_start,xact_start) start_time,  
        locker_act.state,  
        CASE  
    WHEN granted='f' THEN  
    'wait_lock'  
    WHEN granted='t' THEN  
    'get_lock'  
    END lock_satus,current_timestamp - least(query_start,xact_start) AS runtime,  
    locker_act.query  
FROM pg_locks locker,pg_stat_activity locker_act, pg_class pc  
WHERE locker.pid=locker_act.pid  
        AND NOT locker.pid=pg_backend_pid()  
        AND application_name<>'pg_statsinfod'  
        AND locker.relation = pc.oid  
        AND pc.reltype<>0 --and pc.relname='t'
ORDER BY  runtime desc;


select pid from pg_locks where relation in (select oid from pg_class where relname='user_member')
--取消后台操作，回滚未提交事物
select PG_CANCEL_BACKEND('2872’);
--中断session，回滚未提交事物
select pg_terminate_backend(2872);

数据库占用连接

查看连接

-- 数据库占用连接
select * from pg_stat_activity where datname='device';

主动关闭连接

--pid为pg_stat_activity 中的字段
select pg_terminate_backend(pid) 
--可以配合查看链接sql进行使用
select pg_terminate_backend(pid) from pg_stat_activity where datname = 'xxx'

查看配置

select *
from pg_settings
-- 超时相关
select *
from pg_settings
where name like '%timeout%'