【PostageSQL】日常常用SQL语句操作

1  序列相关

--1.1创建序列
CREATE SEQUENCE 序列名 START 1;  tip:规范名称：表名_字段名
--示例
CREATE SEQUENCE ac_front_resource_id_seq START 1;

--1.2更改序列起始数
SELECT setval('序列名', 起始数);
--示例
SELECT setval('ac_front_resource_id_seq', 1);

--1.3删除序列
DROP SEQUENCE 序列名;
--示例
DROP SEQUENCE ac_front_resource_id_seq;

--1.4给某个表字段赋值序列
ALTER TABLE ac_front_resource ALTER COLUMN id SET DEFAULT nextval('ac_front_resource_id_seq'::regclass);

SELECT 'DROP SEQUENCE "'||c.relname||'";' FROM pg_class c WHERE c.relkind ='S' and c.relname not like '%1'
ORDER BY c.relname

2  表相关

--2.1创建表
CREATE TABLE "public"."ac_front_resource" (
    "id" int8 PRIMARY KEY NOT NULL DEFAULT nextval('ac_front_resource_id_seq'::regclass),
    "code" varchar(64) COLLATE "pg_catalog"."default",
    "type" int4,
    "status" int2,
    "showLink" bool,
    "deleted" bool,
    "create_user_id" int8,
    "create_user_name" varchar(32) COLLATE "pg_catalog"."default",
    "create_time" timestamp(6),
    "modify_user_id" int8,
    "modify_user_name" varchar(32) COLLATE "pg_catalog"."default",
    "modify_time" timestamp(6),
    "isv_id" int8 DEFAULT 1,
    "app_id" int8 DEFAULT 1,
    "bg" int8 DEFAULT 1,
    "tenant_id" int8 DEFAULT 1
);
ALTER TABLE "public"."ac_front_resource" 
  OWNER TO "postgres";
COMMENT ON COLUMN "public"."ac_front_resource"."id" IS '前端资源表主键';
COMMENT ON COLUMN "public"."ac_front_resource"."deleted" IS '删除状态';
COMMENT ON COLUMN "public"."ac_front_resource"."create_user_id" IS '创建人用户ID';
COMMENT ON COLUMN "public"."ac_front_resource"."create_user_name" IS '创建人用户名';
COMMENT ON COLUMN "public"."ac_front_resource"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."ac_front_resource"."modify_user_id" IS '修改人用户ID';
COMMENT ON COLUMN "public"."ac_front_resource"."modify_user_name" IS '修改人用户名';
COMMENT ON COLUMN "public"."ac_front_resource"."modify_time" IS '修改时间';
COMMENT ON COLUMN "public"."ac_front_resource"."isv_id" IS 'ISV的ID';
COMMENT ON COLUMN "public"."ac_front_resource"."app_id" IS '应用ID';
COMMENT ON COLUMN "public"."ac_front_resource"."bg" IS 'bg';
COMMENT ON COLUMN "public"."ac_front_resource"."tenant_id" IS '租户ID';
COMMENT ON TABLE "public"."ac_front_resource" IS '前端资源表';

ALTER TABLE cc_channel_group_item_detail ALTER COLUMN channel_code DROP NOT NULL;

ALTER TABLE log ALTER ip SET NOT NULL;

3  场景操作

-- 查询 json 某个属性值
SELECT  ( extend_info :: json ->> '属性名' )  FROM 表名;
-- 查询文本不带百分号的
字段 not LIKE '%\%%' ESCAPE '\'；
-- 更新某个属性的值
update 表名 set json字段 = (json字段::jsonb || '{"属性名":"属性值"}')  where id =xx;
-- 比如更新商品表id=1的extend_info字段的deliveryType属性值为10,20
update ic_item set extend_info = (extend_info::jsonb || '{"deliveryType":"10,20"}')  where id = 1;
UPDATE 表 SET info = JSONB_SET(info, '{字段}', '"6.00%"') WHERE id = xxx;
-- 空值处理业务中需要对某个varchar类型的字段数据做转换，但是部分字段为""，用field::numeric 会报异常，可用以下方式：
CAST ( COALESCE ( NULLIF ( t2.info ->> 'F14287', '' ), '0' ) AS NUMERIC ( 16 ) ) num
-- 多条件匹配
SELECT
    store_code,
    payment_channel_code 
FROM
    pmtc_payment_action 
WHERE
    ( store_code, payment_channel_code ) IN ( ( '10230', '110' ), ( '10231', '50' ) ) 
GROUP BY
    store_code,
    payment_channel_code
-- 计算占比并保证占比和为100%
SELECT
    t1.*,
    (
        CASE WHEN t1.pai_xu <= t1.sheng_yu THEN round( ( t1.down_num + 1 ) / 100, 2 ) 
        ELSE round( t1.down_num / 100, 2 ) 
        END  
    ) 占比值,
    (
        CASE WHEN t1.pai_xu <= t1.sheng_yu THEN concat(round( ( t1.down_num + 1 ) / 100, 2 ), '%') 
        ELSE concat(round( t1.down_num / 100, 2 ) , '%') 
        END  
    ) 占比
FROM
    (
    SELECT
        t1.customer_code 客户编码,
        t1.customer_name 客户名称,
        t1.dimension 维度,
        SUM ( t1.num ) 数量,
        t1.total,
        FLOOR ( SUM ( t1.num ) / t1.total * 10000 ) down_num,
        SUM ( FLOOR ( SUM ( t1.num ) / t1.total * 10000 ) ) OVER ( PARTITION BY 1 ) down_num_sum,
        10000-sum ( FLOOR ( SUM ( t1.num ) / t1.total * 10000 ) ) OVER ( PARTITION BY 1 ) sheng_yu,
        ROW_NUMBER ( ) OVER ( PARTITION BY 1 ORDER BY SUM ( t1.num ) DESC ) pai_xu 
        from xxx
    GROUP BY t1.customer_code, t1.customer_name, t1.total, t1.dimension 
    ORDER BY t1.customer_code, t1.dimension DESC 
) t1
-- 拼接SQL
SELECT concat('UPDATE 表 SET info = JSONB_SET(info, ''', '{字段}'', ''"', res , '"'') WHERE id = ', id, ';')
from (
select id, res from xx
) t1;

4  元数据信息

-- 查询某张表的存储空间 单位MB
select concat(pg_total_relation_size('表名')/1024/1024,'m') as 存储
-- 查看表死元组以及autovacuum信息的语句
select * from pg_stat_all_tables where relname in (select tablename from pg_tables where schemaname='public')
select  concat(round(pg_total_relation_size(relname::TEXT)/1024/1024/1024.0, 2),'G'), * from pg_stat_all_tables where relname in (select tablename from pg_tables where schemaname='public')
-- 手动清理某张表的磁盘空间
-- 以下语句清除 的磁盘空间只能被 当前表 使用
vacuum 表名
--以下语句清除 的磁盘空间可以被为所有表 使用
-- 以下清理语句 会使得 清理期间 对该表的所有操作 都会被停止
vacuum full 表名
-- 查询表字段信息
select * from information_schema.columns
-- 查询表的行数
SELECT
    relname,
    reltuples::INT
FROM
    pg_class r
    JOIN pg_namespace n ON ( relnamespace = n.oid ) 
WHERE
    relkind = 'r' 
    AND n.nspname = 'public';

ALTER TABLE example_table ALTER COLUMN age SET DEFAULT 20;

5  视图依赖关系

--查询视图的oid
SELECT oid, relname FROM pg_class WHERE relname = 'cm_batch_quality';
--查询视图的依赖
SELECT 
    C.ev_class :: regclass :: VARCHAR AS objname,
    pc.oid :: regclass :: VARCHAR AS refobjname 
FROM
    pg_depend A,
    pg_depend b,
    pg_class pc,
    pg_rewrite C 
WHERE
    A.refclassid = 1259 -- 1259是pg_depend的oid
    AND A.classid = 2618 -- 2618是pg_rewrite的oid
    AND b.deptype = 'i' -- 内部依赖
    AND A.objid = b.objid 
    AND A.classid = b.classid 
    AND A.refclassid = b.refclassid 
    AND A.refobjid <> b.refobjid 
    AND pc.oid = A.refobjid 
    AND C.oid = b.objid 
    AND A.refobjid = 3708094  --要查询视图的oid
    AND ( A.objid >= 16384 OR A.refobjid >= 16384 ) 
GROUP BY
    C.ev_class,
    pc.oid;

6  Excel 拼写

--更新JSON的  但是不怎么好使
=CONCAT("update uc_cus set ext_field_json = JSONB_SET(ext_field_json,", "'{",  "xx", "}'", ",", "'", "[",  AN3, "]'", ")", " || JSONB_SET (ext_field_json,", "'{",  "dd", "}'", ",", "'", "[",   AO3, "]'", ")",     " || JSONB_SET (ext_field_json,", "'{",  "d", "}'", ",", "'", "[", AP3, "]'", ")",    " || JSONB_SET (ext_field_json,", "'{",  "d", "}'", ",", "'", "[",  AY3, "]'", ")",    " || JSONB_SET (ext_field_json,", "'{",  "w", "}'", ",", "'", "[", AR3, "]'", ")",  " || JSONB_SET (ext_field_json,", "'{",  "q", "}'", ",", "'", "[",  AS3, "]'", ")",  " || JSONB_SET (ext_field_json,", "'{",  "d", "}'", ",", "'", "[", AX3, "]'", ")",  " || JSONB_SET (ext_field_json,", "'{",  "q", "}'", ",", "'", "[",  AU3, "]'", ")",  " || JSONB_SET (ext_field_json,", "'{",  "e", "}'", ",", "'", "[",  AV3, "]'", ")"," where code = ", "'",  A3,"'", " and org_code = ", "'", F3, "'", " and deleted = false", ";")
--vlookup匹配
=IFERROR(VLOOKUP(AT14,$A$3253:$B$3285,2,0),"")
--更新拼写
=CONCAT("update uc_cus set spu_code = '",E2,"'"," where code = ", "'", A2,"'", " and pre_org_code = ", "'", D2, "'", " and deleted = false", ";")
--更新JSON的 全量更新的
update uc_cus set ext_field_json = '{"xx":"","dd":"","ff":"","qq":"27","dd":""}'::jsonb where code = '111' and deleted = false;

7  窗口函数

SELECT
    string_agg ( ID :: TEXT, ',' ) 
FROM
    (
    SELECT 
        ID,
        relation_id,
        property_id,
        ROW_NUMBER ( ) OVER ( PARTITION BY relation_id, property_id ORDER BY create_time DESC ) AS ROW 
    FROM
        property_tab
    WHERE
        deleted = FALSE 
    ) t1 
WHERE
    t1.ROW > 1

8  常用JSON

-- 更新某个json字段的值 有就更新没有就新增
update store set ext_field_json=jsonb_set(ext_field_json::jsonb,'{F1}','"Y"', true);
UPDATE customer SET ext_field_json = jsonb_set(ext_field_json, '{F1}', '"23"') WHERE id = 1;
-- 给某个空的json字段 直接赋值
update uc_store set ext_field_json = '{"F17319": "Y"}'::jsonb where ext_field_json is null;
-- json数组的取值 json某个字段是数组 取数组中的某个属性进行分析 比如订单明细中商品多行的
SELECT
    * 
FROM
    (
    SELECT
        code,
        string_agg ( sku_code, ',' ),
        COUNT ( sku_code ) cc,
        COUNT ( DISTINCT sku_code ) ccd 
    FROM
        (
        SELECT
            code,
            jsonb_array_elements ( body -> 'detailList' ) ->> 'skuCode' sku_code 
        FROM
            xxx t1 
        WHERE
            t1.deleted = FALSE 
            AND status = xx
            AND t1.business_type = xxx
        ) t1 
    GROUP BY
        code 
    ) t1 
WHERE
    cc != ccd

9  数组去重

SELECT
    code 
FROM
    ( SELECT DISTINCT UNNEST ( ARRAY [ '111', '111' ] ) code ) t1 
ORDER BY
    code