PostgreSQL 常用SQL（持续更新...）

熊猫Jay

已于 2023-03-19 15:04:41 修改

阅读量345

点赞数 1

分类专栏： # postgreSQL 文章标签： postgresql sql 数据库

于 2022-12-14 18:33:19 首次发布

本文链接：https://blog.youkuaiyun.com/m0_37996629/article/details/128320332

版权

postgreSQL 专栏收录该内容

7 篇文章

订阅专栏

本文介绍如何使用SQL查询来获取数据库中的表名、表注释、表结构及字段默认值等元数据信息。通过具体SQL语句示例，帮助读者更好地理解和操作数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

获取表名和表注释

select
	relname as tabname,
	obj_description(c.oid)
from
	pg_class c
where
	obj_description(c.oid) is not null ;

获取表/视图结构

SELECT * FROM pg_views;
desc v_object_mfr_where_used


select
	a.attnum as "序号",
	c.relname as "表名",
	cast(obj_description(c.oid) as varchar) as "表名描述",
	a.attname as "列名",
	concat_ws('', t.typname, SUBSTRING(format_type(a.atttypid, a.atttypmod) from '\(.*\)')) as "字段类型",
	d.description as "备注"
from
	pg_attribute a
left join pg_description d on
	d.objoid = a.attrelid
	and d.objsubid = a.attnum
left join pg_class c on
	a.attrelid = c.oid
left join pg_type t on
	a.atttypid = t.oid
where
	a.attnum >= 0
	and c.relname like '表名'
order by
	c.relname desc,
	a.attnum asc

查询某表某列的默认值

SELECT column_default AS default_value FROM information_schema.columns WHERE  table_name='表名' AND column_name='字段名' LIMIT 1;