Contents

pg_shdescription

A system table recording comments for shared database objects

pg_shdescription is a system catalogue table recording comments for shared database objects

pg_shdescription was added in PostgreSQL 8.2.

This table is shared across all databases in a cluster.

Definition by PostgreSQL version

pg_shdescription (PostgreSQL 16)

         Table "pg_catalog.pg_shdescription"
   Column    | Type | Collation | Nullable | Default 
-------------+------+-----------+----------+---------
 objoid      | oid  |           | not null | 
 classoid    | oid  |           | not null | 
 description | text | C         | not null | 
Indexes:
    "pg_shdescription_o_c_index" PRIMARY KEY, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

pg_shdescription (PostgreSQL 15)

         Table "pg_catalog.pg_shdescription"
   Column    | Type | Collation | Nullable | Default 
-------------+------+-----------+----------+---------
 objoid      | oid  |           | not null | 
 classoid    | oid  |           | not null | 
 description | text | C         | not null | 
Indexes:
    "pg_shdescription_o_c_index" PRIMARY KEY, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

pg_shdescription (PostgreSQL 14)

         Table "pg_catalog.pg_shdescription"
   Column    | Type | Collation | Nullable | Default 
-------------+------+-----------+----------+---------
 objoid      | oid  |           | not null | 
 classoid    | oid  |           | not null | 
 description | text | C         | not null | 
Indexes:
    "pg_shdescription_o_c_index" PRIMARY KEY, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

pg_shdescription (PostgreSQL 13)

         Table "pg_catalog.pg_shdescription"
   Column    | Type | Collation | Nullable | Default 
-------------+------+-----------+----------+---------
 objoid      | oid  |           | not null | 
 classoid    | oid  |           | not null | 
 description | text | C         | not null | 
Indexes:
    "pg_shdescription_o_c_index" UNIQUE, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

pg_shdescription (PostgreSQL 12)

         Table "pg_catalog.pg_shdescription"
   Column    | Type | Collation | Nullable | Default 
-------------+------+-----------+----------+---------
 objoid      | oid  |           | not null | 
 classoid    | oid  |           | not null | 
 description | text | C         | not null | 
Indexes:
    "pg_shdescription_o_c_index" UNIQUE, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

pg_shdescription (PostgreSQL 11)

         Table "pg_catalog.pg_shdescription"
   Column    | Type | Collation | Nullable | Default 
-------------+------+-----------+----------+---------
 objoid      | oid  |           | not null | 
 classoid    | oid  |           | not null | 
 description | text |           | not null | 
Indexes:
    "pg_shdescription_o_c_index" UNIQUE, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

pg_shdescription (PostgreSQL 10)

         Table "pg_catalog.pg_shdescription"
   Column    | Type | Collation | Nullable | Default 
-------------+------+-----------+----------+---------
 objoid      | oid  |           | not null | 
 classoid    | oid  |           | not null | 
 description | text |           | not null | 
Indexes:
    "pg_shdescription_o_c_index" UNIQUE, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

pg_shdescription (PostgreSQL 9.6)

Table "pg_catalog.pg_shdescription"
   Column    | Type | Modifiers 
-------------+------+-----------
 objoid      | oid  | not null
 classoid    | oid  | not null
 description | text | not null
Indexes:
    "pg_shdescription_o_c_index" UNIQUE, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

pg_shdescription (PostgreSQL 9.5)

Table "pg_catalog.pg_shdescription"
   Column    | Type | Modifiers 
-------------+------+-----------
 objoid      | oid  | not null
 classoid    | oid  | not null
 description | text | not null
Indexes:
    "pg_shdescription_o_c_index" UNIQUE, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

pg_shdescription (PostgreSQL 9.4)

Table "pg_catalog.pg_shdescription"
   Column    | Type | Modifiers 
-------------+------+-----------
 objoid      | oid  | not null
 classoid    | oid  | not null
 description | text | 
Indexes:
    "pg_shdescription_o_c_index" UNIQUE, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

pg_shdescription (PostgreSQL 9.3)

Table "pg_catalog.pg_shdescription"
   Column    | Type | Modifiers 
-------------+------+-----------
 objoid      | oid  | not null
 classoid    | oid  | not null
 description | text | 
Indexes:
    "pg_shdescription_o_c_index" UNIQUE, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

pg_shdescription (PostgreSQL 9.2)

Table "pg_catalog.pg_shdescription"
   Column    | Type | Modifiers 
-------------+------+-----------
 objoid      | oid  | not null
 classoid    | oid  | not null
 description | text | 
Indexes:
    "pg_shdescription_o_c_index" UNIQUE, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

pg_shdescription (PostgreSQL 9.1)

Table "pg_catalog.pg_shdescription"
   Column    | Type | Modifiers 
-------------+------+-----------
 objoid      | oid  | not null
 classoid    | oid  | not null
 description | text | 
Indexes:
    "pg_shdescription_o_c_index" UNIQUE, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

pg_shdescription (PostgreSQL 9.0)

Table "pg_catalog.pg_shdescription"
   Column    | Type | Modifiers 
-------------+------+-----------
 objoid      | oid  | not null
 classoid    | oid  | not null
 description | text | 
Indexes:
    "pg_shdescription_o_c_index" UNIQUE, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

pg_shdescription (PostgreSQL 8.4)

Table "pg_catalog.pg_shdescription"
   Column    | Type | Modifiers 
-------------+------+-----------
 objoid      | oid  | not null
 classoid    | oid  | not null
 description | text | 
Indexes:
    "pg_shdescription_o_c_index" UNIQUE, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

pg_shdescription (PostgreSQL 8.3)

Table "pg_catalog.pg_shdescription"
   Column    | Type | Modifiers 
-------------+------+-----------
 objoid      | oid  | not null
 classoid    | oid  | not null
 description | text | 
Indexes:
    "pg_shdescription_o_c_index" UNIQUE, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

pg_shdescription (PostgreSQL 8.2)

Table "pg_catalog.pg_shdescription"
   Column    | Type | Modifiers 
-------------+------+-----------
 objoid      | oid  | not null
 classoid    | oid  | not null
 description | text | 
Indexes:
    "pg_shdescription_o_c_index" UNIQUE, btree (objoid, classoid), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_shdescription

Change history

Examples

Add a comment to a tablespace:

postgres=# COMMENT ON TABLESPACE tblspc_1 IS 'Store some of the things';
COMMENT

postgres=# SELECT * FROM pg_shdescription WHERE objoid = 
          (SELECT oid FROM pg_tablespace WHERE spcname = 'tblspc_1');
 objoid | classoid |       description        
--------+----------+--------------------------
  16385 |     1213 | Store some of the things
(1 row)

Tablespace comments can also be retrieved using the function shobj_description():

postgres=# SELECT shobj_description(
                 (SELECT oid FROM pg_tablespace WHERE spcname = 'tblspc_1'),
                 'pg_tablespace'); 
    shobj_description     
--------------------------
 Store some of the things
(1 row)

Remove a comment from a tablespace:

postgres=# COMMENT ON TABLESPACE space_1 IS NULL;
COMMENT

postgres=# SELECT * FROM pg_shdescription WHERE objoid = 
          (SELECT oid FROM pg_tablespace WHERE spcname = 'tblspc_1');
 objoid | classoid | description 
--------+----------+-------------
(0 rows)

Categories

Cluster-wide table, DDL, System catalogue

See also

pg_description, pg_shdepend, COMMENT ON, shobj_description()