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

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;

Categories

Configuration, System catalogue

See also

postgresql.conf, postgresql.auto.conf, ALTER SYSTEM, pg_file_settings