PostgreSQL System Catalogs are the system tables to hold the metadata information of the objects such as tables, columns, indexes, functions, procedures, users, roles, and so on. Users are restricted to change or perform any kind of changes in the system tables to avoid mess. In this section of the PostgreSQL tutorial, we will go through some of the important PostgreSQL System Catalogs tables.
Let us see some major PG System Catalogs in the following section.
| Pg System Catalogs Name | Description |
|---|---|
| pg_database | This System Catalog stores the database information. |
| pg_stat_database | The Stats information of databases is stored in this System Catalog. |
| pg_tablespace | The tablespace details are stored in this System Catalog. |
| pg_operator | The operator details are stored in this System Catalog. |
| pg_available_extensions | The list of all available extensions is stored in this System Catalog. |
| pg_shadow | This System Catalog stores the information about all database users. |
| pg_stats | This System Catalog stores the information about planner stats. |
| pg_timezone_names | It stores the information about Time Zone names. |
| pg_locks | It stores the information about currently kept locks. |
| pg_tables | This System Catalog stores the information about all tables in the database. |
| pg_settings | The Parameter Setting is stored in this System Catalog. |
| pg_user_mappings | All User Mapping details are stored in this System Catalog. |
| pg_indexes | All index details of the database are stored in this System Catalog. |
| pg_views | All Views details of the database are stored in this System Catalog. |
PostgreSQL System Functions
PostgreSQL System Functions are used to provide the system information such as current user, schema details, version, current database, and so on.
Let us see some of the important PostgreSQL System Functions with examples in the following section.
1. PostgreSQL current_Schema() Function
The current_Schema() function provides the current schema detail as mentioned in the below screen shot.
Command:
postgres=# select current_Schema();
Output:
2. PostgreSQL current_user Function
The current_user function returns the current username detail.
Command:
postgres=# select current_user;
Output:
3. PostgreSQL current_database() Function
The current_database(); function provides the detail about the current database name.
Command:
postgres=# select current_database();
Output:
4. PostgreSQL current_database() Function
The current_setting('max_parallel_workers') function return any setting detail of PostgreSQL. In this example, we are passing the max_parallel_workers to see how many max parallel workers are set.
Command:
postgres=# select current_setting('max_parallel_workers');
Output:
5. PostgreSQL pg_postmaster_start_time() Function
The pg_postmaster_start_time() function provides the postmaster start time.
Command:
postgres=# select pg_postmaster_start_time();
Output:
6. PostgreSQL pg_backend_pid() Function
The pg_backend_pid() function provides the PID of current user.
Command:
postgres=# select pg_backend_pid();
Output:
7. PostgreSQL version() Function
The version() function provides the currently installed version of PostgreSQL.
Command:
postgres=# select version();
Output:
8. PostgreSQL pg_is_in_backup() Function
The pg_is_in_backup() function shows the status of whether the backup is running or not.
Command:
postgres=# select pg_is_in_backup();
Output:
9. PostgreSQL now () as current Function
The now () as current function returns the result of date and time information with timezone.
Command:
postgres=# select now () as current;
Output:
10. PostgreSQL now ()::timestamp Function
The now ()::timestampt function returns the result of date and time information without timezone.
Command:
postgres=# select now() ::timestamp;
Output:
11. PostgreSQL (nwo () + interval '2 hour') AS two_hour_later Function
The (now () + interval '2 hour') function adds two hours to the existing date and time.
Command:
postgres=# select (now () + interval '2 hour');
Output:
12. PostgreSQL (NOW () + interval '1 day') Function
The (NOW () + interval '1 day') function return the tomorrow same time detail.
Command:
postgres=# select (NOW () + interval '1 day') AS tomorrow_time;
Output:
13. PostgreSQL now() - interval '5 hours' AS minus_5_hours Function
The now() - interval '5 hours' AS minus_5_hours function reduced 5 hours from the current time.
Command:
postgres=# select now() - interval '5 hours' AS minus_5_hours;
Output:
