电子商务网站开发背景与原因,在线购物网站怎么做,移动端使用wordpress富文本编辑器,海报模板在线制作免费网站postpresql 查询某张表的字段名和字段类型
工作中第一次接触postpresql#xff0c;接触到这么个需求#xff0c;只是对sql有点了解#xff0c;于是就网上查阅资料。得知通过系统表可以查询#xff0c;设计到几张系统表#xff1a;pg_class、pg_attrubute、information_sc…postpresql 查询某张表的字段名和字段类型
工作中第一次接触postpresql接触到这么个需求只是对sql有点了解于是就网上查阅资料。得知通过系统表可以查询设计到几张系统表pg_class、pg_attrubute、information_schema.columns 。
其中pg_class 这张表记录了所有表或者像表的东西。包括表、索引、视图、物化视图、组合类型和TOAST表。其中一些字段的含义如下表所示。
pg_class
字段名字段描述oid表的唯一标识符Object IDrelname表的名称relnamespace表所属的命名空间pg_namespace 表的 oidreltype表的类型。对于表这通常是 pg_type 表中的 oidreloftype对于复合类型的表它表示相关联的基础类型relowner表的所有者用户的 oidrelam索引使用的存储方法的 oidrelfilenode表在磁盘上的文件节点号reltablespace表所在的表空间的 oidrelpages表占用的页数reltuples表中的元组数。reltoastrelid如果存在指向 pg_class 中的 TOAST 表的 oidreltoastidxid如果存在指向 TOAST 表的索引的 oidrelhasindex表是否有索引relisshared表是否是共享的relpersistence表的持久性永久的还是临时的relkind表的类型可能是 ‘r’表、‘i’索引等relchecks表约束的数量reltriggers表触发器的数量relhasrules表是否有规则relhasoids表是否有 OIDsObject Identifiers
pg_attrubute
这张表包含了有关表的每一列的详细信息例如数据类型、是否为空等。
字段名字段描述attrelid属性所属的表的 OID。attname列名。atttypid列的数据类型的 OID对应于 pg_type 表中的 oid。attstattarget用于统计信息的目标值。attlen列的长度以字节为单位。attnum列的序号。正整数表示用户定义的列0 表示系统列。attndims数组的维数如果不是数组则为 0。attcacheoff用于计算偏移量的缓存位置。atttypmod类型修饰符。对于 varchar(n) 这样的类型它存储 n 的值。attbyval如果列的传递是按值传递则为 true否则为 false。attstorage列的存储方式‘p’ 表示普通、‘e’ 表示外部、‘m’ 表示主内存。attalign列的对齐方式‘c’ 表示 CHAR、‘s’ 表示 SHORT、‘i’ 表示 INT、‘d’ 表示 DOUBLE。attnotnull如果列不允许为空则为 true否则为 false。atthasdef如果列有默认值则为 true否则为 false。attisdropped如果列已被删除则为 true否则为 false。attislocal如果列是表的本地列则为 true否则为 false。attinhcount列是否继承自父表。attcollation列的排序规则的 OID。attacl列的访问控制列表。attoptions列的选项。attfdwoptions表示列是否有存储外部化的选项。attmissingval缺失值。
原本的想法或者说是拿到的代码。要查的是adb_task_daily_detail_log 这张表的字段类型和数据
-- 取字段名和字段类型
select
a.attname as name,
format_type(a.atttypid,a.atttypmod) as type,
col_description(a.attrelid,a.attnum) as comment,
a.attnotnull as notnull
from
pg_class as c,
pg_attribute as a
where
c.relnameadb_task_daily_detail_log
and
a.attrelidc.oid -- 关联条件
and
a.attnum0;问题出现了对于adb_task_daily_detail_log这张表pg_class出现了2条记录。
查了半天也搞不懂为什么有两条记录如果有大哥知道的话请指导下小弟。
有说多一条索引就会多一条记录但是这张表也没有索引。
有说表记录了TOAST相关的信息也会多存储一条relkind ‘t’ 的记录但是这两条记录都是’r’。
所以后果就是查询出来的字段数量会重复。
方式2:
查information_schema.columns
information_schema.columns 是 PostgreSQL 中的系统视图之一它存储了数据库中所有表的列信息。这个视图允许用户查询表的元数据包括列名、数据类型、是否为主键、是否允许为空等。
字段名称字段描述table_catalog表所属的数据库名称。table_schema表所属的模式Schema名称。table_name表的名称。column_name列的名称。ordinal_position列在表中的位置从 1 开始。column_default列的默认值。is_nullable如果列允许为 NULL则为 “YES”否则为 “NO”。data_type列的数据类型。character_maximum_length如果数据类型是字符型则是字符的最大长度。character_octet_length字符的八位字节长度。numeric_precision如果数据类型是数字型则是精度。numeric_precision_radix数字的基数通常为 10。numeric_scale如果数据类型是数字型则是小数点后的位数。datetime_precision如果数据类型是日期时间型则是小数秒的位数。interval_type如果数据类型是间隔型则是间隔类型。interval_precision如果数据类型是间隔型则是间隔的精度。character_set_catalog字符集所属的数据库名称。character_set_schema字符集所属的模式名称。character_set_name字符集的名称。collation_catalog校对规则所属的数据库名称。collation_schem校对规则所属的模式名称。collation_name校对规则的名称。domain_catalog如果列是域类型的基础类型则是基础类型所属的数据库名称。domain_schema如果列是域类型的基础类型则是基础类型所属的模式名称。domain_name如果列是域类型的基础类型则是基础类型的名称。
select column_nameconcat(data_type,case when character_maximum_length is not null then ( || character_maximum_length || )else end) as typefrom information_schema.columns
where table_name adb_task_daily_detail_log;为了要使得数据类型和长度一起显示 做了一个拼接但是只有字符类型的数据才会被拼接。需求是对数字类型的数字也拼接。
format_type 这个函数得到的数据就是满足要求的。
方式3:
select attname as nameformat_type (atttypeid,atttypmod) as typefrompg_attributewhereattrelid adb_task_daily_detail_log::regclass and attnum0;::regclass 是 PostgreSQL 的类型转换语法。它将一个标识符在这里是字符串 adb_task_daily_detail_log转换为 regclass 类型。attrelid 是 pg_attribute 表中的一个字段表示属性列所属的表的 OID。
所以attrelid adb_task_daily_detail_log::regclass 这个条件是在过滤 pg_attribute 表的记录只选择属于名为 adb_task_daily_detail_log 的表的记录。
这样做是因为在 PostgreSQL 中每个表都有一个唯一的 OID而 pg_attribute 表存储了关于表的每个列的信息。通过检查 attrelid我们可以限制结果只包括特定表的列信息。
