postgreSQL常用sql语句集合

一只很酸de橘子

已于 2024-03-01 16:47:49 修改

阅读量1.1k

点赞数

分类专栏： postgreSQL 文章标签：数据库

于 2023-03-07 17:08:58 首次发布

本文链接：https://blog.csdn.net/wxx_0124/article/details/129386175

版权

postgreSQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

不定期更新。。。如有不对，欢迎指教


--等同于Oracle的NVL()
select COALESCE(null,0);
--某个字符在字符串中首次出现的位置
position
--截取某个字符分割的任意部分：截取'->'分割的第3部分，即：社会互联网
select split_part('电子渠道->社会电子->社会互联网->' ,'->', 3);
--获取字符长度
select length('电子渠道->社会电子->社会互联网->');
--截取指定长度字符
select substr('电子渠道->社会电子->社会互联网->', 1, 17);
--字符转数字（模式位数不够的话，会忽略掉多出的部分）
select to_number('12345','9999999999');
--数字转字符串，当无法判断数据位数时，可用以下方法
''||12345
--查询模式下所有表
select * from pg_tables where schemaname='qdxn';
--查询表的结构信息
select * from information_schema.columns where table_name = 'dm_xnjk_xndf_m';
--添加字段
ALTER TABLE x_report_info ADD COLUMN in_param text;
--获取32位UUID（前提是支持uuid_generate_v4()函数）
--若不支持，执行create extension "uuid-ossp"即可
select upper(REPLACE(uuid_generate_v4()::varchar,'-', ''));
--小写转大写，结果：HELLO WORD
select UPPEr('Hello Word');
--大写转小写，结果：hello word
select lower('Hello Word');
--根据身份证计算年龄
select date_part('year',age(SUBSTR('13092919931001xxxx', 7, 8)::timestamp)) as age;
--查看数据库版本（SQL方式）
SELECT version();或者show server_version;
--查看数据库版本（pg_config方式）
pg_config|grep VERSION
--字符串截取
SELECT substr('15388997755', 1, 3);
--字符拼接
SELECT concat(substr('15388997755', 1, 3),'******',substr('15388997755', 10, 2));
--根据身份证判断男女（倒数第二位奇男偶女）
SELECT CASE WHEN substring(身份证号，17， 1) % 2 = 1 THEN '男' ELSE '女' END AS 性别 FROM 表名;

自定义UUID


//自定义生成uuid不确定是否会生成重复值
CREATE OR REPLACE FUNCTION uuid()
    RETURNS varchar AS
$uuid_str$
DECLARE
    uuid_str varchar;
BEGIN
    uuid_str := (select array_to_string(
                                array(select substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
                                                       FROM (ceil(random() * 62))::int FOR 1)
                                      FROM generate_series(1, 32)), ''));
    RETURN uuid_str;
END;
$uuid_str$ LANGUAGE plpgsql;
//自定义长度随机字符串
CREATE 
    OR REPLACE FUNCTION random_string ( INTEGER ) RETURNS TEXT AS $body$ SELECT
    array_to_string(
        ARRAY (
        SELECT SUBSTRING
            ( '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' FROM ( CEIL ( random( ) * 62 ) ) :: INT FOR 1 ) 
        FROM
            generate_series ( 1, $1 ) 
        ),
        '' 
    );

$body$ LANGUAGE SQL VOLATILE;


//根据输入的数据，返回对应的等级
create or replace function getLevel(score int)
    returns varchar as
$level$
declare
    level varchar;
begin
    case
        when score between 0 and 60 then level = 'C';
        when score between 60 and 80 then level = 'B';
        when score between 80 and 100 then level = 'A';
        else level = 'A++';
        end case;
    return level;
end;
$level$ language plpgsql;
//根据输入的数据，返回对应的等级
create or replace function myuuid(n int)
    returns varchar as
$uuid$
declare
    uuid varchar;
begin
--     if elseif 只能在方法中使用
    if n < 40 then
        uuid = 'D';
    elseif n between 40 and 60 then
        uuid = 'C';
    elseif n between 60 and 80 then
        uuid = 'B';
    else
        uuid = 'A';
    end if;
    return uuid;
end;
$uuid$ language plpgsql;

递归查询


//递归查询，查询当前节点及所有下级节点
with recursive cte as (
select T.org_code,T.org_name,T.parent_org_code,T.area_code,T.city_code,T.sort from HR_FRAME.CMCODE_ZHRL_MAIN_ORGANIZ T
where T.ORG_CODE = '2'
union all
select T.org_code,T.org_name,T.parent_org_code,T.area_code,T.city_code,T.sort from HR_FRAME.CMCODE_ZHRL_MAIN_ORGANIZ T
join cte c on c.ORG_CODE = T.parent_org_code
)select * from cte;
//递归查询，查询当前节点及所有上级节点
WITH RECURSIVE cte AS (
    SELECT x.* FROM v_all_org_tree x 
    WHERE x.org_code = '150879' UNION ALL
    SELECT y.* FROM v_all_org_tree y
        JOIN cte C ON C.parent_org_code = y.org_code 
    ) SELECT * FROM cte;

表授权：test2要访问test1的表(有些需要1+2才能实现)


--1、赋权SCHEMA 
GRANT ALL PRIVILEGES ON SCHEMA test1 TO test2;
--2、单表授权：授权test账号可以访问schema为test的t1表
grant select,insert,update,delete on test1.table_name to test2;
--3、所有表授权：
--shema说明：选择的模式为public，我将public模式下的所有表的增删改查授权给角色名为test的角色
grant select,insert,update,delete on all tables in schema "public" to test;

PG自定义decode函数


//创建函数
CREATE OR REPLACE FUNCTION "qdxn"."decode"(VARIADIC "p_decode_list" _text)
  RETURNS "pg_catalog"."text" AS $BODY$
declare
 -- 获取数组长度(即入参个数)
 v_len integer := array_length(p_decode_list, 1);
 -- 声明存放返回值的变量
 v_ret text;
begin
 /*
 * 功能说明:模拟Oracle中的DECODE功能(字符串处理, 其它格式可以自行转换返回值)
 * 参数说明:格式同Oracle相同,至少三个参数
 * 实现原理: 1、VARIADIC 允许变参; 2、Oracle中的DECODE是拿第一个数依次和之后的偶数位值进行比较,相同则取偶数位+1的数值,否则取最后一位值(最后一位为偶数为,否则为null)
 */
 
 -- 同Oracle相同当参数不足三个抛出异常
 if v_len >= 3 then
  -- Oracle中的DECODE是拿第一个数依次和之后的偶数位值进行比较,相同则取偶数位+1的数值
  for i in 2..(v_len - 1) loop
   v_ret := null;
   if mod(i, 2) = 0 then
    if p_decode_list[1] = p_decode_list[i] then
     v_ret := p_decode_list[i+1];
    elsif p_decode_list[1] <> p_decode_list[i] then
     if v_len = i + 2 and v_len > 3 then
      v_ret := p_decode_list[v_len];
     end if;
    end if;
   end if;
   exit when v_ret is not null;
  end loop;
 else
  raise exception 'UPG-00938: not enough args for function.';
 end if;
 return v_ret;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

//测试函数
SELECT DECODE('A', 'A', '1', 'B', '2', 'C', '3', '未匹配' )

分区表，分为声明式分区和表继承分区，前者的内部实现实际上也是利用表继承分区，但是前者使用较为简单，建议使用声明式的分区方式。（只写声明式分区，继承分区太麻烦，就不写了）


--创建基础表
create table tf_rzl_operate_log (id int,user_id int,operate_type int,content text,operate_date date) partition by range(operate_date);
--不创建分区就插入数据会报错（不信你试试）
insert into tf_rzl_operate_log values(1,1,1,'用户注册','2023-06-22');
--创建分区表
create table tf_rzl_operate_log_202306 partition of tf_rzl_operate_log for values from ('2023-06-01') to ('2023-06-30');
--这样就可以插入成功了（不信你再试试）
insert into tf_rzl_operate_log values(1,1,1,'用户注册','2023-06-22');
--可以这样查
select * from tf_rzl_operate_log where operate_date='2023-06-22';
--也可以这样查
select * from tf_rzl_operate_log_202306 where operate_date='2023-06-22';
--这样查就会报错（因为没有分区）
select * from tf_rzl_operate_log_202305 where operate_date='2023-06-22';
--向不存在的分区插入数据（会报错，不信你试试）
insert into tf_rzl_operate_log values(1,1,1,'用户注册','2023-07-22');
--删除分区，这样可以快速移除大量数据，而不是逐条删除数据
drop table tf_rzl_operate_log_202307;

关联更新

UPDATE table1
SET item_org_id = t1.item_org_id_new 
FROM
	table2 t1
WHERE
	table1.item_org_id = t1.item_org_id

日期格式化

--当前时间
select now();
select to_char(to_date('202010','YYYYMM')- interval '1 month','YYYYMM');
select to_char(to_date('20201213','YYYYMMDD')- interval '1 month','YYYYMM');
select TO_CHAR( TO_DATE( '202010', 'YYYYMM' ) + - 3 * '1 month' :: INTERVAL, 'YYYYMM' );
--昨天
select to_char( now() - interval '1 day','YYYYMMDD');
--明天
select to_char( now() + interval '1 day','YYYYMMDD');
--上月
select to_char( now() - interval '1 month','YYYYMM');
--下个月
select to_char( now() + interval '1 month','YYYYMM');
--current_timestamp 同 now() 函数等效
select current_timestamp;
SELECT to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS');
--近一周
select now() - interval '1 week';
--近一月
select now() - interval '1 month';
--近一年
select now() - interval '1 year';
--下一周
select now() + interval '1 week';
--下一月
select now() + interval '1 month';
--下一年
select now() + interval '1 year';
--上个月、上月第一天、上月最后一天
select TO_CHAR((DATE_TRUNC('MONTH', to_date('20230307','YYYYMMDD')) + INTERVAL '-1 MONTH')::DATE,'YYYYMM') AS "LAST_MONTH",
TO_CHAR((DATE_TRUNC('MONTH', to_date('20230307','YYYYMMDD')) + INTERVAL '-1 MONTH')::DATE,'YYYYMMDD') AS "LASST_MONTH_FIRST_DAY",
TO_CHAR((DATE_TRUNC('MONTH', to_date('20230307','YYYYMMDD')) + INTERVAL '-1 DAY')::DATE,'YYYYMMDD') AS "LASST_MONTH_END_DAY"
--时区转换
select to_char('Wed Sep 06 09:42:17 CST 2023' at time zone 'CST', 'YYYY-MM-DD HH24:MI:SS');

空字符串和null

--仅去除了为null的数据
is not null
--去除了null和空字符串的数据
!= ''

一只很酸de橘子

关注

0
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
postgreSQL常用sql语句集合

postgreSQL常用sql语句（日期格式化、字符截取、表赋权、UUID、自定义UUID，decode、递归查询、分区表）
复制链接

扫一扫

专栏目录

postgreSQL常用sql语句集合

“相关推荐”对你有帮助么？