Postgres provides various built-in functions that are used to get the system and session information, such as VERSION(), CURRENT_DATABASE(), CURRENT_CATALOG, etc. In SQL, the CURRENT_SCHEMA, CURRENT_CATALOG, and USER functions are used without parenthesis “()”.
In Postgres, the “CURRENT_SCHEMA” function can be used with or without parenthesis while the CURRENT_CATALOG and USER functions will be used without parenthesis.
This write-up will present a detailed guide on how to get:
- Currently Installed Postgres Version
- Current Database or Catalog Name
- Current Schema Name
- Current User or Role
- Server’s IP Address
- Server’s Starting Time
- Current Query
How to Get/Fetch the Postgres Version?
In PostgreSQL, the “VERSION()” function is used to get the detailed information regarding the currently installed Postgres version on your system:
SELECT VERSION();
The output snippet signifies that currently “PostgreSQL 15.1” is running on our system.
How to Get/Check Database Name in Postgres?
In Postgres, the CURRENT_DATABASE() function retrieves the name of the current database:
SELECT CURRENT_DATABASE();
The CURRENT_CATALOG function can also be used to get the current database name in Postgres:
SELECT CURRENT_CATALOG;
The output snippet shows that both the “CURRENT_DATABASE” and “CURRENT_CATALOG” functions return the name of the current database.
How to Check Schema Name in Postgres?
To get the name of the current schema, the CURRENT_SCHEMA function is used in Postgres:
SELECT CURRENT_SCHEMA;
The output demonstrates that the name of the current schema is “public”.
How to Get/Check the Current User or Role in Postgres?
In PostgreSQL, the CURRENT_ROLE, CURRENT_USER, or USER functions are used to get the current user or role:
SELECT CURRENT_USER, CURRENT_ROLE, USER;
The output snippet validates that the stated functions return the name of the current user/role.
How to Find the IP Address of the Postgres Server?
To get the server’s IP address, use the built-in INET_SERVER_ADDR() function:
SELECT INET_SERVER_ADDR();
The output retrieves “::1”, which is nothing but an IPV6 address that represents the local machine.
How to Check the Server’s Starting Time in Postgres?
Postgres provides a built-in PG_POSTMASTER_START_TIME() function to get the server’s starting time:
SELECT PG_POSTMASTER_START_TIME();
The output shows that the stated function retrieves the DateTime at which the server was started, along with the time zone information.
How to Find the Current Query in Postgres?
The CURRENT_QUERY() function in Postgres retrieves one or more queries that are currently executing:
SELECT CURRENT_QUERY();
The output validates the working of the CURRENT_QUERY() function.
Conclusion
PostgreSQL supports various in-built functions to get or fetch the system and session information. For instance, the VERSION(), CURRENT_DATABASE(), CURRENT_SCHEMA, CURRENT_USER, and CURRENT_QUERY() functions are used to get the current Postgres version, database, schema, user, and query.
To get the server’s IP address and the server’s starting time, use the INET_SERVER_ADDR() and PG_POSTMASTER_START_TIME() functions. This post explained numerous functions that are used to get the session or system information.
