PGSQL语句收集

1.SQL复制原表数据结构

create table au.test1 as select * from au.config where 1=2;

2.在复制的新表中的某列插入原始数据

insert into au.test 1(uuid) select uuid from au.config;

3.将其它列数据复制到新表中

update au.test1 a SET (name) = (select name from config where uuid = a.uuid);

4.将表拥有者赋予c:ALTER TABLE a.a_text OWNER TO c;

5.查询db数据库所占内存大小：select pg_size_pretty(pg_database_size('db'));

6.查询test表所占内存大小：select pg_size_pretty(pg_relation_size('test'));

7.查询数据库中所有表所占内存大小：
SELECT  
    schemaname || '.' || tablename AS table_full_name,  
    pg_size_pretty(pg_total_relation_size('"' || schemaname || '"."' || tablename || '"')) AS size  
FROM pg_tables  
ORDER BY  

    pg_total_relation_size('"' || schemaname || '"."' || tablename || '"') DESC

8.PGXL创建表两种模式：复制模式和分片模式。
CREATE TABLE table_name(...)
DISTRIBUTE BY 
HASH(col)|MODULO(col)|ROUNDROBIN|REPLICATION
TO NODE(nodename1,nodename2...)