gp学习1

gp加可自增长的列

ALTER TABLE school_info ADD COLUMN dataId serial;

select * from school_info where "ID" <100 order by "ID" asc;

gp的配置信息

select * from gp_segment_configuration ;

gp数据的所有表的信息

select * from pg_tables;

 

哪些库是pg_database； 用select * from pg_database 即可

哪些些表就pg_class; 一样用select 语句即可

表的字段信息查询pg_attribute;

 

 #gp数据库查询：表，字段，字段注释，字段类型

SELECT pg_namespace.nspname 
      , pg_class.relname 
      ,a.attname AS fields_name, 
       pg_catalog.format_type(a.atttypid, a.atttypmod), 
      pg_description.description AS fields_comment, 
   pg_class.relstorage 
 FROM 
       pg_catalog.pg_attribute a 
       INNER JOIN pg_class  ON a.attrelid = pg_class.oid and pg_class.relstorage not in ('x', 'v')  AND pg_class.relname not like '%\_1\_prt\_%' 
       INNER JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid AND pg_namespace.nspname not in ('my_schema1', 'my_schema2') 
      INNER JOIN pg_type   ON a.atttypid = pg_type.oid 
       LEFT OUTER JOIN pg_description ON pg_description.objoid = pg_class.oid AND pg_description.objsubid = a.attnum 
 WHERE 
       a.attnum > 0 
       AND attisdropped <> 't'  
       AND a.attname <> 'oid' 

 

#gp的指定表的字段，字段描述

SELECT a.attname AS fields_name, 
      pg_description.description AS fields_comment
 FROM 
       pg_catalog.pg_attribute a 
       INNER JOIN pg_class  ON a.attrelid = pg_class.oid and pg_class.relstorage not in ('x', 'v')  AND pg_class.relname not like '%\_1\_prt\_%' 
       LEFT OUTER JOIN pg_description ON pg_description.objoid = pg_class.oid AND pg_description.objsubid = a.attnum 
 WHERE 
       a.attnum > 0 
       AND attisdropped <> 't'  
       AND a.attname <> 'oid' 
       and pg_class.relname='school_info'

 

:
alter TABLE tcs_shxx add column dataid serial;
:
alter TABLE tcs_shxx set DISTRIBUTED BY (dataid)
:
alter  TABLE tcs_shxx add inxtm TIMESTAMP ; update tcs_shxx set inxtm = '1906-01-01 00:00:00' where dataid<1000000