postgresql 备忘

　查看postgresql版本：select version()

1. postgresql 查询多行合并成一行

SELECT  string_agg (DISTINCT relname, ',' order by relname ASC)
FROM pg_stat_user_tables 
WHERE schemaname='public'

2. postgresql 查询所有表记录数

SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables 
where schemaname='public'
ORDER BY n_live_tup DESC;

3.postgresql 增大连接数(更改后需要重启数据库)

--alter system set max_connections=10000
show max_connections;
--select count(*) from pg_stat_activity;
--show superuser_reserved_connections;

4.postgresql 连接字符串（连接 'a' 和 'b'）

select 'a' || 'b'

5.查看数据库存储目录psql命令行

show data_directory;

6.搜索数据库所有表数据

--搜索'扣分'
DO $$
DECLARE
  pattern text := '%扣分%';
  value int := 0;
  sql text := '';
  rec1 record;
  rec2 record;
BEGIN
  DROP TABLE IF EXISTS _x;
  CREATE TEMPORARY TABLE _x (
    schema_name text, 
    table_name text, 
    column_name text,
    found text
  );
  FOR rec1 IN 
        SELECT table_schema, table_name, column_name
        FROM information_schema.columns 
        WHERE table_name <> '_x'
                AND UPPER(column_name) LIKE UPPER('%%')                  
                AND table_schema <> 'pg_catalog'
                AND table_schema <> 'information_schema'
                AND data_type IN ('character varying', 'text', 'character', 'char', 'varchar')
        LOOP
    sql := concat('SELECT ', rec1."column_name", ' AS "found" FROM ',rec1."table_schema" , '.',rec1."table_name" , ' WHERE UPPER(',rec1."column_name" , ') LIKE UPPER(''', pattern , ''')');
    RAISE NOTICE '%', sql;
    BEGIN
        FOR rec2 IN EXECUTE sql LOOP
            RAISE NOTICE '%', sql;
            INSERT INTO _x VALUES (rec1."table_schema", rec1."table_name", rec1."column_name", rec2."found");
        END LOOP;
    EXCEPTION WHEN OTHERS THEN
    END;
  END LOOP;
  END; $$;

SELECT * FROM _x;

View Code

7.搜索表注释

with tmp_tab as (
    select pc.oid as ooid,pn.nspname,pc.*
      from pg_class pc
           left outer join pg_namespace pn
                        on pc.relnamespace = pn.oid
      where 1=1
       and pc.relkind in ('r','v','m','f','p')
       and pn.nspname not in ('pg_catalog','information_schema') -- select pn.oid, pn.* from pg_namespace pn where 1=1
       and pn.nspname not like 'pg_toast%'
       /*and pc.oid not in (
          select inhrelid
            from pg_inherits
       )*/
    order by pc.relname
),tmp_desc as (
   select pd.*
     from pg_description pd
    where 1=1
      and pd.objsubid = 0 --objsubid 对于一个表列上的一个注释，这里是列号（objoid和classoid指表本身）。对所有其他对象类型，此列为0。
      --and pd.objoid=168605
)
select t0.*,
       case when t0.relkind in ('r','p')
                 then 'comment on table '||t0.nspname||'.'||t0.relname||' is '''||coalesce(t0.description,'')||''';' 
            when t0.relkind='v' 
                 then 'comment on view '||t0.nspname||'.'||t0.relname||' is '''||coalesce(t0.description,'')||''';' 
        end as table_description
  from (
        select tab.nspname,
               tab.relname,
               tab.relkind,
               de.description
          from tmp_tab tab
               left outer join tmp_desc de
                            on tab.ooid = de.objoid 
         where 1=1    
        ) t0
 where 1=1
   and t0.description like '%教师%'
order by t0.nspname,t0.relname   
;

View Code

8.搜索字段注释

with tmp_tab as (
    select pc.oid as ooid,pn.nspname,pc.*
      from pg_class pc
           left outer join pg_namespace pn
                        on pc.relnamespace = pn.oid
      where 1=1
       and pc.relkind in ('r','v','m','f','p')
       and pn.nspname not in ('pg_catalog','information_schema') -- select pn.oid, pn.* from pg_namespace pn where 1=1
       and pn.nspname not like 'pg_toast%'
       /*
       and pc.oid not in (
          select inhrelid
            from pg_inherits
       )*/
    order by pc.relname
),tmp_col as (
   select pa.*
     from pg_attribute pa
    where 1=1
      --and pa.attrelid = 168605
      and pa.attisdropped = false
      and pa.attname not in (
      'tableoid',
      'cmax',
      'xmax',
      'cmin',
      'xmin',
      'ctid'
      )
),tmp_desc as (
   select pd.*
     from pg_description pd
    where 1=1
      and pd.objsubid <> 0 --objsubid 对于一个表列上的一个注释，这里是列号（objoid和classoid指表本身）。对所有其他对象类型，此列为0。
      --and pd.objoid=168605
)
select t0.*,
       'comment on COLUMN '||t0.nspname||'.'||t0.relname||'.'||t0.attname||' is '''||coalesce(t0.description,'')||''';' as column_description
  from (
        select tab.nspname,
               tab.relname,
               tc.attname,
               tc.attnum,
               de.description
          from tmp_tab tab
               left outer join tmp_col tc
                            on tab.ooid = tc.attrelid
               left outer join tmp_desc de
                            on tc.attrelid = de.objoid and tc.attnum = de.objsubid
       ) t0
 where 1=1
   --and t0.description like '%教师%'
order by t0.nspname,t0.relname, t0.attnum

View Code

9. 强制用自定义值插入覆盖自增列

INSERT INTO public.t_check_item2(
    id, check_type_id, name, min_score, max_score, valid)
    OVERRIDING SYSTEM VALUE
    SELECT id, check_type_id, name, min_score, max_score, valid
    FROM public.t_check_item

10. postgresql 定义临时表变量

DROP TABLE IF EXISTS temptb;
CREATE TEMP TABLE temptb AS SELECT * FROM t_check_item;
SELECT * FROM temptb;
DROP TABLE temptb

11. 查询传递变量和输出结果

DO $$
DECLARE
    _id integer := 2;
    _name varchar(10) :='ZhangSan';
BEGIN
    _id := 20;
    DROP TABLE IF EXISTS temptb;
    CREATE TEMP TABLE temptb AS SELECT _id as id, _name as name;
END; $$;

select * from temptb;

也可以手动创建临时表

---创建临时表
CREATE TEMPORARY TABLE temptb (
  id int,
  name varchar(10),
)ON COMMIT PRESERVE ROWS;

临时表配合CTE

DO $$
DECLARE
    _id integer := 2;
    _name varchar(10) :='ZhangSan';
BEGIN
    DROP TABLE IF EXISTS temptb;
    CREATE TEMP TABLE temptb AS
    (
        with a as
        (
            select 1
        )
        select * from a
    );
END; $$;

select * from temptb;

12. 动态执行sql字符串命令,因为只有在函数中才允许调用EXECUTE执行字符串，所以有如下代码(只能执行更新，查询结果无法返回，可用来执行DDL语句)：

CREATE OR REPLACE FUNCTION execute_none_query(sql varchar(10000))
RETURNS VARCHAR AS $$
BEGIN
    EXECUTE(sql);
    RETURN 1;
END; $$ LANGUAGE plpgsql;

select execute_none_query('drop table t_demo')

对于非DDL语句可以使用：

PREPARE DemoSearch (int) AS
    SELECT * FROM t_demo WHERE id = $1;
EXECUTE DemoSearch(2);
DEALLOCATE DemoSearch

如果语句过于复杂必须拼接，可以定义返回record类型的函数：

CREATE OR REPLACE FUNCTION execute_query(sql varchar(10000))
RETURNS SETOF record AS
$BODY$
declare
    r record;
begin
    for r in EXECUTE sql loop
        return next r;
    end loop;
    return;
end
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION execute_query OWNER TO postgres;
select * from execute_query('select * from t_demo') as record(id integer,name varchar(255));

13. 重置自增列起始值，在做一些需要重建表的操作还要保留升级历史数据的场景下需要这个功能，在使用IDENTITY定义的字段使用 java mybatis 的项目中有效

ALTER TABLE t_check_item ALTER COLUMN id RESTART WITH 1000

14. 各种工具和使用场景

a. 单库备份和还原，使用工具pgadmin或dbeaver社区版。

b. 数据库重命名删除等，使用工具dbeaver可批量结束终端连接，pgadmin只能一个一个结束终端连接。

c. 数据库架构同步使用 pgadmin 或收费工具 navicat ，数据同步建议使用收费工具 navicate。

d. 性能监测使用 pgadmin 或 dbeaver ，有图形化动态界面，可直观查看各种运行时参数水平。

15. postgresql 如何实现事务

BEGIN;
DO $$
BEGIN
  raise notice 'Hello World!';
END;
$$;
COMMIT;

16. if 判断

BEGIN;
DO $$
BEGIN
    IF EXISTS (SELECT 1) THEN
        raise notice 'OK!';
    END IF;
      raise notice 'Hello World!';
END;
$$;
COMMIT;

17. 抛出异常

BEGIN;
DO $$
BEGIN
    IF EXISTS (SELECT 1) THEN
        RAISE EXCEPTION '执行出错了';
    END IF;
      raise notice 'Hello World!';
END;
$$;
COMMIT;

18.跨库查询

WITH t_ac_org_info AS (
  SELECT *
  FROM dblink('dbname=bicp host=localhost port=5432 user=postgres password=pass','SELECT uuid, org_name FROM public.t_ac_org_info') AS t(uuid varchar(20), org_name varchar(50))
)
SELECT *
FROM t_ac_org_info;

在 PostgreSQL 中，使用 dblink 执行远程查询需要满足以下几个前提条件：

1. 安装 dblink 扩展：使用 CREATE EXTENSION dblink; 命令创建扩展。
2. 授予 dblink 使用权限：授予使用 dblink 函数的用户 SELECT 权限。
3. 配置数据库连接：为了执行远程查询，需要提供远程服务器的连接信息，例如服务器地址、数据库名、用户名和密码等。

19. 隔离级别

PostgreSQL 支持以下四种事务隔离级别：

读未提交（Read Uncommitted）：一个事务可以看到另一个事务未提交的修改，存在脏读、幻读、不可重复读等问题。

读已提交（Read Committed）：一个事务只能看到已提交的修改，可以避免脏读，但是仍然存在幻读和不可重复读问题。

可重复读（Repeatable Read）：一个事务在执行期间看到的数据是不变的，避免了脏读和不可重复读，但仍然存在幻读问题。

串行化（Serializable）：所有事务按顺序一个接一个执行，避免了所有的并发问题，但是可能会带来较低的并发性能。

可以使用以下语句将事务隔离级别设置为串行化： SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

20. 错误捕获示例

BEGIN;
DO $$
DECLARE
    var_msg TEXT; -- 声明变量 var_msg
BEGIN

    IF EXISTS (
        SELECT uuid
        FROM public.t_ac_module_info
        WHERE uuid = '8ec526fc8f5f31d2babb'
              ) THEN
        RAISE EXCEPTION '脚本已经执行过了，请勿重复执行！';
    END IF;

    INSERT INTO public.t_ac_module_info ("uuid", "module_name", "module_icon", "module_path", "matched_sys_uuid", "superior_module_uuid", "display_order_no", "is_menu", "menu_level", "is_display", "is_enabled", "creator_uuid", "create_time", "mender_uuid", "modify_time", "comments", "fragment1", "fragment2", "fragment3") VALUES ('8ec526fc8f5f31d2babb', '检查明细', NULL, '/checkResultView/checkRecordDetail', 'dygl', 'b42c846c6c0b40cf8288', '100-104-103', 1, 3, 1, 1, '0f1232987dc94c0593bb', '2022-10-25 15:03:54', NULL, NULL, NULL, NULL, NULL, NULL);
    INSERT INTO public.t_ac_permission_info ("uuid", "permission_code", "permission_name", "permission_type", "matched_system_uuid", "matched_module_uuid", "matched_function_uuid", "matched_dom_id", "creator_uuid", "create_time", "mender_uuid", "modify_time", "is_enabled", "delete_flag", "is_basic", "is_system_embedded", "comments") VALUES ('8ec526fc8f5f31d2babb', 'MENU:BCCP_DYGL_JCMX:3:view', '检查明细', '3', 'dygl', 'b42c846c6c0b40cf8288', '46b63b2342354857b1b7', 'menu', '0f1232987dc94c0593bb', '2022-10-25 15:03:54', '0f1232987dc94c0593bb', '2022-10-25 15:03:54', 1, 0, 0, 0, '检查明细');

    RAISE NOTICE '脚本执行成功!';
    
EXCEPTION
    WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS var_msg = MESSAGE_TEXT;
        RAISE NOTICE '%', var_msg;
    
END;
$$;
COMMIT;

posted on 2022-09-16 15:33 空明流光阅读(123) 评论(0) 编辑收藏举报

刷新页面返回顶部

空明流光

postgresql 备忘

导航

公告