PostgreSQL 常用命令

  • 查询是否锁表了

    1
    2
    select oid from pg_class where relname='可能锁表了的表'
    select pid from pg_locks where relation='上面查出的oid'

  • 释放锁定表

    1
    select pg_cancel_backend(pg_locks.pid)

  • 索引

    1
    select * from pg_indexes where tablename not like 'pg%'

  • 查询表及表列

    1
    select table_name, string_agg(column_name, ', ') as columns from information_schema.columns group by table_name

  • 查询表列名，类型，注释

    1
    SELECT a.attname as name, col_description(a.attrelid,a.attnum) as comment,format_type(a.atttypid,a.atttypmod) as type FROM pg_class as c, pg_attribute as a where c.relname = 'table_name' and a.attrelid = c.oid and a.attnum > 0

  • 更新 Json 类型的字段

    1
    update table_name set json_column = jsonb_set(json_column::jsonb, '{field}', 'field_value')

  • 生成随机字符串

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    Create or replace function random_string(length integer) returns text as
    $$
    declare
      chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
      result text := '';
      i integer := 0;
    begin
      if length < 0 then
        raise exception 'Given length cannot be less than 0';
      end if;
      for i in 1..length loop
        result := result || chars[1+random()*(array_length(chars, 1)-1)];
      end loop;
      return result;
    end;
    $$ language plpgsql;