SoFunction
Updated on 2025-04-06

PostgreSQL Tutorial (15): Detailed explanation of the system table

1. pg_class:

This system table records metadata for data tables, indexes (really refer to pg_index), sequences, views, composite types and some special relationship types. Note: Not all fields are meaningful to all object types.

 

name type Quote describe
relname name   Data type name.
relnamespace oid pg_namespace.oid OI containing the namespace (mode) of this object.
reltype oid pg_type.oid The data type corresponding to the row type of this table.
relowner oid pg_authid.oid Owner of the object.
relam oid pg_am.oid For an index object, the type of the index (B-tree, hash) is represented.
relfilenode oid   The file name of the object stored on disk, if not, is 0.
reltablespace oid pg_tablespace.oid The tablespace where the object resides. If zero, it means that the default tablespace for the database is used. (If the object has no files on disk, this field has no meaning)
relpages int4   The query planner will use this value to select the optimal path for the number of disk pages occupied by the data table or index.
reltuples float4   The number of rows in the table, this value is just an estimated value used by the planner.
reltoastrelid oid pg_class.oid The OID of the TOAST table associated with this table, if not 0. The TOAST table stores large fields "offline" in a slave table.
reltoastidxid oid pg_class.oid If it is a TOAST table, the field is the OID it indexes, and if it is not a TOAST table, it is 0.
relhasindex bool    True if this is a data table and there is at least (or recently) an index. It is set by CREATE INDEX, but DROP INDEX does not clear it immediately. If VACUUM finds that a table has no index, then it cleans up relhasindex.
relisshared bool    True if the table is shared by all databases throughout the cluster.
relkind char   r = normal table, i = index, S = sequence, v = view, c = composite type, s = special, t = TOAST table
relnatts int2   The number of user fields in the data table (except system fields, such as oid). There must be the same number of data rows in pg_attribute. See pg_attribute.attnum.
relchecks int2   Check the number of constraints in the table, see the pg_constraint table.
reltriggers int2   The number of triggers in the table; see the pg_trigger table.
relhasoids bool   True if we generate an OID for each row in the object.
relhaspkey bool   True if the table has a primary key.
relhasrules  bool   If there are rules on the table, it is true; see the pg_rewrite table.
relhassubclass bool    True if the table has a subtable.
relacl aclitem[]   Access permissions.

See the following application example:
 

Copy the codeThe code is as follows:

#View the pattern of the specified table object testtable
    postgres=# SELECT relname,relnamespace,nspname FROM pg_class c,pg_namespace n WHERE relname = 'testtable' AND relnamespace = ;
      relname   | relnamespace | nspname
    -------------+--------------+---------
     testtable   |         2200    | public
    (1 row)
#View the owner (i.e. role) of the specified table object testtable.
    postgres=# select relname,rolname from pg_class c,pg_authid au where relname = 'testtable' and relowner = ;
      relname   | rolname
    -------------+----------
     testtable   | postgres
    (1 row)

2. pg_attribute:

This system table stores field information for all tables (including system tables, such as pg_class). Each field of each table in the database has a row record in the pg_attribute table.

 

name type Quote describe
attrelid oid pg_class.oid The table to which this field belongs.
attname name   Field name.
atttypid oid pg_type.oid The data type of the field.
attstattarget int4   attstattarget controls the level of statistical details set by ANALYZE for this field. A zero value means that statistical information is not collected, and a negative number means that the system default statistical object is used. The exact information of a positive value is related to the data type.
attlen int2   The length of the type to which this field belongs. (Copy of pg_type.typlen)
attnum int2   The number of the field, the normal field is counted from 1. System fields, such as oid, are any negative numbers.
attndims int4   If the field is an array, the value represents the dimension of the array, otherwise it is 0.
attcacheoff int4   Always -1 on disk, but if loaded into the row descriptor in memory, it may be updated to buffer the offset of fields in rows.
atttypmod int4   Represents the type-related data provided by the data table at creation time (for example, the maximum length of the varchar field). Its value is usually -1 for those types that do not require atttypmod.
attbyval bool   A copy of the pg_type.typebyval field value.
attstorage char   Copy of the pg_type.typestorage field value.
attalign char   A copy of the pg_type.typalign field value.
attnotnull bool   True if the field has a non-null constraint, otherwise false.
atthasdef bool   Whether the default value exists in this field, it corresponds to the record that actually defines this value in the pg_attrdef table.
attisdropped bool   Whether this field has been deleted. If deleted, the field remains physically in the table, but is ignored by the parser and therefore cannot be accessed through SQL.
attislocal bool   Whether this field is locally defined in the object.
attinhcount int4   The number of direct ancestors that this field has. If the number of ancestors of a field is non-zero, it cannot be deleted or renamed.

See the following application example:
 

Copy the codeThe code is as follows:

#View the field name and field number contained in the specified table.
    postgres=# SELECT relname, attname,attnum FROM pg_class c,pg_attribute attr WHERE relname  = 'testtable' AND = ;
      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)
#View only the types of user-defined fields
    postgres=# SELECT relname,attname,typname FROM pg_class c,pg_attribute a,pg_type t WHERE = 'testtable' AND = attrelid AND atttypid = AND attnum > 0;
      relname   | attname  | typname
    -------------+----------+---------
     testtable   | i             | int4
    (7 rows)

3. pg_attrdef:

This system table mainly stores the default value of the field, and the main information in the field is stored in the pg_attribute system table. Note: Only fields that explicitly declare the default value will be recorded in this table.

 

name type Quote describe
adrelid oid pg_class.oid The table to which this field belongs
adnum int2 pg_attribute.attnum field number, whose rules are equivalent to pg_attribute.attnum
adbin text   The internal representation of the default value of the field.
adsrc text   The default value is a human-readable representation.

See the following application example:
 

Copy the codeThe code is as follows:

#View which fields in the specified table have default values, and display the definition of the field name and default values.
    postgres=# CREATE TABLE testtable2 (i integer DEFAULT 100);
    CREATE TABLE        
    postgres=# SELECT , , , FROM pg_class c, pg_attribute a, pg_attrdef ad WHERE relname = 'testtable2' AND = AND adnum = AND attrelid = ;
      relname    | attname | adnum  | adsrc
    -------------+----------+---------+-------
     testtable2  | i            |         1  | 100
    (1 row)
 

4. pg_authid:

This system table stores role information about database authentication. In PostgreSQL, roles can be represented in two forms: user and group. For users, just a role with the rolcanlogin flag set. Since the table contains password data, it is not publicly readable. Another system view pg_roles built on this table is provided in PostgreSQL, which fills the password field into a blank.

name type Quote describe
rolname name Role name.
rolsuper bool Whether the role has superuser permissions.
rolcreaterole bool Whether a character can create other roles.
rolcreatedb bool Whether a role can create a database.
rolcatupdate bool Whether the role can directly update the system table (if this is set to false, even the superuser cannot update the system table).
rolcanlogin bool Whether the role can be logged in, in other words, whether the role can give the session authentication identifier.
rolpassword text Password (probably encrypted); if not, NULL.
rolvaliduntil timestamptz Password expiration time (for password authentication only); if there is no expiration time, it is NULL.
rolconfig text[] The session default for running-time configuration variables.

See the following application example:

Copy the codeThe code is as follows:

# From the output result, you can see that the exit command field has been encrypted.
    postgres=# SELECT rolname,rolpassword FROM pg_authid;
     rolname  |             rolpassword
    -----------+-------------------------------------
     postgres | md5a3556571e93b0d20722ba62be61e8c2d

5. pg_auth_members:

This system table stores membership relationships between roles.

 

name type Quote describe
roleid oid pg_authid.oid Group role ID.
member oid pg_authid.oid The ID of the member role that belongs to the roleid of the group role.
grantor oid pg_authid.oid The ID of the role assigned to this membership.
admin_option bool True if you have permission to add other member roles to the group role.

See the following application example:
 

Copy the codeThe code is as follows:

#1. First check the affiliation between the roles in the role member table. In the current result set, only one member role belongs to a group role.
#    If multiple member roles are affiliated with the same group role, there will be multiple records.
    postgres=# SELECT * FROM pg_auth_members ;
     roleid  | member | grantor | admin_option
    --------+--------+---------+--------------
      16446 |  16445  |      10   | f
    (1 row)
#2. Check the name of the group character.
    postgres=# SELECT rolname FROM pg_authid a,pg_auth_members am WHERE = ;
     rolname
    ---------
     mygroup
    (1 row)
#3. Check the name of the member role.
#4. If you need to use a result set to obtain the affiliation between roles, you can use these two result sets as subqueries before relating them.
    postgres=# SELECT rolname FROM pg_authid a,pg_auth_members am WHERE = ;
     rolname
    ---------
     myuser
    (1 row)

6. pg_constraint:

This system table stores check constraints, primary keys, unique constraints and foreign key constraints for table objects in PostgreSQL.

 

name type Quote describe
conname name Constraint names (not necessarily unique).
connamespace oid pg_namespace.oid OID of the namespace (mode) containing this constraint.
contype char c = check constraints, f = foreign key constraints, p = primary key constraints, u = unique constraints
condeferrable bool Whether this constraint can be delayed.
condeferred bool Is this constraint delayed by default?
conrelid oid pg_class.oid The table where this constraint is located is 0 if it is not a table constraint.
contypid oid pg_type.oid The domain where the constraint is located is 0 if it is not a domain constraint.
confrelid oid pg_class.oid If it is a foreign key, it points to the referenced table, otherwise it is 0.
confupdtype char Foreign key updates action code.
confdeltype char Foreign keys delete action code.
confmatchtype char Foreign key matching type.
conkey int2[] pg_attribute.attnum If it is a table constraint, it is a list of fields controlled by constraints.
confkey int2[] pg_attribute.attnum If it is a foreign key, it is a list of reference fields.
conbin text If it is a check constraint, it indicates the internal form of the expression.
consrc text If it is a check constraint, it is a human-readable form of the expression.

7. pg_tablespace:

This system table stores information about table space. Note: Tables can be placed in specific tablespaces to help manage disk layout and resolve IO bottlenecks.

 

name type Quote describe
spcname name   Tablespace name.
spcowner oid pg_authid.oid The owner of the tablespace, usually the role that creates it.
spclocation text   The location of the tablespace (directory path).
spcacl aclitem[]   Access permissions.

See the following application example:

Copy the codeThe code is as follows:

#1. Create a tablespace.
    postgres=# CREATE TABLESPACE my_tablespace LOCATION '/opt/PostgreSQL/9.1/mydata';
    CREATE TABLESPACE
#2. Assign CREATE permissions to public for the newly created tablespace.
    postgres=# GRANT CREATE ON TABLESPACE my_tablespace TO public;
    GRANT
#3. View the name of the user-defined tablespace within the system, the file location, and the name of the role that created it.
#4. The file locations of the two tablespaces (pg_default and pg_global) that are automatically created during system creation are empty (not NULL).
    postgres=# SELECT spcname,rolname,spclocation FROM pg_tablespace ts,pg_authid a WHERE = AND spclocation <> '';
        spcname    | rolname  |        spclocation
    ---------------+----------+----------------------------
     my_tablespace | postgres | /opt/PostgreSQL/9.1/mydata
    (1 row)

8. pg_namespace:

This system table stores namespace (mode).

 

name type Quote describe
nspname name   The name of the namespace (mode).
nspowner oid pg_authid.oid Owner of namespace (mode)
nspacl aclitem[]   Access permissions.

See the following application example:

Copy the codeThe code is as follows:

#View the name of the creator of the current database public schema.
    postgres=# SELECT nspname,rolname FROM pg_namespace n, pg_authid a WHERE nspname = 'public' AND nspowner = ;
     nspname | rolname
    ----------+----------
     public    | postgres
    (1 row)

9. pg_database:

This system table stores database information. Unlike most system tables, in a cluster, the table is shared by all databases, that is, each cluster has only one copy of pg_database, rather than one copy per database.

name type Quote describe
datname name   Database name.
datdba oid pg_authid.oid Database owner, usually the role that creates the database.
encoding int4   The character encoding method of the database.
datistemplate bool   If true, this database can be used in the CREATE DATABASE TEMPLATE clause to create the new database as a clone of this database.
datallowconn bool   If false, no one can join to this database.
datlastsysoid oid   The last system OID in the database, this value is particularly useful for pg_dump.
datvacuumxid xid    
datfrozenxid xid    
dattablespace text pg_tablespace.oid The default tablespace for this database. In this database, all tables with zero pg_class.reltablespace will be stored in this tablespace. It should be noted that all non-shared system tables are also stored here.
datconfig text[]   The session default value of the runtime configuration variable.
datacl aclitem[]   Access permissions.

10. pg_index:

This system table stores some information about the index. Most of the other information is stored in pg_class.

 

name type Quote describe
indexrelid oid pg_class.oid The OID of the record indexed in pg_class.
indrelid oid pg_class.oid The OID of the record of the indexed table in pg_class.
indnatts int2   Number of fields in the index (copy pg_class.relnatts).
indisunique bool   If true, the index is the unique index.
indisprimary bool   If true, the index is the primary key of the table.
indisclustered bool   If true, then the table has clustered on this index.
indkey int2vector pg_attribute.attnum The number of elements in this array is indnatts. The array element value represents the field number that depends on when establishing this index. For example, 1 3 represents the key value of the first and third fields that constitute this index. If 0, it means it is an expression index, not a simple field-based index.
indclass oidvector pg_opclass.oid For each field that makes up the index key value, this field contains an OID pointing to the operator table used.
indexprs text   Expression trees are used for index properties that are not simple field references. It is a list with one element in each zero entry in the indkey. If all index properties are simple references, then empty.
indpred text   Expression tree for partially indexed assertions. If it is not a partial index, it is an empty string.

See the following application example:

Copy the codeThe code is as follows:

#View the name of the table where the index is located, as well as the number of key values ​​that make up the index and the field number of the specific key values.
    postgres=# SELECT indnatts,indkey,relname FROM pg_index i, pg_class c WHERE = 'testtable2' AND indrelid = ;
     indnatts | indkey |  relname
    ----------+--------+------------
            2 | 1 3    | testtable2
    (1 row)
#View the index contained in the specified table and list the index name.
    postgres=# SELECT AS table_name, AS index_name FROM (SELECT relname,indexrelid FROM pg_index i, pg_class c WHERE = 'testtable2' AND indrelid = ) t, pg_index i,pg_class c WHERE = AND = ;
     table_name |   index_name
    ------------+----------------
     testtable2 | testtable2_idx
    (1 row)