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.

pg_settings was added in PostgreSQL 7.3.

Definition by PostgreSQL version

pg_settings (PostgreSQL 14)

               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 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;

Examples

postgres=# SELECT * FROM pg_settings WHERE name = 'port';
-[ RECORD 1 ]---+-----------------------------------------------------
name            | port
setting         | 5433
unit            | 
category        | Connections and Authentication / Connection Settings
short_desc      | Sets the TCP port the server listens on.
extra_desc      | 
context         | postmaster
vartype         | integer
source          | configuration file
min_val         | 1
max_val         | 65535
enumvals        | 
boot_val        | 5432
reset_val       | 5433
sourcefile      | /var/lib/pgsql/postgresql.local.conf
sourceline      | 3
pending_restart | f

Categories

Configuration, System catalogue

See also

postgresql.conf, postgresql.auto.conf, ALTER SYSTEM, pg_file_settings, current_setting()