psql内部命令及对应sql语句

作者：瀚高PG实验室 （Highgo PG Lab）

\?: 查看所有帮助 

\l: 查看所有数据库 

SELECT d.datname as "Name",

      pg_catalog.pg_get_userbyid(d.datdba) as "Owner",

      pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",

      d.datcollate as "Collate",

      d.datctype as "Ctype",

      pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"

FROM pg_catalog.pg_database d

ORDER BY 1;

\c dataname: 切换数据库 

\dt: 查看所有自己创建的表 

SELECT n.nspname as "Schema",

  c.relname as "Name",

  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' 

THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"

FROM pg_catalog.pg_class c

    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind IN ('r','')

      AND n.nspname <> 'pg_catalog'

      AND n.nspname <> 'information_schema'

      AND n.nspname !~ '^pg_toast'

  AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1,2;

\dt+: 查看所有自己创建的表，显示表的相关内容占的磁盘大小 

SELECT n.nspname as "Schema",

  c.relname as "Name",

  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' 

THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",

  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",

  pg_catalog.obj_description(c.oid, 'pg_class') as "Description"

FROM pg_catalog.pg_class c

    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind IN ('r','')

      AND n.nspname <> 'pg_catalog'

      AND n.nspname <> 'information_schema'

      AND n.nspname !~ '^pg_toast'

  AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1,2;

\dt(+) tablename: 参看指定表 

SELECT n.nspname as "Schema",

  c.relname as "Name",

  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' 

THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",

  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",

  pg_catalog.obj_description(c.oid, 'pg_class') as "Description"

FROM pg_catalog.pg_class c

    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind IN ('r','s','')

      AND n.nspname !~ '^pg_toast'

  AND c.relname ~ '^( tablename)$'

  AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1,2;

\dv: 查看所有自己创建的视图 

SELECT n.nspname as "Schema",

  c.relname as "Name",

  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' 

THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"

FROM pg_catalog.pg_class c

    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind IN ('v','')

      AND n.nspname <> 'pg_catalog'

      AND n.nspname <> 'information_schema'

      AND n.nspname !~ '^pg_toast'

  AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1,2;

\dv+: 查看所有自己创建的视图，显示大小 

SELECT n.nspname as "Schema",

  c.relname as "Name",

  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' 

THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",

  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",

  pg_catalog.obj_description(c.oid, 'pg_class') as "Description"

FROM pg_catalog.pg_class c

    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind IN ('v','')

      AND n.nspname <> 'pg_catalog'

      AND n.nspname <> 'information_schema'

      AND n.nspname !~ '^pg_toast'

  AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1,2;

\df: 查看所有自己创建的function 

SELECT n.nspname as "Schema",

  p.proname as "Name",

  pg_catalog.pg_get_function_result(p.oid) as "Result data type",

  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",

CASE

  WHEN p.proisagg THEN 'agg'

  WHEN p.proiswindow THEN 'window'

  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'

  ELSE 'normal'

END as "Type"

FROM pg_catalog.pg_proc p

    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace

WHERE pg_catalog.pg_function_is_visible(p.oid)

      AND n.nspname <> 'pg_catalog'

      AND n.nspname <> 'information_schema'

ORDER BY 1, 2, 4;

\df+: 查看所有自己创建的function,显示function的内容 

SELECT n.nspname as "Schema",

  p.proname as "Name",

  pg_catalog.pg_get_function_result(p.oid) as "Result data type",

  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",

CASE

  WHEN p.proisagg THEN 'agg'

  WHEN p.proiswindow THEN 'window'

  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'

  ELSE 'normal'

END as "Type",

CASE

  WHEN p.provolatile = 'i' THEN 'immutable'

  WHEN p.provolatile = 's' THEN 'stable'

  WHEN p.provolatile = 'v' THEN 'volatile'

END as "Volatility",

CASE

  WHEN p.proparallel = 'r' THEN 'restricted'

  WHEN p.proparallel = 's' THEN 'safe'

  WHEN p.proparallel = 'u' THEN 'unsafe'

END as "Parallel",

pg_catalog.pg_get_userbyid(p.proowner) as "Owner",

CASE WHEN prosecdef THEN 'definer' ELSE 'invoker' END AS "Security",

pg_catalog.array_to_string(p.proacl, E'\n') AS "Access privileges",

l.lanname as "Language",

p.prosrc as "Source code",

pg_catalog.obj_description(p.oid, 'pg_proc') as "Description"

FROM pg_catalog.pg_proc p

    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace

    LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang

WHERE pg_catalog.pg_function_is_visible(p.oid)

      AND n.nspname <> 'pg_catalog'

      AND n.nspname <> 'information_schema'

ORDER BY 1, 2, 4;

\df func_name: 显示指定的function 

SELECT n.nspname as "Schema",

  p.proname as "Name",

  pg_catalog.pg_get_function_result(p.oid) as "Result data type",

  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",

CASE

  WHEN p.proisagg THEN 'agg'

  WHEN p.proiswindow THEN 'window'

  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'

  ELSE 'normal'

END as "Type"

FROM pg_catalog.pg_proc p

    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace

WHERE p.proname ~ '^(func_name)$'

  AND pg_catalog.pg_function_is_visible(p.oid)

ORDER BY 1, 2, 4;

\ef func_name: 编辑function 

\dy：查看触发器 

SELECT evtname as "Name", evtevent as "Event", pg_catalog.pg_get_userbyid(e.evtowner) as "Owner",

case evtenabled when 'O' then 'enabled'  when 'R' then 'replica'  when 'A' then 'always'  when 'D' then 'disabled' end as "Enabled",

e.evtfoid::pg_catalog.regproc as "Procedure", pg_catalog.array_to_string(array(select x from pg_catalog.unnest(evttags) as t(x)), ', ') as "Tags"

FROM pg_catalog.pg_event_trigger e ORDER BY 1

\dx： 查看添加的PostgreSQL扩展模块 

SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description"

FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid

AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclassORDER BY 1;

\du： 查看当前用户的所有角色 

SELECT r.rolname, r.rolsuper, r.rolinherit,

  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,

  r.rolconnlimit, r.rolvaliduntil,

  ARRAY(SELECT b.rolname

        FROM pg_catalog.pg_auth_members m

        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)

        WHERE m.member = r.oid) as memberof

, r.rolreplication

, r.rolbypassrls

FROM pg_catalog.pg_roles r

WHERE r.rolname !~ '^pg_'

ORDER BY 1;

\dp view or table： 查看表或视图的权限 

SELECT n.nspname as "Schema",

  c.relname as "Name",

  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' E

ND as "Type",  pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",

  pg_catalog.array_to_string(ARRAY(

    SELECT attname || E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')

    FROM pg_catalog.pg_attribute a

    WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL

  ), E'\n') AS "Column privileges",

  pg_catalog.array_to_string(ARRAY(

    SELECT polname

    || CASE WHEN polcmd != '*' THEN

          E' (' || polcmd || E'):'

      ELSE E':' 

      END

    || CASE WHEN polqual IS NOT NULL THEN

          E'\n  (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)

      ELSE E''

      END

    || CASE WHEN polwithcheck IS NOT NULL THEN

          E'\n  (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)

      ELSE E''

      END    || CASE WHEN polroles <> '{0}' THEN

          E'\n  to: ' || pg_catalog.array_to_string(

              ARRAY(

                  SELECT rolname

                  FROM pg_catalog.pg_roles

                  WHERE oid = ANY (polroles)

                  ORDER BY 1

              ), E', ')

      ELSE E''

      END

    FROM pg_catalog.pg_policy pol

    WHERE polrelid = c.oid), E'\n')

    AS "Policies"

FROM pg_catalog.pg_class c

    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')

  AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1, 2;

\sf  函数名: 查看函数的创建语句

SELECT 'func_name'::pg_catalog.regproc::pg_catalog.oid