常用数据库对象存在性查询语句

常用数据库对象存在性查询语句

1、判断某个表是否存在

SELECT * FROM pg_tables WHERE tablename='tbl_res';

2、判断某个表的某个字段是否存在

SELECT attname FROM pg_attribute WHERE attname = 'autoid' and  attrelid = 'tbl_version'::regclass;

3、判断某个表的某个约束是否存在

SELECT conname FROM pg_constraint WHERE conname = 'fk_tbl_ec_info_dev_code' and  conrelid = 'tbl_ec_info'::regclass;

4、判断某个表的索引是否存在

SELECT * FROM pg_indexes WHERE tablename='tbl_operlog' and indexname='idx_tbl_operlog_oper_time_operlog_id';

5、判断某个序列是否存在

SELECT * FROM information_schema.sequences WHERE sequence_name='seq_tbl_user_favorite_fav_res_order';

6、判断某个触发器是否存在

SELECT tgrelid::regclass,tgname, (select proname from pg_proc where oid =tgfoid)  FROM pg_trigger WHERE tgname='tri_res_name_2_pinyin';

7、判断某个视图是否存在

SELECT * FROM pg_views WHERE viewname='videorestable';

8、判断某个类型是否存在

SELECT * FROM pg_type WHERE typname='imos_code';

9、判断某个字段的数据类型是否为某类型

10、根据数据库oid查询数据库名称

select datname from pg_database where oid=16384;

11、根据表的oid查询表名称

select relname, relfilenode from pg_class where relfilenode=25811;