GP数据库开发语句

select to_number('1234','99999')  --1234
select to_number('1234','999')  --123

查看建表语句
select show_tableinfo('dw', 'tb_cartoon_mag_info_d');


在命令窗口查看表结构
\d 


gp查看credit4表字段信息
select * from pg_attribute where attrelid = 'credit4'::regclass limit 100;


gp查看表字段描述信息  包括表字段类型，长度，描述
select n.nspname,c.relname,col.column_name, col.data_type, col.character_maximum_length, d.description 
 from pg_class c,pg_description d,pg_namespace n, information_schema.columns col
where c.oid=d.objoid
  and c.relnamespace = n.oid
  and d.objsubid = col.ordinal_position
  and c.relname = col.table_name
  and n.nspname = col.table_schema
  and c.relname = 'tdw_labelw_user_labelweb_w'
  and n.nspname='sc'
order by d.objsubid;


----获取表的comment
select n.nspname,c.relname,d.description from pg_class c,pg_description d,pg_namespace n
where c.oid=d.objoid
  and c.relnamespace = n.oid
  and d.objsubid=0
  and c.relname like 'tmk_%'
  and n.nspname='mk'
order by c.relname


----获取表的字段信息
select col.ordinal_position,col.table_schema,col.table_name,col.column_name,
col.data_type||'('||col.character_maximum_length||')', col.data_type||'('||col.numeric_precision||','||col.numeric_scale||')'
from information_schema.columns col
where col.table_schema='sc' 
  and col.table_name ='tdw_usr_order_mail_d'
order by col.ordinal_position


select * from mk.tmk_cartoon_portal_visit_dt_cp_1_prt_pd_20150515 limit 10;


GP入库添加list分区语句：
alter table dw.tdw_serv_compln_info_file_d add partition partition_date_20150401 values(20150401)  WITH (appendonly=true, compresstype=zlib, compresslevel=5);
----删除范围分区语句
ALTER TABLE mk.tmk_mail_sms_mt_h DROP PARTITION FOR (RANK(168));


--查看GP中表分区情况
select tablename, partitiontablename, partitionrank from pg_partitions
where schemaname = 'mk'
and tablename = 'tmk_mail_sms_mt_h'
and partitiontablename = 'tmk_mail_sms_mt_h_1_prt_ph_2015060100';


----添加范围分区
ALTER TABLE mk.tmk_mail_sms_mt_h ADD PARTITION ph_20150601
START (2015060100) INCLUSIVE
END (2015060123) EXCLUSIVE;


修改GP库中字段长度(也可以修改外部表的表结构)：
alter table dw.tb_det_music_serv_info_tab_d alter column author type character varying(80);
alter table dw.tb_det_music_serv_info_tab_d alter author type character varying(90);
修改字段值：（注意：外部表可以修改表结构，但是不能做update，delete操作）
update tablename set column_name = '';
----修改表的字段名称
alter table table_name rename [column] col_name to new_name;


分区名命名原则：
日的：partition_date_yyyymmdd
月的：partition_date_yyyymm
小时的：partition_date_yyyymmddhh


删除范围分区语句
ALTER TABLE mk.tmk_mail_sms_mt_h DROP PARTITION FOR (RANK(168));


select tablename, partitiontablename, partitionrank from pg_partitions
where schemaname = 'mk'
and tablename = 'tmk_mail_sms_mt_h'
and partitiontablename = 'tmk_mail_sms_mt_h_1_prt_ph_2015060100';


添加范围分区
ALTER TABLE mk.tmk_mail_sms_mt_h ADD PARTITION ph_20150601
START (2015060100) INCLUSIVE

END (2015060123) EXCLUSIVE;