Postgre的常用系统表

查询数据库列表：

select * from pg_database WHERE datistemplate = false;

 

查询组合类型：

 select * from pg_type where typtype = 'c' and typarray > 0 

AND typname NOT IN (SELECT TABLE_NAME FROM information_schema.tables)

order by typname 

 

根据名称查询OID：

select 'testdbschema1.t1'::regclass::oid

 

根据OID查询名称：

select 16392::regclass

 

查询所有表：

select * from information_schema.tables where  table_Catalog = 'testdb1' and table_schema='testdbschema1'

 

Domain:

可以基于某个基本数据类型，定义一个domain，并在这个domain上定义一个或多个check，举例：

N个表都有一个邮政编码字段，为了防止用户输入非法的邮政编码，我们需要在这个字段上定义一个check来验证用户的输入数据，显然，如果有100个表有这个字段，那么我需要定义100次这个验证规则，而且一旦规则发生变化，又需要做100次的修改

正确的做法是，定义一个domain，该domain上定义这个check，然后这100个表的邮政编码字段，都使用这个domain作为其数据类型即可：

 

CREATE DOMAIN testdbschema1.us_postal_code

  AS text

  COLLATE pg_catalog."default"

  DEFAULT '000000'::text

  CONSTRAINT c1 CHECK (VALUE ~ '^\d{5}$'::text OR VALUE ~ '^\d{5}-\d{4}$'::text)

  CONSTRAINT c2 CHECK (VALUE IS NOT NULL);

ALTER DOMAIN testdbschema1.us_postal_code

  OWNER TO testdbrole1;

COMMENT ON DOMAIN testdbschema1.us_postal_code

  IS '测试domain1';

 

domain的default默认值:如果在列上定义了default，则覆盖domain上定义的default值，同样，如果在domain上定义了默认值，则覆盖其基础类型的默认值

 

查询domain列表：

select * from information_schema.domains;

查询domain的OID、OWBER、默认值：

select oid,* from pg_type where typname='us_postal_code' and typtype='d' 

查询domain的备注：

select * from pg_description where objoid = 24605

查询domain的check:

select * from information_schema.domain_constraints where domain_schema = 'testdbschema1' and domain_name = 'us_postal_code'

select * from information_schema.check_constraints where constraint_schema='testdbschema1' and constraint_name = 'c1'

 

查询表的所有列：

 select table_catalog,table_schema,table_name,column_name,ordinal_position,column_default,

 is_nullable,data_type,character_maximum_length,character_octet_length,

 numeric_precision,numeric_precision_radix,numeric_scale,datetime_precision,interval_type,

 interval_precision,collation_catalog,collation_schema,

 collation_name,domain_catalog,domain_schema,domain_name,udt_catalog,udt_schema,udt_name,dtd_identifier,

 is_updatable     from information_schema.Columns where table_name = 't1'

注意，列的数据类型为数组时，部分数据类型，不是存放在data_type字段中，而是存在在udt_name字段中，然后某些类型，还需要通过

 

select * from information_schema.element_types where object_name = 't2' or object_name = 't1' ;

查询出其显示的字面类型，比如 "_timestamp" -->"timestamp without time zone"

 

查询所有可用的数据类型：

系统原生态的那些类型+自定义符合类型(前面已写如何获取)+domain(前面已写如何获取)

 

查询表上的约束：

select * from information_schema.table_constraints

 

查询表上的索引：

SELECT i.relname as indname,

       i.relowner as indowner,

       idx.indrelid::regclass,

       am.amname as indam,

       idx.indkey,

       ARRAY(

       SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)

       FROM generate_subscripts(idx.indkey, 1) as k

       ORDER BY k

       ) as indkey_names,

       idx.indexprs IS NOT NULL as indexprs,

       idx.indpred IS NOT NULL as indpred

FROM   pg_index as idx

JOIN   pg_class as i

ON     i.oid = idx.indexrelid

JOIN   pg_am as am

ON     i.relam = am.oid

where idx.indrelid::regclass = 'testdbschema1.t1'::regclass;