From 6eb9c5a5657d1fe77b55cc261450f3538d35a94d Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 14:19:15 +0200 Subject: Adding upstream version 13.4. Signed-off-by: Daniel Baumann --- doc/src/sgml/catalogs.sgml | 13329 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 13329 insertions(+) create mode 100644 doc/src/sgml/catalogs.sgml (limited to 'doc/src/sgml/catalogs.sgml') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml new file mode 100644 index 0000000..4dff3f6 --- /dev/null +++ b/doc/src/sgml/catalogs.sgml @@ -0,0 +1,13329 @@ + + + + + System Catalogs + + + The system catalogs are the place where a relational database + management system stores schema metadata, such as information about + tables and columns, and internal bookkeeping information. + PostgreSQL's system catalogs are regular + tables. You can drop and recreate the tables, add columns, insert + and update values, and severely mess up your system that way. + Normally, one should not change the system catalogs by hand, there + are normally SQL commands to do that. (For example, CREATE + DATABASE inserts a row into the + pg_database catalog — and actually + creates the database on disk.) There are some exceptions for + particularly esoteric operations, but many of those have been made + available as SQL commands over time, and so the need for direct manipulation + of the system catalogs is ever decreasing. + + + + Overview + + + lists the system catalogs. + More detailed documentation of each catalog follows below. + + + + Most system catalogs are copied from the template database during + database creation and are thereafter database-specific. A few + catalogs are physically shared across all databases in a cluster; + these are noted in the descriptions of the individual catalogs. + + + + System Catalogs + + + + + Catalog Name + Purpose + + + + + + pg_aggregate + aggregate functions + + + + pg_am + relation access methods + + + + pg_amop + access method operators + + + + pg_amproc + access method support functions + + + + pg_attrdef + column default values + + + + pg_attribute + table columns (attributes) + + + + pg_authid + authorization identifiers (roles) + + + + pg_auth_members + authorization identifier membership relationships + + + + pg_cast + casts (data type conversions) + + + + pg_class + tables, indexes, sequences, views (relations) + + + + pg_collation + collations (locale information) + + + + pg_constraint + check constraints, unique constraints, primary key constraints, foreign key constraints + + + + pg_conversion + encoding conversion information + + + + pg_database + databases within this database cluster + + + + pg_db_role_setting + per-role and per-database settings + + + + pg_default_acl + default privileges for object types + + + + pg_depend + dependencies between database objects + + + + pg_description + descriptions or comments on database objects + + + + pg_enum + enum label and value definitions + + + + pg_event_trigger + event triggers + + + + pg_extension + installed extensions + + + + pg_foreign_data_wrapper + foreign-data wrapper definitions + + + + pg_foreign_server + foreign server definitions + + + + pg_foreign_table + additional foreign table information + + + + pg_index + additional index information + + + + pg_inherits + table inheritance hierarchy + + + + pg_init_privs + object initial privileges + + + + pg_language + languages for writing functions + + + + pg_largeobject + data pages for large objects + + + + pg_largeobject_metadata + metadata for large objects + + + + pg_namespace + schemas + + + + pg_opclass + access method operator classes + + + + pg_operator + operators + + + + pg_opfamily + access method operator families + + + + pg_partitioned_table + information about partition key of tables + + + + pg_policy + row-security policies + + + + pg_proc + functions and procedures + + + + pg_publication + publications for logical replication + + + + pg_publication_rel + relation to publication mapping + + + + pg_range + information about range types + + + + pg_replication_origin + registered replication origins + + + + pg_rewrite + query rewrite rules + + + + pg_seclabel + security labels on database objects + + + + pg_sequence + information about sequences + + + + pg_shdepend + dependencies on shared objects + + + + pg_shdescription + comments on shared objects + + + + pg_shseclabel + security labels on shared database objects + + + + pg_statistic + planner statistics + + + + pg_statistic_ext + extended planner statistics (definition) + + + + pg_statistic_ext_data + extended planner statistics (built statistics) + + + + pg_subscription + logical replication subscriptions + + + + pg_subscription_rel + relation state for subscriptions + + + + pg_tablespace + tablespaces within this database cluster + + + + pg_transform + transforms (data type to procedural language conversions) + + + + pg_trigger + triggers + + + + pg_ts_config + text search configurations + + + + pg_ts_config_map + text search configurations' token mappings + + + + pg_ts_dict + text search dictionaries + + + + pg_ts_parser + text search parsers + + + + pg_ts_template + text search templates + + + + pg_type + data types + + + + pg_user_mapping + mappings of users to foreign servers + + + +
+ + + + + <structname>pg_aggregate</structname> + + + pg_aggregate + + + + The catalog pg_aggregate stores information about + aggregate functions. An aggregate function is a function that + operates on a set of values (typically one column from each row + that matches a query condition) and returns a single value computed + from all these values. Typical aggregate functions are + sum, count, and + max. Each entry in + pg_aggregate is an extension of an entry + in pg_proc. The pg_proc + entry carries the aggregate's name, input and output data types, and + other information that is similar to ordinary functions. + + + + <structname>pg_aggregate</structname> Columns + + + + + Column Type + + + Description + + + + + + + + aggfnoid regproc + (references pg_proc.oid) + + + pg_proc OID of the aggregate function + + + + + + aggkind char + + + Aggregate kind: + n for normal aggregates, + o for ordered-set aggregates, or + h for hypothetical-set aggregates + + + + + + aggnumdirectargs int2 + + + Number of direct (non-aggregated) arguments of an ordered-set or + hypothetical-set aggregate, counting a variadic array as one argument. + If equal to pronargs, the aggregate must be variadic + and the variadic array describes the aggregated arguments as well as + the final direct arguments. + Always zero for normal aggregates. + + + + + + aggtransfn regproc + (references pg_proc.oid) + + + Transition function + + + + + + aggfinalfn regproc + (references pg_proc.oid) + + + Final function (zero if none) + + + + + + aggcombinefn regproc + (references pg_proc.oid) + + + Combine function (zero if none) + + + + + + aggserialfn regproc + (references pg_proc.oid) + + + Serialization function (zero if none) + + + + + + aggdeserialfn regproc + (references pg_proc.oid) + + + Deserialization function (zero if none) + + + + + + aggmtransfn regproc + (references pg_proc.oid) + + + Forward transition function for moving-aggregate mode (zero if none) + + + + + + aggminvtransfn regproc + (references pg_proc.oid) + + + Inverse transition function for moving-aggregate mode (zero if none) + + + + + + aggmfinalfn regproc + (references pg_proc.oid) + + + Final function for moving-aggregate mode (zero if none) + + + + + + aggfinalextra bool + + + True to pass extra dummy arguments to aggfinalfn + + + + + + aggmfinalextra bool + + + True to pass extra dummy arguments to aggmfinalfn + + + + + + aggfinalmodify char + + + Whether aggfinalfn modifies the + transition state value: + r if it is read-only, + s if the aggtransfn + cannot be applied after the aggfinalfn, or + w if it writes on the value + + + + + + aggmfinalmodify char + + + Like aggfinalmodify, but for + the aggmfinalfn + + + + + + aggsortop oid + (references pg_operator.oid) + + + Associated sort operator (zero if none) + + + + + + aggtranstype oid + (references pg_type.oid) + + + Data type of the aggregate function's internal transition (state) data + + + + + + aggtransspace int4 + + + Approximate average size (in bytes) of the transition state + data, or zero to use a default estimate + + + + + + aggmtranstype oid + (references pg_type.oid) + + + Data type of the aggregate function's internal transition (state) + data for moving-aggregate mode (zero if none) + + + + + + aggmtransspace int4 + + + Approximate average size (in bytes) of the transition state data + for moving-aggregate mode, or zero to use a default estimate + + + + + + agginitval text + + + The initial value of the transition state. This is a text + field containing the initial value in its external string + representation. If this field is null, the transition state + value starts out null. + + + + + + aggminitval text + + + The initial value of the transition state for moving-aggregate mode. + This is a text field containing the initial value in its external + string representation. If this field is null, the transition state + value starts out null. + + + + +
+ + + New aggregate functions are registered with the + command. See for more information about + writing aggregate functions and the meaning of the transition + functions, etc. + + + + + + + <structname>pg_am</structname> + + + pg_am + + + + The catalog pg_am stores information about + relation access methods. There is one row for each access method supported + by the system. + Currently, only tables and indexes have access methods. The requirements for table + and index access methods are discussed in detail in and + respectively. + + + + <structname>pg_am</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + amname name + + + Name of the access method + + + + + + amhandler regproc + (references pg_proc.oid) + + + OID of a handler function that is responsible for supplying information + about the access method + + + + + + amtype char + + + t = table (including materialized views), + i = index. + + + + +
+ + + + Before PostgreSQL 9.6, pg_am + contained many additional columns representing properties of index access + methods. That data is now only directly visible at the C code level. + However, pg_index_column_has_property() and related + functions have been added to allow SQL queries to inspect index access + method properties; see . + + + + + + + + <structname>pg_amop</structname> + + + pg_amop + + + + The catalog pg_amop stores information about + operators associated with access method operator families. There is one + row for each operator that is a member of an operator family. A family + member can be either a search operator or an + ordering operator. An operator + can appear in more than one family, but cannot appear in more than one + search position nor more than one ordering position within a family. + (It is allowed, though unlikely, for an operator to be used for both + search and ordering purposes.) + + + + <structname>pg_amop</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + amopfamily oid + (references pg_opfamily.oid) + + + The operator family this entry is for + + + + + + amoplefttype oid + (references pg_type.oid) + + + Left-hand input data type of operator + + + + + + amoprighttype oid + (references pg_type.oid) + + + Right-hand input data type of operator + + + + + + amopstrategy int2 + + + Operator strategy number + + + + + + amoppurpose char + + + Operator purpose, either s for search or + o for ordering + + + + + + amopopr oid + (references pg_operator.oid) + + + OID of the operator + + + + + + amopmethod oid + (references pg_am.oid) + + + Index access method operator family is for + + + + + + amopsortfamily oid + (references pg_opfamily.oid) + + + The B-tree operator family this entry sorts according to, if an + ordering operator; zero if a search operator + + + + +
+ + + A search operator entry indicates that an index of this operator + family can be searched to find all rows satisfying + WHERE + indexed_column + operator + constant. + Obviously, such an operator must return boolean, and its left-hand input + type must match the index's column data type. + + + + An ordering operator entry indicates that an index of this + operator family can be scanned to return rows in the order represented by + ORDER BY + indexed_column + operator + constant. + Such an operator could return any sortable data type, though again + its left-hand input type must match the index's column data type. + The exact semantics of the ORDER BY are specified by the + amopsortfamily column, which must reference + a B-tree operator family for the operator's result type. + + + + + At present, it's assumed that the sort order for an ordering operator + is the default for the referenced operator family, i.e., ASC NULLS + LAST. This might someday be relaxed by adding additional columns + to specify sort options explicitly. + + + + + An entry's amopmethod must match the + opfmethod of its containing operator family (including + amopmethod here is an intentional denormalization of the + catalog structure for performance reasons). Also, + amoplefttype and amoprighttype must match + the oprleft and oprright fields of the + referenced pg_operator entry. + + + + + + + <structname>pg_amproc</structname> + + + pg_amproc + + + + The catalog pg_amproc stores information about + support functions associated with access method operator families. There + is one row for each support function belonging to an operator family. + + + + <structname>pg_amproc</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + amprocfamily oid + (references pg_opfamily.oid) + + + The operator family this entry is for + + + + + + amproclefttype oid + (references pg_type.oid) + + + Left-hand input data type of associated operator + + + + + + amprocrighttype oid + (references pg_type.oid) + + + Right-hand input data type of associated operator + + + + + + amprocnum int2 + + + Support function number + + + + + + amproc regproc + (references pg_proc.oid) + + + OID of the function + + + + +
+ + + The usual interpretation of the + amproclefttype and amprocrighttype fields + is that they identify the left and right input types of the operator(s) + that a particular support function supports. For some access methods + these match the input data type(s) of the support function itself, for + others not. There is a notion of default support functions for + an index, which are those with amproclefttype and + amprocrighttype both equal to the index operator class's + opcintype. + + + + + + + <structname>pg_attrdef</structname> + + + pg_attrdef + + + + The catalog pg_attrdef stores column default + values. The main information about columns is stored in + pg_attribute. + Only columns for which a default value has been explicitly set will have + an entry here. + + + + <structname>pg_attrdef</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + adrelid oid + (references pg_class.oid) + + + The table this column belongs to + + + + + + adnum int2 + (references pg_attribute.attnum) + + + The number of the column + + + + + + adbin pg_node_tree + + + The column default value, in nodeToString() + representation. Use pg_get_expr(adbin, adrelid) to + convert it to an SQL expression. + + + + +
+ + + + + <structname>pg_attribute</structname> + + + pg_attribute + + + + The catalog pg_attribute stores information about + table columns. There will be exactly one + pg_attribute row for every column in every + table in the database. (There will also be attribute entries for + indexes, and indeed all objects that have pg_class + entries.) + + + + The term attribute is equivalent to column and is used for + historical reasons. + + + + <structname>pg_attribute</structname> Columns + + + + + Column Type + + + Description + + + + + + + + attrelid oid + (references pg_class.oid) + + + The table this column belongs to + + + + + + attname name + + + The column name + + + + + + atttypid oid + (references pg_type.oid) + + + The data type of this column + + + + + + attstattarget int4 + + + attstattarget controls the level of detail + of statistics accumulated for this column by + . + A zero value indicates that no statistics should be collected. + A negative value says to use the system default statistics target. + The exact meaning of positive values is data type-dependent. + For scalar data types, attstattarget + is both the target number of most common values + to collect, and the target number of histogram bins to create. + + + + + + attlen int2 + + + A copy of pg_type.typlen of this column's + type + + + + + + attnum int2 + + + The number of the column. Ordinary columns are numbered from 1 + up. System columns, such as ctid, + have (arbitrary) negative numbers. + + + + + + attndims int4 + + + Number of dimensions, if the column is an array type; otherwise 0. + (Presently, the number of dimensions of an array is not enforced, + so any nonzero value effectively means it's an array.) + + + + + + attcacheoff int4 + + + Always -1 in storage, but when loaded into a row descriptor + in memory this might be updated to cache the offset of the attribute + within the row + + + + + + atttypmod int4 + + + atttypmod records type-specific data + supplied at table creation time (for example, the maximum + length of a varchar column). It is passed to + type-specific input functions and length coercion functions. + The value will generally be -1 for types that do not need atttypmod. + + + + + + attbyval bool + + + A copy of pg_type.typbyval of this column's type + + + + + + attstorage char + + + Normally a copy of pg_type.typstorage of this + column's type. For TOAST-able data types, this can be altered + after column creation to control storage policy. + + + + + + attalign char + + + A copy of pg_type.typalign of this column's type + + + + + + attnotnull bool + + + This represents a not-null constraint. + + + + + + atthasdef bool + + + This column has a default expression or generation expression, in which + case there will be a corresponding entry in the + pg_attrdef catalog that actually defines the + expression. (Check attgenerated to + determine whether this is a default or a generation expression.) + + + + + + atthasmissing bool + + + This column has a value which is used where the column is entirely + missing from the row, as happens when a column is added with a + non-volatile DEFAULT value after the row is created. + The actual value used is stored in the + attmissingval column. + + + + + + attidentity char + + + If a zero byte (''), then not an identity column. + Otherwise, a = generated + always, d = generated by default. + + + + + + attgenerated char + + + If a zero byte (''), then not a generated column. + Otherwise, s = stored. (Other values might be added + in the future.) + + + + + + attisdropped bool + + + This column has been dropped and is no longer valid. A dropped + column is still physically present in the table, but is + ignored by the parser and so cannot be accessed via SQL. + + + + + + attislocal bool + + + This column is defined locally in the relation. Note that a column can + be locally defined and inherited simultaneously. + + + + + + attinhcount int4 + + + The number of direct ancestors this column has. A column with a + nonzero number of ancestors cannot be dropped nor renamed. + + + + + + attcollation oid + (references pg_collation.oid) + + + The defined collation of the column, or zero if the column is + not of a collatable data type. + + + + + + attacl aclitem[] + + + Column-level access privileges, if any have been granted specifically + on this column + + + + + + attoptions text[] + + + Attribute-level options, as keyword=value strings + + + + + + attfdwoptions text[] + + + Attribute-level foreign data wrapper options, as keyword=value strings + + + + + + attmissingval anyarray + + + This column has a one element array containing the value used when the + column is entirely missing from the row, as happens when the column is + added with a non-volatile DEFAULT value after the + row is created. The value is only used when + atthasmissing is true. If there is no value + the column is null. + + + + +
+ + + In a dropped column's pg_attribute entry, + atttypid is reset to zero, but + attlen and the other fields copied from + pg_type are still valid. This arrangement is needed + to cope with the situation where the dropped column's data type was + later dropped, and so there is no pg_type row anymore. + attlen and the other fields can be used + to interpret the contents of a row of the table. + + + + + + <structname>pg_authid</structname> + + + pg_authid + + + + The catalog pg_authid contains information about + database authorization identifiers (roles). A role subsumes the concepts + of users and groups. A user is essentially just a + role with the rolcanlogin flag set. Any role (with or + without rolcanlogin) can have other roles as members; see + pg_auth_members. + + + + Since this catalog contains passwords, it must not be publicly readable. + pg_roles + is a publicly readable view on + pg_authid that blanks out the password field. + + + + contains detailed information about user and + privilege management. + + + + Because user identities are cluster-wide, + pg_authid + is shared across all databases of a cluster: there is only one + copy of pg_authid per cluster, not + one per database. + + + + <structname>pg_authid</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + rolname name + + + Role name + + + + + + rolsuper bool + + + Role has superuser privileges + + + + + + rolinherit bool + + + Role automatically inherits privileges of roles it is a + member of + + + + + + rolcreaterole bool + + + Role can create more roles + + + + + + rolcreatedb bool + + + Role can create databases + + + + + + rolcanlogin bool + + + Role can log in. That is, this role can be given as the initial + session authorization identifier. + + + + + + rolreplication bool + + + Role is a replication role. A replication role can initiate replication + connections and create and drop replication slots. + + + + + + rolbypassrls bool + + + Role bypasses every row level security policy, see + for more information. + + + + + + rolconnlimit int4 + + + For roles that can log in, this sets maximum number of concurrent + connections this role can make. -1 means no limit. + + + + + + rolpassword text + + + Password (possibly encrypted); null if none. The format depends + on the form of encryption used. + + + + + + rolvaliduntil timestamptz + + + Password expiry time (only used for password authentication); + null if no expiration + + + + +
+ + + For an MD5 encrypted password, rolpassword + column will begin with the string md5 followed by a + 32-character hexadecimal MD5 hash. The MD5 hash will be of the user's + password concatenated to their user name. For example, if user + joe has password xyzzy, PostgreSQL + will store the md5 hash of xyzzyjoe. + + + + If the password is encrypted with SCRAM-SHA-256, it has the format: + +SCRAM-SHA-256$<iteration count>:<salt>$<StoredKey>:<ServerKey> + + where salt, StoredKey and + ServerKey are in Base64 encoded format. This format is + the same as that specified by RFC 5803. + + + + A password that does not follow either of those formats is assumed to be + unencrypted. + + + + + + <structname>pg_auth_members</structname> + + + pg_auth_members + + + + The catalog pg_auth_members shows the membership + relations between roles. Any non-circular set of relationships is allowed. + + + + Because user identities are cluster-wide, + pg_auth_members + is shared across all databases of a cluster: there is only one + copy of pg_auth_members per cluster, not + one per database. + + + + <structname>pg_auth_members</structname> Columns + + + + + Column Type + + + Description + + + + + + + + roleid oid + (references pg_authid.oid) + + + ID of a role that has a member + + + + + + member oid + (references pg_authid.oid) + + + ID of a role that is a member of roleid + + + + + + grantor oid + (references pg_authid.oid) + + + ID of the role that granted this membership + + + + + + admin_option bool + + + True if member can grant membership in + roleid to others + + + + +
+ + + + + + <structname>pg_cast</structname> + + + pg_cast + + + + The catalog pg_cast stores data type conversion + paths, both built-in and user-defined. + + + + It should be noted that pg_cast does not represent + every type conversion that the system knows how to perform; only those that + cannot be deduced from some generic rule. For example, casting between a + domain and its base type is not explicitly represented in + pg_cast. Another important exception is that + automatic I/O conversion casts, those performed using a data + type's own I/O functions to convert to or from text or other + string types, are not explicitly represented in + pg_cast. + + + + <structname>pg_cast</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + castsource oid + (references pg_type.oid) + + + OID of the source data type + + + + + + casttarget oid + (references pg_type.oid) + + + OID of the target data type + + + + + + castfunc oid + (references pg_proc.oid) + + + The OID of the function to use to perform this cast. Zero is + stored if the cast method doesn't require a function. + + + + + + castcontext char + + + Indicates what contexts the cast can be invoked in. + e means only as an explicit cast (using + CAST or :: syntax). + a means implicitly in assignment + to a target column, as well as explicitly. + i means implicitly in expressions, as well as the + other cases. + + + + + + castmethod char + + + Indicates how the cast is performed. + f means that the function specified in the castfunc field is used. + i means that the input/output functions are used. + b means that the types are binary-coercible, thus no conversion is required. + + + + +
+ + + The cast functions listed in pg_cast must + always take the cast source type as their first argument type, and + return the cast destination type as their result type. A cast + function can have up to three arguments. The second argument, + if present, must be type integer; it receives the type + modifier associated with the destination type, or -1 + if there is none. The third argument, + if present, must be type boolean; it receives true + if the cast is an explicit cast, false otherwise. + + + + It is legitimate to create a pg_cast entry + in which the source and target types are the same, if the associated + function takes more than one argument. Such entries represent + length coercion functions that coerce values of the type + to be legal for a particular type modifier value. + + + + When a pg_cast entry has different source and + target types and a function that takes more than one argument, it + represents converting from one type to another and applying a length + coercion in a single step. When no such entry is available, coercion + to a type that uses a type modifier involves two steps, one to + convert between data types and a second to apply the modifier. + + + + + <structname>pg_class</structname> + + + pg_class + + + + The catalog pg_class catalogs tables and most + everything else that has columns or is otherwise similar to a + table. This includes indexes (but see also + pg_index), sequences (but see also + pg_sequence), views, materialized + views, composite types, and TOAST tables; see relkind. + Below, when we mean all of these + kinds of objects we speak of relations. Not all + columns are meaningful for all relation types. + + + + <structname>pg_class</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + relname name + + + Name of the table, index, view, etc. + + + + + + relnamespace oid + (references pg_namespace.oid) + + + The OID of the namespace that contains this relation + + + + + + reltype oid + (references pg_type.oid) + + + The OID of the data type that corresponds to this table's row type, + if any (zero for indexes, which have no pg_type entry) + + + + + + reloftype oid + (references pg_type.oid) + + + For typed tables, the OID of the underlying composite type, + zero for all other relations + + + + + + relowner oid + (references pg_authid.oid) + + + Owner of the relation + + + + + + relam oid + (references pg_am.oid) + + + If this is a table or an index, the access method used (heap, + B-tree, hash, etc.) + + + + + + relfilenode oid + + + Name of the on-disk file of this relation; zero means this + is a mapped relation whose disk file name is determined + by low-level state + + + + + + reltablespace oid + (references pg_tablespace.oid) + + + The tablespace in which this relation is stored. If zero, + the database's default tablespace is implied. (Not meaningful + if the relation has no on-disk file.) + + + + + + relpages int4 + + + Size of the on-disk representation of this table in pages (of size + BLCKSZ). This is only an estimate used by the + planner. It is updated by VACUUM, + ANALYZE, and a few DDL commands such as + CREATE INDEX. + + + + + + reltuples float4 + + + Number of live rows in the table. This is only an estimate used by + the planner. It is updated by VACUUM, + ANALYZE, and a few DDL commands such as + CREATE INDEX. + + + + + + relallvisible int4 + + + Number of pages that are marked all-visible in the table's + visibility map. This is only an estimate used by the + planner. It is updated by VACUUM, + ANALYZE, and a few DDL commands such as + CREATE INDEX. + + + + + + reltoastrelid oid + (references pg_class.oid) + + + OID of the TOAST table associated with this table, 0 if none. The + TOAST table stores large attributes out of line in a + secondary table. + + + + + + relhasindex bool + + + True if this is a table and it has (or recently had) any indexes + + + + + + relisshared bool + + + True if this table is shared across all databases in the cluster. Only + certain system catalogs (such as pg_database) + are shared. + + + + + + relpersistence char + + + p = permanent table, u = unlogged table, + t = temporary table + + + + + + relkind char + + + r = ordinary table, + i = index, + S = sequence, + t = TOAST table, + v = view, + m = materialized view, + c = composite type, + f = foreign table, + p = partitioned table, + I = partitioned index + + + + + + relnatts int2 + + + Number of user columns in the relation (system columns not + counted). There must be this many corresponding entries in + pg_attribute. See also + pg_attribute.attnum. + + + + + + relchecks int2 + + + Number of CHECK constraints on the table; see + pg_constraint catalog + + + + + + relhasrules bool + + + True if table has (or once had) rules; see + pg_rewrite catalog + + + + + + relhastriggers bool + + + True if table has (or once had) triggers; see + pg_trigger catalog + + + + + + relhassubclass bool + + + True if table or index has (or once had) any inheritance children + + + + + + relrowsecurity bool + + + True if table has row level security enabled; see + pg_policy catalog + + + + + + relforcerowsecurity bool + + + True if row level security (when enabled) will also apply to table owner; see + pg_policy catalog + + + + + + relispopulated bool + + + True if relation is populated (this is true for all + relations other than some materialized views) + + + + + + relreplident char + + + Columns used to form replica identity for rows: + d = default (primary key, if any), + n = nothing, + f = all columns, + i = index with + indisreplident set (same as nothing if the + index used has been dropped) + + + + + + relispartition bool + + + True if table or index is a partition + + + + + + relrewrite oid + (references pg_class.oid) + + + For new relations being written during a DDL operation that requires a + table rewrite, this contains the OID of the original relation; + otherwise 0. That state is only visible internally; this field should + never contain anything other than 0 for a user-visible relation. + + + + + + relfrozenxid xid + + + All transaction IDs before this one have been replaced with a permanent + (frozen) transaction ID in this table. This is used to track + whether the table needs to be vacuumed in order to prevent transaction + ID wraparound or to allow pg_xact to be shrunk. Zero + (InvalidTransactionId) if the relation is not a table. + + + + + + relminmxid xid + + + All multixact IDs before this one have been replaced by a + transaction ID in this table. This is used to track + whether the table needs to be vacuumed in order to prevent multixact ID + wraparound or to allow pg_multixact to be shrunk. Zero + (InvalidMultiXactId) if the relation is not a table. + + + + + + relacl aclitem[] + + + Access privileges; see for details + + + + + + reloptions text[] + + + Access-method-specific options, as keyword=value strings + + + + + + relpartbound pg_node_tree + + + If table is a partition (see relispartition), + internal representation of the partition bound + + + + +
+ + + Several of the Boolean flags in pg_class are maintained + lazily: they are guaranteed to be true if that's the correct state, but + may not be reset to false immediately when the condition is no longer + true. For example, relhasindex is set by + CREATE INDEX, but it is never cleared by + DROP INDEX. Instead, VACUUM clears + relhasindex if it finds the table has no indexes. This + arrangement avoids race conditions and improves concurrency. + + + + + <structname>pg_collation</structname> + + + pg_collation + + + + The catalog pg_collation describes the + available collations, which are essentially mappings from an SQL + name to operating system locale categories. + See for more information. + + + + <structname>pg_collation</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + collname name + + + Collation name (unique per namespace and encoding) + + + + + + collnamespace oid + (references pg_namespace.oid) + + + The OID of the namespace that contains this collation + + + + + + collowner oid + (references pg_authid.oid) + + + Owner of the collation + + + + + + collprovider char + + + Provider of the collation: d = database + default, c = libc, i = icu + + + + + + collisdeterministic bool + + + Is the collation deterministic? + + + + + + collencoding int4 + + + Encoding in which the collation is applicable, or -1 if it + works for any encoding + + + + + + collcollate name + + + LC_COLLATE for this collation object + + + + + + collctype name + + + LC_CTYPE for this collation object + + + + + + collversion text + + + Provider-specific version of the collation. This is recorded when the + collation is created and then checked when it is used, to detect + changes in the collation definition that could lead to data corruption. + + + + +
+ + + Note that the unique key on this catalog is (collname, + collencoding, collnamespace) not just + (collname, collnamespace). + PostgreSQL generally ignores all + collations that do not have collencoding equal to + either the current database's encoding or -1, and creation of new entries + with the same name as an entry with collencoding = -1 + is forbidden. Therefore it is sufficient to use a qualified SQL name + (schema.name) to identify a collation, + even though this is not unique according to the catalog definition. + The reason for defining the catalog this way is that + initdb fills it in at cluster initialization time with + entries for all locales available on the system, so it must be able to + hold entries for all encodings that might ever be used in the cluster. + + + + In the template0 database, it could be useful to create + collations whose encoding does not match the database encoding, + since they could match the encodings of databases later cloned from + template0. This would currently have to be done manually. + + + + + <structname>pg_constraint</structname> + + + pg_constraint + + + + The catalog pg_constraint stores check, primary + key, unique, foreign key, and exclusion constraints on tables. + (Column constraints are not treated specially. Every column constraint is + equivalent to some table constraint.) + Not-null constraints are represented in the pg_attribute + catalog, not here. + + + + User-defined constraint triggers (created with CREATE CONSTRAINT + TRIGGER) also give rise to an entry in this table. + + + + Check constraints on domains are stored here, too. + + + + <structname>pg_constraint</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + conname name + + + Constraint name (not necessarily unique!) + + + + + + connamespace oid + (references pg_namespace.oid) + + + The OID of the namespace that contains this constraint + + + + + + contype char + + + c = check constraint, + f = foreign key constraint, + p = primary key constraint, + u = unique constraint, + t = constraint trigger, + x = exclusion constraint + + + + + + condeferrable bool + + + Is the constraint deferrable? + + + + + + condeferred bool + + + Is the constraint deferred by default? + + + + + + convalidated bool + + + Has the constraint been validated? + Currently, can only be false for foreign keys and CHECK constraints + + + + + + conrelid oid + (references pg_class.oid) + + + The table this constraint is on; 0 if not a table constraint + + + + + + contypid oid + (references pg_type.oid) + + + The domain this constraint is on; 0 if not a domain constraint + + + + + + conindid oid + (references pg_class.oid) + + + The index supporting this constraint, if it's a unique, primary + key, foreign key, or exclusion constraint; else 0 + + + + + + conparentid oid + (references pg_constraint.oid) + + + The corresponding constraint in the parent partitioned table, + if this is a constraint in a partition; else 0 + + + + + + confrelid oid + (references pg_class.oid) + + + If a foreign key, the referenced table; else 0 + + + + + + confupdtype char + + + Foreign key update action code: + a = no action, + r = restrict, + c = cascade, + n = set null, + d = set default + + + + + + confdeltype char + + + Foreign key deletion action code: + a = no action, + r = restrict, + c = cascade, + n = set null, + d = set default + + + + + + confmatchtype char + + + Foreign key match type: + f = full, + p = partial, + s = simple + + + + + + conislocal bool + + + This constraint is defined locally for the relation. Note that a + constraint can be locally defined and inherited simultaneously. + + + + + + coninhcount int4 + + + The number of direct inheritance ancestors this constraint has. + A constraint with + a nonzero number of ancestors cannot be dropped nor renamed. + + + + + + connoinherit bool + + + This constraint is defined locally for the relation. It is a + non-inheritable constraint. + + + + + + conkey int2[] + (references pg_attribute.attnum) + + + If a table constraint (including foreign keys, but not constraint + triggers), list of the constrained columns + + + + + + confkey int2[] + (references pg_attribute.attnum) + + + If a foreign key, list of the referenced columns + + + + + + conpfeqop oid[] + (references pg_operator.oid) + + + If a foreign key, list of the equality operators for PK = FK comparisons + + + + + + conppeqop oid[] + (references pg_operator.oid) + + + If a foreign key, list of the equality operators for PK = PK comparisons + + + + + + conffeqop oid[] + (references pg_operator.oid) + + + If a foreign key, list of the equality operators for FK = FK comparisons + + + + + + conexclop oid[] + (references pg_operator.oid) + + + If an exclusion constraint, list of the per-column exclusion operators + + + + + + conbin pg_node_tree + + + If a check constraint, an internal representation of the + expression. (It's recommended to use + pg_get_constraintdef() to extract the definition of + a check constraint.) + + + + +
+ + + In the case of an exclusion constraint, conkey + is only useful for constraint elements that are simple column references. + For other cases, a zero appears in conkey + and the associated index must be consulted to discover the expression + that is constrained. (conkey thus has the + same contents as pg_index.indkey for the + index.) + + + + + pg_class.relchecks needs to agree with the + number of check-constraint entries found in this table for each + relation. + + + + + + + <structname>pg_conversion</structname> + + + pg_conversion + + + + The catalog pg_conversion describes + encoding conversion functions. See + for more information. + + + + <structname>pg_conversion</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + conname name + + + Conversion name (unique within a namespace) + + + + + + connamespace oid + (references pg_namespace.oid) + + + The OID of the namespace that contains this conversion + + + + + + conowner oid + (references pg_authid.oid) + + + Owner of the conversion + + + + + + conforencoding int4 + + + Source encoding ID + + + + + + contoencoding int4 + + + Destination encoding ID + + + + + + conproc regproc + (references pg_proc.oid) + + + Conversion function + + + + + + condefault bool + + + True if this is the default conversion + + + + +
+ + + + + <structname>pg_database</structname> + + + pg_database + + + + The catalog pg_database stores information about + the available databases. Databases are created with the command. + Consult for details about the meaning + of some of the parameters. + + + + Unlike most system catalogs, pg_database + is shared across all databases of a cluster: there is only one + copy of pg_database per cluster, not + one per database. + + + + <structname>pg_database</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + datname name + + + Database name + + + + + + datdba oid + (references pg_authid.oid) + + + Owner of the database, usually the user who created it + + + + + + encoding int4 + + + Character encoding for this database + (pg_encoding_to_char() can translate + this number to the encoding name) + + + + + + datcollate name + + + LC_COLLATE for this database + + + + + + datctype name + + + LC_CTYPE for this database + + + + + + datistemplate bool + + + If true, then this database can be cloned by + any user with CREATEDB privileges; + if false, then only superusers or the owner of + the database can clone it. + + + + + + datallowconn bool + + + If false then no one can connect to this database. This is + used to protect the template0 database from being altered. + + + + + + datconnlimit int4 + + + Sets maximum number of concurrent connections that can be made + to this database. -1 means no limit. + + + + + + datlastsysoid oid + + + Last system OID in the database; useful + particularly to pg_dump + + + + + + datfrozenxid xid + + + All transaction IDs before this one have been replaced with a permanent + (frozen) transaction ID in this database. This is used to + track whether the database needs to be vacuumed in order to prevent + transaction ID wraparound or to allow pg_xact to be shrunk. + It is the minimum of the per-table + pg_class.relfrozenxid values. + + + + + + datminmxid xid + + + All multixact IDs before this one have been replaced with a + transaction ID in this database. This is used to + track whether the database needs to be vacuumed in order to prevent + multixact ID wraparound or to allow pg_multixact to be shrunk. + It is the minimum of the per-table + pg_class.relminmxid values. + + + + + + dattablespace oid + (references pg_tablespace.oid) + + + The default tablespace for the database. + Within this database, all tables for which + pg_class.reltablespace is zero + will be stored in this tablespace; in particular, all the non-shared + system catalogs will be there. + + + + + + datacl aclitem[] + + + Access privileges; see for details + + + + +
+ + + + + <structname>pg_db_role_setting</structname> + + + pg_db_role_setting + + + + The catalog pg_db_role_setting records the default + values that have been set for run-time configuration variables, + for each role and database combination. + + + + Unlike most system catalogs, pg_db_role_setting + is shared across all databases of a cluster: there is only one + copy of pg_db_role_setting per cluster, not + one per database. + + + + <structname>pg_db_role_setting</structname> Columns + + + + + Column Type + + + Description + + + + + + + + setdatabase oid + (references pg_database.oid) + + + The OID of the database the setting is applicable to, or zero if not database-specific + + + + + + setrole oid + (references pg_authid.oid) + + + The OID of the role the setting is applicable to, or zero if not role-specific + + + + + + setconfig text[] + + + Defaults for run-time configuration variables + + + + +
+ + + + + <structname>pg_default_acl</structname> + + + pg_default_acl + + + + The catalog pg_default_acl stores initial + privileges to be assigned to newly created objects. + + + + <structname>pg_default_acl</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + defaclrole oid + (references pg_authid.oid) + + + The OID of the role associated with this entry + + + + + + defaclnamespace oid + (references pg_namespace.oid) + + + The OID of the namespace associated with this entry, + or 0 if none + + + + + + defaclobjtype char + + + Type of object this entry is for: + r = relation (table, view), + S = sequence, + f = function, + T = type, + n = schema + + + + + + defaclacl aclitem[] + + + Access privileges that this type of object should have on creation + + + + +
+ + + A pg_default_acl entry shows the initial privileges to + be assigned to an object belonging to the indicated user. There are + currently two types of entry: global entries with + defaclnamespace = 0, and per-schema entries + that reference a particular schema. If a global entry is present then + it overrides the normal hard-wired default privileges + for the object type. A per-schema entry, if present, represents privileges + to be added to the global or hard-wired default privileges. + + + + Note that when an ACL entry in another catalog is null, it is taken + to represent the hard-wired default privileges for its object, + not whatever might be in pg_default_acl + at the moment. pg_default_acl is only consulted during + object creation. + + + + + + + <structname>pg_depend</structname> + + + pg_depend + + + + The catalog pg_depend records the dependency + relationships between database objects. This information allows + DROP commands to find which other objects must be dropped + by DROP CASCADE or prevent dropping in the DROP + RESTRICT case. + + + + See also pg_shdepend, + which performs a similar function for dependencies involving objects + that are shared across a database cluster. + + + + <structname>pg_depend</structname> Columns + + + + + Column Type + + + Description + + + + + + + + classid oid + (references pg_class.oid) + + + The OID of the system catalog the dependent object is in + + + + + + objid oid + (references any OID column) + + + The OID of the specific dependent object + + + + + + objsubid int4 + + + For a table column, this is the column number (the + objid and classid refer to the + table itself). For all other object types, this column is + zero. + + + + + + refclassid oid + (references pg_class.oid) + + + The OID of the system catalog the referenced object is in + + + + + + refobjid oid + (references any OID column) + + + The OID of the specific referenced object + + + + + + refobjsubid int4 + + + For a table column, this is the column number (the + refobjid and refclassid refer + to the table itself). For all other object types, this column + is zero. + + + + + + deptype char + + + A code defining the specific semantics of this dependency relationship; see text + + + + +
+ + + In all cases, a pg_depend entry indicates that the + referenced object cannot be dropped without also dropping the dependent + object. However, there are several subflavors identified by + deptype: + + + + DEPENDENCY_NORMAL (n) + + + A normal relationship between separately-created objects. The + dependent object can be dropped without affecting the + referenced object. The referenced object can only be dropped + by specifying CASCADE, in which case the dependent + object is dropped, too. Example: a table column has a normal + dependency on its data type. + + + + + + DEPENDENCY_AUTO (a) + + + The dependent object can be dropped separately from the + referenced object, and should be automatically dropped + (regardless of RESTRICT or CASCADE + mode) if the referenced object is dropped. Example: a named + constraint on a table is made auto-dependent on the table, so + that it will go away if the table is dropped. + + + + + + DEPENDENCY_INTERNAL (i) + + + The dependent object was created as part of creation of the + referenced object, and is really just a part of its internal + implementation. A direct DROP of the dependent + object will be disallowed outright (we'll tell the user to issue + a DROP against the referenced object, instead). + A DROP of the referenced object will result in + automatically dropping the dependent object + whether CASCADE is specified or not. If the + dependent object has to be dropped due to a dependency on some other + object being removed, its drop is converted to a drop of the referenced + object, so that NORMAL and AUTO + dependencies of the dependent object behave much like they were + dependencies of the referenced object. + Example: a view's ON SELECT rule is made + internally dependent on the view, preventing it from being dropped + while the view remains. Dependencies of the rule (such as tables it + refers to) act as if they were dependencies of the view. + + + + + + DEPENDENCY_PARTITION_PRI (P) + DEPENDENCY_PARTITION_SEC (S) + + + The dependent object was created as part of creation of the + referenced object, and is really just a part of its internal + implementation; however, unlike INTERNAL, + there is more than one such referenced object. The dependent object + must not be dropped unless at least one of these referenced objects + is dropped; if any one is, the dependent object should be dropped + whether or not CASCADE is specified. Also + unlike INTERNAL, a drop of some other object + that the dependent object depends on does not result in automatic + deletion of any partition-referenced object. Hence, if the drop + does not cascade to at least one of these objects via some other + path, it will be refused. (In most cases, the dependent object + shares all its non-partition dependencies with at least one + partition-referenced object, so that this restriction does not + result in blocking any cascaded delete.) + Primary and secondary partition dependencies behave identically + except that the primary dependency is preferred for use in error + messages; hence, a partition-dependent object should have one + primary partition dependency and one or more secondary partition + dependencies. + Note that partition dependencies are made in addition to, not + instead of, any dependencies the object would normally have. This + simplifies ATTACH/DETACH PARTITION operations: + the partition dependencies need only be added or removed. + Example: a child partitioned index is made partition-dependent + on both the partition table it is on and the parent partitioned + index, so that it goes away if either of those is dropped, but + not otherwise. The dependency on the parent index is primary, + so that if the user tries to drop the child partitioned index, + the error message will suggest dropping the parent index instead + (not the table). + + + + + + DEPENDENCY_EXTENSION (e) + + + The dependent object is a member of the extension that is + the referenced object (see + pg_extension). + The dependent object can be dropped only via + DROP EXTENSION on the referenced object. + Functionally this dependency type acts the same as + an INTERNAL dependency, but it's kept separate for + clarity and to simplify pg_dump. + + + + + + DEPENDENCY_AUTO_EXTENSION (x) + + + The dependent object is not a member of the extension that is the + referenced object (and so it should not be ignored + by pg_dump), but it cannot function + without the extension and should be auto-dropped if the extension is. + The dependent object may be dropped on its own as well. + Functionally this dependency type acts the same as + an AUTO dependency, but it's kept separate for + clarity and to simplify pg_dump. + + + + + + DEPENDENCY_PIN (p) + + + There is no dependent object; this type of entry is a signal + that the system itself depends on the referenced object, and so + that object must never be deleted. Entries of this type are + created only by initdb. The columns for the + dependent object contain zeroes. + + + + + + Other dependency flavors might be needed in future. + + + + Note that it's quite possible for two objects to be linked by more than + one pg_depend entry. For example, a child + partitioned index would have both a partition-type dependency on its + associated partition table, and an auto dependency on each column of + that table that it indexes. This sort of situation expresses the union + of multiple dependency semantics. A dependent object can be dropped + without CASCADE if any of its dependencies satisfies + its condition for automatic dropping. Conversely, all the + dependencies' restrictions about which objects must be dropped together + must be satisfied. + + + + + + + <structname>pg_description</structname> + + + pg_description + + + + The catalog pg_description stores optional descriptions + (comments) for each database object. Descriptions can be manipulated + with the command and viewed with + psql's \d commands. + Descriptions of many built-in system objects are provided in the initial + contents of pg_description. + + + + See also pg_shdescription, + which performs a similar function for descriptions involving objects that + are shared across a database cluster. + + + + <structname>pg_description</structname> Columns + + + + + Column Type + + + Description + + + + + + + + objoid oid + (references any OID column) + + + The OID of the object this description pertains to + + + + + + classoid oid + (references pg_class.oid) + + + The OID of the system catalog this object appears in + + + + + + objsubid int4 + + + For a comment on a table column, this is the column number (the + objoid and classoid refer to + the table itself). For all other object types, this column is + zero. + + + + + + description text + + + Arbitrary text that serves as the description of this object + + + + +
+ + + + + + <structname>pg_enum</structname> + + + pg_enum + + + + The pg_enum catalog contains entries + showing the values and labels for each enum type. The + internal representation of a given enum value is actually the OID + of its associated row in pg_enum. + + + + <structname>pg_enum</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + enumtypid oid + (references pg_type.oid) + + + The OID of the pg_type entry owning this enum value + + + + + + enumsortorder float4 + + + The sort position of this enum value within its enum type + + + + + + enumlabel name + + + The textual label for this enum value + + + + +
+ + + The OIDs for pg_enum rows follow a special + rule: even-numbered OIDs are guaranteed to be ordered in the same way + as the sort ordering of their enum type. That is, if two even OIDs + belong to the same enum type, the smaller OID must have the smaller + enumsortorder value. Odd-numbered OID values + need bear no relationship to the sort order. This rule allows the + enum comparison routines to avoid catalog lookups in many common cases. + The routines that create and alter enum types attempt to assign even + OIDs to enum values whenever possible. + + + + When an enum type is created, its members are assigned sort-order + positions 1..n. But members added later might be given + negative or fractional values of enumsortorder. + The only requirement on these values is that they be correctly + ordered and unique within each enum type. + + + + + + <structname>pg_event_trigger</structname> + + + pg_event_trigger + + + + The catalog pg_event_trigger stores event triggers. + See for more information. + + + + <structname>pg_event_trigger</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + evtname name + + + Trigger name (must be unique) + + + + + + evtevent name + + + Identifies the event for which this trigger fires + + + + + + evtowner oid + (references pg_authid.oid) + + + Owner of the event trigger + + + + + + evtfoid oid + (references pg_proc.oid) + + + The function to be called + + + + + + evtenabled char + + + Controls in which modes + the event trigger fires. + O = trigger fires in origin and local modes, + D = trigger is disabled, + R = trigger fires in replica mode, + A = trigger fires always. + + + + + + evttags text[] + + + Command tags for which this trigger will fire. If NULL, the firing + of this trigger is not restricted on the basis of the command tag. + + + + +
+ + + + + <structname>pg_extension</structname> + + + pg_extension + + + + The catalog pg_extension stores information + about the installed extensions. See + for details about extensions. + + + + <structname>pg_extension</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + extname name + + + Name of the extension + + + + + + extowner oid + (references pg_authid.oid) + + + Owner of the extension + + + + + + extnamespace oid + (references pg_namespace.oid) + + + Schema containing the extension's exported objects + + + + + + extrelocatable bool + + + True if extension can be relocated to another schema + + + + + + extversion text + + + Version name for the extension + + + + + + extconfig oid[] + (references pg_class.oid) + + + Array of regclass OIDs for the extension's configuration + table(s), or NULL if none + + + + + + extcondition text[] + + + Array of WHERE-clause filter conditions for the + extension's configuration table(s), or NULL if none + + + + +
+ + + Note that unlike most catalogs with a namespace column, + extnamespace is not meant to imply + that the extension belongs to that schema. Extension names are never + schema-qualified. Rather, extnamespace + indicates the schema that contains most or all of the extension's + objects. If extrelocatable is true, then + this schema must in fact contain all schema-qualifiable objects + belonging to the extension. + + + + + + <structname>pg_foreign_data_wrapper</structname> + + + pg_foreign_data_wrapper + + + + The catalog pg_foreign_data_wrapper stores + foreign-data wrapper definitions. A foreign-data wrapper is the + mechanism by which external data, residing on foreign servers, is + accessed. + + + + <structname>pg_foreign_data_wrapper</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + fdwname name + + + Name of the foreign-data wrapper + + + + + + fdwowner oid + (references pg_authid.oid) + + + Owner of the foreign-data wrapper + + + + + + fdwhandler oid + (references pg_proc.oid) + + + References a handler function that is responsible for + supplying execution routines for the foreign-data wrapper. + Zero if no handler is provided + + + + + + fdwvalidator oid + (references pg_proc.oid) + + + References a validator function that is responsible for + checking the validity of the options given to the + foreign-data wrapper, as well as options for foreign servers and user + mappings using the foreign-data wrapper. Zero if no validator + is provided + + + + + + fdwacl aclitem[] + + + Access privileges; see for details + + + + + + fdwoptions text[] + + + Foreign-data wrapper specific options, as keyword=value strings + + + + +
+ + + + + <structname>pg_foreign_server</structname> + + + pg_foreign_server + + + + The catalog pg_foreign_server stores + foreign server definitions. A foreign server describes a source + of external data, such as a remote server. Foreign + servers are accessed via foreign-data wrappers. + + + + <structname>pg_foreign_server</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + srvname name + + + Name of the foreign server + + + + + + srvowner oid + (references pg_authid.oid) + + + Owner of the foreign server + + + + + + srvfdw oid + (references pg_foreign_data_wrapper.oid) + + + OID of the foreign-data wrapper of this foreign server + + + + + + srvtype text + + + Type of the server (optional) + + + + + + srvversion text + + + Version of the server (optional) + + + + + + srvacl aclitem[] + + + Access privileges; see for details + + + + + + srvoptions text[] + + + Foreign server specific options, as keyword=value strings + + + + +
+ + + + + <structname>pg_foreign_table</structname> + + + pg_foreign_table + + + + The catalog pg_foreign_table contains + auxiliary information about foreign tables. A foreign table is + primarily represented by a pg_class entry, + just like a regular table. Its pg_foreign_table + entry contains the information that is pertinent only to foreign tables + and not any other kind of relation. + + + + <structname>pg_foreign_table</structname> Columns + + + + + Column Type + + + Description + + + + + + + + ftrelid oid + (references pg_class.oid) + + + OID of the pg_class entry for this foreign table + + + + + + ftserver oid + (references pg_foreign_server.oid) + + + OID of the foreign server for this foreign table + + + + + + ftoptions text[] + + + Foreign table options, as keyword=value strings + + + + +
+ + + + + <structname>pg_index</structname> + + + pg_index + + + + The catalog pg_index contains part of the information + about indexes. The rest is mostly in + pg_class. + + + + <structname>pg_index</structname> Columns + + + + + Column Type + + + Description + + + + + + + + indexrelid oid + (references pg_class.oid) + + + The OID of the pg_class entry for this index + + + + + + indrelid oid + (references pg_class.oid) + + + The OID of the pg_class entry for the table this index is for + + + + + + indnatts int2 + + + The total number of columns in the index (duplicates + pg_class.relnatts); this number includes both key and included attributes + + + + + + indnkeyatts int2 + + + The number of key columns in the index, + not counting any included columns, which are + merely stored and do not participate in the index semantics + + + + + + indisunique bool + + + If true, this is a unique index + + + + + + indisprimary bool + + + If true, this index represents the primary key of the table + (indisunique should always be true when this is true) + + + + + + indisexclusion bool + + + If true, this index supports an exclusion constraint + + + + + + indimmediate bool + + + If true, the uniqueness check is enforced immediately on + insertion + (irrelevant if indisunique is not true) + + + + + + indisclustered bool + + + If true, the table was last clustered on this index + + + + + + indisvalid bool + + + If true, the index is currently valid for queries. False means the + index is possibly incomplete: it must still be modified by + INSERT/UPDATE operations, but it cannot safely + be used for queries. If it is unique, the uniqueness property is not + guaranteed true either. + + + + + + indcheckxmin bool + + + If true, queries must not use the index until the xmin + of this pg_index row is below their TransactionXmin + event horizon, because the table may contain broken HOT chains with + incompatible rows that they can see + + + + + + indisready bool + + + If true, the index is currently ready for inserts. False means the + index must be ignored by INSERT/UPDATE + operations. + + + + + + indislive bool + + + If false, the index is in process of being dropped, and should be + ignored for all purposes (including HOT-safety decisions) + + + + + + indisreplident bool + + + If true this index has been chosen as replica identity + using ALTER TABLE ... REPLICA IDENTITY USING INDEX + ... + + + + + + indkey int2vector + (references pg_attribute.attnum) + + + This is an array of indnatts values that + indicate which table columns this index indexes. For example a value + of 1 3 would mean that the first and the third table + columns make up the index entries. Key columns come before non-key + (included) columns. A zero in this array indicates that the + corresponding index attribute is an expression over the table columns, + rather than a simple column reference. + + + + + + indcollation oidvector + (references pg_collation.oid) + + + For each column in the index key + (indnkeyatts values), this contains the OID + of the collation to use for the index, or zero if the column is not of + a collatable data type. + + + + + + indclass oidvector + (references pg_opclass.oid) + + + For each column in the index key + (indnkeyatts values), this contains the OID + of the operator class to use. See + pg_opclass for details. + + + + + + indoption int2vector + + + This is an array of indnkeyatts values that + store per-column flag bits. The meaning of the bits is defined by + the index's access method. + + + + + + indexprs pg_node_tree + + + Expression trees (in nodeToString() + representation) for index attributes that are not simple column + references. This is a list with one element for each zero + entry in indkey. Null if all index attributes + are simple references. + + + + + + indpred pg_node_tree + + + Expression tree (in nodeToString() + representation) for partial index predicate. Null if not a + partial index. + + + + +
+ + + + + + <structname>pg_inherits</structname> + + + pg_inherits + + + + The catalog pg_inherits records information about + table and index inheritance hierarchies. There is one entry for each direct + parent-child table or index relationship in the database. (Indirect + inheritance can be determined by following chains of entries.) + + + + <structname>pg_inherits</structname> Columns + + + + + Column Type + + + Description + + + + + + + + inhrelid oid + (references pg_class.oid) + + + The OID of the child table or index + + + + + + inhparent oid + (references pg_class.oid) + + + The OID of the parent table or index + + + + + + inhseqno int4 + + + If there is more than one direct parent for a child table (multiple + inheritance), this number tells the order in which the + inherited columns are to be arranged. The count starts at 1. + + + Indexes cannot have multiple inheritance, since they can only inherit + when using declarative partitioning. + + + + +
+ + + + + <structname>pg_init_privs</structname> + + + pg_init_privs + + + + The catalog pg_init_privs records information about + the initial privileges of objects in the system. There is one entry + for each object in the database which has a non-default (non-NULL) + initial set of privileges. + + + + Objects can have initial privileges either by having those privileges set + when the system is initialized (by initdb) or when the + object is created during a CREATE EXTENSION and the + extension script sets initial privileges using the GRANT + system. Note that the system will automatically handle recording of the + privileges during the extension script and that extension authors need + only use the GRANT and REVOKE + statements in their script to have the privileges recorded. The + privtype column indicates if the initial privilege was + set by initdb or during a + CREATE EXTENSION command. + + + + Objects which have initial privileges set by initdb will + have entries where privtype is + 'i', while objects which have initial privileges set + by CREATE EXTENSION will have entries where + privtype is 'e'. + + + + <structname>pg_init_privs</structname> Columns + + + + + Column Type + + + Description + + + + + + + + objoid oid + (references any OID column) + + + The OID of the specific object + + + + + + classoid oid + (references pg_class.oid) + + + The OID of the system catalog the object is in + + + + + + objsubid int4 + + + For a table column, this is the column number (the + objoid and classoid refer to the + table itself). For all other object types, this column is + zero. + + + + + + privtype char + + + A code defining the type of initial privilege of this object; see text + + + + + + initprivs aclitem[] + + + The initial access privileges; see + for details + + + + +
+ + + + + + <structname>pg_language</structname> + + + pg_language + + + + The catalog pg_language registers + languages in which you can write functions or stored procedures. + See + and for more information about language handlers. + + + + <structname>pg_language</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + lanname name + + + Name of the language + + + + + + lanowner oid + (references pg_authid.oid) + + + Owner of the language + + + + + + lanispl bool + + + This is false for internal languages (such as + SQL) and true for user-defined languages. + Currently, pg_dump still uses this + to determine which languages need to be dumped, but this might be + replaced by a different mechanism in the future. + + + + + + lanpltrusted bool + + + True if this is a trusted language, which means that it is believed + not to grant access to anything outside the normal SQL execution + environment. Only superusers can create functions in untrusted + languages. + + + + + + lanplcallfoid oid + (references pg_proc.oid) + + + For noninternal languages this references the language + handler, which is a special function that is responsible for + executing all functions that are written in the particular + language + + + + + + laninline oid + (references pg_proc.oid) + + + This references a function that is responsible for executing + inline anonymous code blocks + ( blocks). + Zero if inline blocks are not supported. + + + + + + lanvalidator oid + (references pg_proc.oid) + + + This references a language validator function that is responsible + for checking the syntax and validity of new functions when they + are created. Zero if no validator is provided. + + + + + + lanacl aclitem[] + + + Access privileges; see for details + + + + +
+ + + + + + <structname>pg_largeobject</structname> + + + pg_largeobject + + + + The catalog pg_largeobject holds the data making up + large objects. A large object is identified by an OID + assigned when it is created. Each large object is broken into + segments or pages small enough to be conveniently stored as rows + in pg_largeobject. + The amount of data per page is defined to be LOBLKSIZE (which is currently + BLCKSZ/4, or typically 2 kB). + + + + Prior to PostgreSQL 9.0, there was no permission structure + associated with large objects. As a result, + pg_largeobject was publicly readable and could be + used to obtain the OIDs (and contents) of all large objects in the system. + This is no longer the case; use + pg_largeobject_metadata + to obtain a list of large object OIDs. + + + + <structname>pg_largeobject</structname> Columns + + + + + Column Type + + + Description + + + + + + + + loid oid + (references pg_largeobject_metadata.oid) + + + Identifier of the large object that includes this page + + + + + + pageno int4 + + + Page number of this page within its large object + (counting from zero) + + + + + + data bytea + + + Actual data stored in the large object. + This will never be more than LOBLKSIZE bytes and might be less. + + + + +
+ + + Each row of pg_largeobject holds data + for one page of a large object, beginning at + byte offset (pageno * LOBLKSIZE) within the object. The implementation + allows sparse storage: pages might be missing, and might be shorter than + LOBLKSIZE bytes even if they are not the last page of the object. + Missing regions within a large object read as zeroes. + + + + + + <structname>pg_largeobject_metadata</structname> + + + pg_largeobject_metadata + + + + The catalog pg_largeobject_metadata + holds metadata associated with large objects. The actual large object + data is stored in + pg_largeobject. + + + + <structname>pg_largeobject_metadata</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + lomowner oid + (references pg_authid.oid) + + + Owner of the large object + + + + + + lomacl aclitem[] + + + Access privileges; see for details + + + + +
+ + + + + <structname>pg_namespace</structname> + + + pg_namespace + + + + The catalog pg_namespace stores namespaces. + A namespace is the structure underlying SQL schemas: each namespace + can have a separate collection of relations, types, etc. without name + conflicts. + + + + <structname>pg_namespace</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + nspname name + + + Name of the namespace + + + + + + nspowner oid + (references pg_authid.oid) + + + Owner of the namespace + + + + + + nspacl aclitem[] + + + Access privileges; see for details + + + + +
+ + + + + + <structname>pg_opclass</structname> + + + pg_opclass + + + + The catalog pg_opclass defines + index access method operator classes. Each operator class defines + semantics for index columns of a particular data type and a particular + index access method. An operator class essentially specifies that a + particular operator family is applicable to a particular indexable column + data type. The set of operators from the family that are actually usable + with the indexed column are whichever ones accept the column's data type + as their left-hand input. + + + + Operator classes are described at length in . + + + + <structname>pg_opclass</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + opcmethod oid + (references pg_am.oid) + + + Index access method operator class is for + + + + + + opcname name + + + Name of this operator class + + + + + + opcnamespace oid + (references pg_namespace.oid) + + + Namespace of this operator class + + + + + + opcowner oid + (references pg_authid.oid) + + + Owner of the operator class + + + + + + opcfamily oid + (references pg_opfamily.oid) + + + Operator family containing the operator class + + + + + + opcintype oid + (references pg_type.oid) + + + Data type that the operator class indexes + + + + + + opcdefault bool + + + True if this operator class is the default for opcintype + + + + + + opckeytype oid + (references pg_type.oid) + + + Type of data stored in index, or zero if same as opcintype + + + + +
+ + + An operator class's opcmethod must match the + opfmethod of its containing operator family. + Also, there must be no more than one pg_opclass + row having opcdefault true for any given combination of + opcmethod and opcintype. + + + + + + + <structname>pg_operator</structname> + + + pg_operator + + + + The catalog pg_operator stores information about operators. + See + and for more information. + + + + <structname>pg_operator</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + oprname name + + + Name of the operator + + + + + + oprnamespace oid + (references pg_namespace.oid) + + + The OID of the namespace that contains this operator + + + + + + oprowner oid + (references pg_authid.oid) + + + Owner of the operator + + + + + + oprkind char + + + b = infix (both), l = prefix + (left), r = postfix (right) + + + + + + oprcanmerge bool + + + This operator supports merge joins + + + + + + oprcanhash bool + + + This operator supports hash joins + + + + + + oprleft oid + (references pg_type.oid) + + + Type of the left operand + + + + + + oprright oid + (references pg_type.oid) + + + Type of the right operand + + + + + + oprresult oid + (references pg_type.oid) + + + Type of the result + + + + + + oprcom oid + (references pg_operator.oid) + + + Commutator of this operator, if any + + + + + + oprnegate oid + (references pg_operator.oid) + + + Negator of this operator, if any + + + + + + oprcode regproc + (references pg_proc.oid) + + + Function that implements this operator + + + + + + oprrest regproc + (references pg_proc.oid) + + + Restriction selectivity estimation function for this operator + + + + + + oprjoin regproc + (references pg_proc.oid) + + + Join selectivity estimation function for this operator + + + + +
+ + + Unused column contain zeroes. For example, oprleft + is zero for a prefix operator. + + + + + + + <structname>pg_opfamily</structname> + + + pg_opfamily + + + + The catalog pg_opfamily defines operator families. + Each operator family is a collection of operators and associated + support routines that implement the semantics specified for a particular + index access method. Furthermore, the operators in a family are all + compatible, in a way that is specified by the access method. + The operator family concept allows cross-data-type operators to be used + with indexes and to be reasoned about using knowledge of access method + semantics. + + + + Operator families are described at length in . + + + + <structname>pg_opfamily</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + opfmethod oid + (references pg_am.oid) + + + Index access method operator family is for + + + + + + opfname name + + + Name of this operator family + + + + + + opfnamespace oid + (references pg_namespace.oid) + + + Namespace of this operator family + + + + + + opfowner oid + (references pg_authid.oid) + + + Owner of the operator family + + + + +
+ + + The majority of the information defining an operator family is not in its + pg_opfamily row, but in the associated rows in + pg_amop, + pg_amproc, + and + pg_opclass. + + + + + + + <structname>pg_partitioned_table</structname> + + + pg_partitioned_table + + + + The catalog pg_partitioned_table stores + information about how tables are partitioned. + + + + <structname>pg_partitioned_table</structname> Columns + + + + + Column Type + + + Description + + + + + + + + partrelid oid + (references pg_class.oid) + + + The OID of the pg_class entry for this partitioned table + + + + + + partstrat char + + + Partitioning strategy; h = hash partitioned table, + l = list partitioned table, r = range partitioned table + + + + + + partnatts int2 + + + The number of columns in partition key + + + + + + partdefid oid + (references pg_class.oid) + + + The OID of the pg_class entry for the default partition + of this partitioned table, or zero if this partitioned table does not + have a default partition. + + + + + + partattrs int2vector + (references pg_attribute.attnum) + + + This is an array of partnatts values that + indicate which table columns are part of the partition key. For + example, a value of 1 3 would mean that the first + and the third table columns make up the partition key. A zero in this + array indicates that the corresponding partition key column is an + expression, rather than a simple column reference. + + + + + + partclass oidvector + (references pg_opclass.oid) + + + For each column in the partition key, this contains the OID of the + operator class to use. See + pg_opclass for details. + + + + + + partcollation oidvector + (references pg_collation.oid) + + + For each column in the partition key, this contains the OID of the + collation to use for partitioning, or zero if the column is not + of a collatable data type. + + + + + + partexprs pg_node_tree + + + Expression trees (in nodeToString() + representation) for partition key columns that are not simple column + references. This is a list with one element for each zero + entry in partattrs. Null if all partition key columns + are simple references. + + + + +
+ + + + + <structname>pg_policy</structname> + + + pg_policy + + + + The catalog pg_policy stores row level + security policies for tables. A policy includes the kind of + command that it applies to (possibly all commands), the roles that it + applies to, the expression to be added as a security-barrier + qualification to queries that include the table, and the expression + to be added as a WITH CHECK option for queries that attempt to + add new records to the table. + + + + <structname>pg_policy</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + polname name + + + The name of the policy + + + + + + polrelid oid + (references pg_class.oid) + + + The table to which the policy applies + + + + + + polcmd char + + + The command type to which the policy is applied: + r for SELECT, + a for INSERT, + w for UPDATE, + d for DELETE, + or * for all + + + + + + polpermissive bool + + + Is the policy permissive or restrictive? + + + + + + polroles oid[] + (references pg_authid.oid) + + + The roles to which the policy is applied + + + + + + polqual pg_node_tree + + + The expression tree to be added to the security barrier qualifications for queries that use the table + + + + + + polwithcheck pg_node_tree + + + The expression tree to be added to the WITH CHECK qualifications for queries that attempt to add rows to the table + + + + +
+ + + + Policies stored in pg_policy are applied only when + pg_class.relrowsecurity is set for + their table. + + + + + + + <structname>pg_proc</structname> + + + pg_proc + + + + The catalog pg_proc stores information about + functions, procedures, aggregate functions, and window functions + (collectively also known as routines). See , , and + for more information. + + + + If prokind indicates that the entry is for an + aggregate function, there should be a matching row in + pg_aggregate. + + + + <structname>pg_proc</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + proname name + + + Name of the function + + + + + + pronamespace oid + (references pg_namespace.oid) + + + The OID of the namespace that contains this function + + + + + + proowner oid + (references pg_authid.oid) + + + Owner of the function + + + + + + prolang oid + (references pg_language.oid) + + + Implementation language or call interface of this function + + + + + + procost float4 + + + Estimated execution cost (in units of + ); if proretset, + this is cost per row returned + + + + + + prorows float4 + + + Estimated number of result rows (zero if not proretset) + + + + + + provariadic oid + (references pg_type.oid) + + + Data type of the variadic array parameter's elements, + or zero if the function does not have a variadic parameter + + + + + + prosupport regproc + (references pg_proc.oid) + + + Optional planner support function for this function + (see ) + + + + + + prokind char + + + f for a normal function, p + for a procedure, a for an aggregate function, or + w for a window function + + + + + + prosecdef bool + + + Function is a security definer (i.e., a setuid + function) + + + + + + proleakproof bool + + + The function has no side effects. No information about the + arguments is conveyed except via the return value. Any function + that might throw an error depending on the values of its arguments + is not leak-proof. + + + + + + proisstrict bool + + + Function returns null if any call argument is null. In that + case the function won't actually be called at all. Functions + that are not strict must be prepared to handle + null inputs. + + + + + + proretset bool + + + Function returns a set (i.e., multiple values of the specified + data type) + + + + + + provolatile char + + + provolatile tells whether the function's + result depends only on its input arguments, or is affected by outside + factors. + It is i for immutable functions, + which always deliver the same result for the same inputs. + It is s for stable functions, + whose results (for fixed inputs) do not change within a scan. + It is v for volatile functions, + whose results might change at any time. (Use v also + for functions with side-effects, so that calls to them cannot get + optimized away.) + + + + + + proparallel char + + + proparallel tells whether the function + can be safely run in parallel mode. + It is s for functions which are safe to run in + parallel mode without restriction. + It is r for functions which can be run in parallel + mode, but their execution is restricted to the parallel group leader; + parallel worker processes cannot invoke these functions. + It is u for functions which are unsafe in parallel + mode; the presence of such a function forces a serial execution plan. + + + + + + pronargs int2 + + + Number of input arguments + + + + + + pronargdefaults int2 + + + Number of arguments that have defaults + + + + + + prorettype oid + (references pg_type.oid) + + + Data type of the return value + + + + + + proargtypes oidvector + (references pg_type.oid) + + + An array of the data types of the function arguments. This includes + only input arguments (including INOUT and + VARIADIC arguments), and thus represents + the call signature of the function. + + + + + + proallargtypes oid[] + (references pg_type.oid) + + + An array of the data types of the function arguments. This includes + all arguments (including OUT and + INOUT arguments); however, if all the + arguments are IN arguments, this field will be null. + Note that subscripting is 1-based, whereas for historical reasons + proargtypes is subscripted from 0. + + + + + + proargmodes char[] + + + An array of the modes of the function arguments, encoded as + i for IN arguments, + o for OUT arguments, + b for INOUT arguments, + v for VARIADIC arguments, + t for TABLE arguments. + If all the arguments are IN arguments, + this field will be null. + Note that subscripts correspond to positions of + proallargtypes not proargtypes. + + + + + + proargnames text[] + + + An array of the names of the function arguments. + Arguments without a name are set to empty strings in the array. + If none of the arguments have a name, this field will be null. + Note that subscripts correspond to positions of + proallargtypes not proargtypes. + + + + + + proargdefaults pg_node_tree + + + Expression trees (in nodeToString() representation) + for default values. This is a list with + pronargdefaults elements, corresponding to the last + N input arguments (i.e., the last + N proargtypes positions). + If none of the arguments have defaults, this field will be null. + + + + + + protrftypes oid[] + (references pg_type.oid) + + + An array of the argument/result data type(s) for which to apply + transforms (from the function's TRANSFORM + clause). Null if none. + + + + + + prosrc text + + + This tells the function handler how to invoke the function. It + might be the actual source code of the function for interpreted + languages, a link symbol, a file name, or just about anything + else, depending on the implementation language/call convention. + + + + + + probin text + + + Additional information about how to invoke the function. + Again, the interpretation is language-specific. + + + + + + proconfig text[] + + + Function's local settings for run-time configuration variables + + + + + + proacl aclitem[] + + + Access privileges; see for details + + + + +
+ + + For compiled functions, both built-in and dynamically loaded, + prosrc contains the function's C-language + name (link symbol). For all other currently-known language types, + prosrc contains the function's source + text. probin is unused except for + dynamically-loaded C functions, for which it gives the name of the + shared library file containing the function. + + + + + + <structname>pg_publication</structname> + + + pg_publication + + + + The catalog pg_publication contains all + publications created in the database. For more on publications see + . + + + + <structname>pg_publication</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + pubname name + + + Name of the publication + + + + + + pubowner oid + (references pg_authid.oid) + + + Owner of the publication + + + + + + puballtables bool + + + If true, this publication automatically includes all tables + in the database, including any that will be created in the future. + + + + + + pubinsert bool + + + If true, INSERT operations are replicated for + tables in the publication. + + + + + + pubupdate bool + + + If true, UPDATE operations are replicated for + tables in the publication. + + + + + + pubdelete bool + + + If true, DELETE operations are replicated for + tables in the publication. + + + + + + pubtruncate bool + + + If true, TRUNCATE operations are replicated for + tables in the publication. + + + + + + pubviaroot bool + + + If true, operations on a leaf partition are replicated using the + identity and schema of its topmost partitioned ancestor mentioned in the + publication instead of its own. + + + + +
+ + + + <structname>pg_publication_rel</structname> + + + pg_publication_rel + + + + The catalog pg_publication_rel contains the + mapping between relations and publications in the database. This is a + many-to-many mapping. See also + for a more user-friendly view of this information. + + + + <structname>pg_publication_rel</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + prpubid oid + (references pg_publication.oid) + + + Reference to publication + + + + + + prrelid oid + (references pg_class.oid) + + + Reference to relation + + + + +
+ + + + <structname>pg_range</structname> + + + pg_range + + + + The catalog pg_range stores information about + range types. This is in addition to the types' entries in + pg_type. + + + + <structname>pg_range</structname> Columns + + + + + Column Type + + + Description + + + + + + + + rngtypid oid + (references pg_type.oid) + + + OID of the range type + + + + + + rngsubtype oid + (references pg_type.oid) + + + OID of the element type (subtype) of this range type + + + + + + rngcollation oid + (references pg_collation.oid) + + + OID of the collation used for range comparisons, or 0 if none + + + + + + rngsubopc oid + (references pg_opclass.oid) + + + OID of the subtype's operator class used for range comparisons + + + + + + rngcanonical regproc + (references pg_proc.oid) + + + OID of the function to convert a range value into canonical form, + or 0 if none + + + + + + rngsubdiff regproc + (references pg_proc.oid) + + + OID of the function to return the difference between two element + values as double precision, or 0 if none + + + + +
+ + + rngsubopc (plus rngcollation, if the + element type is collatable) determines the sort ordering used by the range + type. rngcanonical is used when the element type is + discrete. rngsubdiff is optional but should be supplied to + improve performance of GiST indexes on the range type. + + + + + + <structname>pg_replication_origin</structname> + + + pg_replication_origin + + + + The pg_replication_origin catalog contains + all replication origins created. For more on replication origins + see . + + + + Unlike most system catalogs, pg_replication_origin + is shared across all databases of a cluster: there is only one copy + of pg_replication_origin per cluster, not one per + database. + + + + <structname>pg_replication_origin</structname> Columns + + + + + Column Type + + + Description + + + + + + + + roident oid + + + A unique, cluster-wide identifier for the replication + origin. Should never leave the system. + + + + + + roname text + + + The external, user defined, name of a replication + origin. + + + + +
+ + + + <structname>pg_rewrite</structname> + + + pg_rewrite + + + + The catalog pg_rewrite stores rewrite rules for tables and views. + + + + <structname>pg_rewrite</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + rulename name + + + Rule name + + + + + + ev_class oid + (references pg_class.oid) + + + The table this rule is for + + + + + + ev_type char + + + Event type that the rule is for: 1 = SELECT, 2 = + UPDATE, 3 = INSERT, 4 = + DELETE + + + + + + ev_enabled char + + + Controls in which modes + the rule fires. + O = rule fires in origin and local modes, + D = rule is disabled, + R = rule fires in replica mode, + A = rule fires always. + + + + + + is_instead bool + + + True if the rule is an INSTEAD rule + + + + + + ev_qual pg_node_tree + + + Expression tree (in the form of a + nodeToString() representation) for the + rule's qualifying condition + + + + + + ev_action pg_node_tree + + + Query tree (in the form of a + nodeToString() representation) for the + rule's action + + + + +
+ + + + pg_class.relhasrules + must be true if a table has any rules in this catalog. + + + + + + + <structname>pg_seclabel</structname> + + + pg_seclabel + + + + The catalog pg_seclabel stores security + labels on database objects. Security labels can be manipulated + with the command. For an easier + way to view security labels, see . + + + + See also pg_shseclabel, + which performs a similar function for security labels of database objects + that are shared across a database cluster. + + + + <structname>pg_seclabel</structname> Columns + + + + + Column Type + + + Description + + + + + + + + objoid oid + (references any OID column) + + + The OID of the object this security label pertains to + + + + + + classoid oid + (references pg_class.oid) + + + The OID of the system catalog this object appears in + + + + + + objsubid int4 + + + For a security label on a table column, this is the column number (the + objoid and classoid refer to + the table itself). For all other object types, this column is + zero. + + + + + + provider text + + + The label provider associated with this label. + + + + + + label text + + + The security label applied to this object. + + + + +
+ + + + <structname>pg_sequence</structname> + + + pg_sequence + + + + The catalog pg_sequence contains information about + sequences. Some of the information about sequences, such as the name and + the schema, is in pg_class. + + + + <structname>pg_sequence</structname> Columns + + + + + Column Type + + + Description + + + + + + + + seqrelid oid + (references pg_class.oid) + + + The OID of the pg_class entry for this sequence + + + + + + seqtypid oid + (references pg_type.oid) + + + Data type of the sequence + + + + + + seqstart int8 + + + Start value of the sequence + + + + + + seqincrement int8 + + + Increment value of the sequence + + + + + + seqmax int8 + + + Maximum value of the sequence + + + + + + seqmin int8 + + + Minimum value of the sequence + + + + + + seqcache int8 + + + Cache size of the sequence + + + + + + seqcycle bool + + + Whether the sequence cycles + + + + +
+ + + + <structname>pg_shdepend</structname> + + + pg_shdepend + + + + The catalog pg_shdepend records the + dependency relationships between database objects and shared objects, + such as roles. This information allows + PostgreSQL to ensure that those objects are + unreferenced before attempting to delete them. + + + + See also pg_depend, + which performs a similar function for dependencies involving objects + within a single database. + + + + Unlike most system catalogs, pg_shdepend + is shared across all databases of a cluster: there is only one + copy of pg_shdepend per cluster, not + one per database. + + + + <structname>pg_shdepend</structname> Columns + + + + + Column Type + + + Description + + + + + + + + dbid oid + (references pg_database.oid) + + + The OID of the database the dependent object is in, + or zero for a shared object + + + + + + classid oid + (references pg_class.oid) + + + The OID of the system catalog the dependent object is in + + + + + + objid oid + (references any OID column) + + + The OID of the specific dependent object + + + + + + objsubid int4 + + + For a table column, this is the column number (the + objid and classid refer to the + table itself). For all other object types, this column is zero. + + + + + + refclassid oid + (references pg_class.oid) + + + The OID of the system catalog the referenced object is in + (must be a shared catalog) + + + + + + refobjid oid + (references any OID column) + + + The OID of the specific referenced object + + + + + + deptype char + + + A code defining the specific semantics of this dependency relationship; see text + + + + +
+ + + In all cases, a pg_shdepend entry indicates that + the referenced object cannot be dropped without also dropping the dependent + object. However, there are several subflavors identified by + deptype: + + + + SHARED_DEPENDENCY_OWNER (o) + + + The referenced object (which must be a role) is the owner of the + dependent object. + + + + + + SHARED_DEPENDENCY_ACL (a) + + + The referenced object (which must be a role) is mentioned in the + ACL (access control list, i.e., privileges list) of the + dependent object. (A SHARED_DEPENDENCY_ACL entry is + not made for the owner of the object, since the owner will have + a SHARED_DEPENDENCY_OWNER entry anyway.) + + + + + + SHARED_DEPENDENCY_POLICY (r) + + + The referenced object (which must be a role) is mentioned as the + target of a dependent policy object. + + + + + + SHARED_DEPENDENCY_PIN (p) + + + There is no dependent object; this type of entry is a signal + that the system itself depends on the referenced object, and so + that object must never be deleted. Entries of this type are + created only by initdb. The columns for the + dependent object contain zeroes. + + + + + + SHARED_DEPENDENCY_TABLESPACE (t) + + + The referenced object (which must be a tablespace) is mentioned as + the tablespace for a relation that doesn't have storage. + + + + + + Other dependency flavors might be needed in future. Note in particular + that the current definition only supports roles and tablespaces as referenced + objects. + + + + + + <structname>pg_shdescription</structname> + + + pg_shdescription + + + + The catalog pg_shdescription stores optional + descriptions (comments) for shared database objects. Descriptions can be + manipulated with the command and viewed with + psql's \d commands. + + + + See also pg_description, + which performs a similar function for descriptions involving objects + within a single database. + + + + Unlike most system catalogs, pg_shdescription + is shared across all databases of a cluster: there is only one + copy of pg_shdescription per cluster, not + one per database. + + + + <structname>pg_shdescription</structname> Columns + + + + + Column Type + + + Description + + + + + + + + objoid oid + (references any OID column) + + + The OID of the object this description pertains to + + + + + + classoid oid + (references pg_class.oid) + + + The OID of the system catalog this object appears in + + + + + + description text + + + Arbitrary text that serves as the description of this object + + + + +
+ + + + + <structname>pg_shseclabel</structname> + + + pg_shseclabel + + + + The catalog pg_shseclabel stores security + labels on shared database objects. Security labels can be manipulated + with the command. For an easier + way to view security labels, see . + + + + See also pg_seclabel, + which performs a similar function for security labels involving objects + within a single database. + + + + Unlike most system catalogs, pg_shseclabel + is shared across all databases of a cluster: there is only one + copy of pg_shseclabel per cluster, not + one per database. + + + + <structname>pg_shseclabel</structname> Columns + + + + + Column Type + + + Description + + + + + + + + objoid oid + (references any OID column) + + + The OID of the object this security label pertains to + + + + + + classoid oid + (references pg_class.oid) + + + The OID of the system catalog this object appears in + + + + + + provider text + + + The label provider associated with this label. + + + + + + label text + + + The security label applied to this object. + + + + +
+ + + + <structname>pg_statistic</structname> + + + pg_statistic + + + + The catalog pg_statistic stores + statistical data about the contents of the database. Entries are + created by + and subsequently used by the query planner. Note that all the + statistical data is inherently approximate, even assuming that it + is up-to-date. + + + + Normally there is one entry, with stainherit = + false, for each table column that has been analyzed. + If the table has inheritance children, a second entry with + stainherit = true is also created. This row + represents the column's statistics over the inheritance tree, i.e., + statistics for the data you'd see with + SELECT column FROM table*, + whereas the stainherit = false row represents + the results of + SELECT column FROM ONLY table. + + + + pg_statistic also stores statistical data about + the values of index expressions. These are described as if they were + actual data columns; in particular, starelid + references the index. No entry is made for an ordinary non-expression + index column, however, since it would be redundant with the entry + for the underlying table column. Currently, entries for index expressions + always have stainherit = false. + + + + Since different kinds of statistics might be appropriate for different + kinds of data, pg_statistic is designed not + to assume very much about what sort of statistics it stores. Only + extremely general statistics (such as nullness) are given dedicated + columns in pg_statistic. Everything else + is stored in slots, which are groups of associated columns + whose content is identified by a code number in one of the slot's columns. + For more information see + src/include/catalog/pg_statistic.h. + + + + pg_statistic should not be readable by the + public, since even statistical information about a table's contents + might be considered sensitive. (Example: minimum and maximum values + of a salary column might be quite interesting.) + pg_stats + is a publicly readable view on + pg_statistic that only exposes information + about those tables that are readable by the current user. + + + + <structname>pg_statistic</structname> Columns + + + + + Column Type + + + Description + + + + + + + + starelid oid + (references pg_class.oid) + + + The table or index that the described column belongs to + + + + + + staattnum int2 + (references pg_attribute.attnum) + + + The number of the described column + + + + + + stainherit bool + + + If true, the stats include inheritance child columns, not just the + values in the specified relation + + + + + + stanullfrac float4 + + + The fraction of the column's entries that are null + + + + + + stawidth int4 + + + The average stored width, in bytes, of nonnull entries + + + + + + stadistinct float4 + + + The number of distinct nonnull data values in the column. + A value greater than zero is the actual number of distinct values. + A value less than zero is the negative of a multiplier for the number + of rows in the table; for example, a column in which about 80% of the + values are nonnull and each nonnull value appears about twice on + average could be represented by stadistinct = -0.4. + A zero value means the number of distinct values is unknown. + + + + + + stakindN int2 + + + A code number indicating the kind of statistics stored in the + Nth slot of the + pg_statistic row. + + + + + + staopN oid + (references pg_operator.oid) + + + An operator used to derive the statistics stored in the + Nth slot. For example, a + histogram slot would show the < operator + that defines the sort order of the data. + + + + + + stacollN oid + (references pg_collation.oid) + + + The collation used to derive the statistics stored in the + Nth slot. For example, a + histogram slot for a collatable column would show the collation that + defines the sort order of the data. Zero for noncollatable data. + + + + + + stanumbersN float4[] + + + Numerical statistics of the appropriate kind for the + Nth slot, or null if the slot + kind does not involve numerical values + + + + + + stavaluesN anyarray + + + Column data values of the appropriate kind for the + Nth slot, or null if the slot + kind does not store any data values. Each array's element + values are actually of the specific column's data type, or a related + type such as an array's element type, so there is no way to define + these columns' type more specifically than anyarray. + + + + +
+ + + + + <structname>pg_statistic_ext</structname> + + + pg_statistic_ext + + + + The catalog pg_statistic_ext + holds definitions of extended planner statistics. + Each row in this catalog corresponds to a statistics object + created with . + + + + <structname>pg_statistic_ext</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + stxrelid oid + (references pg_class.oid) + + + Table containing the columns described by this object + + + + + + stxname name + + + Name of the statistics object + + + + + + stxnamespace oid + (references pg_namespace.oid) + + + The OID of the namespace that contains this statistics object + + + + + + stxowner oid + (references pg_authid.oid) + + + Owner of the statistics object + + + + + + stxstattarget int4 + + + stxstattarget controls the level of detail + of statistics accumulated for this statistics object by + . + A zero value indicates that no statistics should be collected. + A negative value says to use the maximum of the statistics targets of + the referenced columns, if set, or the system default statistics target. + Positive values of stxstattarget + determine the target number of most common values + to collect. + + + + + + stxkeys int2vector + (references pg_attribute.attnum) + + + An array of attribute numbers, indicating which table columns are + covered by this statistics object; + for example a value of 1 3 would + mean that the first and the third table columns are covered + + + + + + stxkind char[] + + + An array containing codes for the enabled statistics kinds; + valid values are: + d for n-distinct statistics, + f for functional dependency statistics, and + m for most common values (MCV) list statistics + + + + +
+ + + The pg_statistic_ext entry is filled in + completely during CREATE STATISTICS, but the actual + statistical values are not computed then. + Subsequent ANALYZE commands compute the desired values + and populate an entry in the + pg_statistic_ext_data + catalog. + + + + + <structname>pg_statistic_ext_data</structname> + + + pg_statistic_ext_data + + + + The catalog pg_statistic_ext_data + holds data for extended planner statistics defined in pg_statistic_ext. + Each row in this catalog corresponds to a statistics object + created with . + + + + Like pg_statistic, + pg_statistic_ext_data should not be + readable by the public, since the contents might be considered sensitive. + (Example: most common combinations of values in columns might be quite + interesting.) + pg_stats_ext + is a publicly readable view + on pg_statistic_ext_data (after joining + with pg_statistic_ext) that only exposes + information about those tables and columns that are readable by the + current user. + + + + <structname>pg_statistic_ext_data</structname> Columns + + + + + Column Type + + + Description + + + + + + + + stxoid oid + (references pg_statistic_ext.oid) + + + Extended statistic object containing the definition for this data + + + + + + stxdndistinct pg_ndistinct + + + N-distinct counts, serialized as pg_ndistinct type + + + + + + stxddependencies pg_dependencies + + + Functional dependency statistics, serialized + as pg_dependencies type + + + + + + stxdmcv pg_mcv_list + + + MCV (most-common values) list statistics, serialized as + pg_mcv_list type + + + + +
+ + + + + <structname>pg_subscription</structname> + + + pg_subscription + + + + The catalog pg_subscription contains all existing + logical replication subscriptions. For more information about logical + replication see . + + + + Unlike most system catalogs, pg_subscription is + shared across all databases of a cluster: there is only one copy + of pg_subscription per cluster, not one per + database. + + + + Access to the column subconninfo is revoked from + normal users, because it could contain plain-text passwords. + + + + <structname>pg_subscription</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + subdbid oid + (references pg_database.oid) + + + OID of the database which the subscription resides in + + + + + + subname name + + + Name of the subscription + + + + + + subowner oid + (references pg_authid.oid) + + + Owner of the subscription + + + + + + subenabled bool + + + If true, the subscription is enabled and should be replicating. + + + + + + subconninfo text + + + Connection string to the upstream database + + + + + + subslotname name + + + Name of the replication slot in the upstream database (also used + for the local replication origin name); + null represents NONE + + + + + + subsynccommit text + + + Contains the value of the synchronous_commit + setting for the subscription workers. + + + + + + subpublications text[] + + + Array of subscribed publication names. These reference the + publications on the publisher server. For more on publications + see . + + + + +
+ + + + <structname>pg_subscription_rel</structname> + + + pg_subscription_rel + + + + The catalog pg_subscription_rel contains the + state for each replicated relation in each subscription. This is a + many-to-many mapping. + + + + This catalog only contains tables known to the subscription after running + either CREATE SUBSCRIPTION or + ALTER SUBSCRIPTION ... REFRESH PUBLICATION. + + + + <structname>pg_subscription_rel</structname> Columns + + + + + Column Type + + + Description + + + + + + + + srsubid oid + (references pg_subscription.oid) + + + Reference to subscription + + + + + + srrelid oid + (references pg_class.oid) + + + Reference to relation + + + + + + srsubstate char + + + State code: + i = initialize, + d = data is being copied, + s = synchronized, + r = ready (normal replication) + + + + + + srsublsn pg_lsn + + + Remote LSN of the state change used for synchronization coordination + when in s or r states, + otherwise null + + + + +
+ + + + <structname>pg_tablespace</structname> + + + pg_tablespace + + + + The catalog pg_tablespace stores information + about the available tablespaces. Tables can be placed in particular + tablespaces to aid administration of disk layout. + + + + Unlike most system catalogs, pg_tablespace + is shared across all databases of a cluster: there is only one + copy of pg_tablespace per cluster, not + one per database. + + + + <structname>pg_tablespace</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + spcname name + + + Tablespace name + + + + + + spcowner oid + (references pg_authid.oid) + + + Owner of the tablespace, usually the user who created it + + + + + + spcacl aclitem[] + + + Access privileges; see for details + + + + + + spcoptions text[] + + + Tablespace-level options, as keyword=value strings + + + + +
+ + + + + <structname>pg_transform</structname> + + + pg_transform + + + + The catalog pg_transform stores information about + transforms, which are a mechanism to adapt data types to procedural + languages. See for more information. + + + + <structname>pg_transform</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + trftype oid + (references pg_type.oid) + + + OID of the data type this transform is for + + + + + + trflang oid + (references pg_language.oid) + + + OID of the language this transform is for + + + + + + trffromsql regproc + (references pg_proc.oid) + + + The OID of the function to use when converting the data type for input + to the procedural language (e.g., function parameters). Zero is stored + if this operation is not supported. + + + + + + trftosql regproc + (references pg_proc.oid) + + + The OID of the function to use when converting output from the + procedural language (e.g., return values) to the data type. Zero is + stored if this operation is not supported. + + + + +
+ + + + + <structname>pg_trigger</structname> + + + pg_trigger + + + + The catalog pg_trigger stores triggers on tables + and views. + See + for more information. + + + + <structname>pg_trigger</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + tgrelid oid + (references pg_class.oid) + + + The table this trigger is on + + + + + + tgparentid oid + (references pg_trigger.oid) + + + Parent trigger that this trigger is cloned from, zero if not a clone; + this happens when partitions are created or attached to a partitioned + table. + + + + + + tgname name + + + Trigger name (must be unique among triggers of same table) + + + + + + tgfoid oid + (references pg_proc.oid) + + + The function to be called + + + + + + tgtype int2 + + + Bit mask identifying trigger firing conditions + + + + + + tgenabled char + + + Controls in which modes + the trigger fires. + O = trigger fires in origin and local modes, + D = trigger is disabled, + R = trigger fires in replica mode, + A = trigger fires always. + + + + + + tgisinternal bool + + + True if trigger is internally generated (usually, to enforce + the constraint identified by tgconstraint) + + + + + + tgconstrrelid oid + (references pg_class.oid) + + + The table referenced by a referential integrity constraint + + + + + + tgconstrindid oid + (references pg_class.oid) + + + The index supporting a unique, primary key, referential integrity, + or exclusion constraint + + + + + + tgconstraint oid + (references pg_constraint.oid) + + + The pg_constraint entry associated with the trigger, if any + + + + + + tgdeferrable bool + + + True if constraint trigger is deferrable + + + + + + tginitdeferred bool + + + True if constraint trigger is initially deferred + + + + + + tgnargs int2 + + + Number of argument strings passed to trigger function + + + + + + tgattr int2vector + (references pg_attribute.attnum) + + + Column numbers, if trigger is column-specific; otherwise an + empty array + + + + + + tgargs bytea + + + Argument strings to pass to trigger, each NULL-terminated + + + + + + tgqual pg_node_tree + + + Expression tree (in nodeToString() + representation) for the trigger's WHEN condition, or null + if none + + + + + + tgoldtable name + + + REFERENCING clause name for OLD TABLE, + or null if none + + + + + + tgnewtable name + + + REFERENCING clause name for NEW TABLE, + or null if none + + + + +
+ + + Currently, column-specific triggering is supported only for + UPDATE events, and so tgattr is relevant + only for that event type. tgtype might + contain bits for other event types as well, but those are presumed + to be table-wide regardless of what is in tgattr. + + + + + When tgconstraint is nonzero, + tgconstrrelid, tgconstrindid, + tgdeferrable, and tginitdeferred are + largely redundant with the referenced pg_constraint entry. + However, it is possible for a non-deferrable trigger to be associated + with a deferrable constraint: foreign key constraints can have some + deferrable and some non-deferrable triggers. + + + + + + pg_class.relhastriggers + must be true if a relation has any triggers in this catalog. + + + + + + + + <structname>pg_ts_config</structname> + + + pg_ts_config + + + + The pg_ts_config catalog contains entries + representing text search configurations. A configuration specifies + a particular text search parser and a list of dictionaries to use + for each of the parser's output token types. The parser is shown + in the pg_ts_config entry, but the + token-to-dictionary mapping is defined by subsidiary entries in pg_ts_config_map. + + + + PostgreSQL's text search features are + described at length in . + + + + <structname>pg_ts_config</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + cfgname name + + + Text search configuration name + + + + + + cfgnamespace oid + (references pg_namespace.oid) + + + The OID of the namespace that contains this configuration + + + + + + cfgowner oid + (references pg_authid.oid) + + + Owner of the configuration + + + + + + cfgparser oid + (references pg_ts_parser.oid) + + + The OID of the text search parser for this configuration + + + + +
+ + + + + <structname>pg_ts_config_map</structname> + + + pg_ts_config_map + + + + The pg_ts_config_map catalog contains entries + showing which text search dictionaries should be consulted, and in + what order, for each output token type of each text search configuration's + parser. + + + + PostgreSQL's text search features are + described at length in . + + + + <structname>pg_ts_config_map</structname> Columns + + + + + Column Type + + + Description + + + + + + + + mapcfg oid + (references pg_ts_config.oid) + + + The OID of the pg_ts_config entry owning this map entry + + + + + + maptokentype int4 + + + A token type emitted by the configuration's parser + + + + + + mapseqno int4 + + + Order in which to consult this entry (lower + mapseqnos first) + + + + + + mapdict oid + (references pg_ts_dict.oid) + + + The OID of the text search dictionary to consult + + + + +
+ + + + + <structname>pg_ts_dict</structname> + + + pg_ts_dict + + + + The pg_ts_dict catalog contains entries + defining text search dictionaries. A dictionary depends on a text + search template, which specifies all the implementation functions + needed; the dictionary itself provides values for the user-settable + parameters supported by the template. This division of labor allows + dictionaries to be created by unprivileged users. The parameters + are specified by a text string dictinitoption, + whose format and meaning vary depending on the template. + + + + PostgreSQL's text search features are + described at length in . + + + + <structname>pg_ts_dict</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + dictname name + + + Text search dictionary name + + + + + + dictnamespace oid + (references pg_namespace.oid) + + + The OID of the namespace that contains this dictionary + + + + + + dictowner oid + (references pg_authid.oid) + + + Owner of the dictionary + + + + + + dicttemplate oid + (references pg_ts_template.oid) + + + The OID of the text search template for this dictionary + + + + + + dictinitoption text + + + Initialization option string for the template + + + + +
+ + + + + <structname>pg_ts_parser</structname> + + + pg_ts_parser + + + + The pg_ts_parser catalog contains entries + defining text search parsers. A parser is responsible for splitting + input text into lexemes and assigning a token type to each lexeme. + Since a parser must be implemented by C-language-level functions, + creation of new parsers is restricted to database superusers. + + + + PostgreSQL's text search features are + described at length in . + + + + <structname>pg_ts_parser</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + prsname name + + + Text search parser name + + + + + + prsnamespace oid + (references pg_namespace.oid) + + + The OID of the namespace that contains this parser + + + + + + prsstart regproc + (references pg_proc.oid) + + + OID of the parser's startup function + + + + + + prstoken regproc + (references pg_proc.oid) + + + OID of the parser's next-token function + + + + + + prsend regproc + (references pg_proc.oid) + + + OID of the parser's shutdown function + + + + + + prsheadline regproc + (references pg_proc.oid) + + + OID of the parser's headline function + + + + + + prslextype regproc + (references pg_proc.oid) + + + OID of the parser's lextype function + + + + +
+ + + + + <structname>pg_ts_template</structname> + + + pg_ts_template + + + + The pg_ts_template catalog contains entries + defining text search templates. A template is the implementation + skeleton for a class of text search dictionaries. + Since a template must be implemented by C-language-level functions, + creation of new templates is restricted to database superusers. + + + + PostgreSQL's text search features are + described at length in . + + + + <structname>pg_ts_template</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + tmplname name + + + Text search template name + + + + + + tmplnamespace oid + (references pg_namespace.oid) + + + The OID of the namespace that contains this template + + + + + + tmplinit regproc + (references pg_proc.oid) + + + OID of the template's initialization function + + + + + + tmpllexize regproc + (references pg_proc.oid) + + + OID of the template's lexize function + + + + +
+ + + + + <structname>pg_type</structname> + + + pg_type + + + + The catalog pg_type stores information about data + types. Base types and enum types (scalar types) are created with + , and + domains with + . + A composite type is automatically created for each table in the database, to + represent the row structure of the table. It is also possible to create + composite types with CREATE TYPE AS. + + + + <structname>pg_type</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + typname name + + + Data type name + + + + + + typnamespace oid + (references pg_namespace.oid) + + + The OID of the namespace that contains this type + + + + + + typowner oid + (references pg_authid.oid) + + + Owner of the type + + + + + + typlen int2 + + + For a fixed-size type, typlen is the number + of bytes in the internal representation of the type. But for a + variable-length type, typlen is negative. + -1 indicates a varlena type (one that has a length word), + -2 indicates a null-terminated C string. + + + + + + typbyval bool + + + typbyval determines whether internal + routines pass a value of this type by value or by reference. + typbyval had better be false if + typlen is not 1, 2, or 4 (or 8 on machines + where Datum is 8 bytes). + Variable-length types are always passed by reference. Note that + typbyval can be false even if the + length would allow pass-by-value. + + + + + + typtype char + + + typtype is + b for a base type, + c for a composite type (e.g., a table's row type), + d for a domain, + e for an enum type, + p for a pseudo-type, or + r for a range type. + See also typrelid and + typbasetype. + + + + + + typcategory char + + + typcategory is an arbitrary classification + of data types that is used by the parser to determine which implicit + casts should be preferred. + See . + + + + + + typispreferred bool + + + True if the type is a preferred cast target within its + typcategory + + + + + + typisdefined bool + + + True if the type is defined, false if this is a placeholder + entry for a not-yet-defined type. When + typisdefined is false, nothing + except the type name, namespace, and OID can be relied on. + + + + + + typdelim char + + + Character that separates two values of this type when parsing + array input. Note that the delimiter is associated with the array + element data type, not the array data type. + + + + + + typrelid oid + (references pg_class.oid) + + + If this is a composite type (see + typtype), then this column points to + the pg_class entry that defines the + corresponding table. (For a free-standing composite type, the + pg_class entry doesn't really represent + a table, but it is needed anyway for the type's + pg_attribute entries to link to.) + Zero for non-composite types. + + + + + + typelem oid + (references pg_type.oid) + + + If typelem is not 0 then it + identifies another row in pg_type. + The current type can then be subscripted like an array yielding + values of type typelem. A + true array type is variable length + (typlen = -1), + but some fixed-length (typlen > 0) types + also have nonzero typelem, for example + name and point. + If a fixed-length type has a typelem then + its internal representation must be some number of values of the + typelem data type with no other data. + Variable-length array types have a header defined by the array + subroutines. + + + + + + typarray oid + (references pg_type.oid) + + + If typarray is not 0 then it + identifies another row in pg_type, which + is the true array type having this type as element + + + + + + typinput regproc + (references pg_proc.oid) + + + Input conversion function (text format) + + + + + + typoutput regproc + (references pg_proc.oid) + + + Output conversion function (text format) + + + + + + typreceive regproc + (references pg_proc.oid) + + + Input conversion function (binary format), or 0 if none + + + + + + typsend regproc + (references pg_proc.oid) + + + Output conversion function (binary format), or 0 if none + + + + + + typmodin regproc + (references pg_proc.oid) + + + Type modifier input function, or 0 if type does not support modifiers + + + + + + typmodout regproc + (references pg_proc.oid) + + + Type modifier output function, or 0 to use the standard format + + + + + + typanalyze regproc + (references pg_proc.oid) + + + Custom ANALYZE function, or 0 to use the standard function + + + + + + typalign char + + + typalign is the alignment required + when storing a value of this type. It applies to storage on + disk as well as most representations of the value inside + PostgreSQL. + When multiple values are stored consecutively, such + as in the representation of a complete row on disk, padding is + inserted before a datum of this type so that it begins on the + specified boundary. The alignment reference is the beginning + of the first datum in the sequence. + Possible values are: + + + c = char alignment, i.e., no alignment needed. + + + s = short alignment (2 bytes on most machines). + + + i = int alignment (4 bytes on most machines). + + + d = double alignment (8 bytes on many machines, but by no means all). + + + + + + + + typstorage char + + + typstorage tells for varlena + types (those with typlen = -1) if + the type is prepared for toasting and what the default strategy + for attributes of this type should be. + Possible values are: + + + + p (plain): Values must always be stored plain + (non-varlena types always use this value). + + + + + e (external): Values can be stored in a + secondary TOAST relation (if relation has one, see + pg_class.reltoastrelid). + + + + + m (main): Values can be compressed and stored + inline. + + + + + x (extended): Values can be compressed and/or + moved to a secondary relation. + + + + x is the usual choice for toast-able types. + Note that m values can also be moved out to + secondary storage, but only as a last resort (e + and x values are moved first). + + + + + + typnotnull bool + + + typnotnull represents a not-null + constraint on a type. Used for domains only. + + + + + + typbasetype oid + (references pg_type.oid) + + + If this is a domain (see typtype), then + typbasetype identifies the type that this + one is based on. Zero if this type is not a domain. + + + + + + typtypmod int4 + + + Domains use typtypmod to record the typmod + to be applied to their base type (-1 if base type does not use a + typmod). -1 if this type is not a domain. + + + + + + typndims int4 + + + typndims is the number of array dimensions + for a domain over an array (that is, typbasetype is + an array type). + Zero for types other than domains over array types. + + + + + + typcollation oid + (references pg_collation.oid) + + + typcollation specifies the collation + of the type. If the type does not support collations, this will + be zero. A base type that supports collations will have a nonzero + value here, typically DEFAULT_COLLATION_OID. + A domain over a collatable type can have a collation OID different + from its base type's, if one was specified for the domain. + + + + + + typdefaultbin pg_node_tree + + + If typdefaultbin is not null, it is the + nodeToString() + representation of a default expression for the type. This is + only used for domains. + + + + + + typdefault text + + + typdefault is null if the type has no associated + default value. If typdefaultbin is not null, + typdefault must contain a human-readable version of the + default expression represented by typdefaultbin. If + typdefaultbin is null and typdefault is + not, then typdefault is the external representation of + the type's default value, which can be fed to the type's input + converter to produce a constant. + + + + + + typacl aclitem[] + + + Access privileges; see for details + + + + +
+ + + + For fixed-width types used in system tables, it is critical that the size + and alignment defined in pg_type + agree with the way that the compiler will lay out the column in + a structure representing a table row. + + + + + lists the system-defined values + of typcategory. Any future additions to this list will + also be upper-case ASCII letters. All other ASCII characters are reserved + for user-defined categories. + + + + <structfield>typcategory</structfield> Codes + + + + + Code + Category + + + + + + A + Array types + + + B + Boolean types + + + C + Composite types + + + D + Date/time types + + + E + Enum types + + + G + Geometric types + + + I + Network address types + + + N + Numeric types + + + P + Pseudo-types + + + R + Range types + + + S + String types + + + T + Timespan types + + + U + User-defined types + + + V + Bit-string types + + + X + unknown type + + + +
+ + + + + + <structname>pg_user_mapping</structname> + + + pg_user_mapping + + + + The catalog pg_user_mapping stores + the mappings from local user to remote. Access to this catalog is + restricted from normal users, use the view + pg_user_mappings + instead. + + + + <structname>pg_user_mapping</structname> Columns + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + umuser oid + (references pg_authid.oid) + + + OID of the local role being mapped, 0 if the user mapping is public + + + + + + umserver oid + (references pg_foreign_server.oid) + + + The OID of the foreign server that contains this mapping + + + + + + umoptions text[] + + + User mapping specific options, as keyword=value strings + + + + +
+ + + + + System Views + + + In addition to the system catalogs, PostgreSQL + provides a number of built-in views. Some system views provide convenient + access to some commonly used queries on the system catalogs. Other views + provide access to internal server state. + + + + The information schema () provides + an alternative set of views which overlap the functionality of the system + views. Since the information schema is SQL-standard whereas the views + described here are PostgreSQL-specific, + it's usually better to use the information schema if it provides all + the information you need. + + + + lists the system views described here. + More detailed documentation of each view follows below. + There are some additional views that provide access to the results of + the statistics collector; they are described in . + + + + Except where noted, all the views described here are read-only. + + + + System Views + + + + + View Name + Purpose + + + + + + pg_available_extensions + available extensions + + + + pg_available_extension_versions + available versions of extensions + + + + pg_config + compile-time configuration parameters + + + + pg_cursors + open cursors + + + + pg_file_settings + summary of configuration file contents + + + + pg_group + groups of database users + + + + pg_hba_file_rules + summary of client authentication configuration file contents + + + + pg_indexes + indexes + + + + pg_locks + locks currently held or awaited + + + + pg_matviews + materialized views + + + + pg_policies + policies + + + + pg_prepared_statements + prepared statements + + + + pg_prepared_xacts + prepared transactions + + + + pg_publication_tables + publications and their associated tables + + + + pg_replication_origin_status + information about replication origins, including replication progress + + + + pg_replication_slots + replication slot information + + + + pg_roles + database roles + + + + pg_rules + rules + + + + pg_seclabels + security labels + + + + pg_sequences + sequences + + + + pg_settings + parameter settings + + + + pg_shadow + database users + + + + pg_shmem_allocations + shared memory allocations + + + + pg_stats + planner statistics + + + + pg_stats_ext + extended planner statistics + + + + pg_tables + tables + + + + pg_timezone_abbrevs + time zone abbreviations + + + + pg_timezone_names + time zone names + + + + pg_user + database users + + + + pg_user_mappings + user mappings + + + + pg_views + views + + + + +
+ + + + <structname>pg_available_extensions</structname> + + + pg_available_extensions + + + + The pg_available_extensions view lists the + extensions that are available for installation. + See also the + pg_extension + catalog, which shows the extensions currently installed. + + + + <structname>pg_available_extensions</structname> Columns + + + + + Column Type + + + Description + + + + + + + + name name + + + Extension name + + + + + + default_version text + + + Name of default version, or NULL if none is + specified + + + + + + installed_version text + + + Currently installed version of the extension, + or NULL if not installed + + + + + + comment text + + + Comment string from the extension's control file + + + + +
+ + + The pg_available_extensions view is read only. + + + + + <structname>pg_available_extension_versions</structname> + + + pg_available_extension_versions + + + + The pg_available_extension_versions view lists the + specific extension versions that are available for installation. + See also the pg_extension + catalog, which shows the extensions currently installed. + + + + <structname>pg_available_extension_versions</structname> Columns + + + + + Column Type + + + Description + + + + + + + + name name + + + Extension name + + + + + + version text + + + Version name + + + + + + installed bool + + + True if this version of this extension is currently + installed + + + + + + superuser bool + + + True if only superusers are allowed to install this extension + (but see trusted) + + + + + + trusted bool + + + True if the extension can be installed by non-superusers + with appropriate privileges + + + + + + relocatable bool + + + True if extension can be relocated to another schema + + + + + + schema name + + + Name of the schema that the extension must be installed into, + or NULL if partially or fully relocatable + + + + + + requires name[] + + + Names of prerequisite extensions, + or NULL if none + + + + + + comment text + + + Comment string from the extension's control file + + + + +
+ + + The pg_available_extension_versions view is read + only. + + + + + <structname>pg_config</structname> + + + pg_config + + + + The view pg_config describes the + compile-time configuration parameters of the currently installed + version of PostgreSQL. It is intended, for example, to + be used by software packages that want to interface to + PostgreSQL to facilitate finding the required header + files and libraries. It provides the same basic information as the + PostgreSQL client + application. + + + + By default, the pg_config view can be read + only by superusers. + + + + <structname>pg_config</structname> Columns + + + + + Column Type + + + Description + + + + + + + + name text + + + The parameter name + + + + + + setting text + + + The parameter value + + + + +
+ + + + + <structname>pg_cursors</structname> + + + pg_cursors + + + + The pg_cursors view lists the cursors that + are currently available. Cursors can be defined in several ways: + + + + via the + statement in SQL + + + + + + via the Bind message in the frontend/backend protocol, as + described in + + + + + + via the Server Programming Interface (SPI), as described in + + + + + + The pg_cursors view displays cursors + created by any of these means. Cursors only exist for the duration + of the transaction that defines them, unless they have been + declared WITH HOLD. Therefore non-holdable + cursors are only present in the view until the end of their + creating transaction. + + + + Cursors are used internally to implement some of the components + of PostgreSQL, such as procedural languages. + Therefore, the pg_cursors view might include cursors + that have not been explicitly created by the user. + + + + + + <structname>pg_cursors</structname> Columns + + + + + Column Type + + + Description + + + + + + + + name text + + + The name of the cursor + + + + + + statement text + + + The verbatim query string submitted to declare this cursor + + + + + + is_holdable bool + + + true if the cursor is holdable (that is, it + can be accessed after the transaction that declared the cursor + has committed); false otherwise + + + + + + is_binary bool + + + true if the cursor was declared + BINARY; false + otherwise + + + + + + is_scrollable bool + + + true if the cursor is scrollable (that is, it + allows rows to be retrieved in a nonsequential manner); + false otherwise + + + + + + creation_time timestamptz + + + The time at which the cursor was declared + + + + +
+ + + The pg_cursors view is read only. + + + + + + <structname>pg_file_settings</structname> + + + pg_file_settings + + + + The view pg_file_settings provides a summary of + the contents of the server's configuration file(s). A row appears in + this view for each name = value entry appearing in the files, + with annotations indicating whether the value could be applied + successfully. Additional row(s) may appear for problems not linked to + a name = value entry, such as syntax errors in the files. + + + + This view is helpful for checking whether planned changes in the + configuration files will work, or for diagnosing a previous failure. + Note that this view reports on the current contents of the + files, not on what was last applied by the server. (The + pg_settings + view is usually sufficient to determine that.) + + + + By default, the pg_file_settings view can be read + only by superusers. + + + + <structname>pg_file_settings</structname> Columns + + + + + Column Type + + + Description + + + + + + + + sourcefile text + + + Full path name of the configuration file + + + + + + sourceline int4 + + + Line number within the configuration file where the entry appears + + + + + + seqno int4 + + + Order in which the entries are processed (1..n) + + + + + + name text + + + Configuration parameter name + + + + + + setting text + + + Value to be assigned to the parameter + + + + + + applied bool + + + True if the value can be applied successfully + + + + + + error text + + + If not null, an error message indicating why this entry could + not be applied + + + + +
+ + + If the configuration file contains syntax errors or invalid parameter + names, the server will not attempt to apply any settings from it, and + therefore all the applied fields will read as false. + In such a case there will be one or more rows with + non-null error fields indicating the + problem(s). Otherwise, individual settings will be applied if possible. + If an individual setting cannot be applied (e.g., invalid value, or the + setting cannot be changed after server start) it will have an appropriate + message in the error field. Another way that + an entry might have applied = false is that it is + overridden by a later entry for the same parameter name; this case is not + considered an error so nothing appears in + the error field. + + + + See for more information about the various + ways to change run-time parameters. + + + + + + <structname>pg_group</structname> + + + pg_group + + + + The view pg_group exists for backwards + compatibility: it emulates a catalog that existed in + PostgreSQL before version 8.1. + It shows the names and members of all roles that are marked as not + rolcanlogin, which is an approximation to the set + of roles that are being used as groups. + + + + <structname>pg_group</structname> Columns + + + + + Column Type + + + Description + + + + + + + + groname name + (references pg_authid.rolname) + + + Name of the group + + + + + + grosysid oid + (references pg_authid.oid) + + + ID of this group + + + + + + grolist oid[] + (references pg_authid.oid) + + + An array containing the IDs of the roles in this group + + + + +
+ + + + + <structname>pg_hba_file_rules</structname> + + + pg_hba_file_rules + + + + The view pg_hba_file_rules provides a summary of + the contents of the client authentication configuration + file, pg_hba.conf. A row appears in this view for each + non-empty, non-comment line in the file, with annotations indicating + whether the rule could be applied successfully. + + + + This view can be helpful for checking whether planned changes in the + authentication configuration file will work, or for diagnosing a previous + failure. Note that this view reports on the current contents + of the file, not on what was last loaded by the server. + + + + By default, the pg_hba_file_rules view can be read + only by superusers. + + + + <structname>pg_hba_file_rules</structname> Columns + + + + + Column Type + + + Description + + + + + + + + line_number int4 + + + Line number of this rule in pg_hba.conf + + + + + + type text + + + Type of connection + + + + + + database text[] + + + List of database name(s) to which this rule applies + + + + + + user_name text[] + + + List of user and group name(s) to which this rule applies + + + + + + address text + + + Host name or IP address, or one + of all, samehost, + or samenet, or null for local connections + + + + + + netmask text + + + IP address mask, or null if not applicable + + + + + + auth_method text + + + Authentication method + + + + + + options text[] + + + Options specified for authentication method, if any + + + + + + error text + + + If not null, an error message indicating why this + line could not be processed + + + + +
+ + + Usually, a row reflecting an incorrect entry will have values for only + the line_number and error fields. + + + + See for more information about + client authentication configuration. + + + + + <structname>pg_indexes</structname> + + + pg_indexes + + + + The view pg_indexes provides access to + useful information about each index in the database. + + + + <structname>pg_indexes</structname> Columns + + + + + Column Type + + + Description + + + + + + + + schemaname name + (references pg_namespace.nspname) + + + Name of schema containing table and index + + + + + + tablename name + (references pg_class.relname) + + + Name of table the index is for + + + + + + indexname name + (references pg_class.relname) + + + Name of index + + + + + + tablespace name + (references pg_tablespace.spcname) + + + Name of tablespace containing index (null if default for database) + + + + + + indexdef text + + + Index definition (a reconstructed CREATE INDEX + command) + + + + +
+ + + + + <structname>pg_locks</structname> + + + pg_locks + + + + The view pg_locks provides access to + information about the locks held by active processes within the + database server. See for more discussion + of locking. + + + + pg_locks contains one row per active lockable + object, requested lock mode, and relevant process. Thus, the same + lockable object might + appear many times, if multiple processes are holding or waiting + for locks on it. However, an object that currently has no locks on it + will not appear at all. + + + + There are several distinct types of lockable objects: + whole relations (e.g., tables), individual pages of relations, + individual tuples of relations, + transaction IDs (both virtual and permanent IDs), + and general database objects (identified by class OID and object OID, + in the same way as in pg_description or + pg_depend). Also, the right to extend a + relation is represented as a separate lockable object, as is the right to + update pg_database.datfrozenxid. + Also, advisory locks can be taken on numbers that have + user-defined meanings. + + + + <structname>pg_locks</structname> Columns + + + + + Column Type + + + Description + + + + + + + + locktype text + + + Type of the lockable object: + relation, + extend, + frozenid, + page, + tuple, + transactionid, + virtualxid, + spectoken, + object, + userlock, or + advisory. + (See also .) + + + + + + database oid + (references pg_database.oid) + + + OID of the database in which the lock target exists, or + zero if the target is a shared object, or + null if the target is a transaction ID + + + + + + relation oid + (references pg_class.oid) + + + OID of the relation targeted by the lock, or null if the target is not + a relation or part of a relation + + + + + + page int4 + + + Page number targeted by the lock within the relation, + or null if the target is not a relation page or tuple + + + + + + tuple int2 + + + Tuple number targeted by the lock within the page, + or null if the target is not a tuple + + + + + + virtualxid text + + + Virtual ID of the transaction targeted by the lock, + or null if the target is not a virtual transaction ID + + + + + + transactionid xid + + + ID of the transaction targeted by the lock, + or null if the target is not a transaction ID + + + + + + classid oid + (references pg_class.oid) + + + OID of the system catalog containing the lock target, or null if the + target is not a general database object + + + + + + objid oid + (references any OID column) + + + OID of the lock target within its system catalog, or null if the + target is not a general database object + + + + + + objsubid int2 + + + Column number targeted by the lock (the + classid and objid refer to the + table itself), + or zero if the target is some other general database object, + or null if the target is not a general database object + + + + + + virtualtransaction text + + + Virtual ID of the transaction that is holding or awaiting this lock + + + + + + pid int4 + + + Process ID of the server process holding or awaiting this + lock, or null if the lock is held by a prepared transaction + + + + + + mode text + + + Name of the lock mode held or desired by this process (see and ) + + + + + + granted bool + + + True if lock is held, false if lock is awaited + + + + + + fastpath bool + + + True if lock was taken via fast path, false if taken via main + lock table + + + + +
+ + + granted is true in a row representing a lock + held by the indicated process. False indicates that this process is + currently waiting to acquire this lock, which implies that at least one + other process is holding or waiting for a conflicting lock mode on the same + lockable object. The waiting process will sleep until the other lock is + released (or a deadlock situation is detected). A single process can be + waiting to acquire at most one lock at a time. + + + + Throughout running a transaction, a server process holds an exclusive lock + on the transaction's virtual transaction ID. If a permanent ID is assigned + to the transaction (which normally happens only if the transaction changes + the state of the database), it also holds an exclusive lock on the + transaction's permanent transaction ID until it ends. When a process finds + it necessary to wait specifically for another transaction to end, it does + so by attempting to acquire share lock on the other transaction's ID + (either virtual or permanent ID depending on the situation). That will + succeed only when the other transaction terminates and releases its locks. + + + + Although tuples are a lockable type of object, + information about row-level locks is stored on disk, not in memory, + and therefore row-level locks normally do not appear in this view. + If a process is waiting for a + row-level lock, it will usually appear in the view as waiting for the + permanent transaction ID of the current holder of that row lock. + + + + Advisory locks can be acquired on keys consisting of either a single + bigint value or two integer values. + A bigint key is displayed with its + high-order half in the classid column, its low-order half + in the objid column, and objsubid equal + to 1. The original bigint value can be reassembled with the + expression (classid::bigint << 32) | + objid::bigint. Integer keys are displayed with the + first key in the + classid column, the second key in the objid + column, and objsubid equal to 2. The actual meaning of + the keys is up to the user. Advisory locks are local to each database, + so the database column is meaningful for an advisory lock. + + + + pg_locks provides a global view of all locks + in the database cluster, not only those relevant to the current database. + Although its relation column can be joined + against pg_class.oid to identify locked + relations, this will only work correctly for relations in the current + database (those for which the database column + is either the current database's OID or zero). + + + + The pid column can be joined to the + pid column of the + + pg_stat_activity + view to get more + information on the session holding or awaiting each lock, + for example + +SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa + ON pl.pid = psa.pid; + + Also, if you are using prepared transactions, the + virtualtransaction column can be joined to the + transaction column of the pg_prepared_xacts + view to get more information on prepared transactions that hold locks. + (A prepared transaction can never be waiting for a lock, + but it continues to hold the locks it acquired while running.) + For example: + +SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx + ON pl.virtualtransaction = '-1/' || ppx.transaction; + + + + + While it is possible to obtain information about which processes block + which other processes by joining pg_locks against + itself, this is very difficult to get right in detail. Such a query would + have to encode knowledge about which lock modes conflict with which + others. Worse, the pg_locks view does not expose + information about which processes are ahead of which others in lock wait + queues, nor information about which processes are parallel workers running + on behalf of which other client sessions. It is better to use + the pg_blocking_pids() function + (see ) to identify which + process(es) a waiting process is blocked behind. + + + + The pg_locks view displays data from both the + regular lock manager and the predicate lock manager, which are + separate systems; in addition, the regular lock manager subdivides its + locks into regular and fast-path locks. + This data is not guaranteed to be entirely consistent. + When the view is queried, + data on fast-path locks (with fastpath = true) + is gathered from each backend one at a time, without freezing the state of + the entire lock manager, so it is possible for locks to be taken or + released while information is gathered. Note, however, that these locks are + known not to conflict with any other lock currently in place. After + all backends have been queried for fast-path locks, the remainder of the + regular lock manager is locked as a unit, and a consistent snapshot of all + remaining locks is collected as an atomic action. After unlocking the + regular lock manager, the predicate lock manager is similarly locked and all + predicate locks are collected as an atomic action. Thus, with the exception + of fast-path locks, each lock manager will deliver a consistent set of + results, but as we do not lock both lock managers simultaneously, it is + possible for locks to be taken or released after we interrogate the regular + lock manager and before we interrogate the predicate lock manager. + + + + Locking the regular and/or predicate lock manager could have some + impact on database performance if this view is very frequently accessed. + The locks are held only for the minimum amount of time necessary to + obtain data from the lock managers, but this does not completely eliminate + the possibility of a performance impact. + + + + + + <structname>pg_matviews</structname> + + + pg_matviews + + + + materialized views + + + + The view pg_matviews provides access to + useful information about each materialized view in the database. + + + + <structname>pg_matviews</structname> Columns + + + + + Column Type + + + Description + + + + + + + + schemaname name + (references pg_namespace.nspname) + + + Name of schema containing materialized view + + + + + + matviewname name + (references pg_class.relname) + + + Name of materialized view + + + + + + matviewowner name + (references pg_authid.rolname) + + + Name of materialized view's owner + + + + + + tablespace name + (references pg_tablespace.spcname) + + + Name of tablespace containing materialized view (null if default for database) + + + + + + hasindexes bool + + + True if materialized view has (or recently had) any indexes + + + + + + ispopulated bool + + + True if materialized view is currently populated + + + + + + definition text + + + Materialized view definition (a reconstructed SELECT query) + + + + +
+ + + + + <structname>pg_policies</structname> + + + pg_policies + + + + The view pg_policies provides access to + useful information about each row-level security policy in the database. + + + + <structname>pg_policies</structname> Columns + + + + + Column Type + + + Description + + + + + + + + schemaname name + (references pg_namespace.nspname) + + + Name of schema containing table policy is on + + + + + + tablename name + (references pg_class.relname) + + + Name of table policy is on + + + + + + policyname name + (references pg_policy.polname) + + + Name of policy + + + + + + permissive text + + + Is the policy permissive or restrictive? + + + + + + roles name[] + + + The roles to which this policy applies + + + + + + cmd text + + + The command type to which the policy is applied + + + + + + qual text + + + The expression added to the security barrier qualifications for + queries that this policy applies to + + + + + + with_check text + + + The expression added to the WITH CHECK qualifications for + queries that attempt to add rows to this table + + + + +
+ + + + + <structname>pg_prepared_statements</structname> + + + pg_prepared_statements + + + + The pg_prepared_statements view displays + all the prepared statements that are available in the current + session. See for more information about prepared + statements. + + + + pg_prepared_statements contains one row + for each prepared statement. Rows are added to the view when a new + prepared statement is created and removed when a prepared statement + is released (for example, via the command). + + + + <structname>pg_prepared_statements</structname> Columns + + + + + Column Type + + + Description + + + + + + + + name text + + + The identifier of the prepared statement + + + + + + statement text + + + The query string submitted by the client to create this + prepared statement. For prepared statements created via SQL, + this is the PREPARE statement submitted by + the client. For prepared statements created via the + frontend/backend protocol, this is the text of the prepared + statement itself. + + + + + + prepare_time timestamptz + + + The time at which the prepared statement was created + + + + + + parameter_types regtype[] + + + The expected parameter types for the prepared statement in the + form of an array of regtype. The OID corresponding + to an element of this array can be obtained by casting the + regtype value to oid. + + + + + + from_sql bool + + + true if the prepared statement was created + via the PREPARE SQL command; + false if the statement was prepared via the + frontend/backend protocol + + + + +
+ + + The pg_prepared_statements view is read only. + + + + + <structname>pg_prepared_xacts</structname> + + + pg_prepared_xacts + + + + The view pg_prepared_xacts displays + information about transactions that are currently prepared for two-phase + commit (see for details). + + + + pg_prepared_xacts contains one row per prepared + transaction. An entry is removed when the transaction is committed or + rolled back. + + + + <structname>pg_prepared_xacts</structname> Columns + + + + + Column Type + + + Description + + + + + + + + transaction xid + + + Numeric transaction identifier of the prepared transaction + + + + + + gid text + + + Global transaction identifier that was assigned to the transaction + + + + + + prepared timestamptz + + + Time at which the transaction was prepared for commit + + + + + + owner name + (references pg_authid.rolname) + + + Name of the user that executed the transaction + + + + + + database name + (references pg_database.datname) + + + Name of the database in which the transaction was executed + + + + +
+ + + When the pg_prepared_xacts view is accessed, the + internal transaction manager data structures are momentarily locked, and + a copy is made for the view to display. This ensures that the + view produces a consistent set of results, while not blocking + normal operations longer than necessary. Nonetheless + there could be some impact on database performance if this view is + frequently accessed. + + + + + + <structname>pg_publication_tables</structname> + + + pg_publication_tables + + + + The view pg_publication_tables provides + information about the mapping between publications and the tables they + contain. Unlike the underlying + catalog pg_publication_rel, this view expands + publications defined as FOR ALL TABLES, so for such + publications there will be a row for each eligible table. + + + + <structname>pg_publication_tables</structname> Columns + + + + + Column Type + + + Description + + + + + + + + pubname name + (references pg_publication.pubname) + + + Name of publication + + + + + + schemaname name + (references pg_namespace.nspname) + + + Name of schema containing table + + + + + + tablename name + (references pg_class.relname) + + + Name of table + + + + +
+ + + + <structname>pg_replication_origin_status</structname> + + + pg_replication_origin_status + + + + The pg_replication_origin_status view + contains information about how far replay for a certain origin has + progressed. For more on replication origins + see . + + + + <structname>pg_replication_origin_status</structname> Columns + + + + + Column Type + + + Description + + + + + + + + local_id oid + (references pg_replication_origin.roident) + + + internal node identifier + + + + + + external_id text + (references pg_replication_origin.roname) + + + external node identifier + + + + + + remote_lsn pg_lsn + + + The origin node's LSN up to which data has been replicated. + + + + + + local_lsn pg_lsn + + + This node's LSN at which remote_lsn has + been replicated. Used to flush commit records before persisting + data to disk when using asynchronous commits. + + + + +
+ + + + <structname>pg_replication_slots</structname> + + + pg_replication_slots + + + + The pg_replication_slots view provides a listing + of all replication slots that currently exist on the database cluster, + along with their current state. + + + + For more on replication slots, + see and . + + + + <structname>pg_replication_slots</structname> Columns + + + + + Column Type + + + Description + + + + + + + + slot_name name + + + A unique, cluster-wide identifier for the replication slot + + + + + + plugin name + + + The base name of the shared object containing the output plugin this logical slot is using, or null for physical slots. + + + + + + slot_type text + + + The slot type: physical or logical + + + + + + datoid oid + (references pg_database.oid) + + + The OID of the database this slot is associated with, or + null. Only logical slots have an associated database. + + + + + + database name + (references pg_database.datname) + + + The name of the database this slot is associated with, or + null. Only logical slots have an associated database. + + + + + + temporary bool + + + True if this is a temporary replication slot. Temporary slots are + not saved to disk and are automatically dropped on error or when + the session has finished. + + + + + + active bool + + + True if this slot is currently actively being used + + + + + + active_pid int4 + + + The process ID of the session using this slot if the slot + is currently actively being used. NULL if + inactive. + + + + + + xmin xid + + + The oldest transaction that this slot needs the database to + retain. VACUUM cannot remove tuples deleted + by any later transaction. + + + + + + catalog_xmin xid + + + The oldest transaction affecting the system catalogs that this + slot needs the database to retain. VACUUM cannot + remove catalog tuples deleted by any later transaction. + + + + + + restart_lsn pg_lsn + + + The address (LSN) of oldest WAL which still + might be required by the consumer of this slot and thus won't be + automatically removed during checkpoints unless this LSN + gets behind more than + from the current LSN. NULL + if the LSN of this slot has never been reserved. + + + + + + confirmed_flush_lsn pg_lsn + + + The address (LSN) up to which the logical + slot's consumer has confirmed receiving data. Data older than this is + not available anymore. NULL for physical slots. + + + + + + wal_status text + + + Availability of WAL files claimed by this slot. + Possible values are: + + + reserved means that the claimed files + are within max_wal_size. + + + extended means + that max_wal_size is exceeded but the files are + still retained, either by the replication slot or + by wal_keep_size. + + + + + unreserved means that the slot no longer + retains the required WAL files and some of them are to be removed at + the next checkpoint. This state can return + to reserved or extended. + + + + + lost means that some required WAL files have + been removed and this slot is no longer usable. + + + + The last two states are seen only when + is + non-negative. If restart_lsn is NULL, this + field is null. + + + + + + safe_wal_size int8 + + + The number of bytes that can be written to WAL such that this slot + is not in danger of getting in state "lost". It is NULL for lost + slots, as well as if max_slot_wal_keep_size + is -1. + + + + +
+ + + + <structname>pg_roles</structname> + + + pg_roles + + + + The view pg_roles provides access to + information about database roles. This is simply a publicly + readable view of + pg_authid + that blanks out the password field. + + + + <structname>pg_roles</structname> Columns + + + + + Column Type + + + Description + + + + + + + + rolname name + + + Role name + + + + + + rolsuper bool + + + Role has superuser privileges + + + + + + rolinherit bool + + + Role automatically inherits privileges of roles it is a + member of + + + + + + rolcreaterole bool + + + Role can create more roles + + + + + + rolcreatedb bool + + + Role can create databases + + + + + + rolcanlogin bool + + + Role can log in. That is, this role can be given as the initial + session authorization identifier + + + + + + rolreplication bool + + + Role is a replication role. A replication role can initiate replication + connections and create and drop replication slots. + + + + + + rolconnlimit int4 + + + For roles that can log in, this sets maximum number of concurrent + connections this role can make. -1 means no limit. + + + + + + rolpassword text + + + Not the password (always reads as ********) + + + + + + rolvaliduntil timestamptz + + + Password expiry time (only used for password authentication); + null if no expiration + + + + + + rolbypassrls bool + + + Role bypasses every row level security policy, see + for more information. + + + + + + rolconfig text[] + + + Role-specific defaults for run-time configuration variables + + + + + + oid oid + (references pg_authid.oid) + + + ID of role + + + + +
+ + + + + <structname>pg_rules</structname> + + + pg_rules + + + + The view pg_rules provides access to + useful information about query rewrite rules. + + + + <structname>pg_rules</structname> Columns + + + + + Column Type + + + Description + + + + + + + + schemaname name + (references pg_namespace.nspname) + + + Name of schema containing table + + + + + + tablename name + (references pg_class.relname) + + + Name of table the rule is for + + + + + + rulename name + (references pg_rewrite.rulename) + + + Name of rule + + + + + + definition text + + + Rule definition (a reconstructed creation command) + + + + +
+ + + The pg_rules view excludes the ON SELECT rules + of views and materialized views; those can be seen in + pg_views and pg_matviews. + + + + + + <structname>pg_seclabels</structname> + + + pg_seclabels + + + + The view pg_seclabels provides information about + security labels. It as an easier-to-query version of the + pg_seclabel catalog. + + + + <structname>pg_seclabels</structname> Columns + + + + + Column Type + + + Description + + + + + + + + objoid oid + (references any OID column) + + + The OID of the object this security label pertains to + + + + + + classoid oid + (references pg_class.oid) + + + The OID of the system catalog this object appears in + + + + + + objsubid int4 + + + For a security label on a table column, this is the column number (the + objoid and classoid refer to + the table itself). For all other object types, this column is + zero. + + + + + + objtype text + + + The type of object to which this label applies, as text. + + + + + + objnamespace oid + (references pg_namespace.oid) + + + The OID of the namespace for this object, if applicable; + otherwise NULL. + + + + + + objname text + + + The name of the object to which this label applies, as text. + + + + + + provider text + (references pg_seclabel.provider) + + + The label provider associated with this label. + + + + + + label text + (references pg_seclabel.label) + + + The security label applied to this object. + + + + +
+ + + + <structname>pg_sequences</structname> + + + pg_sequences + + + + The view pg_sequences provides access to + useful information about each sequence in the database. + + + + <structname>pg_sequences</structname> Columns + + + + + Column Type + + + Description + + + + + + + + schemaname name + (references pg_namespace.nspname) + + + Name of schema containing sequence + + + + + + sequencename name + (references pg_class.relname) + + + Name of sequence + + + + + + sequenceowner name + (references pg_authid.rolname) + + + Name of sequence's owner + + + + + + data_type regtype + (references pg_type.oid) + + + Data type of the sequence + + + + + + start_value int8 + + + Start value of the sequence + + + + + + min_value int8 + + + Minimum value of the sequence + + + + + + max_value int8 + + + Maximum value of the sequence + + + + + + increment_by int8 + + + Increment value of the sequence + + + + + + cycle bool + + + Whether the sequence cycles + + + + + + cache_size int8 + + + Cache size of the sequence + + + + + + last_value int8 + + + The last sequence value written to disk. If caching is used, + this value can be greater than the last value handed out from the + sequence. Null if the sequence has not been read from yet. Also, if + the current user does not have USAGE + or SELECT privilege on the sequence, the value is + null. + + + + +
+ + + + <structname>pg_settings</structname> + + + pg_settings + + + + The view pg_settings provides access to + run-time parameters of the server. It is essentially an alternative + interface to the + and commands. + It also provides access to some facts about each parameter that are + not directly available from SHOW, such as minimum and + maximum values. + + + + <structname>pg_settings</structname> Columns + + + + + Column Type + + + Description + + + + + + + + name text + + + Run-time configuration parameter name + + + + + + setting text + + + Current value of the parameter + + + + + + unit text + + + Implicit unit of the parameter + + + + + + category text + + + Logical group of the parameter + + + + + + short_desc text + + + A brief description of the parameter + + + + + + extra_desc text + + + Additional, more detailed, description of the parameter + + + + + + context text + + + Context required to set the parameter's value (see below) + + + + + + vartype text + + + Parameter type (bool, enum, + integer, real, or string) + + + + + + source text + + + Source of the current parameter value + + + + + + min_val text + + + Minimum allowed value of the parameter (null for non-numeric + values) + + + + + + max_val text + + + Maximum allowed value of the parameter (null for non-numeric + values) + + + + + + enumvals text[] + + + Allowed values of an enum parameter (null for non-enum + values) + + + + + + boot_val text + + + Parameter value assumed at server startup if the parameter is + not otherwise set + + + + + + reset_val text + + + Value that RESET would reset the parameter to + in the current session + + + + + + sourcefile text + + + Configuration file the current value was set in (null for + values set from sources other than configuration files, or when + examined by a user who is neither a superuser or a member of + pg_read_all_settings); helpful when using + include directives in configuration files + + + + + + sourceline int4 + + + Line number within the configuration file the current value was + set at (null for values set from sources other than configuration files, + or when examined by a user who is neither a superuser or a member of + pg_read_all_settings). + + + + + + pending_restart bool + + + true if the value has been changed in the + configuration file but needs a restart; or false + otherwise. + + + + +
+ + + There are several possible values of context. + In order of decreasing difficulty of changing the setting, they are: + + + + + + internal + + + These settings cannot be changed directly; they reflect internally + determined values. Some of them may be adjustable by rebuilding the + server with different configuration options, or by changing options + supplied to initdb. + + + + + + postmaster + + + These settings can only be applied when the server starts, so any change + requires restarting the server. Values for these settings are typically + stored in the postgresql.conf file, or passed on + the command line when starting the server. Of course, settings with any + of the lower context types can also be + set at server start time. + + + + + + sighup + + + Changes to these settings can be made in + postgresql.conf without restarting the server. + Send a SIGHUP signal to the postmaster to + cause it to re-read postgresql.conf and apply + the changes. The postmaster will also forward the + SIGHUP signal to its child processes so that + they all pick up the new value. + + + + + + superuser-backend + + + Changes to these settings can be made in + postgresql.conf without restarting the server. + They can also be set for a particular session in the connection request + packet (for example, via libpq's PGOPTIONS + environment variable), but only if the connecting user is a superuser. + However, these settings never change in a session after it is started. + If you change them in postgresql.conf, send a + SIGHUP signal to the postmaster to cause it to + re-read postgresql.conf. The new values will only + affect subsequently-launched sessions. + + + + + + backend + + + Changes to these settings can be made in + postgresql.conf without restarting the server. + They can also be set for a particular session in the connection request + packet (for example, via libpq's PGOPTIONS + environment variable); any user can make such a change for their session. + However, these settings never change in a session after it is started. + If you change them in postgresql.conf, send a + SIGHUP signal to the postmaster to cause it to + re-read postgresql.conf. The new values will only + affect subsequently-launched sessions. + + + + + + superuser + + + These settings can be set from postgresql.conf, + or within a session via the SET command; but only superusers + can change them via SET. Changes in + postgresql.conf will affect existing sessions + only if no session-local value has been established with SET. + + + + + + user + + + These settings can be set from postgresql.conf, + or within a session via the SET command. Any user is + allowed to change their session-local value. Changes in + postgresql.conf will affect existing sessions + only if no session-local value has been established with SET. + + + + + + + See for more information about the various + ways to change these parameters. + + + + The pg_settings view cannot be inserted into or + deleted from, but it can be updated. An UPDATE applied + to a row of pg_settings is equivalent to executing + the command on that named + parameter. The change only affects the value used by the current + session. If an UPDATE is issued within a transaction + that is later aborted, the effects of the UPDATE command + disappear when the transaction is rolled back. Once the surrounding + transaction is committed, the effects will persist until the end of the + session, unless overridden by another UPDATE or + SET. + + + + + + <structname>pg_shadow</structname> + + + pg_shadow + + + + The view pg_shadow exists for backwards + compatibility: it emulates a catalog that existed in + PostgreSQL before version 8.1. + It shows properties of all roles that are marked as + rolcanlogin in + pg_authid. + + + + The name stems from the fact that this table + should not be readable by the public since it contains passwords. + pg_user + is a publicly readable view on + pg_shadow that blanks out the password field. + + + + <structname>pg_shadow</structname> Columns + + + + + Column Type + + + Description + + + + + + + + usename name + (references pg_authid.rolname) + + + User name + + + + + + usesysid oid + (references pg_authid.oid) + + + ID of this user + + + + + + usecreatedb bool + + + User can create databases + + + + + + usesuper bool + + + User is a superuser + + + + + + userepl bool + + + User can initiate streaming replication and put the system in and + out of backup mode. + + + + + + usebypassrls bool + + + User bypasses every row level security policy, see + for more information. + + + + + + passwd text + + + Password (possibly encrypted); null if none. See + pg_authid + for details of how encrypted passwords are stored. + + + + + + valuntil timestamptz + + + Password expiry time (only used for password authentication) + + + + + + useconfig text[] + + + Session defaults for run-time configuration variables + + + + +
+ + + + + <structname>pg_shmem_allocations</structname> + + + pg_shmem_allocations + + + + The pg_shmem_allocations view shows allocations + made from the server's main shared memory segment. This includes both + memory allocated by postgres itself and memory + allocated by extensions using the mechanisms detailed in + . + + + + Note that this view does not include memory allocated using the dynamic + shared memory infrastructure. + + + + <structname>pg_shmem_allocations</structname> Columns + + + + + Column Type + + + Description + + + + + + + + name text + + + The name of the shared memory allocation. NULL for unused memory + and <anonymous> for anonymous + allocations. + + + + + + off int8 + + + The offset at which the allocation starts. NULL for anonymous + allocations, since details related to them are not known. + + + + + + size int8 + + + Size of the allocation + + + + + + allocated_size int8 + + + Size of the allocation including padding. For anonymous + allocations, no information about padding is available, so the + size and allocated_size columns + will always be equal. Padding is not meaningful for free memory, so + the columns will be equal in that case also. + + + + +
+ + + Anonymous allocations are allocations that have been made + with ShmemAlloc() directly, rather than via + ShmemInitStruct() or + ShmemInitHash(). + + + + By default, the pg_shmem_allocations view can be + read only by superusers. + + + + + <structname>pg_stats</structname> + + + pg_stats + + + + The view pg_stats provides access to + the information stored in the pg_statistic + catalog. This view allows access only to rows of + pg_statistic that correspond to tables the + user has permission to read, and therefore it is safe to allow public + read access to this view. + + + + pg_stats is also designed to present the + information in a more readable format than the underlying catalog + — at the cost that its schema must be extended whenever new slot types + are defined for pg_statistic. + + + + <structname>pg_stats</structname> Columns + + + + + Column Type + + + Description + + + + + + + + schemaname name + (references pg_namespace.nspname) + + + Name of schema containing table + + + + + + tablename name + (references pg_class.relname) + + + Name of table + + + + + + attname name + (references pg_attribute.attname) + + + Name of the column described by this row + + + + + + inherited bool + + + If true, this row includes inheritance child columns, not just the + values in the specified table + + + + + + null_frac float4 + + + Fraction of column entries that are null + + + + + + avg_width int4 + + + Average width in bytes of column's entries + + + + + + n_distinct float4 + + + If greater than zero, the estimated number of distinct values in the + column. If less than zero, the negative of the number of distinct + values divided by the number of rows. (The negated form is used when + ANALYZE believes that the number of distinct values is + likely to increase as the table grows; the positive form is used when + the column seems to have a fixed number of possible values.) For + example, -1 indicates a unique column in which the number of distinct + values is the same as the number of rows. + + + + + + most_common_vals anyarray + + + A list of the most common values in the column. (Null if + no values seem to be more common than any others.) + + + + + + most_common_freqs float4[] + + + A list of the frequencies of the most common values, + i.e., number of occurrences of each divided by total number of rows. + (Null when most_common_vals is.) + + + + + + histogram_bounds anyarray + + + A list of values that divide the column's values into groups of + approximately equal population. The values in + most_common_vals, if present, are omitted from this + histogram calculation. (This column is null if the column data type + does not have a < operator or if the + most_common_vals list accounts for the entire + population.) + + + + + + correlation float4 + + + Statistical correlation between physical row ordering and + logical ordering of the column values. This ranges from -1 to +1. + When the value is near -1 or +1, an index scan on the column will + be estimated to be cheaper than when it is near zero, due to reduction + of random access to the disk. (This column is null if the column data + type does not have a < operator.) + + + + + + most_common_elems anyarray + + + A list of non-null element values most often appearing within values of + the column. (Null for scalar types.) + + + + + + most_common_elem_freqs float4[] + + + A list of the frequencies of the most common element values, i.e., the + fraction of rows containing at least one instance of the given value. + Two or three additional values follow the per-element frequencies; + these are the minimum and maximum of the preceding per-element + frequencies, and optionally the frequency of null elements. + (Null when most_common_elems is.) + + + + + + elem_count_histogram float4[] + + + A histogram of the counts of distinct non-null element values within the + values of the column, followed by the average number of distinct + non-null elements. (Null for scalar types.) + + + + +
+ + + The maximum number of entries in the array fields can be controlled on a + column-by-column basis using the ALTER TABLE SET STATISTICS + command, or globally by setting the + run-time parameter. + + + + + + <structname>pg_stats_ext</structname> + + + pg_stats_ext + + + + The view pg_stats_ext provides access to + the information stored in the pg_statistic_ext + and pg_statistic_ext_data + catalogs. This view allows access only to rows of + pg_statistic_ext and pg_statistic_ext_data + that correspond to tables the user has permission to read, and therefore + it is safe to allow public read access to this view. + + + + pg_stats_ext is also designed to present the + information in a more readable format than the underlying catalogs + — at the cost that its schema must be extended whenever new types + of extended statistics are added to pg_statistic_ext. + + + + <structname>pg_stats_ext</structname> Columns + + + + + Column Type + + + Description + + + + + + + + schemaname name + (references pg_namespace.nspname) + + + Name of schema containing table + + + + + + tablename name + (references pg_class.relname) + + + Name of table + + + + + + statistics_schemaname name + (references pg_namespace.nspname) + + + Name of schema containing extended statistic + + + + + + statistics_name name + (references pg_statistic_ext.stxname) + + + Name of extended statistics + + + + + + statistics_owner name + (references pg_authid.rolname) + + + Owner of the extended statistics + + + + + + attnames name[] + (references pg_attribute.attname) + + + Names of the columns the extended statistics is defined on + + + + + + kinds char[] + + + Types of extended statistics enabled for this record + + + + + + n_distinct pg_ndistinct + + + N-distinct counts for combinations of column values. If greater + than zero, the estimated number of distinct values in the combination. + If less than zero, the negative of the number of distinct values divided + by the number of rows. + (The negated form is used when ANALYZE believes that + the number of distinct values is likely to increase as the table grows; + the positive form is used when the column seems to have a fixed number + of possible values.) For example, -1 indicates a unique combination of + columns in which the number of distinct combinations is the same as the + number of rows. + + + + + + dependencies pg_dependencies + + + Functional dependency statistics + + + + + + most_common_vals text[] + + + A list of the most common combinations of values in the columns. + (Null if no combinations seem to be more common than any others.) + + + + + + most_common_val_nulls bool[] + + + A list of NULL flags for the most common combinations of values. + (Null when most_common_vals is.) + + + + + + most_common_freqs float8[] + + + A list of the frequencies of the most common combinations, + i.e., number of occurrences of each divided by total number of rows. + (Null when most_common_vals is.) + + + + + + most_common_base_freqs float8[] + + + A list of the base frequencies of the most common combinations, + i.e., product of per-value frequencies. + (Null when most_common_vals is.) + + + + +
+ + + The maximum number of entries in the array fields can be controlled on a + column-by-column basis using the ALTER TABLE SET STATISTICS + command, or globally by setting the + run-time parameter. + + + + + + <structname>pg_tables</structname> + + + pg_tables + + + + The view pg_tables provides access to + useful information about each table in the database. + + + + <structname>pg_tables</structname> Columns + + + + + Column Type + + + Description + + + + + + + + schemaname name + (references pg_namespace.nspname) + + + Name of schema containing table + + + + + + tablename name + (references pg_class.relname) + + + Name of table + + + + + + tableowner name + (references pg_authid.rolname) + + + Name of table's owner + + + + + + tablespace name + (references pg_tablespace.spcname) + + + Name of tablespace containing table (null if default for database) + + + + + + hasindexes bool + (references pg_class.relhasindex) + + + True if table has (or recently had) any indexes + + + + + + hasrules bool + (references pg_class.relhasrules) + + + True if table has (or once had) rules + + + + + + hastriggers bool + (references pg_class.relhastriggers) + + + True if table has (or once had) triggers + + + + + + rowsecurity bool + (references pg_class.relrowsecurity) + + + True if row security is enabled on the table + + + + +
+ + + + + <structname>pg_timezone_abbrevs</structname> + + + pg_timezone_abbrevs + + + + The view pg_timezone_abbrevs provides a list + of time zone abbreviations that are currently recognized by the datetime + input routines. The contents of this view change when the + run-time parameter is modified. + + + + <structname>pg_timezone_abbrevs</structname> Columns + + + + + Column Type + + + Description + + + + + + + + abbrev text + + + Time zone abbreviation + + + + + + utc_offset interval + + + Offset from UTC (positive means east of Greenwich) + + + + + + is_dst bool + + + True if this is a daylight-savings abbreviation + + + + +
+ + + While most timezone abbreviations represent fixed offsets from UTC, + there are some that have historically varied in value + (see for more information). + In such cases this view presents their current meaning. + + + + + + <structname>pg_timezone_names</structname> + + + pg_timezone_names + + + + The view pg_timezone_names provides a list + of time zone names that are recognized by SET TIMEZONE, + along with their associated abbreviations, UTC offsets, + and daylight-savings status. (Technically, + PostgreSQL does not use UTC because leap + seconds are not handled.) + Unlike the abbreviations shown in pg_timezone_abbrevs, many of these names imply a set of daylight-savings transition + date rules. Therefore, the associated information changes across local DST + boundaries. The displayed information is computed based on the current + value of CURRENT_TIMESTAMP. + + + + <structname>pg_timezone_names</structname> Columns + + + + + Column Type + + + Description + + + + + + + + name text + + + Time zone name + + + + + + abbrev text + + + Time zone abbreviation + + + + + + utc_offset interval + + + Offset from UTC (positive means east of Greenwich) + + + + + + is_dst bool + + + True if currently observing daylight savings + + + + +
+ + + + + <structname>pg_user</structname> + + + pg_user + + + + The view pg_user provides access to + information about database users. This is simply a publicly + readable view of + pg_shadow + that blanks out the password field. + + + + <structname>pg_user</structname> Columns + + + + + Column Type + + + Description + + + + + + + + usename name + + + User name + + + + + + usesysid oid + + + ID of this user + + + + + + usecreatedb bool + + + User can create databases + + + + + + usesuper bool + + + User is a superuser + + + + + + userepl bool + + + User can initiate streaming replication and put the system in and + out of backup mode. + + + + + + usebypassrls bool + + + User bypasses every row level security policy, see + for more information. + + + + + + passwd text + + + Not the password (always reads as ********) + + + + + + valuntil timestamptz + + + Password expiry time (only used for password authentication) + + + + + + useconfig text[] + + + Session defaults for run-time configuration variables + + + + +
+ + + + + <structname>pg_user_mappings</structname> + + + pg_user_mappings + + + + The view pg_user_mappings provides access + to information about user mappings. This is essentially a publicly + readable view of + pg_user_mapping + that leaves out the options field if the user has no rights to use + it. + + + + <structname>pg_user_mappings</structname> Columns + + + + + Column Type + + + Description + + + + + + + + umid oid + (references pg_user_mapping.oid) + + + OID of the user mapping + + + + + + srvid oid + (references pg_foreign_server.oid) + + + The OID of the foreign server that contains this mapping + + + + + + srvname name + (references pg_foreign_server.srvname) + + + Name of the foreign server + + + + + + umuser oid + (references pg_authid.oid) + + + OID of the local role being mapped, 0 if the user mapping is public + + + + + + usename name + + + Name of the local user to be mapped + + + + + + umoptions text[] + + + User mapping specific options, as keyword=value strings + + + + +
+ + + To protect password information stored as a user mapping option, + the umoptions column will read as null + unless one of the following applies: + + + + current user is the user being mapped, and owns the server or + holds USAGE privilege on it + + + + + current user is the server owner and mapping is for PUBLIC + + + + + current user is a superuser + + + + + + + + + + <structname>pg_views</structname> + + + pg_views + + + + The view pg_views provides access to + useful information about each view in the database. + + + + <structname>pg_views</structname> Columns + + + + + Column Type + + + Description + + + + + + + + schemaname name + (references pg_namespace.nspname) + + + Name of schema containing view + + + + + + viewname name + (references pg_class.relname) + + + Name of view + + + + + + viewowner name + (references pg_authid.rolname) + + + Name of view's owner + + + + + + definition text + + + View definition (a reconstructed SELECT query) + + + + +
+ + + + -- cgit v1.2.3