postgresql 中常用小语法2

yinl_li

　　1. PG 中测试可能需要循环插入N多数据 这时候写function等就比较麻烦 我们可以用 generate_series 来
　　example:
　　mrapp=# create table test_series(id int) ;
CREATE TABLE
mrapp=# insert into test_series(id) select generate_series(1,100000);
INSERT 0 100000
mrapp=# select count(1) from test_series;
 count
--------
 100000
(1 行记录)
　　2. postgresql 中比较两个字符串中重叠的数量
　　我们先将array转换为行数据 然后和另一个转换过的array进行去重 然后再拼为数组即可
　　example：
　　
mrapp=# select unnest(array[1,2,4]);
 unnest
--------
      1
      2
      4
(3 行记录)
　　
mrapp=# select unnest(array[1,2,4]) intersect select unnest(array[2,3,4]);
 unnest
--------
      2
      4
(2 行记录)
　　
mrapp=# select array(select unnest(array[1,2,4]) intersect select unnest(array[2
,3,4]));
 array
-------
 {2,4}
(1 行记录)
　　3. postgresql 列转数组
　　可以用array_agg 函数来处理
　　example :
　　
mrapp=# select 1 as a union select 2 as a union select 3 as a;
 a
---
 1
 2
 3
(3 行记录)
　　
mrapp=# select array_agg(t.a) from (select 1 as a union select 2 as a union sele
ct 3 as a) as t;
 array_agg
-----------
 {1,2,3}
(1 行记录)
　　3. PG中有时候会碰到一个父表下面带了一堆的字表或者关联表，要删除的话 需要一个一个先将字表或关系先删除掉，可以在删除的时候加上关键字： cascade 可以将和当前关联的东西一起删除掉
　　drop table XX cascade ;
　　drop server XX cascade;
　　drop extension XX cascade;
 4、统计一个库中的所有表的行数和表大小 
 SELECT CASE
           WHEN t.row_total = t.row_seq THEN
            REPLACE(sql_content, ' union all ', ') a order by 2 desc;')
           WHEN t.row_seq = 1 THEN
            'select * from(' || sql_content
           ELSE
            sql_content
       END sql_content
  FROM (SELECT COUNT(*) over() row_total,
               row_number() over() row_seq,
               'SELECT ''' || tablename ||
               ''' 表名, count(*) 表行数,pg_size_pretty(pg_total_relation_size(''' ||
               tablename || '''::regclass)) 表总大小 from ' || tablename ||
               ' union all ' sql_content
          FROM pg_tables
         WHERE schemaname = 'public') t
 ORDER BY t.row_seq;