postgres 常用工具命令、客户端命令

pg工具命令：

1、通过表名查看文件：
./oid2name -t + tablename

pg客户端命令：

1、循环插入多个字段数据：
insert into tablename select i,i||'aaa' from generate_series(1,100) i;

2、

查找自上次使用 重置统计信息以来从未使用的索引，重置统计信息函数pg_stat_reset()
SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
   JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0      -- has never been scanned
  AND 0 <>ALL (i.indkey)  -- no index column is an expression
  AND NOT i.indisunique   -- is not a UNIQUE index
  AND NOT EXISTS          -- does not enforce a constraint
         (SELECT 1 FROM pg_catalog.pg_constraint c
          WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

3、查看数据类型相关操作符

eg：查看numeric相关操作符
\dC numeric

4、查看所有数据库占用大小的语句：
select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database;
select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database; 这个可以带单位