postpresql 查詢某張表的字段名和字段類型

工作中第一次接觸postpresql，接觸到這么個需求，只是對sql有點了解，于是就網上查閱資料。得知通過系統表可以查詢，設計到幾張系統表：pg_class、pg_attrubute、information_schema.columns 。

其中pg_class 這張表記錄了所有表或者像表的東西。包括表、索引、視圖、物化視圖、組合類型和TOAST表。其中一些字段的含義如下表所示。

? pg_class

字段名	字段描述
oid	表的唯一標識符（Object ID）
relname	表的名稱
relnamespace	表所屬的命名空間（pg_namespace 表的 oid）
reltype	表的類型。對于表，這通常是 pg_type 表中的 oid
reloftype	對于復合類型的表，它表示相關聯的基礎類型
relowner	表的所有者（用戶的 oid）
relam	索引使用的存儲方法的 oid
relfilenode	表在磁盤上的文件節點號
reltablespace	表所在的表空間的 oid
relpages	表占用的頁數
reltuples	表中的元組數。
reltoastrelid	如果存在，指向 pg_class 中的 TOAST 表的 oid
reltoastidxid	如果存在，指向 TOAST 表的索引的 oid
relhasindex	表是否有索引
relisshared	表是否是共享的
relpersistence	表的持久性（永久的還是臨時的）
relkind	表的類型，可能是 ‘r’（表）、‘i’（索引）等
relchecks	表約束的數量
reltriggers	表觸發器的數量
relhasrules	表是否有規則
relhasoids	表是否有 OIDs（Object 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.relname='adb_task_daily_detail_log'
and
a.attrelid=c.oid -- 關聯條件
and
a.attnum>0;

問題出現了，對于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 attnum>0;

• ::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，我們可以限制結果只包括特定表的列信息。