CODEWITHSUNDEEP
databasepostgresqlvariablesparameterspsql
700 Software
700 Software

Reputation: 87763

How to list all parameters in Postgres?

I was wondering if there's a parameter for the currently authenticated psql user?

But then I wonder a more broader question - how can I just see what all the paremeters are? I might discover some interesting parameters if I could see a whole list of them?

I'm only seeing online how to get the value of one parameter. Not a list...

Upvotes: 2

Views: 13803

Answers (4)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

SHOW ALL below can show all parameters according to the documentation:

SHOW ALL;

This is how SHOW ALL works below:

postgres=# SHOW ALL;
            name            |   setting   |                                     description

----------------------------+-------------+------------------------------------------------------------------------------------------
 allow_in_place_tablespaces | off         | Allows tablespaces directly inside pg_tblspc, for testing.
 allow_system_table_mods    | off         | Allows modifications of the structure of system tables.
 application_name           | psql        | Sets the application name to be reported in statistics and logs.
 archive_cleanup_command    |             | Sets the shell command that will be executed at every restart point.
 archive_command            | (disabled)  | Sets the shell command that will be called to archive a WAL file.
 archive_mode               | off         | Allows archiving of WAL files using archive_command.
 archive_timeout            | 0           | Forces a switch to the next WAL file if a new file has not been started within N seconds.
 array_nulls                | on          | Enable input of NULL elements in arrays.
 authentication_timeout     | 1min        | Sets the maximum allowed time to complete client authentication.
 autovacuum                 | on          | Starts the autovacuum subprocess.
...

And, you can show one specific parameter with SHOW as shown below:

postgres=# SHOW allow_in_place_tablespaces;
 allow_in_place_tablespaces
----------------------------
 off
(1 row)

But, you cannot show more than one parameters with SHOW as shown below:

postgres=# SHOW allow_in_place_tablespaces, allow_system_table_mods;
ERROR:  syntax error at or near ","
LINE 1: show allow_in_place_tablespaces, allow_system_table_mods;

So to show more than one parameters, use SELECT FROM pg_settings below:

postgres=# SELECT name, setting, short_desc FROM pg_settings WHERE name IN ('allow_in_place_tablespaces', 'allow_system_table_mods');
            name            | setting |                         short_desc
----------------------------+---------+------------------------------------------------------------
 allow_in_place_tablespaces | off     | Allows tablespaces directly inside pg_tblspc, for testing.
 allow_system_table_mods    | off     | Allows modifications of the structure of system tables.
(2 rows)

In addition, current_setting() can show one specific parameter as shown below:

postgres=# SELECT current_setting('allow_in_place_tablespaces');
 current_setting
-----------------
 off
(1 row)

But, you cannot show more than one parameters with current_setting() as shown below:

postgres=# SELECT current_setting('allow_in_place_tablespaces', 'allow_system_table_mods');
ERROR:  invalid input syntax for type boolean: "allow_system_table_mods"
LINE 1: ...ECT current_setting('allow_in_place_tablespaces', 'allow_sys...

Upvotes: 4

Bryn
Bryn

Reputation: 131

Nonsense. Try these two SQL statements:

set foo.bar =42;

and then:

select current_setting('foo.bar');

You’ve just set, and read an entity that the PostgreSQL doc doesn’t seem to name. You might call x.y a “user-defined session parameter”. Where is its value held? Server-side, of course.

I too would like to know how to list the names of all currently defined such entities—system-defined, like TimeZone, and user-defined.

— [email protected]

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 246238

Alvaro has answered the question how to list your current parameter values.

To get the authenticated user, you can call the SQL function session_user:

SELECT session_user;

The currently effective user can be seen with

SELECT current_user;

In psql, you can see details about your current database session with

\conninfo

Upvotes: 2

alvherre
alvherre

Reputation: 2569

PostgreSQL does not have such a thing as server-side session variables, so it's not clear what you are asking about.

Some PLs (such as PL/Python, PL/Perl) have session variables (%_SHARED in PL/Perl, GD and SD in PL/Python for example), but they are internal to the PL, not part of the server proper.

psql also has variables, which you can set with \set, and you can get a list with the same command. I suppose that's not what you want though.

Maybe you refer to so-called custom GUC configuration parameters, which are sometimes abused as session variables. You can get a list of those using SHOW ALL or SELECT * FROM pg_catalog.pg_settings.

Upvotes: 1

Related Questions