System Table Compatibility
Besides compatibility with the PostgreSQL SQL dialect and protocol, SereneDB also supports a large part of the PostgreSQL system table catalog in the pg_catalog schema. These system tables contain information about the system state in the form of metadata. This metadata is often used by external tools and clients to interact with the database system for introspection and reflection, e.g., to show which tables exist.
This page provides an overview of the supported system tables and views.
- 🟢 Supported
- 🟡 Stubbed for compatibility: These do not contain actual content and exist solely for compatibility purposes, as SereneDB does not share PostgreSQL's codebase or internal architecture
- 🔴 Not supported
System Tables
System tables provide a raw view into the state of the database system. In contrast to PostgreSQL, system tables in SereneDB are read-only, and can only be indirectly influenced through DDL statements: INSERT, UPDATE, DELETE, MERGE, TRUNCATE and COPY ... FROM on a system table, in pg_catalog or information_schema, fail with permission denied: "<table>" is a system catalog, the error PostgreSQL raises for what it refuses on its catalogs. No object can be created inside pg_catalog or information_schema, not even by a superuser: CREATE TABLE, CREATE VIEW, CREATE FUNCTION, CREATE TYPE, CREATE INDEX and the other CREATE statements fail there with permission denied to create.
System tables often contain many low-level details. For more accessible and friendly access to the same information, consider using the built-in system views, or the SQL-standard information schema.
Built-in objects carry PostgreSQL's object identifiers: 'pg_class'::regclass is 1259, 'int4'::regtype is 23, and pg_type.typinput names the same pg_proc rows as in PostgreSQL. A built-in function PostgreSQL also has, with the same argument types, has PostgreSQL's OID. These OIDs are the same in every SereneDB release. The system views, information_schema and its objects are numbered from 12000 in the order PostgreSQL 18.6 creates them ('pg_roles'::regclass is 12000, 'information_schema'::regnamespace is 12240), which need not match the OIDs a PostgreSQL server gives them. The other built-in functions and collations get OIDs that are the same in every database of a SereneDB release and can change between releases. Built-in objects only have OIDs below 262144, and objects you create get OIDs from 262144 up. Queries that look objects up by oid, by name, by name within a schema or by the relation they belong to, such as pg_class WHERE oid = ..., pg_class WHERE relname = ... AND relnamespace = ... or pg_attribute WHERE attrelid = ..., read only the matching objects, so their cost does not grow with the number of tables. The same holds when the key comes from a join, as in pg_attribute JOIN pg_class ON attrelid = pg_class.oid WHERE relname = .... Filters on the schema and on the object kind, such as relnamespace = ..., relkind IN ('r', 'v') or prokind = 'a', combine with each other and with these lookups: pg_class WHERE relnamespace = ... AND relkind = 'r' reads only that schema's tables. Rows are produced as the query consumes them, so a LIMIT stops the scan early.
| Feature | Support State | Details |
|---|---|---|
| pg_aggregate | 🟢 | Stores information about aggregate functions. |
| pg_am | 🟢 | Contains information about access methods. |
| pg_amop | 🟡 | Stores information about operators associated with access methods. |
| pg_amproc | 🟡 | Contains information about support procedures associated with access methods. |
| pg_attrdef | 🟢 | Stores column default values. |
| pg_attribute | 🟢 | Contains information about the columns of tables, views, indexes and composite types. |
| pg_authid | 🟢 | Stores information about database roles. |
| pg_auth_members | 🟢 | Tracks role memberships. |
| pg_cast | 🟡 | Contains information about type casts. |
| pg_class | 🟢 | Stores information about tables, indexes, sequences and other relations. |
| pg_collation | 🟢 | Lists PostgreSQL's default, C and POSIX and every SereneDB collation: nocase, noaccent and nfc (provider b) and the ICU locale collations (provider i). SereneDB collations are not deterministic: equal-comparing strings may differ in bytes. |
| pg_constraint | 🟢 | Stores information about table constraints. |
| pg_conversion | 🟡 | Contains information about encoding conversions. |
| pg_database | 🟢 | Stores information about databases. |
| pg_db_role_setting | 🟢 | Contains per-role configuration settings set with ALTER ROLE ... SET. |
| pg_default_acl | 🟢 | Stores default access privileges set with ALTER DEFAULT PRIVILEGES. |
| pg_depend | 🟢 | Tracks dependencies between database objects. |
| pg_description | 🟢 | Stores optional descriptions (comments) for database objects. Built-in objects have no rows. |
| pg_enum | 🟢 | Contains information about enum types. |
| pg_event_trigger | 🟡 | Stores information about event triggers. |
| pg_extension | 🟡 | Contains information about installed extensions. |
| pg_foreign_data_wrapper | 🟢 | Lists the foreign-data wrappers CREATE SERVER accepts: clickhouse_fdw, iceberg_fdw and postgres_fdw. Every role may use them, so each grants USAGE to PUBLIC. |
| pg_foreign_server | 🟢 | Contains information about foreign servers created with CREATE SERVER. Superuser-only -- its srvoptions carry credentials and are shown unredacted. srvfdw names the server's wrapper in pg_foreign_data_wrapper. |
| pg_foreign_table | 🟡 | Stores information about foreign tables. |
| pg_index | 🟢 | Contains information about indexes. |
| pg_inherits | 🟡 | Tracks table inheritance hierarchies. |
| pg_init_privs | 🟢 | Stores initial privileges of database objects: the built-in relations of pg_catalog and the pg_catalog and public schemas, with the privileges they start with. |
| pg_language | 🟢 | Contains information about procedural languages. |
| pg_largeobject | 🟡 | Stores large object data. |
| pg_largeobject_metadata | 🟡 | Contains metadata for large objects. |
| pg_namespace | 🟢 | Stores information about schemas. |
| pg_opclass | 🟢 | Contains information about operator classes. |
| pg_operator | 🟡 | Stores information about operators. |
| pg_opfamily | 🟡 | Contains information about operator families. |
| pg_parameter_acl | 🟡 | Stores access privileges for server parameters. |
| pg_partitioned_table | 🟡 | Contains information about partitioned tables. |
| pg_policy | 🟡 | Stores information about row-level security policies. |
| pg_proc | 🟢 | Contains information about functions and procedures. |
| pg_publication | 🟡 | Contains all publications created in the database. |
| pg_publication_namespace | 🟡 | Maps schemas to publications (many-to-many). |
| pg_publication_rel | 🟡 | Maps relations (tables) to publications (many-to-many). |
| pg_range | 🟡 | Stores information about range types. SereneDB has no range types. |
| pg_replication_origin | 🟢 | One origin pg_<subscription oid> per subscription, see CREATE SUBSCRIPTION, and the origins made with pg_replication_origin_create. |
| pg_rewrite | 🟢 | Stores rewrite rules for tables and views. |
| pg_seclabel | 🟡 | Stores security labels on database objects. |
| pg_sequence | 🟢 | Contains information about sequences. |
| pg_shdepend | 🟢 | Records dependencies between database objects and shared objects. |
| pg_shdescription | 🟡 | Stores optional descriptions for shared database objects. |
| pg_shseclabel | 🟡 | Stores security labels for shared database objects. |
| pg_statistic | 🟡 | Stores planner statistics. |
| pg_statistic_ext | 🟡 | Stores extended statistics definitions. |
| pg_statistic_ext_data | 🟡 | Contains data for extended statistics. |
| pg_subscription | 🟢 | Stores logical replication subscriptions; see CREATE SUBSCRIPTION. |
| pg_subscription_rel | 🟢 | Tracks per-relation subscription state: i before the initial copy, r once replicated, with the copy's LSN. |
| pg_tablespace | 🟢 | Stores information about tablespaces. |
| pg_transform | 🟡 | Stores transforms between data types and languages. |
| pg_trigger | 🟢 | Contains information about table triggers. |
| pg_ts_config | 🟡 | Stores text search configurations. |
| pg_ts_config_map | 🟡 | Maps text search configurations to dictionaries. |
| pg_ts_dict | 🟢 | Lists text search dictionaries (tokenizers). |
| pg_ts_parser | 🟡 | Contains text search parsers. |
| pg_ts_template | 🟡 | Stores text search templates. |
| pg_type | 🟢 | Stores information about data types. Lists every built-in PostgreSQL type; Data Type Compatibility says which of them hold values. |
| pg_user_mapping | 🟡 | Contains user mappings for foreign data access. |
System Views
System views provide convenient access to system information. System tables often contain numeric identifiers, e.g., for the owner of tables. The views instead use more human-readable symbolic names.
| Feature | Support State | Details |
|---|---|---|
| pg_available_extensions | 🟡 | Lists available extensions. |
| pg_available_extension_versions | 🟡 | Shows available versions of extensions. |
| pg_backend_memory_contexts | 🟡 | Displays memory contexts of the backend. |
| pg_config | 🟡 | Provides access to compile-time configuration parameters. |
| pg_cursors | 🟡 | Lists open cursors. |
| pg_file_settings | 🟡 | Summarizes contents of configuration files. |
| pg_group | 🟢 | Displays groups of database users. |
| pg_hba_file_rules | 🟢 | Summarizes client authentication configuration. |
| pg_ident_file_mappings | 🟡 | Summarizes client user name mapping configuration. |
| pg_indexes | 🟢 | Shows information about indexes. |
| pg_locks | 🟡 | Displays locks currently held or awaited. |
| pg_matviews | 🟡 | Lists materialized views. |
| pg_policies | 🟡 | Displays information about policies. |
| pg_prepared_statements | 🟢 | Lists the session's prepared statements, from PREPARE and from the extended protocol; generic_plans and custom_plans are always NULL. |
| pg_prepared_xacts | 🟡 | Shows prepared transactions. |
| pg_publication_tables | 🟡 | Displays publications and their associated tables. |
| pg_replication_origin_status | 🟢 | Shows each replication origin, of a subscription or made with pg_replication_origin_create, with the remote position it has durably applied; local_lsn is always 0/0. |
| pg_replication_slots | 🟡 | Displays replication slot information. |
| pg_roles | 🟢 | Lists database roles. |
| pg_rules | 🟡 | Shows information about rules. |
| pg_seclabels | 🟡 | Displays security labels. |
| pg_sequences | 🟢 | Lists sequences. |
| pg_settings | 🟢 | Lists SereneDB and DuckDB settings and the PostgreSQL settings SereneDB accepts, each with SereneDB's own value, with PostgreSQL's columns. A lookup by name reads only that setting. |
| pg_shadow | 🟢 | Displays database users. |
| pg_shmem_allocations | 🟡 | Shows shared memory allocations. |
| pg_stat_database | 🟢 | Shows one row per database, plus a row with datid 0 for shared objects. numbackends, xact_commit, xact_rollback, sessions and session_time count from the moment the server started; a restart sets them back to 0. The other counters are always 0 and stats_reset is NULL. |
| pg_stats | 🟡 | Provides planner statistics. |
| pg_stats_ext | 🟡 | Displays extended planner statistics. |
| pg_stats_ext_exprs | 🟡 | Shows extended planner statistics for expressions. |
| pg_tables | 🟢 | Lists tables. |
| pg_timezone_abbrevs | 🟡 | Displays time zone abbreviations. |
| pg_timezone_names | 🟢 | Lists time zone names. |
| pg_user | 🟢 | Shows database users. |
| pg_user_mappings | 🟡 | Displays user mappings. |
| pg_views | 🟢 | Lists views. |
SereneDB System Views
SereneDB exposes no system views beyond the PostgreSQL-compatible ones listed above.
Information Schema
The information_schema is a standardized, cross-database schema that allows portable system introspection. For compatibility, SereneDB follows the PostgreSQL Information Schema, which matches the ISO/IEC 9075-11 standard.
| Table name | Support State | Details |
|---|---|---|
| information_schema_catalog_name | 🟢 | |
| administrable_role_authorizations | 🟡 | |
| applicable_roles | 🟢 | |
| attributes | 🟡 | |
| character_sets | 🟢 | |
| check_constraint_routine_usage | 🟡 | |
| check_constraints | 🟢 | |
| collations | 🟡 | |
| collation_character_set_applicability | 🟡 | |
| column_column_usage | 🟡 | |
| column_domain_usage | 🟡 | |
| column_options | 🟡 | |
| column_privileges | 🟢 | |
| column_udt_usage | 🟢 | |
| columns | 🟢 | |
| constraint_column_usage | 🟢 | |
| constraint_table_usage | 🟢 | |
| data_type_privileges | 🟢 | |
| domain_constraints | 🟡 | |
| domain_udt_usage | 🟢 | |
| domains | 🟢 | |
| element_types | 🟢 | |
| enabled_roles | 🟢 | |
| foreign_data_wrapper_options | 🟡 | |
| foreign_data_wrappers | 🟡 | |
| foreign_server_options | 🟡 | |
| foreign_servers | 🟡 | |
| foreign_table_options | 🟡 | |
| foreign_tables | 🟡 | |
| key_column_usage | 🟢 | |
| parameters | 🟢 | |
| referential_constraints | 🟢 | |
| role_column_grants | 🟢 | |
| role_routine_grants | 🟢 | |
| role_table_grants | 🟢 | |
| role_udt_grants | 🟡 | |
| role_usage_grants | 🟢 | |
| routine_column_usage | 🟡 | |
| routine_privileges | 🟢 | |
| routine_routine_usage | 🟡 | |
| routine_sequence_usage | 🟡 | |
| routine_table_usage | 🟡 | |
| routines | 🟢 | |
| schemata | 🟢 | |
| sequences | 🟢 | |
| sql_features | 🟡 | SereneDB makes no claims about individual SQL standard features, so the table is empty. |
| sql_implementation_info | 🟢 | SereneDB's own values: DBMS NAME and DBMS VERSION name SereneDB, the default isolation level is REPEATABLE READ, and nulls sort at the end. |
| sql_parts | 🟢 | Every part of the SQL standard is listed as not supported in full. |
| sql_sizing | 🟢 | Names longer than the 63 characters listed are kept in full. |
| table_constraints | 🟢 | |
| table_privileges | 🟢 | |
| tables | 🟢 | |
| transforms | 🟡 | |
| triggered_update_columns | 🟡 | |
| triggers | 🟢 | |
| udt_privileges | 🟡 | |
| usage_privileges | 🟢 | |
| user_defined_types | 🟡 | |
| user_mapping_options | 🟡 | |
| user_mappings | 🟡 | |
| view_column_usage | 🟡 | |
| view_routine_usage | 🟡 | |
| view_table_usage | 🟢 | |
| views | 🟢 |