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
- PostgreSQL 9.5
- column
pending_restartadded (commit a486e357)
- column
- PostgreSQL 8.4
- PostgreSQL 8.2
- column
unitadded (commit b517e653)
- column
- PostgreSQL 8.0
- following columns added (commit 66989aa2)
categoryshort_descextra_desc
- following columns added (commit 66989aa2)
- 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;
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
References
- PostgreSQL documentation: pg_settings