每天学习一点点
select oid,relname,relkind from pg_class where relname like 'wpp_adefect_%' and length(relname) < 20
--17使用对象标识符定位表名
select 6224742::regclass;
--18 ctid代表数据行在它所处的表的物理位置，vacuum full之后会变化。
--第一个数字标识的是物理块号，第二个数字是物理块号中的行号。
select ctid,glass_id from wpp_adefect_glass_f limit 10;
--第十个物理块第二行的内容是什么？
select ctid,glass_id from wpp_adefect_glass_f where ctid = '(10,5)'--可以利用ctid删除一个table中重复的记录，当然是大表用这种方式删除才有意义，小表用此方法删除就大材小用了。
select * from A t where t.ctid <> (select min(b.ctid) from --20180718 2000
--通过数据字典获取表的字段信息
--pg_attribute存放的是字段信息，需要关联 存放表的数据字典 pg_class & 存放schema的数据字典pg_namespace
select a.attname,pg_catalog.format_type(a.atttypid,a.atttypmod),a.attrelid as data_type from pg_catalog.pg_attribute a
where a.attrelid in (select c.oid from pg_catalog.pg_class c left join pg_catalog.pg_namespace n on n.oid = c.relnamespace
where c.relname = 'pg_class' and n.nspname = 'pg_catalog')
and a.attnum > 0
and not a.attisdropped order by a.attnum
--使用regclass会简化很多
select a.attname,pg_catalog.format_type(a.atttypid,a.atttypmod),a.attrelid as data_type from pg_catalog.pg_attribute a
where a.attrelid = 'pg_catalog.pg_class'::regclass
and a.attnum > 0
and not a.attisdropped order by a.attnum
--通过数据字典获取表的分布键
create table acquire_dsitr(a int,b int, c int,d int) distributed by (c,a);
select * from gp_distribution_policy where localoid = 'acquire_dsitr'::regclass;
--这样就可以关联pg_attribute 来获取分布键了
--attrnums 是一个数组。记录字段的attunm 与pg_attribute中的attnum关联
select a.attrnums[i.i],b.attname,a.localoid::regclass from gp_distribution_policy a,
(select generate_series(1,100))i(i), pg_attribute b
where a.attrnums[i.i] is not null
and a.localoid = b.attrelid
and a.attrnums[i.i] = b.attnum
and a.localoid = 'acquire_dsitr'::regclass
order by i.iA b where t.ctid = b.ctid)
