# 主表和外表都需要修改

ALTER TABLE open.t_pay ALTER f_card_mobile TYPE character varying(64);

ALTER external TABLE pay_data_ext.r_ext_t_pay ALTER f_card_mobile TYPE character varying(64);

# 添加非空约束

alter table fn.t_reserve_bank_account_channel alter COLUMN f_modify_time set not null;

# 删除非空约束

alter table fn.t_scenario_sp_map alter COLUMN f_modify_time drop not null;

# 修改外表字段类型

alter external table fn.rd_ext_t_reserve_bank_account_channel_details alter COLUMN f_org_code_ext type text;

# 修改主表字段类型

alter table fn.t_reserve_bank_account_channel_details alter COLUMN f_org_code_ext type text;

alter table open.t_pay alter COLUMN f_client_ip type bigint;

ALTER external TABLE pay_data_ext.r_ext_t_pay ALTER f_client_ip TYPE bigint;

alter table open.t_pay alter COLUMN f_log_id drop not null;

ALTER external TABLE open.r_ext_bi_data_info ALTER fr TYPE character varying(1024);

ALTER TABLE open.bi_data_info ALTER fr TYPE character varying(1024);

# 清空主表

truncate table fn.t_scenario_sp_map;

# 删除表

drop table if exists fn.t_sp_uid_type_info;

# 删除外部表

drop external table if exists pay_data_ext.r_ext_t_scenario_sp_map;

# 新建schema

CREATE SCHEMA fn AUTHORIZATION fsg_rcc_pay_platncommon_data_rd;

CREATE SCHEMA fn AUTHORIZATION gp_test_user;

# IDC 查看表 分布键

SELECT attname FROM pg_attribute WHERE attrelid='center.t_remit'::regclass

and attnum in (SELECT unnest(attrnums) FROM pg_catalog.gp_distribution_policy t

WHERE localoid='center.t_remit'::regclass);

# 查看表 分区信息

SELECT pg_get_partition_def('center.t_remit'::regclass,true);

# 删除非空约束

alter table fn.t_reserve_bank_account_channel alter COLUMN f_create_time drop not null;

truncate table fn.t_reserve_bank_account_channel;

insert into fn.t_reserve_bank_account_channel select * from fn.rd_ext_t_reserve_bank_account_channel;

# 字段加注释

comment on column t_sp_uid_type_info.f_type is '1-neibu 2-waibu 3-dajinrong 0-else';

# 表添加字段

ALTER TABLE open.t_sp_uid_type_info ADD COLUMN "f_enabled" smallint DEFAULT 1::smallint NOT NULL;

# 查看表 分布键

SELECT attname FROM pg_attribute WHERE attrelid='wallet.t_wallet_info'::regclass

and attnum in (SELECT unnest(attrnums) FROM pg_catalog.gp_distribution_policy t

WHERE localoid='wallet.t_wallet_info'::regclass);

# 查整个库的大小

select pg_size_pretty(pg_database_size('gp_pay'));

# 查看单个表大小 - 主表不存数据 值为0

select pg_size_pretty(pg_relation_size('open.dwd_wallet_info'));

select pg_size_pretty(pg_relation_size('open.t_wallet_info_1_prt_m2019_9'));

#  查schema下所有表的大小

select relname, pg_size_pretty(pg_relation_size(relid))

from pg_stat_user_tables where schemaname='open' order by pg_relation_size(relid) desc;

# 查某一个schema的大小 包括索引 不包括索引可用pg_relation_size

select round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Mb"

from pg_tables where schemaname='test';

# 查schema的大小 包括索引 不包括索引可用pg_relation_size

select schemaname,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Mb"

from pg_tables

where schemaname in ('card','center','cif','core','wallet','fn','open','stat')

group by 1;

# 云上gp 统计每个schema的大小 - GB

select schemaname,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024/1024,2) "GB"

from pg_tables

where schemaname in ('card','center','cif','core','wallet','fn','open','stat')

group by 1;

# 云上gp 统计如下schema的总大小 - GB

select round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024/1024,2) "GB"

from pg_tables

where schemaname in ('card','center','cif','core','wallet','fn','open','stat');

# 集群所有节点:

select distinct(hostname) from gp_segment_configuration;

# 集群master节点:

select distinct(hostname) from gp_segment_configuration  where content=-1 and  role='p';

# 集群standby节点:

select distinct(hostname) from gp_segment_configuration  where content=-1 and  role='m';

# 日期转时间戳

SELECT EXTRACT(epoch FROM NOW());

SELECT EXTRACT(epoch FROM CAST('2017-12-06 00:17:10' AS TIMESTAMP));

# 时间戳转日期

SELECT TO_TIMESTAMP(1512490630);

# 导入sql

insert into ${schema}.${tablename}

select to_date(F_create_time::text,'YYYY-MM-DD'),* from ${schema}.${ext_tablename};

# 上个月的最后一天

select date_trunc('month', now()) - interval '1 day';

select to_char((date_trunc('month', now()) - interval '1 day'),'yyyyMM');

# 当前的后一天

select date_trunc('day'  , now()) + interval '1 day';

# 本月的最后一天

select (date_trunc('month', now()) + interval '1 month - 1 day')::date;

# 本月的第一天

select (date_trunc('month', now()))::date;

# 返回上个月的年月

select to_char((date_trunc('month', date '2019-11-30') - interval '1 day'),'yyyyMM');

# 更改表名

alter table 表名 rename to 新表名

# 更改字段名

alter table 表名 rename 字段名 to 新字段名

# 删除字段

alter table open.t_pay drop column f_reserve_int_1;

# 删除外表字段

ALTER external TABLE pay_data_ext.r_ext_t_pay DROP COLUMN f_reserve_int_1;