postgresql的一些固定用法

工作中用pg,有些语法和其他的并不一样,记录下。

  • 查看数据库中各个表(索引)大小
select
    (n.nspname::text),
    c.relname::text as relation,
    pg_size_pretty(pg_total_relation_size(c.oid::regclass)) as total_table_size,
    pg_size_pretty(pg_indexes_size(c.oid::regclass)) as total_index_size
from
    pg_class c
    left join pg_tablespace t on c.reltablespace = t.oid
    left join pg_namespace n on n.oid = c.relnamespace
where (n.nspname <> all (array['pg_catalog'::name, 'information_schema'::name, 'pg_toast'::name]))
    and c.relkind = 'r'::"char"
order by
    (pg_total_relation_size(c.oid::regclass))
    desc;
  • 查看各个数据库的大小
select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;
  • 查看表的各个字段
   select * from information_schema.columns where table_schema='test_schema' and table_name='test_table';
  • 关联更新
update
    tmp.order_table
set
    create_time = tmp.test_order.create_time
from
    tmp.test_order
where
    tmp.test_order.id = tmp.order_table.id;
  • 去掉字段的非空限制
alter table test.table_name alter column column_1 drop not null;

repeat&regexp_split_to_table方法
在某OTA业务中某规则需按照实际的份数和支付时间来计算黄牛和刷单,但是订单表是按照订单来记录的,例如订单A这个订单包含了2份产品,这五份对用的支付时间都是一样的,pg中可以将这个订单用repect方法拆分:

select order_id,substring(repeat(',' || a.pay_time, b.quantity::int), 2) pay_time from order_table a left join  order_table b on a.order_id = b.order_id

得到的结果如下:
order_id pay_time
A 2014-01-10 13:44:44.927, 2014-01-10 13:44:44.927
在用regexp_split_to_table方法将一行数据拆分到多行:
select order_id,regexp_split_to_table(pay_time) from before_table
得到按照份数粒度的结果表如下:
order_id pay_time
A 2014-01-10 13:44:44.927
A 2014-01-10 13:44:44.927

阅读 284

推荐阅读