PostgreSQL學習手冊(系統表)

一、pg_class:

    該系統表記錄了資料表、索引(仍然需要參閱pg_index)、序列、檢視、複合型別和一些特殊關係型別的後設資料。注意：不是所有欄位對所有物件型別都有意義。

名字	型別	引用	描述
relname	name		資料型別名字。
relnamespace	oid	pg_namespace.oid	包含這個物件的名字空間(模式)的OI。
reltype	oid	pg_type.oid	對應這個表的行型別的資料型別。
relowner	oid	pg_authid.oid	物件的所有者。
relam	oid	pg_am.oid	對於索引物件，表示該索引的型別(B-tree，hash)。
relfilenode	oid		物件儲存在磁碟上的檔名，如果沒有則為0。
reltablespace	oid	pg_tablespace.oid	物件所在的表空間。如果為零，則表示使用該資料庫的預設表空間。(如果物件在磁碟上沒有檔案，這個欄位就沒有什麼意義)
relpages	int4		該資料表或索引所佔用的磁碟頁面數量，查詢規劃器會藉助該值選擇最優路徑。
reltuples	float4		表中行的數量，該值只是被規劃器使用的一個估計值。
reltoastrelid	oid	pg_class.oid	與此表關聯的TOAST表的OID，如果沒有為0。TOAST表在一個從屬表裡"離線"儲存大欄位。
reltoastidxid	oid	pg_class.oid	如果是TOAST表，該欄位為它索引的OID，如果不是TOAST表則為0。
relhasindex	bool		如果這是一個資料表而且至少有(或者最近有過)一個索引，則為真。它是由CREATE INDEX設定的，但DROP INDEX不會立即將它清除。如果VACUUM發現一個表沒有索引，那麼它清理 relhasindex。
relisshared	bool		如果該表在整個叢集中由所有資料庫共享，則為真。
relkind	char		r = 普通表，i = 索引，S = 序列，v = 檢視， c = 複合型別，s = 特殊，t = TOAST表
relnatts	int2		資料表中使用者欄位的數量(除了系統欄位以外，如oid)。在pg_attribute裡肯定有相同數目的資料行。見pg_attribute.attnum.
relchecks	int2		表中檢查約束的數量，參閱pg_constraint表。
reltriggers	int2		表中觸發器的數量；參閱pg_trigger表。
relhasoids	bool		如果我們為物件中的每行都生成一個OID，則為真。
relhaspkey	bool		如果該表存在主鍵，則為真。
relhasrules	bool		如表有規則就為真；參閱pg_rewrite表。
relhassubclass	bool		如果該表有子表，則為真。
relacl	aclitem[]		訪問許可權。

    見如下應用示例：
    #檢視指定表物件testtable的模式
    postgres=# SELECT relname,relnamespace,nspname FROM pg_class c,pg_namespace n WHERE relname = 'testtable' AND relnamespace = n.oid;
      relname   | relnamespace | nspname
    -------------+--------------+---------
     testtable   |         2200    | public
    (1 row)
    #檢視指定表物件testtable的owner(即role)。
    postgres=# select relname,rolname from pg_class c,pg_authid au where relname = 'testtable' and relowner = au.oid;
      relname   | rolname
    -------------+----------
     testtable   | postgres
    (1 row)

二、pg_attribute:

    該系統表儲存所有表(包括系統表，如pg_class)的欄位資訊。資料庫中的每個表的每個欄位在pg_attribute表中都有一行記錄。

名字	型別	引用	描述
attrelid	oid	pg_class.oid	此欄位所屬的表。
attname	name		欄位名。
atttypid	oid	pg_type.oid	欄位的資料型別。
attstattarget	int4		attstattarget控制ANALYZE為這個欄位設定的統計細節的級別。零值表示不收集統計資訊，負數表示使用系統預設的統計物件。正數值的確切資訊是和資料型別相關的。
attlen	int2		該欄位所屬型別的長度。(pg_type.typlen的拷貝)
attnum	int2		欄位的編號，普通欄位是從1開始計數的。系統欄位，如oid，是任意的負數。
attndims	int4		如果該欄位是陣列，該值表示陣列的維數，否則是0。
attcacheoff	int4		在磁碟上總是-1，但是如果裝載入記憶體中的行描述器中， 它可能會被更新為緩衝在行中欄位的偏移量。
atttypmod	int4		表示資料表在建立時提供的型別相關的資料(比如，varchar欄位的最大長度)。其值對那些不需要atttypmod的型別而言通常為-1。
attbyval	bool		pg_type.typbyval欄位值的拷貝。
attstorage	char		pg_type.typstorage欄位值的拷貝。
attalign	char		pg_type.typalign欄位值的拷貝。
attnotnull	bool		如果該欄位帶有非空約束，則為真，否則為假。
atthasdef	bool		該欄位是否存在預設值，此時它對應pg_attrdef表裡實際定義此值的記錄。
attisdropped	bool		該欄位是否已經被刪除。如果被刪除，該欄位在物理上仍然存在表中，但會被分析器忽略，因此不能再通過SQL訪問。
attislocal	bool		該欄位是否區域性定義在物件中的。
attinhcount	int4		該欄位所擁有的直接祖先的個數。如果一個欄位的祖先個數非零，那麼它就不能被刪除或重新命名。

    見如下應用示例：
    #檢視指定表中包含的欄位名和欄位編號。
    postgres=# SELECT relname, attname,attnum FROM pg_class c,pg_attribute attr WHERE relname  = 'testtable' AND c.oid = attr.attrelid;
      relname   | attname  | attnum
    -------------+----------+--------
     testtable   | tableoid   |     -7
     testtable   | cmax       |     -6
     testtable   | xmax      |     -5
     testtable   | cmin       |     -4
     testtable   | xmin       |     -3
     testtable   | ctid         |     -1
     testtable   | i             |      1
    (7 rows)
    #只檢視使用者自定義欄位的型別
    postgres=# SELECT relname,attname,typname FROM pg_class c,pg_attribute a,pg_type t WHERE c.relname = 'testtable' AND c.oid = attrelid AND atttypid = t.oid AND attnum > 0;
      relname   | attname  | typname
    -------------+----------+---------
     testtable   | i             | int4
    (7 rows)

 

三、pg_attrdef:

    該系統表主要儲存欄位預設值，欄位中的主要資訊存放在pg_attribute系統表中。注意：只有明確宣告瞭預設值的欄位在該表中才會有記錄。

名字	型別	引用	描述
adrelid	oid	pg_class.oid	這個欄位所屬的表
adnum	int2	pg_attribute.attnum	欄位編號，其規則等同於pg_attribute.attnum
adbin	text		欄位預設值的內部表現形式。
adsrc	text		預設值的人可讀的表現形式。

    見如下應用示例：
    #檢視指定表有哪些欄位存在預設值，同時顯示出欄位名和預設值的定義方式
    postgres=# CREATE TABLE testtable2 (i integer DEFAULT 100);
    CREATE TABLE        
    postgres=# SELECT c.relname, a.attname, ad.adnum, ad.adsrc FROM pg_class c, pg_attribute a, pg_attrdef ad WHERE relname = 'testtable2' AND ad.adrelid = c.oid AND adnum = a.attnum AND attrelid = c.oid;
      relname    | attname | adnum  | adsrc
    -------------+----------+---------+-------
     testtable2  | i            |         1  | 100
    (1 row)

 

四、pg_authid:

    該系統表儲存有關資料庫認證的角色資訊，在PostgreSQL中角色可以表現為使用者和組兩種形式。對於使用者而言只是設定了rolcanlogin標誌 的角色。由於該表包含口令資料，所以它不是公共可讀的。PostgreSQL中提供了另外一個建立在該表之上的系統檢視pg_roles，該檢視將口令字 段填成空白。

名字	型別	引用	描述
rolname	name		角色名稱。
rolsuper	bool		角色是否擁有超級使用者許可權。
rolcreaterole	bool		角色是否可以建立其它角色。
rolcreatedb	bool		角色是否可以建立資料庫。
rolcatupdate	bool		角色是否可以直接更新系統表(如果該設定為假，即使超級使用者也不能更新系統表)。
rolcanlogin	bool		角色是否可以登入，換句話說，這個角色是否可以給予會話認證識別符號。
rolpassword	text		口令(可能是加密的)；如果沒有則為NULL。
rolvaliduntil	timestamptz		口令失效時間(只用於口令認證)；如果沒有失效期，則為NULL。
rolconfig	text[]		執行時配置變數的會話預設。

    見如下應用示例：
    #從輸出結果可以看出口令欄位已經被加密。
    postgres=# SELECT rolname,rolpassword FROM pg_authid;
     rolname  |             rolpassword
    -----------+-------------------------------------
     postgres | md5a3556571e93b0d20722ba62be61e8c2d
   

五、pg_auth_members:

    該系統表儲存角色之間的成員關係。

名字	型別	引用	描述
roleid	oid	pg_authid.oid	組角色的ID。
member	oid	pg_authid.oid	屬於組角色roleid的成員角色的ID。
grantor	oid	pg_authid.oid	賦予此成員關係的角色的ID。
admin_option	bool		如果具有把其它成員角色加入組角色的許可權，則為真。

    見如下應用示例：
    #1. 先檢視角色成員表中有哪些角色之間的隸屬關係，在當前結果集中只有一個成員角色隸屬於一個組角色，
    #    如果有多個成員角色隸屬於同一個組角色，這樣將會有多條記錄。
    postgres=# SELECT * FROM pg_auth_members ;
     roleid  | member | grantor | admin_option
    --------+--------+---------+--------------
      16446 |  16445  |      10   | f
    (1 row)
    #2. 檢視組角色的名字。
    postgres=# SELECT rolname FROM pg_authid a,pg_auth_members am WHERE a.oid = am.roleid;
     rolname
    ---------
     mygroup
    (1 row)
    #3. 檢視成員角色的名字。
    #4. 如果需要用一個結果集獲取角色之間的隸屬關係，可以將這兩個結果集作為子查詢後再進行關聯。
    postgres=# SELECT rolname FROM pg_authid a,pg_auth_members am WHERE a.oid = am.member;
     rolname
    ---------
     myuser
    (1 row)

六、pg_constraint:

    該系統表儲存PostgreSQL中表物件的檢查約束、主鍵、唯一約束和外來鍵約束。

名字	型別	引用	描述
conname	name		約束名字(不一定是唯一的)。
connamespace	oid	pg_namespace.oid	包含這個約束的名字空間(模式)的OID。
contype	char		c = 檢查約束， f = 外來鍵約束， p = 主鍵約束， u = 唯一約束
condeferrable	bool		該約束是否可以推遲。
condeferred	bool		預設時這個約束是否是推遲的？
conrelid	oid	pg_class.oid	該約束所在的表，如果不是表約束則為0。
contypid	oid	pg_type.oid	該約束所在的域，如果不是域約束則為0。
confrelid	oid	pg_class.oid	如果為外來鍵，則指向參照的表，否則為0。
confupdtype	char		外來鍵更新動作程式碼。
confdeltype	char		外來鍵刪除動作程式碼。
confmatchtype	char		外來鍵匹配型別。
conkey	int2[]	pg_attribute.attnum	如果是表約束，則是約束控制的欄位列表。
confkey	int2[]	pg_attribute.attnum	如果是外來鍵，則是參照欄位的列表。
conbin	text		如果是檢查約束，則表示表示式的內部形式。
consrc	text		如果是檢查約束，則是表示式的人可讀的形式。

七、pg_tablespace:

    該系統表儲存表空間的資訊。注意：表可以放在特定的表空間裡，以幫助管理磁碟佈局和解決IO瓶頸。

名字	型別	引用	描述
spcname	name		表空間名稱。
spcowner	oid	pg_authid.oid	表空間的所有者，通常是建立它的角色。
spclocation	text		表空間的位置(目錄路徑)。
spcacl	aclitem[]		訪問許可權。

    見如下應用示例：
    #1. 建立表空間。
    postgres=# CREATE TABLESPACE my_tablespace LOCATION '/opt/PostgreSQL/9.1/mydata';
    CREATE TABLESPACE
    #2. 將新建表空間的CREATE許可權賦予public。
    postgres=# GRANT CREATE ON TABLESPACE my_tablespace TO public;
    GRANT
    #3. 檢視系統內使用者自定義表空間的名字、檔案位置和建立它的角色名稱。
    #4. 系統建立時自動建立的兩個表空間(pg_default和pg_global)的檔案位置為空(不是NULL)。
    postgres=# SELECT spcname,rolname,spclocation FROM pg_tablespace ts,pg_authid a WHERE ts.spcowner = a.oid AND spclocation <> '';
        spcname    | rolname  |        spclocation
    ---------------+----------+----------------------------
     my_tablespace | postgres | /opt/PostgreSQL/9.1/mydata
    (1 row)

八、pg_namespace:

    該系統表儲存名字空間(模式)。

名字	型別	引用	描述
nspname	name		名字空間(模式)的名稱。
nspowner	oid	pg_authid.oid	名字空間(模式)的所有者
nspacl	aclitem[]		訪問許可權。

    見如下應用示例：    
    #檢視當前資料庫public模式的建立者的名稱。
    postgres=# SELECT nspname,rolname FROM pg_namespace n, pg_authid a WHERE nspname = 'public' AND nspowner = a.oid;
     nspname | rolname
    ----------+----------
     public    | postgres
    (1 row)

九、pg_database:

    該系統表儲存資料庫的資訊。和大多數系統表不同的是，在一個叢集裡該表是所有資料庫共享的，即每個叢集只有一份pg_database拷貝，而不是每個資料庫一份。

名字	型別	引用	描述
datname	name		資料庫名稱。
datdba	oid	pg_authid.oid	資料庫所有者，通常為建立該資料庫的角色。
encoding	int4		資料庫的字元編碼方式。
datistemplate	bool		如果為真，此資料庫可以用於CREATE DATABASE TEMPLATE子句，把新資料庫建立為此資料庫的克隆。
datallowconn	bool		如果為假，則沒有人可以聯接到這個資料庫。
datlastsysoid	oid		資料庫裡最後一個系統OID，此值對pg_dump特別有用。
datvacuumxid	xid
datfrozenxid	xid
dattablespace	text	pg_tablespace.oid	該資料庫的預設表空間。在這個資料庫裡，所有pg_class.reltablespace為零的表都將儲存在這個表空間裡，特別要指出的是，所有非共享的系統表也都存放在這裡。
datconfig	text[]		執行時配置變數的會話預設值。
datacl	aclitem[]		訪問許可權。

十、pg_index:

    該系統表儲存關於索引的一部分資訊。其它的資訊大多數儲存在pg_class。

名字	型別	引用	描述
indexrelid	oid	pg_class.oid	該索引在pg_class裡的記錄的OID。
indrelid	oid	pg_class.oid	索引所在表在pg_class裡的記錄的OID。
indnatts	int2		索引中的欄位數量(拷貝的pg_class.relnatts)。
indisunique	bool		如果為真，該索引是唯一索引。
indisprimary	bool		如果為真，該索引為該表的主鍵。
indisclustered	bool		如果為真，那麼該表在這個索引上建了簇。
indkey	int2vector	pg_attribute.attnum	該陣列的元素數量為indnatts，陣列元素值表示建立這個索引時所依賴的欄位編號，如1 3，表示第一個欄位和第三個欄位構成這個索引的鍵值。如果為0，則表示是表示式索引，而不是基於簡單欄位的索引。
indclass	oidvector	pg_opclass.oid	對於構成索引鍵值的每個欄位，這個欄位都包含一個指向所使用的操作符表的OID。
indexprs	text		表示式樹用於那些非簡單欄位引用的索引屬性。它是一個列表，在indkey裡面的每個零條目一個元素。如果所有索引屬性都是簡單的引用，則為空。
indpred	text		部分索引斷言的表示式樹。如果不是部分索引， 則是空字串。

    見如下應用示例：
    #檢視該索引所在表的名稱，以及構成該索引的鍵值數量和具體鍵值的欄位編號。  
    postgres=# SELECT indnatts,indkey,relname FROM pg_index i, pg_class c WHERE c.relname = 'testtable2' AND indrelid = c.oid;
     indnatts | indkey |  relname
    ----------+--------+------------
            2 | 1 3    | testtable2
    (1 row)
    #檢視指定表包含的索引，同時列出索引的名稱。
    postgres=# SELECT t.relname AS table_name, c.relname AS index_name FROM (SELECT relname,indexrelid FROM pg_index i, pg_class c WHERE c.relname = 'testtable2' AND indrelid = c.oid) t, pg_index i,pg_class c WHERE t.indexrelid = i.indexrelid AND i.indexrelid = c.oid;
     table_name |   index_name
    ------------+----------------
     testtable2 | testtable2_idx
    (1 row)