pg_settings

A system catalogue view which displays the current server configuration settings

pg_settings is a system catalogue view which displays the current server configuration settings; additionally it can be updated, making it functionally the equivalent of a session-local SET command.

Definition by PostgreSQL version

pg_settings (PostgreSQL 13)

               View "pg_catalog.pg_settings"
     Column      |  Type   | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
 name            | text    |           |          |
 setting         | text    |           |          |
 unit            | text    |           |          |
 category        | text    |           |          |
 short_desc      | text    |           |          |
 extra_desc      | text    |           |          |
 context         | text    |           |          |
 vartype         | text    |           |          |
 source          | text    |           |          |
 min_val         | text    |           |          |
 max_val         | text    |           |          |
 enumvals        | text[]  |           |          |
 boot_val        | text    |           |          |
 reset_val       | text    |           |          |
 sourcefile      | text    |           |          |
 sourceline      | integer |           |          |
 pending_restart | boolean |           |          |

Documentation: pg_settings

pg_settings (PostgreSQL 12)

               View "pg_catalog.pg_settings"
     Column      |  Type   | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
 name            | text    |           |          |
 setting         | text    |           |          |
 unit            | text    |           |          |
 category        | text    |           |          |
 short_desc      | text    |           |          |
 extra_desc      | text    |           |          |
 context         | text    |           |          |
 vartype         | text    |           |          |
 source          | text    |           |          |
 min_val         | text    |           |          |
 max_val         | text    |           |          |
 enumvals        | text[]  |           |          |
 boot_val        | text    |           |          |
 reset_val       | text    |           |          |
 sourcefile      | text    |           |          |
 sourceline      | integer |           |          |
 pending_restart | boolean |           |          |

Documentation: pg_settings

pg_settings (PostgreSQL 11)

               View "pg_catalog.pg_settings"
     Column      |  Type   | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
 name            | text    |           |          |
 setting         | text    |           |          |
 unit            | text    |           |          |
 category        | text    |           |          |
 short_desc      | text    |           |          |
 extra_desc      | text    |           |          |
 context         | text    |           |          |
 vartype         | text    |           |          |
 source          | text    |           |          |
 min_val         | text    |           |          |
 max_val         | text    |           |          |
 enumvals        | text[]  |           |          |
 boot_val        | text    |           |          |
 reset_val       | text    |           |          |
 sourcefile      | text    |           |          |
 sourceline      | integer |           |          |
 pending_restart | boolean |           |          |

Documentation: pg_settings

pg_settings (PostgreSQL 10)

               View "pg_catalog.pg_settings"
     Column      |  Type   | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
 name            | text    |           |          |
 setting         | text    |           |          |
 unit            | text    |           |          |
 category        | text    |           |          |
 short_desc      | text    |           |          |
 extra_desc      | text    |           |          |
 context         | text    |           |          |
 vartype         | text    |           |          |
 source          | text    |           |          |
 min_val         | text    |           |          |
 max_val         | text    |           |          |
 enumvals        | text[]  |           |          |
 boot_val        | text    |           |          |
 reset_val       | text    |           |          |
 sourcefile      | text    |           |          |
 sourceline      | integer |           |          |
 pending_restart | boolean |           |          |

Documentation: pg_settings

pg_settings (PostgreSQL 9.6)

     View "pg_catalog.pg_settings"
     Column      |  Type   | Modifiers
-----------------+---------+-----------
 name            | text    |
 setting         | text    |
 unit            | text    |
 category        | text    |
 short_desc      | text    |
 extra_desc      | text    |
 context         | text    |
 vartype         | text    |
 source          | text    |
 min_val         | text    |
 max_val         | text    |
 enumvals        | text[]  |
 boot_val        | text    |
 reset_val       | text    |
 sourcefile      | text    |
 sourceline      | integer |
 pending_restart | boolean |

Documentation: pg_settings

pg_settings (PostgreSQL 9.5)

     View "pg_catalog.pg_settings"
     Column      |  Type   | Modifiers
-----------------+---------+-----------
 name            | text    |
 setting         | text    |
 unit            | text    |
 category        | text    |
 short_desc      | text    |
 extra_desc      | text    |
 context         | text    |
 vartype         | text    |
 source          | text    |
 min_val         | text    |
 max_val         | text    |
 enumvals        | text[]  |
 boot_val        | text    |
 reset_val       | text    |
 sourcefile      | text    |
 sourceline      | integer |
 pending_restart | boolean |

Documentation: pg_settings

pg_settings (PostgreSQL 9.4)

  View "pg_catalog.pg_settings"
   Column   |  Type   | Modifiers
------------+---------+-----------
 name       | text    |
 setting    | text    |
 unit       | text    |
 category   | text    |
 short_desc | text    |
 extra_desc | text    |
 context    | text    |
 vartype    | text    |
 source     | text    |
 min_val    | text    |
 max_val    | text    |
 enumvals   | text[]  |
 boot_val   | text    |
 reset_val  | text    |
 sourcefile | text    |
 sourceline | integer |

Documentation: pg_settings

pg_settings (PostgreSQL 9.3)

  View "pg_catalog.pg_settings"
   Column   |  Type   | Modifiers
------------+---------+-----------
 name       | text    |
 setting    | text    |
 unit       | text    |
 category   | text    |
 short_desc | text    |
 extra_desc | text    |
 context    | text    |
 vartype    | text    |
 source     | text    |
 min_val    | text    |
 max_val    | text    |
 enumvals   | text[]  |
 boot_val   | text    |
 reset_val  | text    |
 sourcefile | text    |
 sourceline | integer |

Documentation: pg_settings

pg_settings (PostgreSQL 9.2)

  View "pg_catalog.pg_settings"
   Column   |  Type   | Modifiers
------------+---------+-----------
 name       | text    |
 setting    | text    |
 unit       | text    |
 category   | text    |
 short_desc | text    |
 extra_desc | text    |
 context    | text    |
 vartype    | text    |
 source     | text    |
 min_val    | text    |
 max_val    | text    |
 enumvals   | text[]  |
 boot_val   | text    |
 reset_val  | text    |
 sourcefile | text    |
 sourceline | integer |

Documentation: pg_settings

pg_settings (PostgreSQL 9.1)

  View "pg_catalog.pg_settings"
   Column   |  Type   | Modifiers
------------+---------+-----------
 name       | text    |
 setting    | text    |
 unit       | text    |
 category   | text    |
 short_desc | text    |
 extra_desc | text    |
 context    | text    |
 vartype    | text    |
 source     | text    |
 min_val    | text    |
 max_val    | text    |
 enumvals   | text[]  |
 boot_val   | text    |
 reset_val  | text    |
 sourcefile | text    |
 sourceline | integer |

Documentation: pg_settings

pg_settings (PostgreSQL 9.0)

  View "pg_catalog.pg_settings"
   Column   |  Type   | Modifiers
------------+---------+-----------
 name       | text    |
 setting    | text    |
 unit       | text    |
 category   | text    |
 short_desc | text    |
 extra_desc | text    |
 context    | text    |
 vartype    | text    |
 source     | text    |
 min_val    | text    |
 max_val    | text    |
 enumvals   | text[]  |
 boot_val   | text    |
 reset_val  | text    |
 sourcefile | text    |
 sourceline | integer |

Documentation: pg_settings

pg_settings (PostgreSQL 8.4)

  View "pg_catalog.pg_settings"
   Column   |  Type   | Modifiers
------------+---------+-----------
 name       | text    |
 setting    | text    |
 unit       | text    |
 category   | text    |
 short_desc | text    |
 extra_desc | text    |
 context    | text    |
 vartype    | text    |
 source     | text    |
 min_val    | text    |
 max_val    | text    |
 enumvals   | text[]  |
 boot_val   | text    |
 reset_val  | text    |
 sourcefile | text    |
 sourceline | integer |
View definition:
 SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline
   FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline);
Rules:
 pg_settings_n AS
    ON UPDATE TO pg_settings DO INSTEAD NOTHING
 pg_settings_u AS
    ON UPDATE TO pg_settings
   WHERE new.name = old.name DO  SELECT set_config(old.name, new.setting, false) AS set_config

Documentation: pg_settings

pg_settings (PostgreSQL 8.3)

 View "pg_catalog.pg_settings"
   Column   | Type | Modifiers
------------+------+-----------
 name       | text |
 setting    | text |
 unit       | text |
 category   | text |
 short_desc | text |
 extra_desc | text |
 context    | text |
 vartype    | text |
 source     | text |
 min_val    | text |
 max_val    | text |
View definition:
 SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val
   FROM pg_show_all_settings() a(name text, setting text, unit text, category text, short_desc text, extra_desc text, context text, vartype text, source text, min_val text, max_val text);
Rules:
 pg_settings_n AS
    ON UPDATE TO pg_settings DO INSTEAD NOTHING
 pg_settings_u AS
    ON UPDATE TO pg_settings
   WHERE new.name = old.name DO  SELECT set_config(old.name, new.setting, false) AS set_config

Documentation: pg_settings

pg_settings (PostgreSQL 8.2)

 View "pg_catalog.pg_settings"
   Column   | Type | Modifiers
------------+------+-----------
 name       | text |
 setting    | text |
 unit       | text |
 category   | text |
 short_desc | text |
 extra_desc | text |
 context    | text |
 vartype    | text |
 source     | text |
 min_val    | text |
 max_val    | text |
View definition:
 SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val
   FROM pg_show_all_settings() a(name text, setting text, unit text, category text, short_desc text, extra_desc text, context text, vartype text, source text, min_val text, max_val text);
Rules:
 pg_settings_n AS
    ON UPDATE TO pg_settings DO INSTEAD NOTHING
 pg_settings_u AS
    ON UPDATE TO pg_settings
   WHERE new.name = old.name DO  SELECT set_config(old.name, new.setting, false) AS set_config

Documentation: pg_settings

Change history

PostgreSQL 9.5
- column pending_restart added (commit a486e357)
PostgreSQL 8.4 - following columns added:
- enumvals (commit 52a8d4f8)
- boot_val (commit 3bea93b3)
- reset_val (commit 3bea93b3)
- sourcefile (commit 3b9ec468)
- sourceline (commit 3b9ec468)
PostgreSQL 8.2
- column unit added (commit b517e653)
PostgreSQL 8.0 - following columns added (commit 66989aa2)
- category
- short_desc
- extra_desc
PostgreSQL 7.3
- added (commit 45e25445)

pg_settings was originally available via the contrib module tablefunc as function show_all_settings().

Source code

pg_settings is derived from the internal function pg_show_all_settings() (src/backend/catalog/system_views.sql):

CREATE VIEW pg_settings AS
    SELECT * FROM pg_show_all_settings() AS A;

CREATE RULE pg_settings_u AS
    ON UPDATE TO pg_settings
    WHERE new.name = old.name DO
    SELECT set_config(old.name, new.setting, 'f');

CREATE RULE pg_settings_n AS
    ON UPDATE TO pg_settings
    DO INSTEAD NOTHING;

GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;

References

PostgreSQL documentation: pg_settings

Useful articles

Useful links

Contents

pg_settings

Definition by PostgreSQL version

pg_settings (PostgreSQL 13)

pg_settings (PostgreSQL 12)

pg_settings (PostgreSQL 11)

pg_settings (PostgreSQL 10)

pg_settings (PostgreSQL 9.6)

pg_settings (PostgreSQL 9.5)

pg_settings (PostgreSQL 9.4)

pg_settings (PostgreSQL 9.3)

pg_settings (PostgreSQL 9.2)

pg_settings (PostgreSQL 9.1)

pg_settings (PostgreSQL 9.0)

pg_settings (PostgreSQL 8.4)

pg_settings (PostgreSQL 8.3)

pg_settings (PostgreSQL 8.2)

Change history

Source code

References

Categories

See also