Postgre-自用

 

1.查询所有表表名

select tablename from pg_tables where tablename not like 'pg%' and tablename not like 'sql_%' order by tablename;

 

2.时间字段转换为字符串

select * from test_board where left(to_char(time,'YYYY-MM-DD'),12) ='2019-10-11' limit 1;

time字段类型为：timestamp without time zone       示例：2019-10-11 11:12:34.235446

 

3.查询数据库数据量大小

select pg_size_pretty(pg_database_size('testdb'));

 

4.查询各表数据量大小

SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables  ORDER BY  pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 100;

 

5.执行delete命令后，查询数据库数据量仍然未减少

====解决：执行VACUUM FULL命令物理释放占用空间

原文链接：https://blog.csdn.net/pg_hgdb/article/details/79490875

6.截取字符串

split_part(字段名,'-',1)

 

7.相当于sparksql的explode函数，将一行爆炸成多行

SELECT regexp_split_to_table('a,b', E'\\,') AS foo,id FROM table_one ;