PostgreSQL教程(十五)：系統(tǒng)表詳解

更新時(shí)間：2015年05月04日 13:25:53 投稿：junjie

這篇文章主要介紹了PostgreSQL教程(十五)：系統(tǒng)表詳解,本文講解了pg_class、pg_attribute、pg_attrdef、pg_authid、pg_auth_members、pg_constraint、pg_tablespace、pg_namespace、pg_database等表的作用和字段介紹,需要的朋友可以參考下

一、pg_class:

該系統(tǒng)表記錄了數(shù)據(jù)表、索引(仍然需要參閱pg_index)、序列、視圖、復(fù)合類(lèi)型和一些特殊關(guān)系類(lèi)型的元數(shù)據(jù)。注意：不是所有字段對(duì)所有對(duì)象類(lèi)型都有意義。

名字	類(lèi)型	引用	描述
relname	name		數(shù)據(jù)類(lèi)型名字。
relnamespace	oid	pg_namespace.oid	包含這個(gè)對(duì)象的名字空間(模式)的OI。
reltype	oid	pg_type.oid	對(duì)應(yīng)這個(gè)表的行類(lèi)型的數(shù)據(jù)類(lèi)型。
relowner	oid	pg_authid.oid	對(duì)象的所有者。
relam	oid	pg_am.oid	對(duì)于索引對(duì)象，表示該索引的類(lèi)型(B-tree，hash)。
relfilenode	oid		對(duì)象存儲(chǔ)在磁盤(pán)上的文件名，如果沒(méi)有則為0。
reltablespace	oid	pg_tablespace.oid	對(duì)象所在的表空間。如果為零，則表示使用該數(shù)據(jù)庫(kù)的缺省表空間。(如果對(duì)象在磁盤(pán)上沒(méi)有文件，這個(gè)字段就沒(méi)有什么意義)
relpages	int4		該數(shù)據(jù)表或索引所占用的磁盤(pán)頁(yè)面數(shù)量，查詢(xún)規(guī)劃器會(huì)借助該值選擇最優(yōu)路徑。
reltuples	float4		表中行的數(shù)量，該值只是被規(guī)劃器使用的一個(gè)估計(jì)值。
reltoastrelid	oid	pg_class.oid	與此表關(guān)聯(lián)的TOAST表的OID，如果沒(méi)有為0。TOAST表在一個(gè)從屬表里"離線(xiàn)"存儲(chǔ)大字段。
reltoastidxid	oid	pg_class.oid	如果是TOAST表，該字段為它索引的OID，如果不是TOAST表則為0。
relhasindex	bool		如果這是一個(gè)數(shù)據(jù)表而且至少有(或者最近有過(guò))一個(gè)索引，則為真。它是由CREATE INDEX設(shè)置的，但DROP INDEX不會(huì)立即將它清除。如果VACUUM發(fā)現(xiàn)一個(gè)表沒(méi)有索引，那么它清理 relhasindex。
relisshared	bool		如果該表在整個(gè)集群中由所有數(shù)據(jù)庫(kù)共享，則為真。
relkind	char		r = 普通表，i = 索引，S = 序列，v = 視圖， c = 復(fù)合類(lèi)型，s = 特殊，t = TOAST表
relnatts	int2		數(shù)據(jù)表中用戶(hù)字段的數(shù)量(除了系統(tǒng)字段以外，如oid)。在pg_attribute里肯定有相同數(shù)目的數(shù)據(jù)行。見(jiàn)pg_attribute.attnum.
relchecks	int2		表中檢查約束的數(shù)量，參閱pg_constraint表。
reltriggers	int2		表中觸發(fā)器的數(shù)量；參閱pg_trigger表。
relhasoids	bool		如果我們?yōu)閷?duì)象中的每行都生成一個(gè)OID，則為真。
relhaspkey	bool		如果該表存在主鍵，則為真。
relhasrules	bool		如表有規(guī)則就為真；參閱pg_rewrite表。
relhassubclass	bool		如果該表有子表，則為真。
relacl	aclitem[]		訪(fǎng)問(wèn)權(quán)限。

見(jiàn)如下應(yīng)用示例：

復(fù)制代碼代碼如下:

    #查看指定表對(duì)象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)

    #查看指定表對(duì)象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:

該系統(tǒng)表存儲(chǔ)所有表(包括系統(tǒng)表，如pg_class)的字段信息。數(shù)據(jù)庫(kù)中的每個(gè)表的每個(gè)字段在pg_attribute表中都有一行記錄。

名字	類(lèi)型	引用	描述
attrelid	oid	pg_class.oid	此字段所屬的表。
attname	name		字段名。
atttypid	oid	pg_type.oid	字段的數(shù)據(jù)類(lèi)型。
attstattarget	int4		attstattarget控制ANALYZE為這個(gè)字段設(shè)置的統(tǒng)計(jì)細(xì)節(jié)的級(jí)別。零值表示不收集統(tǒng)計(jì)信息，負(fù)數(shù)表示使用系統(tǒng)缺省的統(tǒng)計(jì)對(duì)象。正數(shù)值的確切信息是和數(shù)據(jù)類(lèi)型相關(guān)的。
attlen	int2		該字段所屬類(lèi)型的長(zhǎng)度。(pg_type.typlen的拷貝)
attnum	int2		字段的編號(hào)，普通字段是從1開(kāi)始計(jì)數(shù)的。系統(tǒng)字段，如oid，是任意的負(fù)數(shù)。
attndims	int4		如果該字段是數(shù)組，該值表示數(shù)組的維數(shù)，否則是0。
attcacheoff	int4		在磁盤(pán)上總是-1，但是如果裝載入內(nèi)存中的行描述器中，它可能會(huì)被更新為緩沖在行中字段的偏移量。
atttypmod	int4		表示數(shù)據(jù)表在創(chuàng)建時(shí)提供的類(lèi)型相關(guān)的數(shù)據(jù)(比如，varchar字段的最大長(zhǎng)度)。其值對(duì)那些不需要atttypmod的類(lèi)型而言通常為-1。
attbyval	bool		pg_type.typbyval字段值的拷貝。
attstorage	char		pg_type.typstorage字段值的拷貝。
attalign	char		pg_type.typalign字段值的拷貝。
attnotnull	bool		如果該字段帶有非空約束，則為真，否則為假。
atthasdef	bool		該字段是否存在缺省值，此時(shí)它對(duì)應(yīng)pg_attrdef表里實(shí)際定義此值的記錄。
attisdropped	bool		該字段是否已經(jīng)被刪除。如果被刪除，該字段在物理上仍然存在表中，但會(huì)被分析器忽略，因此不能再通過(guò)SQL訪(fǎng)問(wèn)。
attislocal	bool		該字段是否局部定義在對(duì)象中的。
attinhcount	int4		該字段所擁有的直接祖先的個(gè)數(shù)。如果一個(gè)字段的祖先個(gè)數(shù)非零，那么它就不能被刪除或重命名。

見(jiàn)如下應(yīng)用示例：

復(fù)制代碼代碼如下:

    #查看指定表中包含的字段名和字段編號(hào)。

    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)

    #只查看用戶(hù)自定義字段的類(lèi)型

    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:

該系統(tǒng)表主要存儲(chǔ)字段缺省值，字段中的主要信息存放在pg_attribute系統(tǒng)表中。注意：只有明確聲明了缺省值的字段在該表中才會(huì)有記錄。

名字	類(lèi)型	引用	描述
adrelid	oid	pg_class.oid	這個(gè)字段所屬的表
adnum	int2	pg_attribute.attnum	字段編號(hào)，其規(guī)則等同于pg_attribute.attnum
adbin	text		字段缺省值的內(nèi)部表現(xiàn)形式。
adsrc	text		缺省值的人可讀的表現(xiàn)形式。

見(jiàn)如下應(yīng)用示例：

復(fù)制代碼代碼如下:

    #查看指定表有哪些字段存在缺省值，同時(shí)顯示出字段名和缺省值的定義方式

    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:

該系統(tǒng)表存儲(chǔ)有關(guān)數(shù)據(jù)庫(kù)認(rèn)證的角色信息，在PostgreSQL中角色可以表現(xiàn)為用戶(hù)和組兩種形式。對(duì)于用戶(hù)而言只是設(shè)置了rolcanlogin標(biāo)志的角色。由于該表包含口令數(shù)據(jù)，所以它不是公共可讀的。PostgreSQL中提供了另外一個(gè)建立在該表之上的系統(tǒng)視圖pg_roles，該視圖將口令字段填成空白。

名字	類(lèi)型	引用	描述
rolname	name		角色名稱(chēng)。
rolsuper	bool		角色是否擁有超級(jí)用戶(hù)權(quán)限。
rolcreaterole	bool		角色是否可以創(chuàng)建其它角色。
rolcreatedb	bool		角色是否可以創(chuàng)建數(shù)據(jù)庫(kù)。
rolcatupdate	bool		角色是否可以直接更新系統(tǒng)表(如果該設(shè)置為假，即使超級(jí)用戶(hù)也不能更新系統(tǒng)表)。
rolcanlogin	bool		角色是否可以登錄，換句話(huà)說(shuō)，這個(gè)角色是否可以給予會(huì)話(huà)認(rèn)證標(biāo)識(shí)符。
rolpassword	text		口令(可能是加密的)；如果沒(méi)有則為NULL。
rolvaliduntil	timestamptz		口令失效時(shí)間(只用于口令認(rèn)證)；如果沒(méi)有失效期，則為NULL。
rolconfig	text[]		運(yùn)行時(shí)配置變量的會(huì)話(huà)缺省。

見(jiàn)如下應(yīng)用示例：

復(fù)制代碼代碼如下:

    #從輸出結(jié)果可以看出口令字段已經(jīng)被加密。

    postgres=# SELECT rolname,rolpassword FROM pg_authid;

     rolname  |             rolpassword

    -----------+-------------------------------------

     postgres | md5a3556571e93b0d20722ba62be61e8c2d

五、pg_auth_members:

該系統(tǒng)表存儲(chǔ)角色之間的成員關(guān)系。

名字	類(lèi)型	引用	描述
roleid	oid	pg_authid.oid	組角色的ID。
member	oid	pg_authid.oid	屬于組角色roleid的成員角色的ID。
grantor	oid	pg_authid.oid	賦予此成員關(guān)系的角色的ID。
admin_option	bool		如果具有把其它成員角色加入組角色的權(quán)限，則為真。

見(jiàn)如下應(yīng)用示例：

復(fù)制代碼代碼如下:

    #1. 先查看角色成員表中有哪些角色之間的隸屬關(guān)系，在當(dāng)前結(jié)果集中只有一個(gè)成員角色隸屬于一個(gè)組角色，

    #    如果有多個(gè)成員角色隸屬于同一個(gè)組角色，這樣將會(huì)有多條記錄。

    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. 如果需要用一個(gè)結(jié)果集獲取角色之間的隸屬關(guān)系，可以將這兩個(gè)結(jié)果集作為子查詢(xún)后再進(jìn)行關(guān)聯(lián)。

    postgres=# SELECT rolname FROM pg_authid a,pg_auth_members am WHERE a.oid = am.member;

     rolname

    ---------

     myuser

    (1 row)

六、pg_constraint:

該系統(tǒng)表存儲(chǔ)PostgreSQL中表對(duì)象的檢查約束、主鍵、唯一約束和外鍵約束。

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

七、pg_tablespace:

該系統(tǒng)表存儲(chǔ)表空間的信息。注意：表可以放在特定的表空間里，以幫助管理磁盤(pán)布局和解決IO瓶頸。

名字	類(lèi)型	引用	描述
spcname	name		表空間名稱(chēng)。
spcowner	oid	pg_authid.oid	表空間的所有者，通常是創(chuàng)建它的角色。
spclocation	text		表空間的位置(目錄路徑)。
spcacl	aclitem[]		訪(fǎng)問(wèn)權(quán)限。

見(jiàn)如下應(yīng)用示例：

復(fù)制代碼代碼如下:

    #1. 創(chuàng)建表空間。

    postgres=# CREATE TABLESPACE my_tablespace LOCATION '/opt/PostgreSQL/9.1/mydata';

    CREATE TABLESPACE

    #2. 將新建表空間的CREATE權(quán)限賦予public。

    postgres=# GRANT CREATE ON TABLESPACE my_tablespace TO public;

    GRANT

    #3. 查看系統(tǒng)內(nèi)用戶(hù)自定義表空間的名字、文件位置和創(chuàng)建它的角色名稱(chēng)。

    #4. 系統(tǒng)創(chuàng)建時(shí)自動(dòng)創(chuàng)建的兩個(gè)表空間(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:

該系統(tǒng)表存儲(chǔ)名字空間(模式)。

名字	類(lèi)型	引用	描述
nspname	name		名字空間(模式)的名稱(chēng)。
nspowner	oid	pg_authid.oid	名字空間(模式)的所有者
nspacl	aclitem[]		訪(fǎng)問(wèn)權(quán)限。

見(jiàn)如下應(yīng)用示例：

復(fù)制代碼代碼如下:

    #查看當(dāng)前數(shù)據(jù)庫(kù)public模式的創(chuàng)建者的名稱(chēng)。

    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:

該系統(tǒng)表存儲(chǔ)數(shù)據(jù)庫(kù)的信息。和大多數(shù)系統(tǒng)表不同的是，在一個(gè)集群里該表是所有數(shù)據(jù)庫(kù)共享的，即每個(gè)集群只有一份pg_database拷貝，而不是每個(gè)數(shù)據(jù)庫(kù)一份。

名字	類(lèi)型	引用	描述
datname	name		數(shù)據(jù)庫(kù)名稱(chēng)。
datdba	oid	pg_authid.oid	數(shù)據(jù)庫(kù)所有者，通常為創(chuàng)建該數(shù)據(jù)庫(kù)的角色。
encoding	int4		數(shù)據(jù)庫(kù)的字符編碼方式。
datistemplate	bool		如果為真，此數(shù)據(jù)庫(kù)可以用于CREATE DATABASE TEMPLATE子句，把新數(shù)據(jù)庫(kù)創(chuàng)建為此數(shù)據(jù)庫(kù)的克隆。
datallowconn	bool		如果為假，則沒(méi)有人可以聯(lián)接到這個(gè)數(shù)據(jù)庫(kù)。
datlastsysoid	oid		數(shù)據(jù)庫(kù)里最后一個(gè)系統(tǒng)OID，此值對(duì)pg_dump特別有用。
datvacuumxid	xid
datfrozenxid	xid
dattablespace	text	pg_tablespace.oid	該數(shù)據(jù)庫(kù)的缺省表空間。在這個(gè)數(shù)據(jù)庫(kù)里，所有pg_class.reltablespace為零的表都將保存在這個(gè)表空間里，特別要指出的是，所有非共享的系統(tǒng)表也都存放在這里。
datconfig	text[]		運(yùn)行時(shí)配置變量的會(huì)話(huà)缺省值。
datacl	aclitem[]		訪(fǎng)問(wèn)權(quán)限。

十、pg_index:

該系統(tǒng)表存儲(chǔ)關(guān)于索引的一部分信息。其它的信息大多數(shù)存儲(chǔ)在pg_class。

名字	類(lèi)型	引用	描述
indexrelid	oid	pg_class.oid	該索引在pg_class里的記錄的OID。
indrelid	oid	pg_class.oid	索引所在表在pg_class里的記錄的OID。
indnatts	int2		索引中的字段數(shù)量(拷貝的pg_class.relnatts)。
indisunique	bool		如果為真，該索引是唯一索引。
indisprimary	bool		如果為真，該索引為該表的主鍵。
indisclustered	bool		如果為真，那么該表在這個(gè)索引上建了簇。
indkey	int2vector	pg_attribute.attnum	該數(shù)組的元素?cái)?shù)量為indnatts，數(shù)組元素值表示建立這個(gè)索引時(shí)所依賴(lài)的字段編號(hào)，如1 3，表示第一個(gè)字段和第三個(gè)字段構(gòu)成這個(gè)索引的鍵值。如果為0，則表示是表達(dá)式索引，而不是基于簡(jiǎn)單字段的索引。
indclass	oidvector	pg_opclass.oid	對(duì)于構(gòu)成索引鍵值的每個(gè)字段，這個(gè)字段都包含一個(gè)指向所使用的操作符表的OID。
indexprs	text		表達(dá)式樹(shù)用于那些非簡(jiǎn)單字段引用的索引屬性。它是一個(gè)列表，在indkey里面的每個(gè)零條目一個(gè)元素。如果所有索引屬性都是簡(jiǎn)單的引用，則為空。
indpred	text		部分索引斷言的表達(dá)式樹(shù)。如果不是部分索引，則是空字串。

見(jiàn)如下應(yīng)用示例：

復(fù)制代碼代碼如下:

    #查看該索引所在表的名稱(chēng)，以及構(gòu)成該索引的鍵值數(shù)量和具體鍵值的字段編號(hào)。  

    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)

    #查看指定表包含的索引，同時(shí)列出索引的名稱(chēng)。

    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)

您可能感興趣的文章: