1 <!-- doc/src/sgml/catalogs.sgml -->
3 Documentation of the system catalogs, directed toward PostgreSQL developers
6 <chapter id="catalogs">
7 <title>System Catalogs</title>
10 The system catalogs are the place where a relational database
11 management system stores schema metadata, such as information about
12 tables and columns, and internal bookkeeping information.
13 <productname>PostgreSQL</productname>'s system catalogs are regular
14 tables. You can drop and recreate the tables, add columns, insert
15 and update values, and severely mess up your system that way.
16 Normally, one should not change the system catalogs by hand, there
17 are always SQL commands to do that. (For example, <command>CREATE
18 DATABASE</command> inserts a row into the
19 <structname>pg_database</structname> catalog — and actually
20 creates the database on disk.) There are some exceptions for
21 particularly esoteric operations, such as adding index access methods.
24 <sect1 id="catalogs-overview">
25 <title>Overview</title>
28 <xref linkend="catalog-table"> lists the system catalogs.
29 More detailed documentation of each catalog follows below.
33 Most system catalogs are copied from the template database during
34 database creation and are thereafter database-specific. A few
35 catalogs are physically shared across all databases in a cluster;
36 these are noted in the descriptions of the individual catalogs.
39 <table id="catalog-table">
40 <title>System Catalogs</title>
45 <entry>Catalog Name</entry>
46 <entry>Purpose</entry>
52 <entry><link linkend="catalog-pg-aggregate"><structname>pg_aggregate</structname></link></entry>
53 <entry>aggregate functions</entry>
57 <entry><link linkend="catalog-pg-am"><structname>pg_am</structname></link></entry>
58 <entry>index access methods</entry>
62 <entry><link linkend="catalog-pg-amop"><structname>pg_amop</structname></link></entry>
63 <entry>access method operators</entry>
67 <entry><link linkend="catalog-pg-amproc"><structname>pg_amproc</structname></link></entry>
68 <entry>access method support procedures</entry>
72 <entry><link linkend="catalog-pg-attrdef"><structname>pg_attrdef</structname></link></entry>
73 <entry>column default values</entry>
77 <entry><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link></entry>
78 <entry>table columns (<quote>attributes</quote>)</entry>
82 <entry><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link></entry>
83 <entry>authorization identifiers (roles)</entry>
87 <entry><link linkend="catalog-pg-auth-members"><structname>pg_auth_members</structname></link></entry>
88 <entry>authorization identifier membership relationships</entry>
92 <entry><link linkend="catalog-pg-cast"><structname>pg_cast</structname></link></entry>
93 <entry>casts (data type conversions)</entry>
97 <entry><link linkend="catalog-pg-class"><structname>pg_class</structname></link></entry>
98 <entry>tables, indexes, sequences, views (<quote>relations</quote>)</entry>
102 <entry><link linkend="catalog-pg-constraint"><structname>pg_constraint</structname></link></entry>
103 <entry>check constraints, unique constraints, primary key constraints, foreign key constraints</entry>
107 <entry><link linkend="catalog-pg-collation"><structname>pg_collation</structname></link></entry>
108 <entry>collations (locale information)</entry>
112 <entry><link linkend="catalog-pg-conversion"><structname>pg_conversion</structname></link></entry>
113 <entry>encoding conversion information</entry>
117 <entry><link linkend="catalog-pg-database"><structname>pg_database</structname></link></entry>
118 <entry>databases within this database cluster</entry>
122 <entry><link linkend="catalog-pg-db-role-setting"><structname>pg_db_role_setting</structname></link></entry>
123 <entry>per-role and per-database settings</entry>
127 <entry><link linkend="catalog-pg-default-acl"><structname>pg_default_acl</structname></link></entry>
128 <entry>default privileges for object types</entry>
132 <entry><link linkend="catalog-pg-depend"><structname>pg_depend</structname></link></entry>
133 <entry>dependencies between database objects</entry>
137 <entry><link linkend="catalog-pg-description"><structname>pg_description</structname></link></entry>
138 <entry>descriptions or comments on database objects</entry>
142 <entry><link linkend="catalog-pg-enum"><structname>pg_enum</structname></link></entry>
143 <entry>enum label and value definitions</entry>
147 <entry><link linkend="catalog-pg-extension"><structname>pg_extension</structname></link></entry>
148 <entry>installed extensions</entry>
152 <entry><link linkend="catalog-pg-foreign-data-wrapper"><structname>pg_foreign_data_wrapper</structname></link></entry>
153 <entry>foreign-data wrapper definitions</entry>
157 <entry><link linkend="catalog-pg-foreign-server"><structname>pg_foreign_server</structname></link></entry>
158 <entry>foreign server definitions</entry>
162 <entry><link linkend="catalog-pg-foreign-table"><structname>pg_foreign_table</structname></link></entry>
163 <entry>additional foreign table information</entry>
167 <entry><link linkend="catalog-pg-index"><structname>pg_index</structname></link></entry>
168 <entry>additional index information</entry>
172 <entry><link linkend="catalog-pg-inherits"><structname>pg_inherits</structname></link></entry>
173 <entry>table inheritance hierarchy</entry>
177 <entry><link linkend="catalog-pg-language"><structname>pg_language</structname></link></entry>
178 <entry>languages for writing functions</entry>
182 <entry><link linkend="catalog-pg-largeobject"><structname>pg_largeobject</structname></link></entry>
183 <entry>data pages for large objects</entry>
187 <entry><link linkend="catalog-pg-largeobject-metadata"><structname>pg_largeobject_metadata</structname></link></entry>
188 <entry>metadata for large objects</entry>
192 <entry><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link></entry>
193 <entry>schemas</entry>
197 <entry><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link></entry>
198 <entry>access method operator classes</entry>
202 <entry><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link></entry>
203 <entry>operators</entry>
207 <entry><link linkend="catalog-pg-opfamily"><structname>pg_opfamily</structname></link></entry>
208 <entry>access method operator families</entry>
212 <entry><link linkend="catalog-pg-pltemplate"><structname>pg_pltemplate</structname></link></entry>
213 <entry>template data for procedural languages</entry>
217 <entry><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link></entry>
218 <entry>functions and procedures</entry>
222 <entry><link linkend="catalog-pg-rewrite"><structname>pg_rewrite</structname></link></entry>
223 <entry>query rewrite rules</entry>
227 <entry><link linkend="catalog-pg-seclabel"><structname>pg_seclabel</structname></link></entry>
228 <entry>security labels on database objects</entry>
232 <entry><link linkend="catalog-pg-shdepend"><structname>pg_shdepend</structname></link></entry>
233 <entry>dependencies on shared objects</entry>
237 <entry><link linkend="catalog-pg-shdescription"><structname>pg_shdescription</structname></link></entry>
238 <entry>comments on shared objects</entry>
242 <entry><link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link></entry>
243 <entry>planner statistics</entry>
247 <entry><link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link></entry>
248 <entry>tablespaces within this database cluster</entry>
252 <entry><link linkend="catalog-pg-trigger"><structname>pg_trigger</structname></link></entry>
253 <entry>triggers</entry>
257 <entry><link linkend="catalog-pg-ts-config"><structname>pg_ts_config</structname></link></entry>
258 <entry>text search configurations</entry>
262 <entry><link linkend="catalog-pg-ts-config-map"><structname>pg_ts_config_map</structname></link></entry>
263 <entry>text search configurations' token mappings</entry>
267 <entry><link linkend="catalog-pg-ts-dict"><structname>pg_ts_dict</structname></link></entry>
268 <entry>text search dictionaries</entry>
272 <entry><link linkend="catalog-pg-ts-parser"><structname>pg_ts_parser</structname></link></entry>
273 <entry>text search parsers</entry>
277 <entry><link linkend="catalog-pg-ts-template"><structname>pg_ts_template</structname></link></entry>
278 <entry>text search templates</entry>
282 <entry><link linkend="catalog-pg-type"><structname>pg_type</structname></link></entry>
283 <entry>data types</entry>
287 <entry><link linkend="catalog-pg-user-mapping"><structname>pg_user_mapping</structname></link></entry>
288 <entry>mappings of users to foreign servers</entry>
296 <sect1 id="catalog-pg-aggregate">
297 <title><structname>pg_aggregate</structname></title>
299 <indexterm zone="catalog-pg-aggregate">
300 <primary>pg_aggregate</primary>
304 The catalog <structname>pg_aggregate</structname> stores information about
305 aggregate functions. An aggregate function is a function that
306 operates on a set of values (typically one column from each row
307 that matches a query condition) and returns a single value computed
308 from all these values. Typical aggregate functions are
309 <function>sum</function>, <function>count</function>, and
310 <function>max</function>. Each entry in
311 <structname>pg_aggregate</structname> is an extension of an entry
312 in <structname>pg_proc</structname>. The <structname>pg_proc</structname>
313 entry carries the aggregate's name, input and output data types, and
314 other information that is similar to ordinary functions.
318 <title><structname>pg_aggregate</> Columns</title>
325 <entry>References</entry>
326 <entry>Description</entry>
331 <entry><structfield>aggfnoid</structfield></entry>
332 <entry><type>regproc</type></entry>
333 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
334 <entry><structname>pg_proc</structname> OID of the aggregate function</entry>
337 <entry><structfield>aggtransfn</structfield></entry>
338 <entry><type>regproc</type></entry>
339 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
340 <entry>Transition function</entry>
343 <entry><structfield>aggfinalfn</structfield></entry>
344 <entry><type>regproc</type></entry>
345 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
346 <entry>Final function (zero if none)</entry>
349 <entry><structfield>aggsortop</structfield></entry>
350 <entry><type>oid</type></entry>
351 <entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</literal></entry>
352 <entry>Associated sort operator (zero if none)</entry>
355 <entry><structfield>aggtranstype</structfield></entry>
356 <entry><type>oid</type></entry>
357 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
358 <entry>Data type of the aggregate function's internal transition (state) data</entry>
361 <entry><structfield>agginitval</structfield></entry>
362 <entry><type>text</type></entry>
365 The initial value of the transition state. This is a text
366 field containing the initial value in its external string
367 representation. If this field is null, the transition state
368 value starts out null.
376 New aggregate functions are registered with the <xref
377 linkend="sql-createaggregate">
378 command. See <xref linkend="xaggr"> for more information about
379 writing aggregate functions and the meaning of the transition
386 <sect1 id="catalog-pg-am">
387 <title><structname>pg_am</structname></title>
389 <indexterm zone="catalog-pg-am">
390 <primary>pg_am</primary>
394 The catalog <structname>pg_am</structname> stores information about index
395 access methods. There is one row for each index access method supported by
396 the system. The contents of this catalog are discussed in detail in
397 <xref linkend="indexam">.
401 <title><structname>pg_am</> Columns</title>
408 <entry>References</entry>
409 <entry>Description</entry>
415 <entry><structfield>amname</structfield></entry>
416 <entry><type>name</type></entry>
418 <entry>Name of the access method</entry>
422 <entry><structfield>amstrategies</structfield></entry>
423 <entry><type>int2</type></entry>
425 <entry>Number of operator strategies for this access method,
426 or zero if access method does not have a fixed set of operator
431 <entry><structfield>amsupport</structfield></entry>
432 <entry><type>int2</type></entry>
434 <entry>Number of support routines for this access method</entry>
438 <entry><structfield>amcanorder</structfield></entry>
439 <entry><type>bool</type></entry>
441 <entry>Does the access method support ordered scans sorted by the
442 indexed column's value?</entry>
446 <entry><structfield>amcanorderbyop</structfield></entry>
447 <entry><type>bool</type></entry>
449 <entry>Does the access method support ordered scans sorted by the result
450 of an operator on the indexed column?</entry>
454 <entry><structfield>amcanbackward</structfield></entry>
455 <entry><type>bool</type></entry>
457 <entry>Does the access method support backward scanning?</entry>
461 <entry><structfield>amcanunique</structfield></entry>
462 <entry><type>bool</type></entry>
464 <entry>Does the access method support unique indexes?</entry>
468 <entry><structfield>amcanmulticol</structfield></entry>
469 <entry><type>bool</type></entry>
471 <entry>Does the access method support multicolumn indexes?</entry>
475 <entry><structfield>amoptionalkey</structfield></entry>
476 <entry><type>bool</type></entry>
478 <entry>Does the access method support a scan without any constraint
479 for the first index column?</entry>
483 <entry><structfield>amsearchnulls</structfield></entry>
484 <entry><type>bool</type></entry>
486 <entry>Does the access method support <literal>IS NULL</>/<literal>NOT NULL</> searches?</entry>
490 <entry><structfield>amstorage</structfield></entry>
491 <entry><type>bool</type></entry>
493 <entry>Can index storage data type differ from column data type?</entry>
497 <entry><structfield>amclusterable</structfield></entry>
498 <entry><type>bool</type></entry>
500 <entry>Can an index of this type be clustered on?</entry>
504 <entry><structfield>ampredlocks</structfield></entry>
505 <entry><type>bool</type></entry>
507 <entry>Does an index of this type manage fine-grained predicate locks?</entry>
511 <entry><structfield>amkeytype</structfield></entry>
512 <entry><type>oid</type></entry>
513 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
514 <entry>Type of data stored in index, or zero if not a fixed type</entry>
518 <entry><structfield>aminsert</structfield></entry>
519 <entry><type>regproc</type></entry>
520 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
521 <entry><quote>Insert this tuple</quote> function</entry>
525 <entry><structfield>ambeginscan</structfield></entry>
526 <entry><type>regproc</type></entry>
527 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
528 <entry><quote>Prepare for index scan</quote> function</entry>
532 <entry><structfield>amgettuple</structfield></entry>
533 <entry><type>regproc</type></entry>
534 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
535 <entry><quote>Next valid tuple</quote> function, or zero if none</entry>
539 <entry><structfield>amgetbitmap</structfield></entry>
540 <entry><type>regproc</type></entry>
541 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
542 <entry><quote>Fetch all valid tuples</quote> function, or zero if none</entry>
546 <entry><structfield>amrescan</structfield></entry>
547 <entry><type>regproc</type></entry>
548 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
549 <entry><quote>(Re)start index scan</quote> function</entry>
553 <entry><structfield>amendscan</structfield></entry>
554 <entry><type>regproc</type></entry>
555 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
556 <entry><quote>Clean up after index scan</quote> function</entry>
560 <entry><structfield>ammarkpos</structfield></entry>
561 <entry><type>regproc</type></entry>
562 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
563 <entry><quote>Mark current scan position</quote> function</entry>
567 <entry><structfield>amrestrpos</structfield></entry>
568 <entry><type>regproc</type></entry>
569 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
570 <entry><quote>Restore marked scan position</quote> function</entry>
574 <entry><structfield>ambuild</structfield></entry>
575 <entry><type>regproc</type></entry>
576 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
577 <entry><quote>Build new index</quote> function</entry>
581 <entry><structfield>ambuildempty</structfield></entry>
582 <entry><type>regproc</type></entry>
583 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
584 <entry><quote>Build empty index</quote> function</entry>
588 <entry><structfield>ambulkdelete</structfield></entry>
589 <entry><type>regproc</type></entry>
590 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
591 <entry>Bulk-delete function</entry>
595 <entry><structfield>amvacuumcleanup</structfield></entry>
596 <entry><type>regproc</type></entry>
597 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
598 <entry>Post-<command>VACUUM</command> cleanup function</entry>
602 <entry><structfield>amcostestimate</structfield></entry>
603 <entry><type>regproc</type></entry>
604 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
605 <entry>Function to estimate cost of an index scan</entry>
609 <entry><structfield>amoptions</structfield></entry>
610 <entry><type>regproc</type></entry>
611 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
612 <entry>Function to parse and validate <structfield>reloptions</> for an index</entry>
622 <sect1 id="catalog-pg-amop">
623 <title><structname>pg_amop</structname></title>
625 <indexterm zone="catalog-pg-amop">
626 <primary>pg_amop</primary>
630 The catalog <structname>pg_amop</structname> stores information about
631 operators associated with access method operator families. There is one
632 row for each operator that is a member of an operator family. A family
633 member can be either a <firstterm>search</> operator or an
634 <firstterm>ordering</> operator. An operator
635 can appear in more than one family, but cannot appear in more than one
636 search position nor more than one ordering position within a family.
637 (It is allowed, though unlikely, for an operator to be used for both
638 search and ordering purposes.)
642 <title><structname>pg_amop</> Columns</title>
649 <entry>References</entry>
650 <entry>Description</entry>
656 <entry><structfield>amopfamily</structfield></entry>
657 <entry><type>oid</type></entry>
658 <entry><literal><link linkend="catalog-pg-opfamily"><structname>pg_opfamily</structname></link>.oid</literal></entry>
659 <entry>The operator family this entry is for</entry>
663 <entry><structfield>amoplefttype</structfield></entry>
664 <entry><type>oid</type></entry>
665 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
666 <entry>Left-hand input data type of operator</entry>
670 <entry><structfield>amoprighttype</structfield></entry>
671 <entry><type>oid</type></entry>
672 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
673 <entry>Right-hand input data type of operator</entry>
677 <entry><structfield>amopstrategy</structfield></entry>
678 <entry><type>int2</type></entry>
680 <entry>Operator strategy number</entry>
684 <entry><structfield>amoppurpose</structfield></entry>
685 <entry><type>char</type></entry>
687 <entry>Operator purpose, either <literal>s</> for search or
688 <literal>o</> for ordering</entry>
692 <entry><structfield>amopopr</structfield></entry>
693 <entry><type>oid</type></entry>
694 <entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</literal></entry>
695 <entry>OID of the operator</entry>
699 <entry><structfield>amopmethod</structfield></entry>
700 <entry><type>oid</type></entry>
701 <entry><literal><link linkend="catalog-pg-am"><structname>pg_am</structname></link>.oid</literal></entry>
702 <entry>Index access method operator family is for</entry>
706 <entry><structfield>amopsortfamily</structfield></entry>
707 <entry><type>oid</type></entry>
708 <entry><literal><link linkend="catalog-pg-opfamily"><structname>pg_opfamily</structname></link>.oid</literal></entry>
709 <entry>The btree operator family this entry sorts according to, if an
710 ordering operator; zero if a search operator</entry>
718 A <quote>search</> operator entry indicates that an index of this operator
719 family can be searched to find all rows satisfying
721 <replaceable>indexed_column</>
722 <replaceable>operator</>
723 <replaceable>constant</>.
724 Obviously, such an operator must return boolean, and its left-hand input
725 type must match the index's column data type.
729 An <quote>ordering</> operator entry indicates that an index of this
730 operator family can be scanned to return rows in the order represented by
732 <replaceable>indexed_column</>
733 <replaceable>operator</>
734 <replaceable>constant</>.
735 Such an operator could return any sortable data type, though again
736 its left-hand input type must match the index's column data type.
737 The exact semantics of the <literal>ORDER BY</> are specified by the
738 <structfield>amopsortfamily</structfield> column, which must reference
739 a btree operator family for the operator's result type.
744 At present, it's assumed that the sort order for an ordering operator
745 is the default for the referenced opfamily, i.e., <literal>ASC NULLS
746 LAST</>. This might someday be relaxed by adding additional columns
747 to specify sort options explicitly.
752 An entry's <structfield>amopmethod</> must match the
753 <structname>opfmethod</> of its containing operator family (including
754 <structfield>amopmethod</> here is an intentional denormalization of the
755 catalog structure for performance reasons). Also,
756 <structfield>amoplefttype</> and <structfield>amoprighttype</> must match
757 the <structfield>oprleft</> and <structfield>oprright</> fields of the
758 referenced <structname>pg_operator</> entry.
764 <sect1 id="catalog-pg-amproc">
765 <title><structname>pg_amproc</structname></title>
767 <indexterm zone="catalog-pg-amproc">
768 <primary>pg_amproc</primary>
772 The catalog <structname>pg_amproc</structname> stores information about
773 support procedures associated with access method operator families. There
774 is one row for each support procedure belonging to an operator family.
778 <title><structname>pg_amproc</structname> Columns</title>
785 <entry>References</entry>
786 <entry>Description</entry>
792 <entry><structfield>amprocfamily</structfield></entry>
793 <entry><type>oid</type></entry>
794 <entry><literal><link linkend="catalog-pg-opfamily"><structname>pg_opfamily</structname></link>.oid</literal></entry>
795 <entry>The operator family this entry is for</entry>
799 <entry><structfield>amproclefttype</structfield></entry>
800 <entry><type>oid</type></entry>
801 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
802 <entry>Left-hand input data type of associated operator</entry>
806 <entry><structfield>amprocrighttype</structfield></entry>
807 <entry><type>oid</type></entry>
808 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
809 <entry>Right-hand input data type of associated operator</entry>
813 <entry><structfield>amprocnum</structfield></entry>
814 <entry><type>int2</type></entry>
816 <entry>Support procedure number</entry>
820 <entry><structfield>amproc</structfield></entry>
821 <entry><type>regproc</type></entry>
822 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
823 <entry>OID of the procedure</entry>
831 The usual interpretation of the
832 <structfield>amproclefttype</> and <structfield>amprocrighttype</> fields
833 is that they identify the left and right input types of the operator(s)
834 that a particular support procedure supports. For some access methods
835 these match the input data type(s) of the support procedure itself, for
836 others not. There is a notion of <quote>default</> support procedures for
837 an index, which are those with <structfield>amproclefttype</> and
838 <structfield>amprocrighttype</> both equal to the index opclass's
839 <structfield>opcintype</>.
845 <sect1 id="catalog-pg-attrdef">
846 <title><structname>pg_attrdef</structname></title>
848 <indexterm zone="catalog-pg-attrdef">
849 <primary>pg_attrdef</primary>
853 The catalog <structname>pg_attrdef</structname> stores column default values. The main information
854 about columns is stored in <structname>pg_attribute</structname>
855 (see below). Only columns that explicitly specify a default value
856 (when the table is created or the column is added) will have an
861 <title><structname>pg_attrdef</> Columns</title>
868 <entry>References</entry>
869 <entry>Description</entry>
875 <entry><structfield>adrelid</structfield></entry>
876 <entry><type>oid</type></entry>
877 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
878 <entry>The table this column belongs to</entry>
882 <entry><structfield>adnum</structfield></entry>
883 <entry><type>int2</type></entry>
884 <entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</literal></entry>
885 <entry>The number of the column</entry>
889 <entry><structfield>adbin</structfield></entry>
890 <entry><type>pg_node_tree</type></entry>
892 <entry>The internal representation of the column default value</entry>
896 <entry><structfield>adsrc</structfield></entry>
897 <entry><type>text</type></entry>
899 <entry>A human-readable representation of the default value</entry>
906 The <structfield>adsrc</structfield> field is historical, and is best
907 not used, because it does not track outside changes that might affect
908 the representation of the default value. Reverse-compiling the
909 <structfield>adbin</structfield> field (with <function>pg_get_expr</> for
910 example) is a better way to display the default value.
916 <sect1 id="catalog-pg-attribute">
917 <title><structname>pg_attribute</structname></title>
919 <indexterm zone="catalog-pg-attribute">
920 <primary>pg_attribute</primary>
924 The catalog <structname>pg_attribute</structname> stores information about
925 table columns. There will be exactly one
926 <structname>pg_attribute</structname> row for every column in every
927 table in the database. (There will also be attribute entries for
928 indexes, and indeed all objects that have <structname>pg_class</structname>
933 The term attribute is equivalent to column and is used for
938 <title><structname>pg_attribute</> Columns</title>
945 <entry>References</entry>
946 <entry>Description</entry>
952 <entry><structfield>attrelid</structfield></entry>
953 <entry><type>oid</type></entry>
954 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
955 <entry>The table this column belongs to</entry>
959 <entry><structfield>attname</structfield></entry>
960 <entry><type>name</type></entry>
962 <entry>The column name</entry>
966 <entry><structfield>atttypid</structfield></entry>
967 <entry><type>oid</type></entry>
968 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
969 <entry>The data type of this column</entry>
973 <entry><structfield>attstattarget</structfield></entry>
974 <entry><type>int4</type></entry>
977 <structfield>attstattarget</structfield> controls the level of detail
978 of statistics accumulated for this column by
979 <xref linkend="sql-analyze">.
980 A zero value indicates that no statistics should be collected.
981 A negative value says to use the system default statistics target.
982 The exact meaning of positive values is data type-dependent.
983 For scalar data types, <structfield>attstattarget</structfield>
984 is both the target number of <quote>most common values</quote>
985 to collect, and the target number of histogram bins to create.
990 <entry><structfield>attlen</structfield></entry>
991 <entry><type>int2</type></entry>
994 A copy of <literal>pg_type.typlen</literal> of this column's
1000 <entry><structfield>attnum</structfield></entry>
1001 <entry><type>int2</type></entry>
1004 The number of the column. Ordinary columns are numbered from 1
1005 up. System columns, such as <structfield>oid</structfield>,
1006 have (arbitrary) negative numbers.
1011 <entry><structfield>attndims</structfield></entry>
1012 <entry><type>int4</type></entry>
1015 Number of dimensions, if the column is an array type; otherwise 0.
1016 (Presently, the number of dimensions of an array is not enforced,
1017 so any nonzero value effectively means <quote>it's an array</>.)
1022 <entry><structfield>attcacheoff</structfield></entry>
1023 <entry><type>int4</type></entry>
1026 Always -1 in storage, but when loaded into a row descriptor
1027 in memory this might be updated to cache the offset of the attribute
1033 <entry><structfield>atttypmod</structfield></entry>
1034 <entry><type>int4</type></entry>
1037 <structfield>atttypmod</structfield> records type-specific data
1038 supplied at table creation time (for example, the maximum
1039 length of a <type>varchar</type> column). It is passed to
1040 type-specific input functions and length coercion functions.
1041 The value will generally be -1 for types that do not need <structfield>atttypmod</>.
1046 <entry><structfield>attbyval</structfield></entry>
1047 <entry><type>bool</type></entry>
1050 A copy of <literal>pg_type.typbyval</> of this column's type
1055 <entry><structfield>attstorage</structfield></entry>
1056 <entry><type>char</type></entry>
1059 Normally a copy of <literal>pg_type.typstorage</> of this
1060 column's type. For TOAST-able data types, this can be altered
1061 after column creation to control storage policy.
1066 <entry><structfield>attalign</structfield></entry>
1067 <entry><type>char</type></entry>
1070 A copy of <literal>pg_type.typalign</> of this column's type
1075 <entry><structfield>attnotnull</structfield></entry>
1076 <entry><type>bool</type></entry>
1079 This represents a not-null constraint. It is possible to
1080 change this column to enable or disable the constraint.
1085 <entry><structfield>atthasdef</structfield></entry>
1086 <entry><type>bool</type></entry>
1089 This column has a default value, in which case there will be a
1090 corresponding entry in the <structname>pg_attrdef</structname>
1091 catalog that actually defines the value.
1096 <entry><structfield>attisdropped</structfield></entry>
1097 <entry><type>bool</type></entry>
1100 This column has been dropped and is no longer valid. A dropped
1101 column is still physically present in the table, but is
1102 ignored by the parser and so cannot be accessed via SQL.
1107 <entry><structfield>attislocal</structfield></entry>
1108 <entry><type>bool</type></entry>
1111 This column is defined locally in the relation. Note that a column can
1112 be locally defined and inherited simultaneously.
1117 <entry><structfield>attinhcount</structfield></entry>
1118 <entry><type>int4</type></entry>
1121 The number of direct ancestors this column has. A column with a
1122 nonzero number of ancestors cannot be dropped nor renamed.
1127 <entry><structfield>attcollation</structfield></entry>
1128 <entry><type>oid</type></entry>
1129 <entry><literal><link linkend="catalog-pg-collation"><structname>pg_collation</structname></link>.oid</literal></entry>
1131 The defined collation of the column, or zero if the column is
1132 not of a collatable datatype.
1137 <entry><structfield>attacl</structfield></entry>
1138 <entry><type>aclitem[]</type></entry>
1141 Column-level access privileges, if any have been granted specifically
1147 <entry><structfield>attoptions</structfield></entry>
1148 <entry><type>text[]</type></entry>
1151 Attribute-level options, as <quote>keyword=value</> strings
1160 In a dropped column's <structname>pg_attribute</structname> entry,
1161 <structfield>atttypid</structfield> is reset to zero, but
1162 <structfield>attlen</structfield> and the other fields copied from
1163 <structname>pg_type</> are still valid. This arrangement is needed
1164 to cope with the situation where the dropped column's data type was
1165 later dropped, and so there is no <structname>pg_type</> row anymore.
1166 <structfield>attlen</structfield> and the other fields can be used
1167 to interpret the contents of a row of the table.
1172 <sect1 id="catalog-pg-authid">
1173 <title><structname>pg_authid</structname></title>
1175 <indexterm zone="catalog-pg-authid">
1176 <primary>pg_authid</primary>
1180 The catalog <structname>pg_authid</structname> contains information about
1181 database authorization identifiers (roles). A role subsumes the concepts
1182 of <quote>users</> and <quote>groups</>. A user is essentially just a
1183 role with the <structfield>rolcanlogin</> flag set. Any role (with or
1184 without <structfield>rolcanlogin</>) can have other roles as members; see
1185 <link linkend="catalog-pg-auth-members"><structname>pg_auth_members</structname></link>.
1189 Since this catalog contains passwords, it must not be publicly readable.
1190 <link linkend="view-pg-roles"><structname>pg_roles</structname></link>
1191 is a publicly readable view on
1192 <structname>pg_authid</structname> that blanks out the password field.
1196 <xref linkend="user-manag"> contains detailed information about user and
1197 privilege management.
1201 Because user identities are cluster-wide,
1202 <structname>pg_authid</structname>
1203 is shared across all databases of a cluster: there is only one
1204 copy of <structname>pg_authid</structname> per cluster, not
1209 <title><structname>pg_authid</> Columns</title>
1216 <entry>Description</entry>
1222 <entry><structfield>rolname</structfield></entry>
1223 <entry><type>name</type></entry>
1224 <entry>Role name</entry>
1228 <entry><structfield>rolsuper</structfield></entry>
1229 <entry><type>bool</type></entry>
1230 <entry>Role has superuser privileges</entry>
1234 <entry><structfield>rolinherit</structfield></entry>
1235 <entry><type>bool</type></entry>
1236 <entry>Role automatically inherits privileges of roles it is a
1241 <entry><structfield>rolcreaterole</structfield></entry>
1242 <entry><type>bool</type></entry>
1243 <entry>Role can create more roles</entry>
1247 <entry><structfield>rolcreatedb</structfield></entry>
1248 <entry><type>bool</type></entry>
1249 <entry>Role can create databases</entry>
1253 <entry><structfield>rolcatupdate</structfield></entry>
1254 <entry><type>bool</type></entry>
1256 Role can update system catalogs directly. (Even a superuser cannot do
1257 this unless this column is true)
1262 <entry><structfield>rolcanlogin</structfield></entry>
1263 <entry><type>bool</type></entry>
1265 Role can log in. That is, this role can be given as the initial
1266 session authorization identifier.
1271 <entry><structfield>rolreplication</structfield></entry>
1272 <entry><type>bool</type></entry>
1274 Role is a replication role. That is, this role can initiate streaming
1275 replication (see <xref linkend="streaming-replication">) and set/unset
1276 the system backup mode using <function>pg_start_backup</> and
1277 <function>pg_stop_backup</>.
1282 <entry><structfield>rolconnlimit</structfield></entry>
1283 <entry><type>int4</type></entry>
1285 For roles that can log in, this sets maximum number of concurrent
1286 connections this role can make. -1 means no limit.
1291 <entry><structfield>rolpassword</structfield></entry>
1292 <entry><type>text</type></entry>
1294 Password (possibly encrypted); null if none. If the password
1295 is encrypted, this column will begin with the string <literal>md5</>
1296 followed by a 32-character hexadecimal MD5 hash. The MD5 hash
1297 will be of the user's password concatenated to their username.
1298 For example, if user <literal>joe</> has password <literal>xyzzy</>,
1299 <productname>PostgreSQL</> will store the md5 hash of
1300 <literal>xyzzyjoe</>. A password that does not follow that
1301 format is assumed to be unencrypted.
1306 <entry><structfield>rolvaliduntil</structfield></entry>
1307 <entry><type>timestamptz</type></entry>
1308 <entry>Password expiry time (only used for password authentication);
1309 null if no expiration</entry>
1318 <sect1 id="catalog-pg-auth-members">
1319 <title><structname>pg_auth_members</structname></title>
1321 <indexterm zone="catalog-pg-auth-members">
1322 <primary>pg_auth_members</primary>
1326 The catalog <structname>pg_auth_members</structname> shows the membership
1327 relations between roles. Any non-circular set of relationships is allowed.
1331 Because user identities are cluster-wide,
1332 <structname>pg_auth_members</structname>
1333 is shared across all databases of a cluster: there is only one
1334 copy of <structname>pg_auth_members</structname> per cluster, not
1339 <title><structname>pg_auth_members</> Columns</title>
1346 <entry>References</entry>
1347 <entry>Description</entry>
1353 <entry><structfield>roleid</structfield></entry>
1354 <entry><type>oid</type></entry>
1355 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
1356 <entry>ID of a role that has a member</entry>
1360 <entry><structfield>member</structfield></entry>
1361 <entry><type>oid</type></entry>
1362 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
1363 <entry>ID of a role that is a member of <structfield>roleid</></entry>
1367 <entry><structfield>grantor</structfield></entry>
1368 <entry><type>oid</type></entry>
1369 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
1370 <entry>ID of the role that granted this membership</entry>
1374 <entry><structfield>admin_option</structfield></entry>
1375 <entry><type>bool</type></entry>
1377 <entry>True if <structfield>member</> can grant membership in
1378 <structfield>roleid</> to others</entry>
1387 <sect1 id="catalog-pg-cast">
1388 <title><structname>pg_cast</structname></title>
1390 <indexterm zone="catalog-pg-cast">
1391 <primary>pg_cast</primary>
1395 The catalog <structname>pg_cast</structname> stores data type conversion
1396 paths, both built-in paths and those defined with
1397 <xref linkend="sql-createcast">.
1401 It should be noted that <structname>pg_cast</structname> does not represent
1402 every type conversion that the system knows how to perform; only those that
1403 cannot be deduced from some generic rule. For example, casting between a
1404 domain and its base type is not explicitly represented in
1405 <structname>pg_cast</structname>. Another important exception is that
1406 <quote>automatic I/O conversion casts</>, those performed using a data
1407 type's own I/O functions to convert to or from <type>text</> or other
1408 string types, are not explicitly represented in
1409 <structname>pg_cast</structname>.
1413 <title><structname>pg_cast</> Columns</title>
1420 <entry>References</entry>
1421 <entry>Description</entry>
1427 <entry><structfield>castsource</structfield></entry>
1428 <entry><type>oid</type></entry>
1429 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
1430 <entry>OID of the source data type</entry>
1434 <entry><structfield>casttarget</structfield></entry>
1435 <entry><type>oid</type></entry>
1436 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
1437 <entry>OID of the target data type</entry>
1441 <entry><structfield>castfunc</structfield></entry>
1442 <entry><type>oid</type></entry>
1443 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
1445 The OID of the function to use to perform this cast. Zero is
1446 stored if the cast method doesn't require a function.
1451 <entry><structfield>castcontext</structfield></entry>
1452 <entry><type>char</type></entry>
1455 Indicates what contexts the cast can be invoked in.
1456 <literal>e</> means only as an explicit cast (using
1457 <literal>CAST</> or <literal>::</> syntax).
1458 <literal>a</> means implicitly in assignment
1459 to a target column, as well as explicitly.
1460 <literal>i</> means implicitly in expressions, as well as the
1465 <entry><structfield>castmethod</structfield></entry>
1466 <entry><type>char</type></entry>
1469 Indicates how the cast is performed.
1470 <literal>f</> means that the function specified in the <structfield>castfunc</> field is used.
1471 <literal>i</> means that the input/output functions are used.
1472 <literal>b</> means that the types are binary-coercible, thus no conversion is required.
1480 The cast functions listed in <structname>pg_cast</structname> must
1481 always take the cast source type as their first argument type, and
1482 return the cast destination type as their result type. A cast
1483 function can have up to three arguments. The second argument,
1484 if present, must be type <type>integer</>; it receives the type
1485 modifier associated with the destination type, or -1
1486 if there is none. The third argument,
1487 if present, must be type <type>boolean</>; it receives <literal>true</>
1488 if the cast is an explicit cast, <literal>false</> otherwise.
1492 It is legitimate to create a <structname>pg_cast</structname> entry
1493 in which the source and target types are the same, if the associated
1494 function takes more than one argument. Such entries represent
1495 <quote>length coercion functions</> that coerce values of the type
1496 to be legal for a particular type modifier value.
1500 When a <structname>pg_cast</structname> entry has different source and
1501 target types and a function that takes more than one argument, it
1502 represents converting from one type to another and applying a length
1503 coercion in a single step. When no such entry is available, coercion
1504 to a type that uses a type modifier involves two steps, one to
1505 convert between data types and a second to apply the modifier.
1509 <sect1 id="catalog-pg-class">
1510 <title><structname>pg_class</structname></title>
1512 <indexterm zone="catalog-pg-class">
1513 <primary>pg_class</primary>
1517 The catalog <structname>pg_class</structname> catalogs tables and most
1518 everything else that has columns or is otherwise similar to a
1519 table. This includes indexes (but see also
1520 <structname>pg_index</structname>), sequences, views, composite types,
1521 and TOAST tables; see <structfield>relkind</>.
1522 Below, when we mean all of these
1523 kinds of objects we speak of <quote>relations</quote>. Not all
1524 columns are meaningful for all relation types.
1528 <title><structname>pg_class</> Columns</title>
1535 <entry>References</entry>
1536 <entry>Description</entry>
1542 <entry><structfield>relname</structfield></entry>
1543 <entry><type>name</type></entry>
1545 <entry>Name of the table, index, view, etc.</entry>
1549 <entry><structfield>relnamespace</structfield></entry>
1550 <entry><type>oid</type></entry>
1551 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
1553 The OID of the namespace that contains this relation
1558 <entry><structfield>reltype</structfield></entry>
1559 <entry><type>oid</type></entry>
1560 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
1562 The OID of the data type that corresponds to this table's row type,
1563 if any (zero for indexes, which have no <structname>pg_type</> entry)
1568 <entry><structfield>reloftype</structfield></entry>
1569 <entry><type>oid</type></entry>
1570 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
1572 For typed tables, the OID of the underlying composite type,
1573 zero for all other relations
1578 <entry><structfield>relowner</structfield></entry>
1579 <entry><type>oid</type></entry>
1580 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
1581 <entry>Owner of the relation</entry>
1585 <entry><structfield>relam</structfield></entry>
1586 <entry><type>oid</type></entry>
1587 <entry><literal><link linkend="catalog-pg-am"><structname>pg_am</structname></link>.oid</literal></entry>
1588 <entry>If this is an index, the access method used (B-tree, hash, etc.)</entry>
1592 <entry><structfield>relfilenode</structfield></entry>
1593 <entry><type>oid</type></entry>
1595 <entry>Name of the on-disk file of this relation; zero means this
1596 is a <quote>mapped</> relation whose disk file name is determined
1597 by low-level state</entry>
1601 <entry><structfield>reltablespace</structfield></entry>
1602 <entry><type>oid</type></entry>
1603 <entry><literal><link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.oid</literal></entry>
1605 The tablespace in which this relation is stored. If zero,
1606 the database's default tablespace is implied. (Not meaningful
1607 if the relation has no on-disk file.)
1612 <entry><structfield>relpages</structfield></entry>
1613 <entry><type>int4</type></entry>
1616 Size of the on-disk representation of this table in pages (of size
1617 <symbol>BLCKSZ</symbol>). This is only an estimate used by the
1618 planner. It is updated by <command>VACUUM</command>,
1619 <command>ANALYZE</command>, and a few DDL commands such as
1620 <command>CREATE INDEX</command>.
1625 <entry><structfield>reltuples</structfield></entry>
1626 <entry><type>float4</type></entry>
1629 Number of rows in the table. This is only an estimate used by the
1630 planner. It is updated by <command>VACUUM</command>,
1631 <command>ANALYZE</command>, and a few DDL commands such as
1632 <command>CREATE INDEX</command>.
1637 <entry><structfield>reltoastrelid</structfield></entry>
1638 <entry><type>oid</type></entry>
1639 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
1641 OID of the TOAST table associated with this table, 0 if none. The
1642 TOAST table stores large attributes <quote>out of line</quote> in a
1648 <entry><structfield>reltoastidxid</structfield></entry>
1649 <entry><type>oid</type></entry>
1650 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
1652 For a TOAST table, the OID of its index. 0 if not a TOAST table.
1657 <entry><structfield>relhasindex</structfield></entry>
1658 <entry><type>bool</type></entry>
1661 True if this is a table and it has (or recently had) any indexes
1666 <entry><structfield>relisshared</structfield></entry>
1667 <entry><type>bool</type></entry>
1670 True if this table is shared across all databases in the cluster. Only
1671 certain system catalogs (such as <structname>pg_database</structname>)
1677 <entry><structfield>relpersistence</structfield></entry>
1678 <entry><type>char</type></entry>
1681 <literal>p</> = permanent table, <literal>u</> = unlogged table,
1682 <literal>t</> = temporary table
1687 <entry><structfield>relkind</structfield></entry>
1688 <entry><type>char</type></entry>
1691 <literal>r</> = ordinary table, <literal>i</> = index,
1692 <literal>S</> = sequence, <literal>v</> = view, <literal>c</> =
1693 composite type, <literal>t</> = TOAST table,
1694 <literal>f</> = foreign table
1699 <entry><structfield>relnatts</structfield></entry>
1700 <entry><type>int2</type></entry>
1703 Number of user columns in the relation (system columns not
1704 counted). There must be this many corresponding entries in
1705 <structname>pg_attribute</structname>. See also
1706 <literal>pg_attribute.attnum</literal>.
1711 <entry><structfield>relchecks</structfield></entry>
1712 <entry><type>int2</type></entry>
1715 Number of <literal>CHECK</> constraints on the table; see
1716 <link linkend="catalog-pg-constraint"><structname>pg_constraint</structname></link> catalog
1721 <entry><structfield>relhasoids</structfield></entry>
1722 <entry><type>bool</type></entry>
1725 True if we generate an OID for each row of the relation
1730 <entry><structfield>relhaspkey</structfield></entry>
1731 <entry><type>bool</type></entry>
1734 True if the table has (or once had) a primary key
1739 <entry><structfield>relhasrules</structfield></entry>
1740 <entry><type>bool</type></entry>
1743 True if table has (or once had) rules; see
1744 <link linkend="catalog-pg-rewrite"><structname>pg_rewrite</structname></link> catalog
1749 <entry><structfield>relhastriggers</structfield></entry>
1750 <entry><type>bool</type></entry>
1753 True if table has (or once had) triggers; see
1754 <link linkend="catalog-pg-trigger"><structname>pg_trigger</structname></link> catalog
1759 <entry><structfield>relhassubclass</structfield></entry>
1760 <entry><type>bool</type></entry>
1762 <entry>True if table has (or once had) any inheritance children</entry>
1766 <entry><structfield>relfrozenxid</structfield></entry>
1767 <entry><type>xid</type></entry>
1770 All transaction IDs before this one have been replaced with a permanent
1771 (<quote>frozen</>) transaction ID in this table. This is used to track
1772 whether the table needs to be vacuumed in order to prevent transaction
1773 ID wraparound or to allow <literal>pg_clog</> to be shrunk. Zero
1774 (<symbol>InvalidTransactionId</symbol>) if the relation is not a table.
1779 <entry><structfield>relacl</structfield></entry>
1780 <entry><type>aclitem[]</type></entry>
1783 Access privileges; see
1784 <xref linkend="sql-grant"> and
1785 <xref linkend="sql-revoke">
1791 <entry><structfield>reloptions</structfield></entry>
1792 <entry><type>text[]</type></entry>
1795 Access-method-specific options, as <quote>keyword=value</> strings
1803 Several of the Boolean flags in <structname>pg_class</> are maintained
1804 lazily: they are guaranteed to be true if that's the correct state, but
1805 may not be reset to false immediately when the condition is no longer
1806 true. For example, <structfield>relhasindex</> is set by
1807 <command>CREATE INDEX</command>, but it is never cleared by
1808 <command>DROP INDEX</command>. Instead, <command>VACUUM</command> clears
1809 <structfield>relhasindex</> if it finds the table has no indexes. This
1810 arrangement avoids race conditions and improves concurrency.
1814 <sect1 id="catalog-pg-constraint">
1815 <title><structname>pg_constraint</structname></title>
1817 <indexterm zone="catalog-pg-constraint">
1818 <primary>pg_constraint</primary>
1822 The catalog <structname>pg_constraint</structname> stores check, primary
1823 key, unique, foreign key, and exclusion constraints on tables.
1824 (Column constraints are not treated specially. Every column constraint is
1825 equivalent to some table constraint.)
1826 Not-null constraints are represented in the <structname>pg_attribute</>
1831 User-defined constraint triggers (created with <command>CREATE CONSTRAINT
1832 TRIGGER</>) also give rise to an entry in this table.
1836 Check constraints on domains are stored here, too.
1840 <title><structname>pg_constraint</> Columns</title>
1847 <entry>References</entry>
1848 <entry>Description</entry>
1854 <entry><structfield>conname</structfield></entry>
1855 <entry><type>name</type></entry>
1857 <entry>Constraint name (not necessarily unique!)</entry>
1861 <entry><structfield>connamespace</structfield></entry>
1862 <entry><type>oid</type></entry>
1863 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
1865 The OID of the namespace that contains this constraint
1870 <entry><structfield>contype</structfield></entry>
1871 <entry><type>char</type></entry>
1874 <literal>c</> = check constraint,
1875 <literal>f</> = foreign key constraint,
1876 <literal>p</> = primary key constraint,
1877 <literal>u</> = unique constraint,
1878 <literal>t</> = constraint trigger,
1879 <literal>x</> = exclusion constraint
1884 <entry><structfield>condeferrable</structfield></entry>
1885 <entry><type>bool</type></entry>
1887 <entry>Is the constraint deferrable?</entry>
1891 <entry><structfield>condeferred</structfield></entry>
1892 <entry><type>bool</type></entry>
1894 <entry>Is the constraint deferred by default?</entry>
1898 <entry><structfield>convalidated</structfield></entry>
1899 <entry><type>bool</type></entry>
1901 <entry>Has the constraint been validated? Can only be false for foreign keys</entry>
1905 <entry><structfield>conrelid</structfield></entry>
1906 <entry><type>oid</type></entry>
1907 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
1908 <entry>The table this constraint is on; 0 if not a table constraint</entry>
1912 <entry><structfield>contypid</structfield></entry>
1913 <entry><type>oid</type></entry>
1914 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
1915 <entry>The domain this constraint is on; 0 if not a domain constraint</entry>
1919 <entry><structfield>conindid</structfield></entry>
1920 <entry><type>oid</type></entry>
1921 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
1922 <entry>The index supporting this constraint, if it's a unique, primary
1923 key, foreign key, or exclusion constraint; else 0</entry>
1927 <entry><structfield>confrelid</structfield></entry>
1928 <entry><type>oid</type></entry>
1929 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
1930 <entry>If a foreign key, the referenced table; else 0</entry>
1934 <entry><structfield>confupdtype</structfield></entry>
1935 <entry><type>char</type></entry>
1937 <entry>Foreign key update action code:
1938 <literal>a</> = no action,
1939 <literal>r</> = restrict,
1940 <literal>c</> = cascade,
1941 <literal>n</> = set null,
1942 <literal>d</> = set default
1947 <entry><structfield>confdeltype</structfield></entry>
1948 <entry><type>char</type></entry>
1950 <entry>Foreign key deletion action code:
1951 <literal>a</> = no action,
1952 <literal>r</> = restrict,
1953 <literal>c</> = cascade,
1954 <literal>n</> = set null,
1955 <literal>d</> = set default
1960 <entry><structfield>confmatchtype</structfield></entry>
1961 <entry><type>char</type></entry>
1963 <entry>Foreign key match type:
1964 <literal>f</> = full,
1965 <literal>p</> = partial,
1966 <literal>u</> = simple (unspecified)
1971 <entry><structfield>conislocal</structfield></entry>
1972 <entry><type>bool</type></entry>
1975 This constraint is defined locally for the relation. Note that a
1976 constraint can be locally defined and inherited simultaneously.
1981 <entry><structfield>coninhcount</structfield></entry>
1982 <entry><type>int4</type></entry>
1985 The number of direct inheritance ancestors this constraint has.
1987 a nonzero number of ancestors cannot be dropped nor renamed.
1992 <entry><structfield>conkey</structfield></entry>
1993 <entry><type>int2[]</type></entry>
1994 <entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</></entry>
1995 <entry>If a table constraint (including foreign keys, but not constraint
1996 triggers), list of the constrained columns</entry>
2000 <entry><structfield>confkey</structfield></entry>
2001 <entry><type>int2[]</type></entry>
2002 <entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</></entry>
2003 <entry>If a foreign key, list of the referenced columns</entry>
2007 <entry><structfield>conpfeqop</structfield></entry>
2008 <entry><type>oid[]</type></entry>
2009 <entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</></entry>
2010 <entry>If a foreign key, list of the equality operators for PK = FK comparisons</entry>
2014 <entry><structfield>conppeqop</structfield></entry>
2015 <entry><type>oid[]</type></entry>
2016 <entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</></entry>
2017 <entry>If a foreign key, list of the equality operators for PK = PK comparisons</entry>
2021 <entry><structfield>conffeqop</structfield></entry>
2022 <entry><type>oid[]</type></entry>
2023 <entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</></entry>
2024 <entry>If a foreign key, list of the equality operators for FK = FK comparisons</entry>
2028 <entry><structfield>conexclop</structfield></entry>
2029 <entry><type>oid[]</type></entry>
2030 <entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</></entry>
2031 <entry>If an exclusion constraint, list of the per-column exclusion operators</entry>
2035 <entry><structfield>conbin</structfield></entry>
2036 <entry><type>pg_node_tree</type></entry>
2038 <entry>If a check constraint, an internal representation of the expression</entry>
2042 <entry><structfield>consrc</structfield></entry>
2043 <entry><type>text</type></entry>
2045 <entry>If a check constraint, a human-readable representation of the expression</entry>
2052 In the case of an exclusion constraint, <structfield>conkey</structfield>
2053 is only useful for constraint elements that are simple column references.
2054 For other cases, a zero appears in <structfield>conkey</structfield>
2055 and the associated index must be consulted to discover the expression
2056 that is constrained. (<structfield>conkey</structfield> thus has the
2057 same contents as <structname>pg_index</>.<structfield>indkey</> for the
2063 <structfield>consrc</structfield> is not updated when referenced objects
2064 change; for example, it won't track renaming of columns. Rather than
2065 relying on this field, it's best to use <function>pg_get_constraintdef()</>
2066 to extract the definition of a check constraint.
2072 <literal>pg_class.relchecks</literal> needs to agree with the
2073 number of check-constraint entries found in this table for each
2080 <sect1 id="catalog-pg-collation">
2081 <title><structname>pg_collation</structname></title>
2083 <indexterm zone="catalog-pg-collation">
2084 <primary>pg_collation</primary>
2088 The catalog <structname>pg_collation</structname> describes the
2089 available collations, which are essentially mappings from an SQL
2090 name to operating system locale categories.
2091 See <xref linkend="collation"> for more information.
2095 <title><structname>pg_collation</> Columns</title>
2102 <entry>References</entry>
2103 <entry>Description</entry>
2109 <entry><structfield>collname</structfield></entry>
2110 <entry><type>name</type></entry>
2112 <entry>Collation name (unique per namespace and encoding)</entry>
2116 <entry><structfield>collnamespace</structfield></entry>
2117 <entry><type>oid</type></entry>
2118 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
2120 The OID of the namespace that contains this collation
2125 <entry><structfield>collowner</structfield></entry>
2126 <entry><type>oid</type></entry>
2127 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
2128 <entry>Owner of the collation</entry>
2132 <entry><structfield>collencoding</structfield></entry>
2133 <entry><type>int4</type></entry>
2135 <entry>Encoding in which the collation is applicable, or -1 if it
2136 works for any encoding</entry>
2140 <entry><structfield>collcollate</structfield></entry>
2141 <entry><type>name</type></entry>
2143 <entry><symbol>LC_COLLATE</> for this collation object</entry>
2147 <entry><structfield>collctype</structfield></entry>
2148 <entry><type>name</type></entry>
2150 <entry><symbol>LC_CTYPE</> for this collation object</entry>
2157 Note that the unique key on this catalog is (<structfield>collname</>,
2158 <structfield>collencoding</>, <structfield>collnamespace</>) not just
2159 (<structfield>collname</>, <structfield>collnamespace</>).
2160 <productname>PostgreSQL</productname> generally ignores all
2161 collations that do not have <structfield>collencoding</> equal to
2162 either the current database's encoding or -1, and creation of new
2163 entries matching an entry with <structfield>collencoding</> = -1
2164 is forbidden. Therefore it is sufficient to use a qualified SQL name
2165 (<replaceable>schema</>.<replaceable>name</>) to identify a collation,
2166 even though this is not unique according to the catalog definition.
2167 The reason for defining the catalog this way is that
2168 <application>initdb</> fills it in at cluster initialization time with
2169 entries for all locales available on the system, so it must be able to
2170 hold entries for all encodings that might ever be used in the cluster.
2174 In the <literal>template0</> database, it could be useful to create
2175 collations whose encoding does not match the database encoding,
2176 since they could match the encodings of databases later cloned from
2177 <literal>template0</>. This would currently have to be done manually.
2181 <sect1 id="catalog-pg-conversion">
2182 <title><structname>pg_conversion</structname></title>
2184 <indexterm zone="catalog-pg-conversion">
2185 <primary>pg_conversion</primary>
2189 The catalog <structname>pg_conversion</structname> describes
2190 encoding conversion procedures. See <xref linkend="sql-createconversion">
2191 for more information.
2195 <title><structname>pg_conversion</> Columns</title>
2202 <entry>References</entry>
2203 <entry>Description</entry>
2209 <entry><structfield>conname</structfield></entry>
2210 <entry><type>name</type></entry>
2212 <entry>Conversion name (unique within a namespace)</entry>
2216 <entry><structfield>connamespace</structfield></entry>
2217 <entry><type>oid</type></entry>
2218 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
2220 The OID of the namespace that contains this conversion
2225 <entry><structfield>conowner</structfield></entry>
2226 <entry><type>oid</type></entry>
2227 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
2228 <entry>Owner of the conversion</entry>
2232 <entry><structfield>conforencoding</structfield></entry>
2233 <entry><type>int4</type></entry>
2235 <entry>Source encoding ID</entry>
2239 <entry><structfield>contoencoding</structfield></entry>
2240 <entry><type>int4</type></entry>
2242 <entry>Destination encoding ID</entry>
2246 <entry><structfield>conproc</structfield></entry>
2247 <entry><type>regproc</type></entry>
2248 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
2249 <entry>Conversion procedure</entry>
2253 <entry><structfield>condefault</structfield></entry>
2254 <entry><type>bool</type></entry>
2256 <entry>True if this is the default conversion</entry>
2265 <sect1 id="catalog-pg-database">
2266 <title><structname>pg_database</structname></title>
2268 <indexterm zone="catalog-pg-database">
2269 <primary>pg_database</primary>
2273 The catalog <structname>pg_database</structname> stores information about
2274 the available databases. Databases are created with the <xref
2275 linkend="sql-createdatabase"> command.
2276 Consult <xref linkend="managing-databases"> for details about the meaning
2277 of some of the parameters.
2281 Unlike most system catalogs, <structname>pg_database</structname>
2282 is shared across all databases of a cluster: there is only one
2283 copy of <structname>pg_database</structname> per cluster, not
2288 <title><structname>pg_database</> Columns</title>
2295 <entry>References</entry>
2296 <entry>Description</entry>
2302 <entry><structfield>datname</structfield></entry>
2303 <entry><type>name</type></entry>
2305 <entry>Database name</entry>
2309 <entry><structfield>datdba</structfield></entry>
2310 <entry><type>oid</type></entry>
2311 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
2312 <entry>Owner of the database, usually the user who created it</entry>
2316 <entry><structfield>encoding</structfield></entry>
2317 <entry><type>int4</type></entry>
2319 <entry>Character encoding for this database
2320 (<function>pg_encoding_to_char()</function> can translate
2321 this number to the encoding name)</entry>
2325 <entry><structfield>datcollate</structfield></entry>
2326 <entry><type>name</type></entry>
2328 <entry>LC_COLLATE for this database</entry>
2332 <entry><structfield>datctype</structfield></entry>
2333 <entry><type>name</type></entry>
2335 <entry>LC_CTYPE for this database</entry>
2339 <entry><structfield>datistemplate</structfield></entry>
2340 <entry><type>bool</type></entry>
2343 If true then this database can be used in the
2344 <literal>TEMPLATE</literal> clause of <command>CREATE
2345 DATABASE</command> to create a new database as a clone of
2351 <entry><structfield>datallowconn</structfield></entry>
2352 <entry><type>bool</type></entry>
2355 If false then no one can connect to this database. This is
2356 used to protect the <literal>template0</> database from being altered.
2361 <entry><structfield>datconnlimit</structfield></entry>
2362 <entry><type>int4</type></entry>
2365 Sets maximum number of concurrent connections that can be made
2366 to this database. -1 means no limit.
2371 <entry><structfield>datlastsysoid</structfield></entry>
2372 <entry><type>oid</type></entry>
2375 Last system OID in the database; useful
2376 particularly to <application>pg_dump</application>
2381 <entry><structfield>datfrozenxid</structfield></entry>
2382 <entry><type>xid</type></entry>
2385 All transaction IDs before this one have been replaced with a permanent
2386 (<quote>frozen</>) transaction ID in this database. This is used to
2387 track whether the database needs to be vacuumed in order to prevent
2388 transaction ID wraparound or to allow <literal>pg_clog</> to be shrunk.
2389 It is the minimum of the per-table
2390 <structname>pg_class</>.<structfield>relfrozenxid</> values.
2395 <entry><structfield>dattablespace</structfield></entry>
2396 <entry><type>oid</type></entry>
2397 <entry><literal><link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.oid</literal></entry>
2399 The default tablespace for the database.
2400 Within this database, all tables for which
2401 <structname>pg_class</>.<structfield>reltablespace</> is zero
2402 will be stored in this tablespace; in particular, all the non-shared
2403 system catalogs will be there.
2408 <entry><structfield>datacl</structfield></entry>
2409 <entry><type>aclitem[]</type></entry>
2412 Access privileges; see
2413 <xref linkend="sql-grant"> and
2414 <xref linkend="sql-revoke">
2424 <sect1 id="catalog-pg-db-role-setting">
2425 <title><structname>pg_db_role_setting</structname></title>
2427 <indexterm zone="catalog-pg-db-role-setting">
2428 <primary>pg_db_role_setting</primary>
2432 The catalog <structname>pg_db_role_setting</structname> records the default
2433 values that have been set for run-time configuration variables,
2434 for each role and database combination.
2438 Unlike most system catalogs, <structname>pg_db_role_setting</structname>
2439 is shared across all databases of a cluster: there is only one
2440 copy of <structname>pg_db_role_setting</structname> per cluster, not
2445 <title><structname>pg_db_role_setting</> Columns</title>
2452 <entry>References</entry>
2453 <entry>Description</entry>
2459 <entry><structfield>setdatabase</structfield></entry>
2460 <entry><type>oid</type></entry>
2461 <entry><literal><link linkend="catalog-pg-database"><structname>pg_database</structname></link>.oid</literal></entry>
2462 <entry>The OID of the database the setting is applicable to, or zero if not database-specific</entry>
2466 <entry><structfield>setrole</structfield></entry>
2467 <entry><type>oid</type></entry>
2468 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
2469 <entry>The OID of the role the setting is applicable to, or zero if not role-specific</entry>
2473 <entry><structfield>setconfig</structfield></entry>
2474 <entry><type>text[]</type></entry>
2476 <entry>Defaults for run-time configuration variables</entry>
2484 <sect1 id="catalog-pg-default-acl">
2485 <title><structname>pg_default_acl</structname></title>
2487 <indexterm zone="catalog-pg-default-acl">
2488 <primary>pg_default_acl</primary>
2492 The catalog <structname>pg_default_acl</> stores initial
2493 privileges to be assigned to newly created objects.
2497 <title><structname>pg_default_acl</> Columns</title>
2504 <entry>References</entry>
2505 <entry>Description</entry>
2511 <entry><structfield>defaclrole</structfield></entry>
2512 <entry><type>oid</type></entry>
2513 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
2514 <entry>The OID of the role associated with this entry</entry>
2518 <entry><structfield>defaclnamespace</structfield></entry>
2519 <entry><type>oid</type></entry>
2520 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
2521 <entry>The OID of the namespace associated with this entry,
2522 or 0 if none</entry>
2526 <entry><structfield>defaclobjtype</structfield></entry>
2527 <entry><type>char</type></entry>
2530 Type of object this entry is for:
2531 <literal>r</> = relation (table, view),
2532 <literal>S</> = sequence,
2533 <literal>f</> = function
2538 <entry><structfield>defaclacl</structfield></entry>
2539 <entry><type>aclitem[]</type></entry>
2542 Access privileges that this type of object should have on creation
2550 A <structname>pg_default_acl</> entry shows the initial privileges to
2551 be assigned to an object belonging to the indicated user. There are
2552 currently two types of entry: <quote>global</> entries with
2553 <structfield>defaclnamespace</> = 0, and <quote>per-schema</> entries
2554 that reference a particular schema. If a global entry is present then
2555 it <emphasis>overrides</> the normal hard-wired default privileges
2556 for the object type. A per-schema entry, if present, represents privileges
2557 to be <emphasis>added to</> the global or hard-wired default privileges.
2561 Note that when an ACL entry in another catalog is null, it is taken
2562 to represent the hard-wired default privileges for its object,
2563 <emphasis>not</> whatever might be in <structname>pg_default_acl</>
2564 at the moment. <structname>pg_default_acl</> is only consulted during
2571 <sect1 id="catalog-pg-depend">
2572 <title><structname>pg_depend</structname></title>
2574 <indexterm zone="catalog-pg-depend">
2575 <primary>pg_depend</primary>
2579 The catalog <structname>pg_depend</structname> records the dependency
2580 relationships between database objects. This information allows
2581 <command>DROP</> commands to find which other objects must be dropped
2582 by <command>DROP CASCADE</> or prevent dropping in the <command>DROP
2587 See also <link linkend="catalog-pg-shdepend"><structname>pg_shdepend</structname></link>,
2588 which performs a similar function for dependencies involving objects
2589 that are shared across a database cluster.
2593 <title><structname>pg_depend</> Columns</title>
2600 <entry>References</entry>
2601 <entry>Description</entry>
2607 <entry><structfield>classid</structfield></entry>
2608 <entry><type>oid</type></entry>
2609 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
2610 <entry>The OID of the system catalog the dependent object is in</entry>
2614 <entry><structfield>objid</structfield></entry>
2615 <entry><type>oid</type></entry>
2616 <entry>any OID column</entry>
2617 <entry>The OID of the specific dependent object</entry>
2621 <entry><structfield>objsubid</structfield></entry>
2622 <entry><type>int4</type></entry>
2625 For a table column, this is the column number (the
2626 <structfield>objid</> and <structfield>classid</> refer to the
2627 table itself). For all other object types, this column is
2633 <entry><structfield>refclassid</structfield></entry>
2634 <entry><type>oid</type></entry>
2635 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
2636 <entry>The OID of the system catalog the referenced object is in</entry>
2640 <entry><structfield>refobjid</structfield></entry>
2641 <entry><type>oid</type></entry>
2642 <entry>any OID column</entry>
2643 <entry>The OID of the specific referenced object</entry>
2647 <entry><structfield>refobjsubid</structfield></entry>
2648 <entry><type>int4</type></entry>
2651 For a table column, this is the column number (the
2652 <structfield>refobjid</> and <structfield>refclassid</> refer
2653 to the table itself). For all other object types, this column
2659 <entry><structfield>deptype</structfield></entry>
2660 <entry><type>char</type></entry>
2663 A code defining the specific semantics of this dependency relationship; see text
2672 In all cases, a <structname>pg_depend</structname> entry indicates that the
2673 referenced object cannot be dropped without also dropping the dependent
2674 object. However, there are several subflavors identified by
2675 <structfield>deptype</>:
2679 <term><symbol>DEPENDENCY_NORMAL</> (<literal>n</>)</term>
2682 A normal relationship between separately-created objects. The
2683 dependent object can be dropped without affecting the
2684 referenced object. The referenced object can only be dropped
2685 by specifying <literal>CASCADE</>, in which case the dependent
2686 object is dropped, too. Example: a table column has a normal
2687 dependency on its data type.
2693 <term><symbol>DEPENDENCY_AUTO</> (<literal>a</>)</term>
2696 The dependent object can be dropped separately from the
2697 referenced object, and should be automatically dropped
2698 (regardless of <literal>RESTRICT</> or <literal>CASCADE</>
2699 mode) if the referenced object is dropped. Example: a named
2700 constraint on a table is made autodependent on the table, so
2701 that it will go away if the table is dropped.
2707 <term><symbol>DEPENDENCY_INTERNAL</> (<literal>i</>)</term>
2710 The dependent object was created as part of creation of the
2711 referenced object, and is really just a part of its internal
2712 implementation. A <command>DROP</> of the dependent object
2713 will be disallowed outright (we'll tell the user to issue a
2714 <command>DROP</> against the referenced object, instead). A
2715 <command>DROP</> of the referenced object will be propagated
2716 through to drop the dependent object whether
2717 <command>CASCADE</> is specified or not. Example: a trigger
2718 that's created to enforce a foreign-key constraint is made
2719 internally dependent on the constraint's
2720 <structname>pg_constraint</> entry.
2726 <term><symbol>DEPENDENCY_EXTENSION</> (<literal>e</>)</term>
2729 The dependent object is a member of the <firstterm>extension</> that is
2730 the referenced object (see
2731 <link linkend="catalog-pg-extension"><structname>pg_extension</structname></link>).
2732 The dependent object can be dropped only via
2733 <command>DROP EXTENSION</> on the referenced object. Functionally
2734 this dependency type acts the same as an internal dependency, but
2735 it's kept separate for clarity and to simplify <application>pg_dump</>.
2741 <term><symbol>DEPENDENCY_PIN</> (<literal>p</>)</term>
2744 There is no dependent object; this type of entry is a signal
2745 that the system itself depends on the referenced object, and so
2746 that object must never be deleted. Entries of this type are
2747 created only by <command>initdb</command>. The columns for the
2748 dependent object contain zeroes.
2754 Other dependency flavors might be needed in future.
2760 <sect1 id="catalog-pg-description">
2761 <title><structname>pg_description</structname></title>
2763 <indexterm zone="catalog-pg-description">
2764 <primary>pg_description</primary>
2768 The catalog <structname>pg_description</> stores optional descriptions
2769 (comments) for each database object. Descriptions can be manipulated
2770 with the <xref linkend="sql-comment"> command and viewed with
2771 <application>psql</application>'s <literal>\d</literal> commands.
2772 Descriptions of many built-in system objects are provided in the initial
2773 contents of <structname>pg_description</structname>.
2777 See also <link linkend="catalog-pg-shdescription"><structname>pg_shdescription</structname></link>,
2778 which performs a similar function for descriptions involving objects that
2779 are shared across a database cluster.
2783 <title><structname>pg_description</> Columns</title>
2790 <entry>References</entry>
2791 <entry>Description</entry>
2797 <entry><structfield>objoid</structfield></entry>
2798 <entry><type>oid</type></entry>
2799 <entry>any OID column</entry>
2800 <entry>The OID of the object this description pertains to</entry>
2804 <entry><structfield>classoid</structfield></entry>
2805 <entry><type>oid</type></entry>
2806 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
2807 <entry>The OID of the system catalog this object appears in</entry>
2811 <entry><structfield>objsubid</structfield></entry>
2812 <entry><type>int4</type></entry>
2815 For a comment on a table column, this is the column number (the
2816 <structfield>objoid</> and <structfield>classoid</> refer to
2817 the table itself). For all other object types, this column is
2823 <entry><structfield>description</structfield></entry>
2824 <entry><type>text</type></entry>
2826 <entry>Arbitrary text that serves as the description of this object</entry>
2835 <sect1 id="catalog-pg-enum">
2836 <title><structname>pg_enum</structname></title>
2838 <indexterm zone="catalog-pg-enum">
2839 <primary>pg_enum</primary>
2843 The <structname>pg_enum</structname> catalog contains entries
2844 showing the values and labels for each enum type. The
2845 internal representation of a given enum value is actually the OID
2846 of its associated row in <structname>pg_enum</structname>.
2850 <title><structname>pg_enum</> Columns</title>
2857 <entry>References</entry>
2858 <entry>Description</entry>
2864 <entry><structfield>enumtypid</structfield></entry>
2865 <entry><type>oid</type></entry>
2866 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
2867 <entry>The OID of the <structname>pg_type</> entry owning this enum value</entry>
2871 <entry><structfield>enumsortorder</structfield></entry>
2872 <entry><type>float4</type></entry>
2874 <entry>The sort position of this enum value within its enum type</entry>
2878 <entry><structfield>enumlabel</structfield></entry>
2879 <entry><type>name</type></entry>
2881 <entry>The textual label for this enum value</entry>
2888 The OIDs for <structname>pg_enum</structname> rows follow a special
2889 rule: even-numbered OIDs are guaranteed to be ordered in the same way
2890 as the sort ordering of their enum type. That is, if two even OIDs
2891 belong to the same enum type, the smaller OID must have the smaller
2892 <structfield>enumsortorder</structfield> value. Odd-numbered OID values
2893 need bear no relationship to the sort order. This rule allows the
2894 enum comparison routines to avoid catalog lookups in many common cases.
2895 The routines that create and alter enum types attempt to assign even
2896 OIDs to enum values whenever possible.
2900 When an enum type is created, its members are assigned sort-order
2901 positions 1..<replaceable>n</>. But members added later might be given
2902 negative or fractional values of <structfield>enumsortorder</structfield>.
2903 The only requirement on these values is that they be correctly
2904 ordered and unique within each enum type.
2909 <sect1 id="catalog-pg-extension">
2910 <title><structname>pg_extension</structname></title>
2912 <indexterm zone="catalog-pg-extension">
2913 <primary>pg_extension</primary>
2917 The catalog <structname>pg_extension</structname> stores information
2918 about the installed extensions. See <xref linkend="extend-extensions">
2919 for details about extensions.
2923 <title><structname>pg_extension</> Columns</title>
2930 <entry>References</entry>
2931 <entry>Description</entry>
2937 <entry><structfield>extname</structfield></entry>
2938 <entry><type>name</type></entry>
2940 <entry>Name of the extension</entry>
2944 <entry><structfield>extowner</structfield></entry>
2945 <entry><type>oid</type></entry>
2946 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
2947 <entry>Owner of the extension</entry>
2951 <entry><structfield>extnamespace</structfield></entry>
2952 <entry><type>oid</type></entry>
2953 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
2954 <entry>Schema containing the extension's exported objects</entry>
2958 <entry><structfield>extrelocatable</structfield></entry>
2959 <entry><type>bool</type></entry>
2961 <entry>True if extension can be relocated to another schema</entry>
2965 <entry><structfield>extversion</structfield></entry>
2966 <entry><type>text</type></entry>
2968 <entry>Version name for the extension</entry>
2972 <entry><structfield>extconfig</structfield></entry>
2973 <entry><type>oid[]</type></entry>
2974 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
2975 <entry>Array of <type>regclass</> OIDs for the extension's configuration
2976 table(s), or <literal>NULL</> if none</entry>
2980 <entry><structfield>extcondition</structfield></entry>
2981 <entry><type>text[]</type></entry>
2983 <entry>Array of <literal>WHERE</>-clause filter conditions for the
2984 extension's configuration table(s), or <literal>NULL</> if none</entry>
2992 Note that unlike most catalogs with a <quote>namespace</> column,
2993 <structfield>extnamespace</structfield> is not meant to imply
2994 that the extension belongs to that schema. Extension names are never
2995 schema-qualified. Rather, <structfield>extnamespace</structfield>
2996 indicates the schema that contains most or all of the extension's
2997 objects. If <structfield>extrelocatable</structfield> is true, then
2998 this schema must in fact contain all schema-qualifiable objects
2999 belonging to the extension.
3004 <sect1 id="catalog-pg-foreign-data-wrapper">
3005 <title><structname>pg_foreign_data_wrapper</structname></title>
3007 <indexterm zone="catalog-pg-foreign-data-wrapper">
3008 <primary>pg_foreign_data_wrapper</primary>
3012 The catalog <structname>pg_foreign_data_wrapper</structname> stores
3013 foreign-data wrapper definitions. A foreign-data wrapper is the
3014 mechanism by which external data, residing on foreign servers, is
3019 <title><structname>pg_foreign_data_wrapper</> Columns</title>
3026 <entry>References</entry>
3027 <entry>Description</entry>
3033 <entry><structfield>fdwname</structfield></entry>
3034 <entry><type>name</type></entry>
3036 <entry>Name of the foreign-data wrapper</entry>
3040 <entry><structfield>fdwowner</structfield></entry>
3041 <entry><type>oid</type></entry>
3042 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
3043 <entry>Owner of the foreign-data wrapper</entry>
3047 <entry><structfield>fdwhandler</structfield></entry>
3048 <entry><type>oid</type></entry>
3049 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
3051 References a handler function that is responsible for
3052 supplying execution routines for the foreign-data wrapper.
3053 Zero if no handler is provided
3058 <entry><structfield>fdwvalidator</structfield></entry>
3059 <entry><type>oid</type></entry>
3060 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
3062 References a validator function that is responsible for
3063 checking the validity of the options given to the
3064 foreign-data wrapper, as well as options for foreign servers and user
3065 mappings using the foreign-data wrapper. Zero if no validator
3071 <entry><structfield>fdwacl</structfield></entry>
3072 <entry><type>aclitem[]</type></entry>
3075 Access privileges; see
3076 <xref linkend="sql-grant"> and
3077 <xref linkend="sql-revoke">
3083 <entry><structfield>fdwoptions</structfield></entry>
3084 <entry><type>text[]</type></entry>
3087 Foreign-data wrapper specific options, as <quote>keyword=value</> strings
3096 <sect1 id="catalog-pg-foreign-server">
3097 <title><structname>pg_foreign_server</structname></title>
3099 <indexterm zone="catalog-pg-foreign-server">
3100 <primary>pg_foreign_server</primary>
3104 The catalog <structname>pg_foreign_server</structname> stores
3105 foreign server definitions. A foreign server describes a source
3106 of external data, such as a remote server. Foreign
3107 servers are accessed via foreign-data wrappers.
3111 <title><structname>pg_foreign_server</> Columns</title>
3118 <entry>References</entry>
3119 <entry>Description</entry>
3125 <entry><structfield>srvname</structfield></entry>
3126 <entry><type>name</type></entry>
3128 <entry>Name of the foreign server</entry>
3132 <entry><structfield>srvowner</structfield></entry>
3133 <entry><type>oid</type></entry>
3134 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
3135 <entry>Owner of the foreign server</entry>
3139 <entry><structfield>srvfdw</structfield></entry>
3140 <entry><type>oid</type></entry>
3141 <entry><literal><link linkend="catalog-pg-foreign-data-wrapper"><structname>pg_foreign_data_wrapper</structname></link>.oid</literal></entry>
3142 <entry>OID of the foreign-data wrapper of this foreign server</entry>
3146 <entry><structfield>srvtype</structfield></entry>
3147 <entry><type>text</type></entry>
3149 <entry>Type of the server (optional)</entry>
3153 <entry><structfield>srvversion</structfield></entry>
3154 <entry><type>text</type></entry>
3156 <entry>Version of the server (optional)</entry>
3160 <entry><structfield>srvacl</structfield></entry>
3161 <entry><type>aclitem[]</type></entry>
3164 Access privileges; see
3165 <xref linkend="sql-grant"> and
3166 <xref linkend="sql-revoke">
3172 <entry><structfield>srvoptions</structfield></entry>
3173 <entry><type>text[]</type></entry>
3176 Foreign server specific options, as <quote>keyword=value</> strings
3185 <sect1 id="catalog-pg-foreign-table">
3186 <title><structname>pg_foreign_table</structname></title>
3188 <indexterm zone="catalog-pg-foreign-table">
3189 <primary>pg_foreign_table</primary>
3193 The catalog <structname>pg_foreign_table</structname> contains
3194 auxiliary information about foreign tables. A foreign table is
3195 primarily represented by a <structname>pg_class</structname> entry,
3196 just like a regular table. Its <structname>pg_foreign_table</structname>
3197 entry contains the information that is pertinent only to foreign tables
3198 and not any other kind of relation.
3202 <title><structname>pg_foreign_table</> Columns</title>
3209 <entry>References</entry>
3210 <entry>Description</entry>
3216 <entry><structfield>ftrelid</structfield></entry>
3217 <entry><type>oid</type></entry>
3218 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
3219 <entry>OID of the <structname>pg_class</> entry for this foreign table</entry>
3223 <entry><structfield>ftserver</structfield></entry>
3224 <entry><type>oid</type></entry>
3225 <entry><literal><link linkend="catalog-pg-foreign-server"><structname>pg_foreign_server</structname></link>.oid</literal></entry>
3226 <entry>OID of the foreign server for this foreign table</entry>
3230 <entry><structfield>ftoptions</structfield></entry>
3231 <entry><type>text[]</type></entry>
3234 Foreign table options, as <quote>keyword=value</> strings
3243 <sect1 id="catalog-pg-index">
3244 <title><structname>pg_index</structname></title>
3246 <indexterm zone="catalog-pg-index">
3247 <primary>pg_index</primary>
3251 The catalog <structname>pg_index</structname> contains part of the information
3252 about indexes. The rest is mostly in
3253 <structname>pg_class</structname>.
3257 <title><structname>pg_index</> Columns</title>
3264 <entry>References</entry>
3265 <entry>Description</entry>
3271 <entry><structfield>indexrelid</structfield></entry>
3272 <entry><type>oid</type></entry>
3273 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
3274 <entry>The OID of the <structname>pg_class</> entry for this index</entry>
3278 <entry><structfield>indrelid</structfield></entry>
3279 <entry><type>oid</type></entry>
3280 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
3281 <entry>The OID of the <structname>pg_class</> entry for the table this index is for</entry>
3285 <entry><structfield>indnatts</structfield></entry>
3286 <entry><type>int2</type></entry>
3288 <entry>The number of columns in the index (duplicates
3289 <literal>pg_class.relnatts</literal>)</entry>
3293 <entry><structfield>indisunique</structfield></entry>
3294 <entry><type>bool</type></entry>
3296 <entry>If true, this is a unique index</entry>
3300 <entry><structfield>indisprimary</structfield></entry>
3301 <entry><type>bool</type></entry>
3303 <entry>If true, this index represents the primary key of the table
3304 (<structfield>indisunique</> should always be true when this is true)</entry>
3308 <entry><structfield>indisexclusion</structfield></entry>
3309 <entry><type>bool</type></entry>
3311 <entry>If true, this index supports an exclusion constraint</entry>
3315 <entry><structfield>indimmediate</structfield></entry>
3316 <entry><type>bool</type></entry>
3318 <entry>If true, the uniqueness check is enforced immediately on
3320 (irrelevant if <structfield>indisunique</> is not true)</entry>
3324 <entry><structfield>indisclustered</structfield></entry>
3325 <entry><type>bool</type></entry>
3327 <entry>If true, the table was last clustered on this index</entry>
3331 <entry><structfield>indisvalid</structfield></entry>
3332 <entry><type>bool</type></entry>
3335 If true, the index is currently valid for queries. False means the
3336 index is possibly incomplete: it must still be modified by
3337 <command>INSERT</>/<command>UPDATE</> operations, but it cannot safely
3338 be used for queries. If it is unique, the uniqueness property is not
3344 <entry><structfield>indcheckxmin</structfield></entry>
3345 <entry><type>bool</type></entry>
3348 If true, queries must not use the index until the <structfield>xmin</>
3349 of this <structname>pg_index</> row is below their <symbol>TransactionXmin</symbol>
3350 event horizon, because the table may contain broken HOT chains with
3351 incompatible rows that they can see
3356 <entry><structfield>indisready</structfield></entry>
3357 <entry><type>bool</type></entry>
3360 If true, the index is currently ready for inserts. False means the
3361 index must be ignored by <command>INSERT</>/<command>UPDATE</>
3367 <entry><structfield>indkey</structfield></entry>
3368 <entry><type>int2vector</type></entry>
3369 <entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</literal></entry>
3371 This is an array of <structfield>indnatts</structfield> values that
3372 indicate which table columns this index indexes. For example a value
3373 of <literal>1 3</literal> would mean that the first and the third table
3374 columns make up the index key. A zero in this array indicates that the
3375 corresponding index attribute is an expression over the table columns,
3376 rather than a simple column reference.
3381 <entry><structfield>indcollation</structfield></entry>
3382 <entry><type>oidvector</type></entry>
3383 <entry><literal><link linkend="catalog-pg-collation"><structname>pg_collation</structname></link>.oid</literal></entry>
3385 For each column in the index key, this contains the OID of the
3386 collation to use for the index.
3391 <entry><structfield>indclass</structfield></entry>
3392 <entry><type>oidvector</type></entry>
3393 <entry><literal><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link>.oid</literal></entry>
3395 For each column in the index key, this contains the OID of
3396 the operator class to use. See
3397 <link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link> for details.
3402 <entry><structfield>indoption</structfield></entry>
3403 <entry><type>int2vector</type></entry>
3406 This is an array of <structfield>indnatts</structfield> values that
3407 store per-column flag bits. The meaning of the bits is defined by
3408 the index's access method.
3413 <entry><structfield>indexprs</structfield></entry>
3414 <entry><type>pg_node_tree</type></entry>
3417 Expression trees (in <function>nodeToString()</function>
3418 representation) for index attributes that are not simple column
3419 references. This is a list with one element for each zero
3420 entry in <structfield>indkey</>. Null if all index attributes
3421 are simple references.
3426 <entry><structfield>indpred</structfield></entry>
3427 <entry><type>pg_node_tree</type></entry>
3430 Expression tree (in <function>nodeToString()</function>
3431 representation) for partial index predicate. Null if not a
3442 <sect1 id="catalog-pg-inherits">
3443 <title><structname>pg_inherits</structname></title>
3445 <indexterm zone="catalog-pg-inherits">
3446 <primary>pg_inherits</primary>
3450 The catalog <structname>pg_inherits</> records information about
3451 table inheritance hierarchies. There is one entry for each direct
3452 child table in the database. (Indirect inheritance can be determined
3453 by following chains of entries.)
3457 <title><structname>pg_inherits</> Columns</title>
3464 <entry>References</entry>
3465 <entry>Description</entry>
3471 <entry><structfield>inhrelid</structfield></entry>
3472 <entry><type>oid</type></entry>
3473 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
3475 The OID of the child table
3480 <entry><structfield>inhparent</structfield></entry>
3481 <entry><type>oid</type></entry>
3482 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
3484 The OID of the parent table
3489 <entry><structfield>inhseqno</structfield></entry>
3490 <entry><type>int4</type></entry>
3493 If there is more than one direct parent for a child table (multiple
3494 inheritance), this number tells the order in which the
3495 inherited columns are to be arranged. The count starts at 1.
3505 <sect1 id="catalog-pg-language">
3506 <title><structname>pg_language</structname></title>
3508 <indexterm zone="catalog-pg-language">
3509 <primary>pg_language</primary>
3513 The catalog <structname>pg_language</structname> registers
3514 languages in which you can write functions or stored procedures.
3515 See <xref linkend="sql-createlanguage">
3516 and <xref linkend="xplang"> for more information about language handlers.
3520 <title><structname>pg_language</> Columns</title>
3527 <entry>References</entry>
3528 <entry>Description</entry>
3534 <entry><structfield>lanname</structfield></entry>
3535 <entry><type>name</type></entry>
3537 <entry>Name of the language</entry>
3541 <entry><structfield>lanowner</structfield></entry>
3542 <entry><type>oid</type></entry>
3543 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
3544 <entry>Owner of the language</entry>
3548 <entry><structfield>lanispl</structfield></entry>
3549 <entry><type>bool</type></entry>
3552 This is false for internal languages (such as
3553 <acronym>SQL</acronym>) and true for user-defined languages.
3554 Currently, <application>pg_dump</application> still uses this
3555 to determine which languages need to be dumped, but this might be
3556 replaced by a different mechanism in the future.
3561 <entry><structfield>lanpltrusted</structfield></entry>
3562 <entry><type>bool</type></entry>
3565 True if this is a trusted language, which means that it is believed
3566 not to grant access to anything outside the normal SQL execution
3567 environment. Only superusers can create functions in untrusted
3573 <entry><structfield>lanplcallfoid</structfield></entry>
3574 <entry><type>oid</type></entry>
3575 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
3577 For noninternal languages this references the language
3578 handler, which is a special function that is responsible for
3579 executing all functions that are written in the particular
3585 <entry><structfield>laninline</structfield></entry>
3586 <entry><type>oid</type></entry>
3587 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
3589 This references a function that is responsible for executing
3590 <quote>inline</> anonymous code blocks
3591 (<xref linkend="sql-do"> blocks).
3592 Zero if inline blocks are not supported.
3597 <entry><structfield>lanvalidator</structfield></entry>
3598 <entry><type>oid</type></entry>
3599 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
3601 This references a language validator function that is responsible
3602 for checking the syntax and validity of new functions when they
3603 are created. Zero if no validator is provided.
3608 <entry><structfield>lanacl</structfield></entry>
3609 <entry><type>aclitem[]</type></entry>
3612 Access privileges; see
3613 <xref linkend="sql-grant"> and
3614 <xref linkend="sql-revoke">
3625 <sect1 id="catalog-pg-largeobject">
3626 <title><structname>pg_largeobject</structname></title>
3628 <indexterm zone="catalog-pg-largeobject">
3629 <primary>pg_largeobject</primary>
3633 The catalog <structname>pg_largeobject</structname> holds the data making up
3634 <quote>large objects</quote>. A large object is identified by an OID
3635 assigned when it is created. Each large object is broken into
3636 segments or <quote>pages</> small enough to be conveniently stored as rows
3637 in <structname>pg_largeobject</structname>.
3638 The amount of data per page is defined to be <symbol>LOBLKSIZE</> (which is currently
3639 <literal>BLCKSZ/4</>, or typically 2 kB).
3643 Prior to <productname>PostgreSQL</> 9.0, there was no permission structure
3644 associated with large objects. As a result,
3645 <structname>pg_largeobject</structname> was publicly readable and could be
3646 used to obtain the OIDs (and contents) of all large objects in the system.
3647 This is no longer the case; use
3648 <link linkend="catalog-pg-largeobject-metadata"><structname>pg_largeobject_metadata</></link>
3649 to obtain a list of large object OIDs.
3653 <title><structname>pg_largeobject</> Columns</title>
3660 <entry>References</entry>
3661 <entry>Description</entry>
3667 <entry><structfield>loid</structfield></entry>
3668 <entry><type>oid</type></entry>
3669 <entry><literal><link linkend="catalog-pg-largeobject-metadata"><structname>pg_largeobject_metadata</structname></link>.oid</literal></entry>
3670 <entry>Identifier of the large object that includes this page</entry>
3674 <entry><structfield>pageno</structfield></entry>
3675 <entry><type>int4</type></entry>
3677 <entry>Page number of this page within its large object
3678 (counting from zero)</entry>
3682 <entry><structfield>data</structfield></entry>
3683 <entry><type>bytea</type></entry>
3686 Actual data stored in the large object.
3687 This will never be more than <symbol>LOBLKSIZE</> bytes and might be less.
3695 Each row of <structname>pg_largeobject</structname> holds data
3696 for one page of a large object, beginning at
3697 byte offset (<literal>pageno * LOBLKSIZE</>) within the object. The implementation
3698 allows sparse storage: pages might be missing, and might be shorter than
3699 <literal>LOBLKSIZE</> bytes even if they are not the last page of the object.
3700 Missing regions within a large object read as zeroes.
3705 <sect1 id="catalog-pg-largeobject-metadata">
3706 <title><structname>pg_largeobject_metadata</structname></title>
3708 <indexterm zone="catalog-pg-largeobject-metadata">
3709 <primary>pg_largeobject_metadata</primary>
3713 The catalog <structname>pg_largeobject_metadata</structname>
3714 holds metadata associated with large objects. The actual large object
3716 <link linkend="catalog-pg-largeobject"><structname>pg_largeobject</></link>.
3720 <title><structname>pg_largeobject_metadata</> Columns</title>
3727 <entry>References</entry>
3728 <entry>Description</entry>
3734 <entry><structfield>lomowner</structfield></entry>
3735 <entry><type>oid</type></entry>
3736 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
3737 <entry>Owner of the large object</entry>
3741 <entry><structfield>lomacl</structfield></entry>
3742 <entry><type>aclitem[]</type></entry>
3745 Access privileges; see
3746 <xref linkend="sql-grant"> and
3747 <xref linkend="sql-revoke">
3757 <sect1 id="catalog-pg-namespace">
3758 <title><structname>pg_namespace</structname></title>
3760 <indexterm zone="catalog-pg-namespace">
3761 <primary>pg_namespace</primary>
3765 The catalog <structname>pg_namespace</> stores namespaces.
3766 A namespace is the structure underlying SQL schemas: each namespace
3767 can have a separate collection of relations, types, etc. without name
3772 <title><structname>pg_namespace</> Columns</title>
3779 <entry>References</entry>
3780 <entry>Description</entry>
3786 <entry><structfield>nspname</structfield></entry>
3787 <entry><type>name</type></entry>
3789 <entry>Name of the namespace</entry>
3793 <entry><structfield>nspowner</structfield></entry>
3794 <entry><type>oid</type></entry>
3795 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
3796 <entry>Owner of the namespace</entry>
3800 <entry><structfield>nspacl</structfield></entry>
3801 <entry><type>aclitem[]</type></entry>
3804 Access privileges; see
3805 <xref linkend="sql-grant"> and
3806 <xref linkend="sql-revoke">
3817 <sect1 id="catalog-pg-opclass">
3818 <title><structname>pg_opclass</structname></title>
3820 <indexterm zone="catalog-pg-opclass">
3821 <primary>pg_opclass</primary>
3825 The catalog <structname>pg_opclass</structname> defines
3826 index access method operator classes. Each operator class defines
3827 semantics for index columns of a particular data type and a particular
3828 index access method. An operator class essentially specifies that a
3829 particular operator family is applicable to a particular indexable column
3830 data type. The set of operators from the family that are actually usable
3831 with the indexed column are whichever ones accept the column's data type
3832 as their lefthand input.
3836 Operator classes are described at length in <xref linkend="xindex">.
3840 <title><structname>pg_opclass</> Columns</title>
3847 <entry>References</entry>
3848 <entry>Description</entry>
3854 <entry><structfield>opcmethod</structfield></entry>
3855 <entry><type>oid</type></entry>
3856 <entry><literal><link linkend="catalog-pg-am"><structname>pg_am</structname></link>.oid</literal></entry>
3857 <entry>Index access method operator class is for</entry>
3861 <entry><structfield>opcname</structfield></entry>
3862 <entry><type>name</type></entry>
3864 <entry>Name of this operator class</entry>
3868 <entry><structfield>opcnamespace</structfield></entry>
3869 <entry><type>oid</type></entry>
3870 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
3871 <entry>Namespace of this operator class</entry>
3875 <entry><structfield>opcowner</structfield></entry>
3876 <entry><type>oid</type></entry>
3877 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
3878 <entry>Owner of the operator class</entry>
3882 <entry><structfield>opcfamily</structfield></entry>
3883 <entry><type>oid</type></entry>
3884 <entry><literal><link linkend="catalog-pg-opfamily"><structname>pg_opfamily</structname></link>.oid</literal></entry>
3885 <entry>Operator family containing the operator class</entry>
3889 <entry><structfield>opcintype</structfield></entry>
3890 <entry><type>oid</type></entry>
3891 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
3892 <entry>Data type that the operator class indexes</entry>
3896 <entry><structfield>opcdefault</structfield></entry>
3897 <entry><type>bool</type></entry>
3899 <entry>True if this operator class is the default for <structfield>opcintype</></entry>
3903 <entry><structfield>opckeytype</structfield></entry>
3904 <entry><type>oid</type></entry>
3905 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
3906 <entry>Type of data stored in index, or zero if same as <structfield>opcintype</></entry>
3914 An operator class's <structfield>opcmethod</> must match the
3915 <structname>opfmethod</> of its containing operator family.
3916 Also, there must be no more than one <structname>pg_opclass</structname>
3917 row having <structname>opcdefault</> true for any given combination of
3918 <structname>opcmethod</> and <structname>opcintype</>.
3924 <sect1 id="catalog-pg-operator">
3925 <title><structname>pg_operator</structname></title>
3927 <indexterm zone="catalog-pg-operator">
3928 <primary>pg_operator</primary>
3932 The catalog <structname>pg_operator</> stores information about operators.
3933 See <xref linkend="sql-createoperator">
3934 and <xref linkend="xoper"> for more information.
3938 <title><structname>pg_operator</> Columns</title>
3945 <entry>References</entry>
3946 <entry>Description</entry>
3952 <entry><structfield>oprname</structfield></entry>
3953 <entry><type>name</type></entry>
3955 <entry>Name of the operator</entry>
3959 <entry><structfield>oprnamespace</structfield></entry>
3960 <entry><type>oid</type></entry>
3961 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
3963 The OID of the namespace that contains this operator
3968 <entry><structfield>oprowner</structfield></entry>
3969 <entry><type>oid</type></entry>
3970 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
3971 <entry>Owner of the operator</entry>
3975 <entry><structfield>oprkind</structfield></entry>
3976 <entry><type>char</type></entry>
3979 <literal>b</> = infix (<quote>both</quote>), <literal>l</> = prefix
3980 (<quote>left</quote>), <literal>r</> = postfix (<quote>right</quote>)
3985 <entry><structfield>oprcanmerge</structfield></entry>
3986 <entry><type>bool</type></entry>
3988 <entry>This operator supports merge joins</entry>
3992 <entry><structfield>oprcanhash</structfield></entry>
3993 <entry><type>bool</type></entry>
3995 <entry>This operator supports hash joins</entry>
3999 <entry><structfield>oprleft</structfield></entry>
4000 <entry><type>oid</type></entry>
4001 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
4002 <entry>Type of the left operand</entry>
4006 <entry><structfield>oprright</structfield></entry>
4007 <entry><type>oid</type></entry>
4008 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
4009 <entry>Type of the right operand</entry>
4013 <entry><structfield>oprresult</structfield></entry>
4014 <entry><type>oid</type></entry>
4015 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
4016 <entry>Type of the result</entry>
4020 <entry><structfield>oprcom</structfield></entry>
4021 <entry><type>oid</type></entry>
4022 <entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</literal></entry>
4023 <entry>Commutator of this operator, if any</entry>
4027 <entry><structfield>oprnegate</structfield></entry>
4028 <entry><type>oid</type></entry>
4029 <entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</literal></entry>
4030 <entry>Negator of this operator, if any</entry>
4034 <entry><structfield>oprcode</structfield></entry>
4035 <entry><type>regproc</type></entry>
4036 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
4037 <entry>Function that implements this operator</entry>
4041 <entry><structfield>oprrest</structfield></entry>
4042 <entry><type>regproc</type></entry>
4043 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
4044 <entry>Restriction selectivity estimation function for this operator</entry>
4048 <entry><structfield>oprjoin</structfield></entry>
4049 <entry><type>regproc</type></entry>
4050 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
4051 <entry>Join selectivity estimation function for this operator</entry>
4058 Unused column contain zeroes. For example, <structfield>oprleft</structfield>
4059 is zero for a prefix operator.
4065 <sect1 id="catalog-pg-opfamily">
4066 <title><structname>pg_opfamily</structname></title>
4068 <indexterm zone="catalog-pg-opfamily">
4069 <primary>pg_opfamily</primary>
4073 The catalog <structname>pg_opfamily</structname> defines operator families.
4074 Each operator family is a collection of operators and associated
4075 support routines that implement the semantics specified for a particular
4076 index access method. Furthermore, the operators in a family are all
4077 <quote>compatible</>, in a way that is specified by the access method.
4078 The operator family concept allows cross-data-type operators to be used
4079 with indexes and to be reasoned about using knowledge of access method
4084 Operator families are described at length in <xref linkend="xindex">.
4088 <title><structname>pg_opfamily</> Columns</title>
4095 <entry>References</entry>
4096 <entry>Description</entry>
4102 <entry><structfield>opfmethod</structfield></entry>
4103 <entry><type>oid</type></entry>
4104 <entry><literal><link linkend="catalog-pg-am"><structname>pg_am</structname></link>.oid</literal></entry>
4105 <entry>Index access method operator family is for</entry>
4109 <entry><structfield>opfname</structfield></entry>
4110 <entry><type>name</type></entry>
4112 <entry>Name of this operator family</entry>
4116 <entry><structfield>opfnamespace</structfield></entry>
4117 <entry><type>oid</type></entry>
4118 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
4119 <entry>Namespace of this operator family</entry>
4123 <entry><structfield>opfowner</structfield></entry>
4124 <entry><type>oid</type></entry>
4125 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
4126 <entry>Owner of the operator family</entry>
4134 The majority of the information defining an operator family is not in its
4135 <structname>pg_opfamily</structname> row, but in the associated rows in
4136 <link linkend="catalog-pg-amop"><structname>pg_amop</structname></link>,
4137 <link linkend="catalog-pg-amproc"><structname>pg_amproc</structname></link>,
4139 <link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link>.
4145 <sect1 id="catalog-pg-pltemplate">
4146 <title><structname>pg_pltemplate</structname></title>
4148 <indexterm zone="catalog-pg-pltemplate">
4149 <primary>pg_pltemplate</primary>
4153 The catalog <structname>pg_pltemplate</structname> stores
4154 <quote>template</> information for procedural languages.
4155 A template for a language allows the language to be created in a
4156 particular database by a simple <command>CREATE LANGUAGE</> command,
4157 with no need to specify implementation details.
4161 Unlike most system catalogs, <structname>pg_pltemplate</structname>
4162 is shared across all databases of a cluster: there is only one
4163 copy of <structname>pg_pltemplate</structname> per cluster, not
4164 one per database. This allows the information to be accessible in
4165 each database as it is needed.
4169 <title><structname>pg_pltemplate</> Columns</title>
4176 <entry>Description</entry>
4182 <entry><structfield>tmplname</structfield></entry>
4183 <entry><type>name</type></entry>
4184 <entry>Name of the language this template is for</entry>
4188 <entry><structfield>tmpltrusted</structfield></entry>
4189 <entry><type>boolean</type></entry>
4190 <entry>True if language is considered trusted</entry>
4194 <entry><structfield>tmpldbacreate</structfield></entry>
4195 <entry><type>boolean</type></entry>
4196 <entry>True if language may be created by a database owner</entry>
4200 <entry><structfield>tmplhandler</structfield></entry>
4201 <entry><type>text</type></entry>
4202 <entry>Name of call handler function</entry>
4206 <entry><structfield>tmplinline</structfield></entry>
4207 <entry><type>text</type></entry>
4208 <entry>Name of anonymous-block handler function, or null if none</entry>
4212 <entry><structfield>tmplvalidator</structfield></entry>
4213 <entry><type>text</type></entry>
4214 <entry>Name of validator function, or null if none</entry>
4218 <entry><structfield>tmpllibrary</structfield></entry>
4219 <entry><type>text</type></entry>
4220 <entry>Path of shared library that implements language</entry>
4224 <entry><structfield>tmplacl</structfield></entry>
4225 <entry><type>aclitem[]</type></entry>
4226 <entry>Access privileges for template (not actually used)</entry>
4234 There are not currently any commands that manipulate procedural language
4235 templates; to change the built-in information, a superuser must modify
4236 the table using ordinary <command>INSERT</command>, <command>DELETE</command>,
4237 or <command>UPDATE</command> commands.
4242 It is likely that <structname>pg_pltemplate</> will be removed in some
4243 future release of <productname>PostgreSQL</productname>, in favor of
4244 keeping this knowledge about procedural languages in their respective
4245 extension installation scripts.
4252 <sect1 id="catalog-pg-proc">
4253 <title><structname>pg_proc</structname></title>
4255 <indexterm zone="catalog-pg-proc">
4256 <primary>pg_proc</primary>
4260 The catalog <structname>pg_proc</> stores information about functions (or procedures).
4261 See <xref linkend="sql-createfunction">
4262 and <xref linkend="xfunc"> for more information.
4266 The table contains data for aggregate functions as well as plain functions.
4267 If <structfield>proisagg</structfield> is true, there should be a matching
4268 row in <structfield>pg_aggregate</structfield>.
4272 <title><structname>pg_proc</> Columns</title>
4279 <entry>References</entry>
4280 <entry>Description</entry>
4286 <entry><structfield>proname</structfield></entry>
4287 <entry><type>name</type></entry>
4289 <entry>Name of the function</entry>
4293 <entry><structfield>pronamespace</structfield></entry>
4294 <entry><type>oid</type></entry>
4295 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
4297 The OID of the namespace that contains this function
4302 <entry><structfield>proowner</structfield></entry>
4303 <entry><type>oid</type></entry>
4304 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
4305 <entry>Owner of the function</entry>
4309 <entry><structfield>prolang</structfield></entry>
4310 <entry><type>oid</type></entry>
4311 <entry><literal><link linkend="catalog-pg-language"><structname>pg_language</structname></link>.oid</literal></entry>
4312 <entry>Implementation language or call interface of this function</entry>
4316 <entry><structfield>procost</structfield></entry>
4317 <entry><type>float4</type></entry>
4319 <entry>Estimated execution cost (in units of
4320 <xref linkend="guc-cpu-operator-cost">); if <structfield>proretset</>,
4321 this is cost per row returned</entry>
4325 <entry><structfield>prorows</structfield></entry>
4326 <entry><type>float4</type></entry>
4328 <entry>Estimated number of result rows (zero if not <structfield>proretset</>)</entry>
4332 <entry><structfield>provariadic</structfield></entry>
4333 <entry><type>oid</type></entry>
4334 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
4335 <entry>Data type of the variadic array parameter's elements,
4336 or zero if the function does not have a variadic parameter</entry>
4340 <entry><structfield>proisagg</structfield></entry>
4341 <entry><type>bool</type></entry>
4343 <entry>Function is an aggregate function</entry>
4347 <entry><structfield>proiswindow</structfield></entry>
4348 <entry><type>bool</type></entry>
4350 <entry>Function is a window function</entry>
4354 <entry><structfield>prosecdef</structfield></entry>
4355 <entry><type>bool</type></entry>
4357 <entry>Function is a security definer (i.e., a <quote>setuid</>
4362 <entry><structfield>proisstrict</structfield></entry>
4363 <entry><type>bool</type></entry>
4366 Function returns null if any call argument is null. In that
4367 case the function won't actually be called at all. Functions
4368 that are not <quote>strict</quote> must be prepared to handle
4374 <entry><structfield>proretset</structfield></entry>
4375 <entry><type>bool</type></entry>
4377 <entry>Function returns a set (i.e., multiple values of the specified
4382 <entry><structfield>provolatile</structfield></entry>
4383 <entry><type>char</type></entry>
4386 <structfield>provolatile</structfield> tells whether the function's
4387 result depends only on its input arguments, or is affected by outside
4389 It is <literal>i</literal> for <quote>immutable</> functions,
4390 which always deliver the same result for the same inputs.
4391 It is <literal>s</literal> for <quote>stable</> functions,
4392 whose results (for fixed inputs) do not change within a scan.
4393 It is <literal>v</literal> for <quote>volatile</> functions,
4394 whose results might change at any time. (Use <literal>v</literal> also
4395 for functions with side-effects, so that calls to them cannot get
4401 <entry><structfield>pronargs</structfield></entry>
4402 <entry><type>int2</type></entry>
4404 <entry>Number of input arguments</entry>
4408 <entry><structfield>pronargdefaults</structfield></entry>
4409 <entry><type>int2</type></entry>
4411 <entry>Number of arguments that have defaults</entry>
4415 <entry><structfield>prorettype</structfield></entry>
4416 <entry><type>oid</type></entry>
4417 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
4418 <entry>Data type of the return value</entry>
4422 <entry><structfield>proargtypes</structfield></entry>
4423 <entry><type>oidvector</type></entry>
4424 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
4426 An array with the data types of the function arguments. This includes
4427 only input arguments (including <literal>INOUT</literal> and
4428 <literal>VARIADIC</> arguments), and thus represents
4429 the call signature of the function.
4434 <entry><structfield>proallargtypes</structfield></entry>
4435 <entry><type>oid[]</type></entry>
4436 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
4438 An array with the data types of the function arguments. This includes
4439 all arguments (including <literal>OUT</literal> and
4440 <literal>INOUT</literal> arguments); however, if all the
4441 arguments are <literal>IN</literal> arguments, this field will be null.
4442 Note that subscripting is 1-based, whereas for historical reasons
4443 <structfield>proargtypes</> is subscripted from 0.
4448 <entry><structfield>proargmodes</structfield></entry>
4449 <entry><type>char[]</type></entry>
4452 An array with the modes of the function arguments, encoded as
4453 <literal>i</literal> for <literal>IN</> arguments,
4454 <literal>o</literal> for <literal>OUT</> arguments,
4455 <literal>b</literal> for <literal>INOUT</> arguments,
4456 <literal>v</literal> for <literal>VARIADIC</> arguments,
4457 <literal>t</literal> for <literal>TABLE</> arguments.
4458 If all the arguments are <literal>IN</literal> arguments,
4459 this field will be null.
4460 Note that subscripts correspond to positions of
4461 <structfield>proallargtypes</> not <structfield>proargtypes</>.
4466 <entry><structfield>proargnames</structfield></entry>
4467 <entry><type>text[]</type></entry>
4470 An array with the names of the function arguments.
4471 Arguments without a name are set to empty strings in the array.
4472 If none of the arguments have a name, this field will be null.
4473 Note that subscripts correspond to positions of
4474 <structfield>proallargtypes</> not <structfield>proargtypes</>.
4479 <entry><structfield>proargdefaults</structfield></entry>
4480 <entry><type>pg_node_tree</type></entry>
4483 Expression trees (in <function>nodeToString()</function> representation)
4484 for default values. This is a list with
4485 <structfield>pronargdefaults</> elements, corresponding to the last
4486 <replaceable>N</> <emphasis>input</> arguments (i.e., the last
4487 <replaceable>N</> <structfield>proargtypes</> positions).
4488 If none of the arguments have defaults, this field will be null.
4493 <entry><structfield>prosrc</structfield></entry>
4494 <entry><type>text</type></entry>
4497 This tells the function handler how to invoke the function. It
4498 might be the actual source code of the function for interpreted
4499 languages, a link symbol, a file name, or just about anything
4500 else, depending on the implementation language/call convention.
4505 <entry><structfield>probin</structfield></entry>
4506 <entry><type>text</type></entry>
4509 Additional information about how to invoke the function.
4510 Again, the interpretation is language-specific.
4515 <entry><structfield>proconfig</structfield></entry>
4516 <entry><type>text[]</type></entry>
4518 <entry>Function's local settings for run-time configuration variables</entry>
4522 <entry><structfield>proacl</structfield></entry>
4523 <entry><type>aclitem[]</type></entry>
4526 Access privileges; see
4527 <xref linkend="sql-grant"> and
4528 <xref linkend="sql-revoke">
4537 For compiled functions, both built-in and dynamically loaded,
4538 <structfield>prosrc</structfield> contains the function's C-language
4539 name (link symbol). For all other currently-known language types,
4540 <structfield>prosrc</structfield> contains the function's source
4541 text. <structfield>probin</structfield> is unused except for
4542 dynamically-loaded C functions, for which it gives the name of the
4543 shared library file containing the function.
4548 <sect1 id="catalog-pg-rewrite">
4549 <title><structname>pg_rewrite</structname></title>
4551 <indexterm zone="catalog-pg-rewrite">
4552 <primary>pg_rewrite</primary>
4556 The catalog <structname>pg_rewrite</structname> stores rewrite rules for tables and views.
4560 <title><structname>pg_rewrite</> Columns</title>
4567 <entry>References</entry>
4568 <entry>Description</entry>
4574 <entry><structfield>rulename</structfield></entry>
4575 <entry><type>name</type></entry>
4577 <entry>Rule name</entry>
4581 <entry><structfield>ev_class</structfield></entry>
4582 <entry><type>oid</type></entry>
4583 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
4584 <entry>The table this rule is for</entry>
4588 <entry><structfield>ev_attr</structfield></entry>
4589 <entry><type>int2</type></entry>
4591 <entry>The column this rule is for (currently, always zero to
4592 indicate the whole table)</entry>
4596 <entry><structfield>ev_type</structfield></entry>
4597 <entry><type>char</type></entry>
4600 Event type that the rule is for: 1 = <command>SELECT</>, 2 =
4601 <command>UPDATE</>, 3 = <command>INSERT</>, 4 =
4607 <entry><structfield>ev_enabled</structfield></entry>
4608 <entry><type>char</type></entry>
4611 Controls in which <xref linkend="guc-session-replication-role"> modes
4613 <literal>O</> = rule fires in <quote>origin</> and <quote>local</> modes,
4614 <literal>D</> = rule is disabled,
4615 <literal>R</> = rule fires in <quote>replica</> mode,
4616 <literal>A</> = rule fires always.
4621 <entry><structfield>is_instead</structfield></entry>
4622 <entry><type>bool</type></entry>
4624 <entry>True if the rule is an <literal>INSTEAD</literal> rule</entry>
4628 <entry><structfield>ev_qual</structfield></entry>
4629 <entry><type>pg_node_tree</type></entry>
4632 Expression tree (in the form of a
4633 <function>nodeToString()</function> representation) for the
4634 rule's qualifying condition
4639 <entry><structfield>ev_action</structfield></entry>
4640 <entry><type>pg_node_tree</type></entry>
4643 Query tree (in the form of a
4644 <function>nodeToString()</function> representation) for the
4654 <literal>pg_class.relhasrules</literal>
4655 must be true if a table has any rules in this catalog.
4662 <sect1 id="catalog-pg-seclabel">
4663 <title><structname>pg_seclabel</structname></title>
4665 <indexterm zone="catalog-pg-seclabel">
4666 <primary>pg_seclabel</primary>
4670 The catalog <structname>pg_seclabel</structname> stores security
4671 labels on database objects. See the
4672 <xref linkend="sql-security-label"> statement.
4676 <title><structname>pg_seclabel</structname> Columns</title>
4683 <entry>References</entry>
4684 <entry>Description</entry>
4690 <entry><structfield>objoid</structfield></entry>
4691 <entry><type>oid</type></entry>
4692 <entry>any OID column</entry>
4693 <entry>The OID of the object this security label pertains to</entry>
4697 <entry><structfield>classoid</structfield></entry>
4698 <entry><type>oid</type></entry>
4699 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
4700 <entry>The OID of the system catalog this object appears in</entry>
4704 <entry><structfield>objsubid</structfield></entry>
4705 <entry><type>int4</type></entry>
4708 For a security label on a table column, this is the column number (the
4709 <structfield>objoid</> and <structfield>classoid</> refer to
4710 the table itself). For all other object types, this column is
4716 <entry><structfield>provider</structfield></entry>
4717 <entry><type>text</type></entry>
4719 <entry>The label provider associated with this label.</entry>
4723 <entry><structfield>label</structfield></entry>
4724 <entry><type>text</type></entry>
4726 <entry>The security label applied to this object.</entry>
4733 <sect1 id="catalog-pg-shdepend">
4734 <title><structname>pg_shdepend</structname></title>
4736 <indexterm zone="catalog-pg-shdepend">
4737 <primary>pg_shdepend</primary>
4741 The catalog <structname>pg_shdepend</structname> records the
4742 dependency relationships between database objects and shared objects,
4743 such as roles. This information allows
4744 <productname>PostgreSQL</productname> to ensure that those objects are
4745 unreferenced before attempting to delete them.
4749 See also <link linkend="catalog-pg-depend"><structname>pg_depend</structname></link>,
4750 which performs a similar function for dependencies involving objects
4751 within a single database.
4755 Unlike most system catalogs, <structname>pg_shdepend</structname>
4756 is shared across all databases of a cluster: there is only one
4757 copy of <structname>pg_shdepend</structname> per cluster, not
4762 <title><structname>pg_shdepend</> Columns</title>
4769 <entry>References</entry>
4770 <entry>Description</entry>
4776 <entry><structfield>dbid</structfield></entry>
4777 <entry><type>oid</type></entry>
4778 <entry><literal><link linkend="catalog-pg-database"><structname>pg_database</structname></link>.oid</literal></entry>
4779 <entry>The OID of the database the dependent object is in,
4780 or zero for a shared object</entry>
4784 <entry><structfield>classid</structfield></entry>
4785 <entry><type>oid</type></entry>
4786 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
4787 <entry>The OID of the system catalog the dependent object is in</entry>
4791 <entry><structfield>objid</structfield></entry>
4792 <entry><type>oid</type></entry>
4793 <entry>any OID column</entry>
4794 <entry>The OID of the specific dependent object</entry>
4798 <entry><structfield>objsubid</structfield></entry>
4799 <entry><type>int4</type></entry>
4802 For a table column, this is the column number (the
4803 <structfield>objid</> and <structfield>classid</> refer to the
4804 table itself). For all other object types, this column is zero.
4809 <entry><structfield>refclassid</structfield></entry>
4810 <entry><type>oid</type></entry>
4811 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
4812 <entry>The OID of the system catalog the referenced object is in
4813 (must be a shared catalog)</entry>
4817 <entry><structfield>refobjid</structfield></entry>
4818 <entry><type>oid</type></entry>
4819 <entry>any OID column</entry>
4820 <entry>The OID of the specific referenced object</entry>
4824 <entry><structfield>deptype</structfield></entry>
4825 <entry><type>char</type></entry>
4828 A code defining the specific semantics of this dependency relationship; see text
4837 In all cases, a <structname>pg_shdepend</structname> entry indicates that
4838 the referenced object cannot be dropped without also dropping the dependent
4839 object. However, there are several subflavors identified by
4840 <structfield>deptype</>:
4844 <term><symbol>SHARED_DEPENDENCY_OWNER</> (<literal>o</>)</term>
4847 The referenced object (which must be a role) is the owner of the
4854 <term><symbol>SHARED_DEPENDENCY_ACL</> (<literal>a</>)</term>
4857 The referenced object (which must be a role) is mentioned in the
4858 ACL (access control list, i.e., privileges list) of the
4859 dependent object. (A <symbol>SHARED_DEPENDENCY_ACL</> entry is
4860 not made for the owner of the object, since the owner will have
4861 a <symbol>SHARED_DEPENDENCY_OWNER</> entry anyway.)
4867 <term><symbol>SHARED_DEPENDENCY_PIN</> (<literal>p</>)</term>
4870 There is no dependent object; this type of entry is a signal
4871 that the system itself depends on the referenced object, and so
4872 that object must never be deleted. Entries of this type are
4873 created only by <command>initdb</command>. The columns for the
4874 dependent object contain zeroes.
4880 Other dependency flavors might be needed in future. Note in particular
4881 that the current definition only supports roles as referenced objects.
4886 <sect1 id="catalog-pg-shdescription">
4887 <title><structname>pg_shdescription</structname></title>
4889 <indexterm zone="catalog-pg-shdescription">
4890 <primary>pg_shdescription</primary>
4894 The catalog <structname>pg_shdescription</structname> stores optional
4895 descriptions (comments) for shared database objects. Descriptions can be
4896 manipulated with the <xref linkend="sql-comment"> command and viewed with
4897 <application>psql</application>'s <literal>\d</literal> commands.
4901 See also <link linkend="catalog-pg-description"><structname>pg_description</structname></link>,
4902 which performs a similar function for descriptions involving objects
4903 within a single database.
4907 Unlike most system catalogs, <structname>pg_shdescription</structname>
4908 is shared across all databases of a cluster: there is only one
4909 copy of <structname>pg_shdescription</structname> per cluster, not
4914 <title><structname>pg_shdescription</> Columns</title>
4921 <entry>References</entry>
4922 <entry>Description</entry>
4928 <entry><structfield>objoid</structfield></entry>
4929 <entry><type>oid</type></entry>
4930 <entry>any OID column</entry>
4931 <entry>The OID of the object this description pertains to</entry>
4935 <entry><structfield>classoid</structfield></entry>
4936 <entry><type>oid</type></entry>
4937 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
4938 <entry>The OID of the system catalog this object appears in</entry>
4942 <entry><structfield>description</structfield></entry>
4943 <entry><type>text</type></entry>
4945 <entry>Arbitrary text that serves as the description of this object</entry>
4954 <sect1 id="catalog-pg-statistic">
4955 <title><structname>pg_statistic</structname></title>
4957 <indexterm zone="catalog-pg-statistic">
4958 <primary>pg_statistic</primary>
4962 The catalog <structname>pg_statistic</structname> stores
4963 statistical data about the contents of the database. Entries are
4964 created by <xref linkend="sql-analyze">
4965 and subsequently used by the query planner. Note that all the
4966 statistical data is inherently approximate, even assuming that it
4971 Normally there is one entry, with <structfield>stainherit</> =
4972 <literal>false</>, for each table column that has been analyzed.
4973 If the table has inheritance children, a second entry with
4974 <structfield>stainherit</> = <literal>true</> is also created. This row
4975 represents the column's statistics over the inheritance tree, i.e.,
4976 statistics for the data you'd see with
4977 <literal>SELECT <replaceable>column</> FROM <replaceable>table</>*</literal>,
4978 whereas the <structfield>stainherit</> = <literal>false</> row represents
4980 <literal>SELECT <replaceable>column</> FROM ONLY <replaceable>table</></literal>.
4984 <structname>pg_statistic</structname> also stores statistical data about
4985 the values of index expressions. These are described as if they were
4986 actual data columns; in particular, <structfield>starelid</structfield>
4987 references the index. No entry is made for an ordinary non-expression
4988 index column, however, since it would be redundant with the entry
4989 for the underlying table column. Currently, entries for index expressions
4990 always have <structfield>stainherit</> = <literal>false</>.
4994 Since different kinds of statistics might be appropriate for different
4995 kinds of data, <structname>pg_statistic</structname> is designed not
4996 to assume very much about what sort of statistics it stores. Only
4997 extremely general statistics (such as nullness) are given dedicated
4998 columns in <structname>pg_statistic</structname>. Everything else
4999 is stored in <quote>slots</quote>, which are groups of associated columns
5000 whose content is identified by a code number in one of the slot's columns.
5001 For more information see
5002 <filename>src/include/catalog/pg_statistic.h</filename>.
5006 <structname>pg_statistic</structname> should not be readable by the
5007 public, since even statistical information about a table's contents
5008 might be considered sensitive. (Example: minimum and maximum values
5009 of a salary column might be quite interesting.)
5010 <link linkend="view-pg-stats"><structname>pg_stats</structname></link>
5011 is a publicly readable view on
5012 <structname>pg_statistic</structname> that only exposes information
5013 about those tables that are readable by the current user.
5017 <title><structname>pg_statistic</> Columns</title>
5024 <entry>References</entry>
5025 <entry>Description</entry>
5031 <entry><structfield>starelid</structfield></entry>
5032 <entry><type>oid</type></entry>
5033 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
5034 <entry>The table or index that the described column belongs to</entry>
5038 <entry><structfield>staattnum</structfield></entry>
5039 <entry><type>int2</type></entry>
5040 <entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</literal></entry>
5041 <entry>The number of the described column</entry>
5045 <entry><structfield>stainherit</structfield></entry>
5046 <entry><type>bool</type></entry>
5048 <entry>If true, the stats include inheritance child columns, not just the
5049 values in the specified relation</entry>
5053 <entry><structfield>stanullfrac</structfield></entry>
5054 <entry><type>float4</type></entry>
5056 <entry>The fraction of the column's entries that are null</entry>
5060 <entry><structfield>stawidth</structfield></entry>
5061 <entry><type>int4</type></entry>
5063 <entry>The average stored width, in bytes, of nonnull entries</entry>
5067 <entry><structfield>stadistinct</structfield></entry>
5068 <entry><type>float4</type></entry>
5070 <entry>The number of distinct nonnull data values in the column.
5071 A value greater than zero is the actual number of distinct values.
5072 A value less than zero is the negative of a multiplier for the number
5073 of rows in the table; for example, a column in which values appear about
5074 twice on the average could be represented by
5075 <structfield>stadistinct</> = -0.5.
5076 A zero value means the number of distinct values is unknown.
5081 <entry><structfield>stakind<replaceable>N</></structfield></entry>
5082 <entry><type>int2</type></entry>
5085 A code number indicating the kind of statistics stored in the
5086 <replaceable>N</>th <quote>slot</quote> of the
5087 <structname>pg_statistic</structname> row.
5092 <entry><structfield>staop<replaceable>N</></structfield></entry>
5093 <entry><type>oid</type></entry>
5094 <entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</literal></entry>
5096 An operator used to derive the statistics stored in the
5097 <replaceable>N</>th <quote>slot</quote>. For example, a
5098 histogram slot would show the <literal><</literal> operator
5099 that defines the sort order of the data.
5104 <entry><structfield>stanumbers<replaceable>N</></structfield></entry>
5105 <entry><type>float4[]</type></entry>
5108 Numerical statistics of the appropriate kind for the
5109 <replaceable>N</>th <quote>slot</quote>, or null if the slot
5110 kind does not involve numerical values
5115 <entry><structfield>stavalues<replaceable>N</></structfield></entry>
5116 <entry><type>anyarray</type></entry>
5119 Column data values of the appropriate kind for the
5120 <replaceable>N</>th <quote>slot</quote>, or null if the slot
5121 kind does not store any data values. Each array's element
5122 values are actually of the specific column's data type, so there
5123 is no way to define these columns' type more specifically than
5134 <sect1 id="catalog-pg-tablespace">
5135 <title><structname>pg_tablespace</structname></title>
5137 <indexterm zone="catalog-pg-tablespace">
5138 <primary>pg_tablespace</primary>
5142 The catalog <structname>pg_tablespace</structname> stores information
5143 about the available tablespaces. Tables can be placed in particular
5144 tablespaces to aid administration of disk layout.
5148 Unlike most system catalogs, <structname>pg_tablespace</structname>
5149 is shared across all databases of a cluster: there is only one
5150 copy of <structname>pg_tablespace</structname> per cluster, not
5155 <title><structname>pg_tablespace</> Columns</title>
5162 <entry>References</entry>
5163 <entry>Description</entry>
5169 <entry><structfield>spcname</structfield></entry>
5170 <entry><type>name</type></entry>
5172 <entry>Tablespace name</entry>
5176 <entry><structfield>spcowner</structfield></entry>
5177 <entry><type>oid</type></entry>
5178 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
5179 <entry>Owner of the tablespace, usually the user who created it</entry>
5183 <entry><structfield>spclocation</structfield></entry>
5184 <entry><type>text</type></entry>
5186 <entry>Location (directory path) of the tablespace</entry>
5190 <entry><structfield>spcacl</structfield></entry>
5191 <entry><type>aclitem[]</type></entry>
5194 Access privileges; see
5195 <xref linkend="sql-grant"> and
5196 <xref linkend="sql-revoke">
5202 <entry><structfield>spcoptions</structfield></entry>
5203 <entry><type>text[]</type></entry>
5206 Tablespace-level options, as <quote>keyword=value</> strings
5215 <sect1 id="catalog-pg-trigger">
5216 <title><structname>pg_trigger</structname></title>
5218 <indexterm zone="catalog-pg-trigger">
5219 <primary>pg_trigger</primary>
5223 The catalog <structname>pg_trigger</structname> stores triggers on tables
5225 See <xref linkend="sql-createtrigger">
5226 for more information.
5230 <title><structname>pg_trigger</> Columns</title>
5237 <entry>References</entry>
5238 <entry>Description</entry>
5244 <entry><structfield>tgrelid</structfield></entry>
5245 <entry><type>oid</type></entry>
5246 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
5247 <entry>The table this trigger is on</entry>
5251 <entry><structfield>tgname</structfield></entry>
5252 <entry><type>name</type></entry>
5254 <entry>Trigger name (must be unique among triggers of same table)</entry>
5258 <entry><structfield>tgfoid</structfield></entry>
5259 <entry><type>oid</type></entry>
5260 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
5261 <entry>The function to be called</entry>
5265 <entry><structfield>tgtype</structfield></entry>
5266 <entry><type>int2</type></entry>
5268 <entry>Bit mask identifying trigger firing conditions</entry>
5272 <entry><structfield>tgenabled</structfield></entry>
5273 <entry><type>char</type></entry>
5276 Controls in which <xref linkend="guc-session-replication-role"> modes
5278 <literal>O</> = trigger fires in <quote>origin</> and <quote>local</> modes,
5279 <literal>D</> = trigger is disabled,
5280 <literal>R</> = trigger fires in <quote>replica</> mode,
5281 <literal>A</> = trigger fires always.
5286 <entry><structfield>tgisinternal</structfield></entry>
5287 <entry><type>bool</type></entry>
5289 <entry>True if trigger is internally generated (usually, to enforce
5290 the constraint identified by <structfield>tgconstraint</>)</entry>
5294 <entry><structfield>tgconstrrelid</structfield></entry>
5295 <entry><type>oid</type></entry>
5296 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
5297 <entry>The table referenced by a referential integrity constraint</entry>
5301 <entry><structfield>tgconstrindid</structfield></entry>
5302 <entry><type>oid</type></entry>
5303 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
5304 <entry>The index supporting a unique, primary key, or referential integrity constraint</entry>
5308 <entry><structfield>tgconstraint</structfield></entry>
5309 <entry><type>oid</type></entry>
5310 <entry><literal><link linkend="catalog-pg-constraint"><structname>pg_constraint</structname></link>.oid</literal></entry>
5311 <entry>The <structname>pg_constraint</> entry associated with the trigger, if any</entry>
5315 <entry><structfield>tgdeferrable</structfield></entry>
5316 <entry><type>bool</type></entry>
5318 <entry>True if constraint trigger is deferrable</entry>
5322 <entry><structfield>tginitdeferred</structfield></entry>
5323 <entry><type>bool</type></entry>
5325 <entry>True if constraint trigger is initially deferred</entry>
5329 <entry><structfield>tgnargs</structfield></entry>
5330 <entry><type>int2</type></entry>
5332 <entry>Number of argument strings passed to trigger function</entry>
5336 <entry><structfield>tgattr</structfield></entry>
5337 <entry><type>int2vector</type></entry>
5338 <entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</literal></entry>
5339 <entry>Column numbers, if trigger is column-specific; otherwise an
5344 <entry><structfield>tgargs</structfield></entry>
5345 <entry><type>bytea</type></entry>
5347 <entry>Argument strings to pass to trigger, each NULL-terminated</entry>
5351 <entry><structfield>tgqual</structfield></entry>
5352 <entry><type>pg_node_tree</type></entry>
5354 <entry>Expression tree (in <function>nodeToString()</function>
5355 representation) for the trigger's <literal>WHEN</> condition, or null
5363 Currently, column-specific triggering is supported only for
5364 <literal>UPDATE</> events, and so <structfield>tgattr</> is relevant
5365 only for that event type. <structfield>tgtype</structfield> might
5366 contain bits for other event types as well, but those are presumed
5367 to be table-wide regardless of what is in <structfield>tgattr</>.
5372 When <structfield>tgconstraint</> is nonzero,
5373 <structfield>tgconstrrelid</>, <structfield>tgconstrindid</>,
5374 <structfield>tgdeferrable</>, and <structfield>tginitdeferred</> are
5375 largely redundant with the referenced <structname>pg_constraint</> entry.
5376 However, it is possible for a non-deferrable trigger to be associated
5377 with a deferrable constraint: foreign key constraints can have some
5378 deferrable and some non-deferrable triggers.
5384 <literal>pg_class.relhastriggers</literal>
5385 must be true if a relation has any triggers in this catalog.
5392 <sect1 id="catalog-pg-ts-config">
5393 <title><structname>pg_ts_config</structname></title>
5395 <indexterm zone="catalog-pg-ts-config">
5396 <primary>pg_ts_config</primary>
5400 The <structname>pg_ts_config</structname> catalog contains entries
5401 representing text search configurations. A configuration specifies
5402 a particular text search parser and a list of dictionaries to use
5403 for each of the parser's output token types. The parser is shown
5404 in the <structname>pg_ts_config</structname> entry, but the
5405 token-to-dictionary mapping is defined by subsidiary entries in <link
5406 linkend="catalog-pg-ts-config-map"><structname>pg_ts_config_map</structname></link>.
5410 <productname>PostgreSQL</productname>'s text search features are
5411 described at length in <xref linkend="textsearch">.
5415 <title><structname>pg_ts_config</> Columns</title>
5422 <entry>References</entry>
5423 <entry>Description</entry>
5429 <entry><structfield>cfgname</structfield></entry>
5430 <entry><type>name</type></entry>
5432 <entry>Text search configuration name</entry>
5436 <entry><structfield>cfgnamespace</structfield></entry>
5437 <entry><type>oid</type></entry>
5438 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
5440 The OID of the namespace that contains this configuration
5445 <entry><structfield>cfgowner</structfield></entry>
5446 <entry><type>oid</type></entry>
5447 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
5448 <entry>Owner of the configuration</entry>
5452 <entry><structfield>cfgparser</structfield></entry>
5453 <entry><type>oid</type></entry>
5454 <entry><literal><link linkend="catalog-pg-ts-parser"><structname>pg_ts_parser</structname></link>.oid</literal></entry>
5455 <entry>The OID of the text search parser for this configuration</entry>
5463 <sect1 id="catalog-pg-ts-config-map">
5464 <title><structname>pg_ts_config_map</structname></title>
5466 <indexterm zone="catalog-pg-ts-config-map">
5467 <primary>pg_ts_config_map</primary>
5471 The <structname>pg_ts_config_map</structname> catalog contains entries
5472 showing which text search dictionaries should be consulted, and in
5473 what order, for each output token type of each text search configuration's
5478 <productname>PostgreSQL</productname>'s text search features are
5479 described at length in <xref linkend="textsearch">.
5483 <title><structname>pg_ts_config_map</> Columns</title>
5490 <entry>References</entry>
5491 <entry>Description</entry>
5497 <entry><structfield>mapcfg</structfield></entry>
5498 <entry><type>oid</type></entry>
5499 <entry><literal><link linkend="catalog-pg-ts-config"><structname>pg_ts_config</structname></link>.oid</literal></entry>
5500 <entry>The OID of the <structname>pg_ts_config</> entry owning this map entry</entry>
5504 <entry><structfield>maptokentype</structfield></entry>
5505 <entry><type>integer</type></entry>
5507 <entry>A token type emitted by the configuration's parser</entry>
5511 <entry><structfield>mapseqno</structfield></entry>
5512 <entry><type>integer</type></entry>
5514 <entry>Order in which to consult this entry (lower
5515 <structfield>mapseqno</>s first)</entry>
5519 <entry><structfield>mapdict</structfield></entry>
5520 <entry><type>oid</type></entry>
5521 <entry><literal><link linkend="catalog-pg-ts-dict"><structname>pg_ts_dict</structname></link>.oid</literal></entry>
5522 <entry>The OID of the text search dictionary to consult</entry>
5530 <sect1 id="catalog-pg-ts-dict">
5531 <title><structname>pg_ts_dict</structname></title>
5533 <indexterm zone="catalog-pg-ts-dict">
5534 <primary>pg_ts_dict</primary>
5538 The <structname>pg_ts_dict</structname> catalog contains entries
5539 defining text search dictionaries. A dictionary depends on a text
5540 search template, which specifies all the implementation functions
5541 needed; the dictionary itself provides values for the user-settable
5542 parameters supported by the template. This division of labor allows
5543 dictionaries to be created by unprivileged users. The parameters
5544 are specified by a text string <structfield>dictinitoption</>,
5545 whose format and meaning vary depending on the template.
5549 <productname>PostgreSQL</productname>'s text search features are
5550 described at length in <xref linkend="textsearch">.
5554 <title><structname>pg_ts_dict</> Columns</title>
5561 <entry>References</entry>
5562 <entry>Description</entry>
5568 <entry><structfield>dictname</structfield></entry>
5569 <entry><type>name</type></entry>
5571 <entry>Text search dictionary name</entry>
5575 <entry><structfield>dictnamespace</structfield></entry>
5576 <entry><type>oid</type></entry>
5577 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
5579 The OID of the namespace that contains this dictionary
5584 <entry><structfield>dictowner</structfield></entry>
5585 <entry><type>oid</type></entry>
5586 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
5587 <entry>Owner of the dictionary</entry>
5591 <entry><structfield>dicttemplate</structfield></entry>
5592 <entry><type>oid</type></entry>
5593 <entry><literal><link linkend="catalog-pg-ts-template"><structname>pg_ts_template</structname></link>.oid</literal></entry>
5594 <entry>The OID of the text search template for this dictionary</entry>
5598 <entry><structfield>dictinitoption</structfield></entry>
5599 <entry><type>text</type></entry>
5601 <entry>Initialization option string for the template</entry>
5609 <sect1 id="catalog-pg-ts-parser">
5610 <title><structname>pg_ts_parser</structname></title>
5612 <indexterm zone="catalog-pg-ts-parser">
5613 <primary>pg_ts_parser</primary>
5617 The <structname>pg_ts_parser</structname> catalog contains entries
5618 defining text search parsers. A parser is responsible for splitting
5619 input text into lexemes and assigning a token type to each lexeme.
5620 Since a parser must be implemented by C-language-level functions,
5621 creation of new parsers is restricted to database superusers.
5625 <productname>PostgreSQL</productname>'s text search features are
5626 described at length in <xref linkend="textsearch">.
5630 <title><structname>pg_ts_parser</> Columns</title>
5637 <entry>References</entry>
5638 <entry>Description</entry>
5644 <entry><structfield>prsname</structfield></entry>
5645 <entry><type>name</type></entry>
5647 <entry>Text search parser name</entry>
5651 <entry><structfield>prsnamespace</structfield></entry>
5652 <entry><type>oid</type></entry>
5653 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
5655 The OID of the namespace that contains this parser
5660 <entry><structfield>prsstart</structfield></entry>
5661 <entry><type>regproc</type></entry>
5662 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
5663 <entry>OID of the parser's startup function</entry>
5667 <entry><structfield>prstoken</structfield></entry>
5668 <entry><type>regproc</type></entry>
5669 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
5670 <entry>OID of the parser's next-token function</entry>
5674 <entry><structfield>prsend</structfield></entry>
5675 <entry><type>regproc</type></entry>
5676 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
5677 <entry>OID of the parser's shutdown function</entry>
5681 <entry><structfield>prsheadline</structfield></entry>
5682 <entry><type>regproc</type></entry>
5683 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
5684 <entry>OID of the parser's headline function</entry>
5688 <entry><structfield>prslextype</structfield></entry>
5689 <entry><type>regproc</type></entry>
5690 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
5691 <entry>OID of the parser's lextype function</entry>
5699 <sect1 id="catalog-pg-ts-template">
5700 <title><structname>pg_ts_template</structname></title>
5702 <indexterm zone="catalog-pg-ts-template">
5703 <primary>pg_ts_template</primary>
5707 The <structname>pg_ts_template</structname> catalog contains entries
5708 defining text search templates. A template is the implementation
5709 skeleton for a class of text search dictionaries.
5710 Since a template must be implemented by C-language-level functions,
5711 creation of new templates is restricted to database superusers.
5715 <productname>PostgreSQL</productname>'s text search features are
5716 described at length in <xref linkend="textsearch">.
5720 <title><structname>pg_ts_template</> Columns</title>
5727 <entry>References</entry>
5728 <entry>Description</entry>
5734 <entry><structfield>tmplname</structfield></entry>
5735 <entry><type>name</type></entry>
5737 <entry>Text search template name</entry>
5741 <entry><structfield>tmplnamespace</structfield></entry>
5742 <entry><type>oid</type></entry>
5743 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
5745 The OID of the namespace that contains this template
5750 <entry><structfield>tmplinit</structfield></entry>
5751 <entry><type>regproc</type></entry>
5752 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
5753 <entry>OID of the template's initialization function</entry>
5757 <entry><structfield>tmpllexize</structfield></entry>
5758 <entry><type>regproc</type></entry>
5759 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
5760 <entry>OID of the template's lexize function</entry>
5768 <sect1 id="catalog-pg-type">
5769 <title><structname>pg_type</structname></title>
5771 <indexterm zone="catalog-pg-type">
5772 <primary>pg_type</primary>
5776 The catalog <structname>pg_type</structname> stores information about data
5777 types. Base types and enum types (scalar types) are created with
5778 <xref linkend="sql-createtype">, and
5780 <xref linkend="sql-createdomain">.
5781 A composite type is automatically created for each table in the database, to
5782 represent the row structure of the table. It is also possible to create
5783 composite types with <command>CREATE TYPE AS</command>.
5787 <title><structname>pg_type</> Columns</title>
5794 <entry>References</entry>
5795 <entry>Description</entry>
5801 <entry><structfield>typname</structfield></entry>
5802 <entry><type>name</type></entry>
5804 <entry>Data type name</entry>
5808 <entry><structfield>typnamespace</structfield></entry>
5809 <entry><type>oid</type></entry>
5810 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
5812 The OID of the namespace that contains this type
5817 <entry><structfield>typowner</structfield></entry>
5818 <entry><type>oid</type></entry>
5819 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
5820 <entry>Owner of the type</entry>
5824 <entry><structfield>typlen</structfield></entry>
5825 <entry><type>int2</type></entry>
5828 For a fixed-size type, <structfield>typlen</structfield> is the number
5829 of bytes in the internal representation of the type. But for a
5830 variable-length type, <structfield>typlen</structfield> is negative.
5831 -1 indicates a <quote>varlena</> type (one that has a length word),
5832 -2 indicates a null-terminated C string.
5837 <entry><structfield>typbyval</structfield></entry>
5838 <entry><type>bool</type></entry>
5841 <structfield>typbyval</structfield> determines whether internal
5842 routines pass a value of this type by value or by reference.
5843 <structfield>typbyval</structfield> had better be false if
5844 <structfield>typlen</structfield> is not 1, 2, or 4 (or 8 on machines
5845 where Datum is 8 bytes).
5846 Variable-length types are always passed by reference. Note that
5847 <structfield>typbyval</structfield> can be false even if the
5848 length would allow pass-by-value.
5853 <entry><structfield>typtype</structfield></entry>
5854 <entry><type>char</type></entry>
5857 <structfield>typtype</structfield> is
5858 <literal>b</literal> for a base type,
5859 <literal>c</literal> for a composite type (e.g., a table's row type),
5860 <literal>d</literal> for a domain,
5861 <literal>e</literal> for an enum type,
5862 or <literal>p</literal> for a pseudo-type.
5863 See also <structfield>typrelid</structfield> and
5864 <structfield>typbasetype</structfield>.
5869 <entry><structfield>typcategory</structfield></entry>
5870 <entry><type>char</type></entry>
5873 <structfield>typcategory</structfield> is an arbitrary classification
5874 of data types that is used by the parser to determine which implicit
5875 casts should be <quote>preferred</>.
5876 See <xref linkend="catalog-typcategory-table">.
5881 <entry><structfield>typispreferred</structfield></entry>
5882 <entry><type>bool</type></entry>
5885 True if the type is a preferred cast target within its
5886 <structfield>typcategory</structfield>
5891 <entry><structfield>typisdefined</structfield></entry>
5892 <entry><type>bool</type></entry>
5895 True if the type is defined, false if this is a placeholder
5896 entry for a not-yet-defined type. When
5897 <structfield>typisdefined</structfield> is false, nothing
5898 except the type name, namespace, and OID can be relied on.
5903 <entry><structfield>typdelim</structfield></entry>
5904 <entry><type>char</type></entry>
5907 Character that separates two values of this type when parsing
5908 array input. Note that the delimiter is associated with the array
5909 element data type, not the array data type.
5914 <entry><structfield>typrelid</structfield></entry>
5915 <entry><type>oid</type></entry>
5916 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
5918 If this is a composite type (see
5919 <structfield>typtype</structfield>), then this column points to
5920 the <structname>pg_class</structname> entry that defines the
5921 corresponding table. (For a free-standing composite type, the
5922 <structname>pg_class</structname> entry doesn't really represent
5923 a table, but it is needed anyway for the type's
5924 <structname>pg_attribute</structname> entries to link to.)
5925 Zero for non-composite types.
5930 <entry><structfield>typelem</structfield></entry>
5931 <entry><type>oid</type></entry>
5932 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
5934 If <structfield>typelem</structfield> is not 0 then it
5935 identifies another row in <structname>pg_type</structname>.
5936 The current type can then be subscripted like an array yielding
5937 values of type <structfield>typelem</structfield>. A
5938 <quote>true</quote> array type is variable length
5939 (<structfield>typlen</structfield> = -1),
5940 but some fixed-length (<structfield>typlen</structfield> > 0) types
5941 also have nonzero <structfield>typelem</structfield>, for example
5942 <type>name</type> and <type>point</type>.
5943 If a fixed-length type has a <structfield>typelem</structfield> then
5944 its internal representation must be some number of values of the
5945 <structfield>typelem</structfield> data type with no other data.
5946 Variable-length array types have a header defined by the array
5952 <entry><structfield>typarray</structfield></entry>
5953 <entry><type>oid</type></entry>
5954 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
5956 If <structfield>typarray</structfield> is not 0 then it
5957 identifies another row in <structname>pg_type</structname>, which
5958 is the <quote>true</quote> array type having this type as element
5963 <entry><structfield>typinput</structfield></entry>
5964 <entry><type>regproc</type></entry>
5965 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
5966 <entry>Input conversion function (text format)</entry>
5970 <entry><structfield>typoutput</structfield></entry>
5971 <entry><type>regproc</type></entry>
5972 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
5973 <entry>Output conversion function (text format)</entry>
5977 <entry><structfield>typreceive</structfield></entry>
5978 <entry><type>regproc</type></entry>
5979 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
5980 <entry>Input conversion function (binary format), or 0 if none</entry>
5984 <entry><structfield>typsend</structfield></entry>
5985 <entry><type>regproc</type></entry>
5986 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
5987 <entry>Output conversion function (binary format), or 0 if none</entry>
5991 <entry><structfield>typmodin</structfield></entry>
5992 <entry><type>regproc</type></entry>
5993 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
5994 <entry>Type modifier input function, or 0 if type does not support modifiers</entry>
5998 <entry><structfield>typmodout</structfield></entry>
5999 <entry><type>regproc</type></entry>
6000 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
6001 <entry>Type modifier output function, or 0 to use the standard format</entry>
6005 <entry><structfield>typanalyze</structfield></entry>
6006 <entry><type>regproc</type></entry>
6007 <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
6008 <entry>Custom <command>ANALYZE</command> function, or 0 to use the standard function</entry>
6012 <entry><structfield>typalign</structfield></entry>
6013 <entry><type>char</type></entry>
6017 <structfield>typalign</structfield> is the alignment required
6018 when storing a value of this type. It applies to storage on
6019 disk as well as most representations of the value inside
6020 <productname>PostgreSQL</>.
6021 When multiple values are stored consecutively, such
6022 as in the representation of a complete row on disk, padding is
6023 inserted before a datum of this type so that it begins on the
6024 specified boundary. The alignment reference is the beginning
6025 of the first datum in the sequence.
6029 Possible values are:
6032 <para><literal>c</> = <type>char</type> alignment, i.e., no alignment needed.</para>
6035 <para><literal>s</> = <type>short</type> alignment (2 bytes on most machines).</para>
6038 <para><literal>i</> = <type>int</type> alignment (4 bytes on most machines).</para>
6041 <para><literal>d</> = <type>double</type> alignment (8 bytes on many machines, but by no means all).</para>
6046 For types used in system tables, it is critical that the size
6047 and alignment defined in <structname>pg_type</structname>
6048 agree with the way that the compiler will lay out the column in
6049 a structure representing a table row.
6055 <entry><structfield>typstorage</structfield></entry>
6056 <entry><type>char</type></entry>
6059 <structfield>typstorage</structfield> tells for varlena
6060 types (those with <structfield>typlen</structfield> = -1) if
6061 the type is prepared for toasting and what the default strategy
6062 for attributes of this type should be.
6066 <para><literal>p</>: Value must always be stored plain.</para>
6070 <literal>e</>: Value can be stored in a <quote>secondary</quote>
6071 relation (if relation has one, see
6072 <literal>pg_class.reltoastrelid</literal>).
6076 <para><literal>m</>: Value can be stored compressed inline.</para>
6079 <para><literal>x</>: Value can be stored compressed inline or stored in <quote>secondary</quote> storage.</para>
6082 Note that <literal>m</> columns can also be moved out to secondary
6083 storage, but only as a last resort (<literal>e</> and <literal>x</> columns are
6089 <entry><structfield>typnotnull</structfield></entry>
6090 <entry><type>bool</type></entry>
6093 <structfield>typnotnull</structfield> represents a not-null
6094 constraint on a type. Used for domains only.
6099 <entry><structfield>typbasetype</structfield></entry>
6100 <entry><type>oid</type></entry>
6101 <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
6103 If this is a domain (see <structfield>typtype</structfield>), then
6104 <structfield>typbasetype</structfield> identifies the type that this
6105 one is based on. Zero if this type is not a domain.
6110 <entry><structfield>typtypmod</structfield></entry>
6111 <entry><type>int4</type></entry>
6114 Domains use <structfield>typtypmod</structfield> to record the <literal>typmod</>
6115 to be applied to their base type (-1 if base type does not use a
6116 <literal>typmod</>). -1 if this type is not a domain.
6121 <entry><structfield>typndims</structfield></entry>
6122 <entry><type>int4</type></entry>
6125 <structfield>typndims</structfield> is the number of array dimensions
6126 for a domain over an array (that is, <structfield>typbasetype</> is
6128 Zero for types other than domains over array types.
6133 <entry><structfield>typcollation</structfield></entry>
6134 <entry><type>oid</type></entry>
6135 <entry><literal><link linkend="catalog-pg-collation"><structname>pg_collation</structname></link>.oid</literal></entry>
6137 <structfield>typcollation</structfield> specifies the collation
6138 of the type. If the type does not support collations, this will
6139 be zero. A base type that supports collations will have
6140 <symbol>DEFAULT_COLLATION_OID</symbol> here. A domain over a
6141 collatable type can have some other collation OID, if one was defined
6147 <entry><structfield>typdefaultbin</structfield></entry>
6148 <entry><type>pg_node_tree</type></entry>
6151 If <structfield>typdefaultbin</> is not null, it is the
6152 <function>nodeToString()</function>
6153 representation of a default expression for the type. This is
6154 only used for domains.
6159 <entry><structfield>typdefault</structfield></entry>
6160 <entry><type>text</type></entry>
6163 <structfield>typdefault</> is null if the type has no associated
6164 default value. If <structfield>typdefaultbin</> is not null,
6165 <structfield>typdefault</> must contain a human-readable version of the
6166 default expression represented by <structfield>typdefaultbin</>. If
6167 <structfield>typdefaultbin</> is null and <structfield>typdefault</> is
6168 not, then <structfield>typdefault</> is the external representation of
6169 the type's default value, which can be fed to the type's input
6170 converter to produce a constant.
6178 <xref linkend="catalog-typcategory-table"> lists the system-defined values
6179 of <structfield>typcategory</>. Any future additions to this list will
6180 also be upper-case ASCII letters. All other ASCII characters are reserved
6181 for user-defined categories.
6184 <table id="catalog-typcategory-table">
6185 <title><structfield>typcategory</> Codes</title>
6191 <entry>Category</entry>
6197 <entry><literal>A</literal></entry>
6198 <entry>Array types</entry>
6201 <entry><literal>B</literal></entry>
6202 <entry>Boolean types</entry>
6205 <entry><literal>C</literal></entry>
6206 <entry>Composite types</entry>
6209 <entry><literal>D</literal></entry>
6210 <entry>Date/time types</entry>
6213 <entry><literal>E</literal></entry>
6214 <entry>Enum types</entry>
6217 <entry><literal>G</literal></entry>
6218 <entry>Geometric types</entry>
6221 <entry><literal>I</literal></entry>
6222 <entry>Network address types</entry>
6225 <entry><literal>N</literal></entry>
6226 <entry>Numeric types</entry>
6229 <entry><literal>P</literal></entry>
6230 <entry>Pseudo-types</entry>
6233 <entry><literal>S</literal></entry>
6234 <entry>String types</entry>
6237 <entry><literal>T</literal></entry>
6238 <entry>Timespan types</entry>
6241 <entry><literal>U</literal></entry>
6242 <entry>User-defined types</entry>
6245 <entry><literal>V</literal></entry>
6246 <entry>Bit-string types</entry>
6249 <entry><literal>X</literal></entry>
6250 <entry><type>unknown</> type</entry>
6259 <sect1 id="catalog-pg-user-mapping">
6260 <title><structname>pg_user_mapping</structname></title>
6262 <indexterm zone="catalog-pg-user-mapping">
6263 <primary>pg_user_mapping</primary>
6267 The catalog <structname>pg_user_mapping</structname> stores
6268 the mappings from local user to remote. Access to this catalog is
6269 restricted from normal users, use the view
6270 <link linkend="view-pg-user-mappings"><structname>pg_user_mappings</structname></link>
6275 <title><structname>pg_user_mapping</> Columns</title>
6282 <entry>References</entry>
6283 <entry>Description</entry>
6289 <entry><structfield>umuser</structfield></entry>
6290 <entry><type>oid</type></entry>
6291 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
6292 <entry>OID of the local role being mapped, 0 if the user mapping is public</entry>
6296 <entry><structfield>umserver</structfield></entry>
6297 <entry><type>oid</type></entry>
6298 <entry><literal><link linkend="catalog-pg-foreign-server"><structname>pg_foreign_server</structname></link>.oid</literal></entry>
6300 The OID of the foreign server that contains this mapping
6305 <entry><structfield>umoptions</structfield></entry>
6306 <entry><type>text[]</type></entry>
6309 User mapping specific options, as <quote>keyword=value</> strings
6318 <sect1 id="views-overview">
6319 <title>System Views</title>
6322 In addition to the system catalogs, <productname>PostgreSQL</productname>
6323 provides a number of built-in views. Some system views provide convenient
6324 access to some commonly used queries on the system catalogs. Other views
6325 provide access to internal server state.
6329 The information schema (<xref linkend="information-schema">) provides
6330 an alternative set of views which overlap the functionality of the system
6331 views. Since the information schema is SQL-standard whereas the views
6332 described here are <productname>PostgreSQL</productname>-specific,
6333 it's usually better to use the information schema if it provides all
6334 the information you need.
6338 <xref linkend="view-table"> lists the system views described here.
6339 More detailed documentation of each view follows below.
6340 There are some additional views that provide access to the results of
6341 the statistics collector; they are described in <xref
6342 linkend="monitoring-stats-views-table">.
6346 Except where noted, all the views described here are read-only.
6349 <table id="view-table">
6350 <title>System Views</title>
6355 <entry>View Name</entry>
6356 <entry>Purpose</entry>
6362 <entry><link linkend="view-pg-available-extensions"><structname>pg_available_extensions</structname></link></entry>
6363 <entry>available extensions</entry>
6367 <entry><link linkend="view-pg-available-extension-versions"><structname>pg_available_extension_versions</structname></link></entry>
6368 <entry>available versions of extensions</entry>
6372 <entry><link linkend="view-pg-cursors"><structname>pg_cursors</structname></link></entry>
6373 <entry>open cursors</entry>
6377 <entry><link linkend="view-pg-group"><structname>pg_group</structname></link></entry>
6378 <entry>groups of database users</entry>
6382 <entry><link linkend="view-pg-indexes"><structname>pg_indexes</structname></link></entry>
6383 <entry>indexes</entry>
6387 <entry><link linkend="view-pg-locks"><structname>pg_locks</structname></link></entry>
6388 <entry>currently held locks</entry>
6392 <entry><link linkend="view-pg-prepared-statements"><structname>pg_prepared_statements</structname></link></entry>
6393 <entry>prepared statements</entry>
6397 <entry><link linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link></entry>
6398 <entry>prepared transactions</entry>
6402 <entry><link linkend="view-pg-roles"><structname>pg_roles</structname></link></entry>
6403 <entry>database roles</entry>
6407 <entry><link linkend="view-pg-rules"><structname>pg_rules</structname></link></entry>
6408 <entry>rules</entry>
6412 <entry><link linkend="view-pg-seclabels"><structname>pg_seclabels</structname></link></entry>
6413 <entry>security labels</entry>
6417 <entry><link linkend="view-pg-settings"><structname>pg_settings</structname></link></entry>
6418 <entry>parameter settings</entry>
6422 <entry><link linkend="view-pg-shadow"><structname>pg_shadow</structname></link></entry>
6423 <entry>database users</entry>
6427 <entry><link linkend="view-pg-stats"><structname>pg_stats</structname></link></entry>
6428 <entry>planner statistics</entry>
6432 <entry><link linkend="view-pg-tables"><structname>pg_tables</structname></link></entry>
6433 <entry>tables</entry>
6437 <entry><link linkend="view-pg-timezone-abbrevs"><structname>pg_timezone_abbrevs</structname></link></entry>
6438 <entry>time zone abbreviations</entry>
6442 <entry><link linkend="view-pg-timezone-names"><structname>pg_timezone_names</structname></link></entry>
6443 <entry>time zone names</entry>
6447 <entry><link linkend="view-pg-user"><structname>pg_user</structname></link></entry>
6448 <entry>database users</entry>
6452 <entry><link linkend="view-pg-user-mappings"><structname>pg_user_mappings</structname></link></entry>
6453 <entry>user mappings</entry>
6457 <entry><link linkend="view-pg-views"><structname>pg_views</structname></link></entry>
6458 <entry>views</entry>
6466 <sect1 id="view-pg-available-extensions">
6467 <title><structname>pg_available_extensions</structname></title>
6469 <indexterm zone="view-pg-available-extensions">
6470 <primary>pg_available_extensions</primary>
6474 The <structname>pg_available_extensions</structname> view lists the
6475 extensions that are available for installation.
6477 <link linkend="catalog-pg-extension"><structname>pg_extension</structname></link>
6478 catalog, which shows the extensions currently installed.
6482 <title><structname>pg_available_extensions</> Columns</title>
6489 <entry>Description</entry>
6495 <entry><structfield>name</structfield></entry>
6496 <entry><type>name</type></entry>
6497 <entry>Extension name</entry>
6501 <entry><structfield>default_version</structfield></entry>
6502 <entry><type>text</type></entry>
6503 <entry>Name of default version, or <literal>NULL</literal> if none is
6508 <entry><structfield>installed_version</structfield></entry>
6509 <entry><type>text</type></entry>
6510 <entry>Currently installed version of the extension,
6511 or <literal>NULL</literal> if not installed</entry>
6515 <entry><structfield>comment</structfield></entry>
6516 <entry><type>text</type></entry>
6517 <entry>Comment string from the extension's control file</entry>
6524 The <structname>pg_available_extensions</structname> view is read only.
6528 <sect1 id="view-pg-available-extension-versions">
6529 <title><structname>pg_available_extension_versions</structname></title>
6531 <indexterm zone="view-pg-available-extension-versions">
6532 <primary>pg_available_extension_versions</primary>
6536 The <structname>pg_available_extension_versions</structname> view lists the
6537 specific extension versions that are available for installation.
6539 linkend="catalog-pg-extension"><structname>pg_extension</structname></link>
6540 catalog, which shows the extensions currently installed.
6544 <title><structname>pg_available_extension_versions</> Columns</title>
6551 <entry>Description</entry>
6557 <entry><structfield>name</structfield></entry>
6558 <entry><type>name</type></entry>
6559 <entry>Extension name</entry>
6563 <entry><structfield>version</structfield></entry>
6564 <entry><type>text</type></entry>
6565 <entry>Version name</entry>
6569 <entry><structfield>installed</structfield></entry>
6570 <entry><type>bool</type></entry>
6571 <entry>True if this version of this extension is currently
6576 <entry><structfield>superuser</structfield></entry>
6577 <entry><type>bool</type></entry>
6578 <entry>True if only superusers are allowed to install this extension</entry>
6582 <entry><structfield>relocatable</structfield></entry>
6583 <entry><type>bool</type></entry>
6584 <entry>True if extension can be relocated to another schema</entry>
6588 <entry><structfield>schema</structfield></entry>
6589 <entry><type>name</type></entry>
6590 <entry>Name of the schema that the extension must be installed into,
6591 or <literal>NULL</literal> if partially or fully relocatable</entry>
6595 <entry><structfield>requires</structfield></entry>
6596 <entry><type>name[]</type></entry>
6597 <entry>Names of prerequisite extensions,
6598 or <literal>NULL</literal> if none</entry>
6602 <entry><structfield>comment</structfield></entry>
6603 <entry><type>text</type></entry>
6604 <entry>Comment string from the extension's control file</entry>
6611 The <structname>pg_available_extension_versions</structname> view is read
6616 <sect1 id="view-pg-cursors">
6617 <title><structname>pg_cursors</structname></title>
6619 <indexterm zone="view-pg-cursors">
6620 <primary>pg_cursors</primary>
6624 The <structname>pg_cursors</structname> view lists the cursors that
6625 are currently available. Cursors can be defined in several ways:
6629 via the <xref linkend="sql-declare">
6636 via the Bind message in the frontend/backend protocol, as
6637 described in <xref linkend="protocol-flow-ext-query">
6643 via the Server Programming Interface (SPI), as described in
6644 <xref linkend="spi-interface">
6649 The <structname>pg_cursors</structname> view displays cursors
6650 created by any of these means. Cursors only exist for the duration
6651 of the transaction that defines them, unless they have been
6652 declared <literal>WITH HOLD</literal>. Therefore non-holdable
6653 cursors are only present in the view until the end of their
6654 creating transaction.
6658 Cursors are used internally to implement some of the components
6659 of <productname>PostgreSQL</>, such as procedural languages.
6660 Therefore, the <structname>pg_cursors</> view might include cursors
6661 that have not been explicitly created by the user.
6667 <title><structname>pg_cursors</> Columns</title>
6674 <entry>Description</entry>
6680 <entry><structfield>name</structfield></entry>
6681 <entry><type>text</type></entry>
6682 <entry>The name of the cursor</entry>
6686 <entry><structfield>statement</structfield></entry>
6687 <entry><type>text</type></entry>
6688 <entry>The verbatim query string submitted to declare this cursor</entry>
6692 <entry><structfield>is_holdable</structfield></entry>
6693 <entry><type>boolean</type></entry>
6695 <literal>true</literal> if the cursor is holdable (that is, it
6696 can be accessed after the transaction that declared the cursor
6697 has committed); <literal>false</literal> otherwise
6702 <entry><structfield>is_binary</structfield></entry>
6703 <entry><type>boolean</type></entry>
6705 <literal>true</literal> if the cursor was declared
6706 <literal>BINARY</literal>; <literal>false</literal>
6712 <entry><structfield>is_scrollable</structfield></entry>
6713 <entry><type>boolean</type></entry>
6715 <literal>true</> if the cursor is scrollable (that is, it
6716 allows rows to be retrieved in a nonsequential manner);
6717 <literal>false</literal> otherwise
6722 <entry><structfield>creation_time</structfield></entry>
6723 <entry><type>timestamptz</type></entry>
6724 <entry>The time at which the cursor was declared</entry>
6731 The <structname>pg_cursors</structname> view is read only.
6736 <sect1 id="view-pg-group">
6737 <title><structname>pg_group</structname></title>
6739 <indexterm zone="view-pg-group">
6740 <primary>pg_group</primary>
6744 The view <structname>pg_group</structname> exists for backwards
6745 compatibility: it emulates a catalog that existed in
6746 <productname>PostgreSQL</productname> before version 8.1.
6747 It shows the names and members of all roles that are marked as not
6748 <structfield>rolcanlogin</>, which is an approximation to the set
6749 of roles that are being used as groups.
6753 <title><structname>pg_group</> Columns</title>
6760 <entry>References</entry>
6761 <entry>Description</entry>
6767 <entry><structfield>groname</structfield></entry>
6768 <entry><type>name</type></entry>
6769 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.rolname</literal></entry>
6770 <entry>Name of the group</entry>
6774 <entry><structfield>grosysid</structfield></entry>
6775 <entry><type>oid</type></entry>
6776 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
6777 <entry>ID of this group</entry>
6781 <entry><structfield>grolist</structfield></entry>
6782 <entry><type>oid[]</type></entry>
6783 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
6784 <entry>An array containing the IDs of the roles in this group</entry>
6792 <sect1 id="view-pg-indexes">
6793 <title><structname>pg_indexes</structname></title>
6795 <indexterm zone="view-pg-indexes">
6796 <primary>pg_indexes</primary>
6800 The view <structname>pg_indexes</structname> provides access to
6801 useful information about each index in the database.
6805 <title><structname>pg_indexes</> Columns</title>
6812 <entry>References</entry>
6813 <entry>Description</entry>
6818 <entry><structfield>schemaname</structfield></entry>
6819 <entry><type>name</type></entry>
6820 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.nspname</literal></entry>
6821 <entry>Name of schema containing table and index</entry>
6824 <entry><structfield>tablename</structfield></entry>
6825 <entry><type>name</type></entry>
6826 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relname</literal></entry>
6827 <entry>Name of table the index is for</entry>
6830 <entry><structfield>indexname</structfield></entry>
6831 <entry><type>name</type></entry>
6832 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relname</literal></entry>
6833 <entry>Name of index</entry>
6836 <entry><structfield>tablespace</structfield></entry>
6837 <entry><type>name</type></entry>
6838 <entry><literal><link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.spcname</literal></entry>
6839 <entry>Name of tablespace containing index (null if default for database)</entry>
6842 <entry><structfield>indexdef</structfield></entry>
6843 <entry><type>text</type></entry>
6845 <entry>Index definition (a reconstructed <command>CREATE INDEX</command>
6854 <sect1 id="view-pg-locks">
6855 <title><structname>pg_locks</structname></title>
6857 <indexterm zone="view-pg-locks">
6858 <primary>pg_locks</primary>
6862 The view <structname>pg_locks</structname> provides access to
6863 information about the locks held by open transactions within the
6864 database server. See <xref linkend="mvcc"> for more discussion
6869 <structname>pg_locks</structname> contains one row per active lockable
6870 object, requested lock mode, and relevant transaction. Thus, the same
6871 lockable object might
6872 appear many times, if multiple transactions are holding or waiting
6873 for locks on it. However, an object that currently has no locks on it
6874 will not appear at all.
6878 There are several distinct types of lockable objects:
6879 whole relations (e.g., tables), individual pages of relations,
6880 individual tuples of relations,
6881 transaction IDs (both virtual and permanent IDs),
6882 and general database objects (identified by class OID and object OID,
6883 in the same way as in <structname>pg_description</structname> or
6884 <structname>pg_depend</structname>). Also, the right to extend a
6885 relation is represented as a separate lockable object.
6889 <title><structname>pg_locks</> Columns</title>
6896 <entry>References</entry>
6897 <entry>Description</entry>
6902 <entry><structfield>locktype</structfield></entry>
6903 <entry><type>text</type></entry>
6906 Type of the lockable object:
6907 <literal>relation</>,
6911 <literal>transactionid</>,
6912 <literal>virtualxid</>,
6914 <literal>userlock</>, or
6915 <literal>advisory</>
6919 <entry><structfield>database</structfield></entry>
6920 <entry><type>oid</type></entry>
6921 <entry><literal><link linkend="catalog-pg-database"><structname>pg_database</structname></link>.oid</literal></entry>
6923 OID of the database in which the object exists, or
6924 zero if the object is a shared object, or
6925 null if the object is a transaction ID
6929 <entry><structfield>relation</structfield></entry>
6930 <entry><type>oid</type></entry>
6931 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
6933 OID of the relation, or null if the object is not
6934 a relation or part of a relation
6938 <entry><structfield>page</structfield></entry>
6939 <entry><type>integer</type></entry>
6942 Page number within the relation, or null if the object
6943 is not a tuple or relation page
6947 <entry><structfield>tuple</structfield></entry>
6948 <entry><type>smallint</type></entry>
6951 Tuple number within the page, or null if the object is not a tuple
6955 <entry><structfield>virtualxid</structfield></entry>
6956 <entry><type>text</type></entry>
6959 Virtual ID of a transaction, or null if the object is not a
6960 virtual transaction ID
6964 <entry><structfield>transactionid</structfield></entry>
6965 <entry><type>xid</type></entry>
6968 ID of a transaction, or null if the object is not a transaction ID
6972 <entry><structfield>classid</structfield></entry>
6973 <entry><type>oid</type></entry>
6974 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
6976 OID of the system catalog containing the object, or null if the
6977 object is not a general database object
6981 <entry><structfield>objid</structfield></entry>
6982 <entry><type>oid</type></entry>
6983 <entry>any OID column</entry>
6985 OID of the object within its system catalog, or null if the
6986 object is not a general database object.
6987 For advisory locks it is used to distinguish the two key
6988 spaces (1 for an int8 key, 2 for two int4 keys).
6992 <entry><structfield>objsubid</structfield></entry>
6993 <entry><type>smallint</type></entry>
6996 For a table column, this is the column number (the
6997 <structfield>classid</> and <structfield>objid</> refer to the
6998 table itself). For all other object types, this column is
6999 zero. Null if the object is not a general database object
7003 <entry><structfield>virtualtransaction</structfield></entry>
7004 <entry><type>text</type></entry>
7007 Virtual ID of the transaction that is holding or awaiting this lock
7011 <entry><structfield>pid</structfield></entry>
7012 <entry><type>integer</type></entry>
7015 Process ID of the server process holding or awaiting this
7016 lock. Null if the lock is held by a prepared transaction.
7020 <entry><structfield>mode</structfield></entry>
7021 <entry><type>text</type></entry>
7023 <entry>Name of the lock mode held or desired by this process (see <xref
7024 linkend="locking-tables"> and <xref linkend="xact-serializable">)</entry>
7027 <entry><structfield>granted</structfield></entry>
7028 <entry><type>boolean</type></entry>
7030 <entry>True if lock is held, false if lock is awaited</entry>
7037 <structfield>granted</structfield> is true in a row representing a lock
7038 held by the indicated transaction. False indicates that this transaction is
7039 currently waiting to acquire this lock, which implies that some other
7040 transaction is holding a conflicting lock mode on the same lockable object.
7041 The waiting transaction will sleep until the other lock is released (or a
7042 deadlock situation is detected). A single transaction can be waiting to
7043 acquire at most one lock at a time.
7047 Every transaction holds an exclusive lock on its virtual transaction ID for
7048 its entire duration. If a permanent ID is assigned to the transaction
7049 (which normally happens only if the transaction changes the state of the
7050 database), it also holds an exclusive lock on its permanent transaction ID
7051 until it ends. When one transaction finds it necessary to wait specifically
7052 for another transaction, it does so by attempting to acquire share lock on
7053 the other transaction ID (either virtual or permanent ID depending on the
7054 situation). That will succeed only when the other transaction
7055 terminates and releases its locks.
7059 Although tuples are a lockable type of object,
7060 information about row-level locks is stored on disk, not in memory,
7061 and therefore row-level locks normally do not appear in this view.
7062 If a transaction is waiting for a
7063 row-level lock, it will usually appear in the view as waiting for the
7064 permanent transaction ID of the current holder of that row lock.
7068 Advisory locks can be acquired on keys consisting of either a single
7069 <type>bigint</type> value or two integer values. A <type>bigint</type> key is displayed with its
7070 high-order half in the <structfield>classid</> column, its low-order half
7071 in the <structfield>objid</> column, and <structfield>objsubid</> equal
7072 to 1. Integer keys are displayed with the first key in the
7073 <structfield>classid</> column, the second key in the <structfield>objid</>
7074 column, and <structfield>objsubid</> equal to 2. The actual meaning of
7075 the keys is up to the user. Advisory locks are local to each database,
7076 so the <structfield>database</> column is meaningful for an advisory lock.
7080 When the <structname>pg_locks</structname> view is accessed, the
7081 internal lock manager data structures are momentarily locked, and
7082 a copy is made for the view to display. This ensures that the
7083 view produces a consistent set of results, while not blocking
7084 normal lock manager operations longer than necessary. Nonetheless
7085 there could be some impact on database performance if this view is
7086 frequently accessed.
7090 <structname>pg_locks</structname> provides a global view of all locks
7091 in the database cluster, not only those relevant to the current database.
7092 Although its <structfield>relation</structfield> column can be joined
7093 against <structname>pg_class</>.<structfield>oid</> to identify locked
7094 relations, this will only work correctly for relations in the current
7095 database (those for which the <structfield>database</structfield> column
7096 is either the current database's OID or zero).
7100 The <structfield>pid</structfield> column can be joined to the
7101 <structfield>procpid</structfield> column of the
7102 <structname>pg_stat_activity</structname> view to get more
7103 information on the session holding or waiting to hold each lock.
7104 Also, if you are using prepared transactions, the
7105 <structfield>transaction</> column can be joined to the
7106 <structfield>transaction</structfield> column of the
7107 <structname>pg_prepared_xacts</structname> view to get more
7108 information on prepared transactions that hold locks.
7109 (A prepared transaction can never be waiting for a lock,
7110 but it continues to hold the locks it acquired while running.)
7115 <sect1 id="view-pg-prepared-statements">
7116 <title><structname>pg_prepared_statements</structname></title>
7118 <indexterm zone="view-pg-prepared-statements">
7119 <primary>pg_prepared_statements</primary>
7123 The <structname>pg_prepared_statements</structname> view displays
7124 all the prepared statements that are available in the current
7125 session. See <xref linkend="sql-prepare"> for more information about prepared
7130 <structname>pg_prepared_statements</structname> contains one row
7131 for each prepared statement. Rows are added to the view when a new
7132 prepared statement is created and removed when a prepared statement
7133 is released (for example, via the <xref linkend="sql-deallocate"> command).
7137 <title><structname>pg_prepared_statements</> Columns</title>
7144 <entry>Description</entry>
7149 <entry><structfield>name</structfield></entry>
7150 <entry><type>text</type></entry>
7152 The identifier of the prepared statement
7156 <entry><structfield>statement</structfield></entry>
7157 <entry><type>text</type></entry>
7159 The query string submitted by the client to create this
7160 prepared statement. For prepared statements created via SQL,
7161 this is the <command>PREPARE</command> statement submitted by
7162 the client. For prepared statements created via the
7163 frontend/backend protocol, this is the text of the prepared
7168 <entry><structfield>prepare_time</structfield></entry>
7169 <entry><type>timestamptz</type></entry>
7171 The time at which the prepared statement was created
7175 <entry><structfield>parameter_types</structfield></entry>
7176 <entry><type>regtype[]</type></entry>
7178 The expected parameter types for the prepared statement in the
7179 form of an array of <type>regtype</type>. The OID corresponding
7180 to an element of this array can be obtained by casting the
7181 <type>regtype</type> value to <type>oid</type>.
7185 <entry><structfield>from_sql</structfield></entry>
7186 <entry><type>boolean</type></entry>
7188 <literal>true</literal> if the prepared statement was created
7189 via the <command>PREPARE</command> SQL statement;
7190 <literal>false</literal> if the statement was prepared via the
7191 frontend/backend protocol
7199 The <structname>pg_prepared_statements</structname> view is read only.
7203 <sect1 id="view-pg-prepared-xacts">
7204 <title><structname>pg_prepared_xacts</structname></title>
7206 <indexterm zone="view-pg-prepared-xacts">
7207 <primary>pg_prepared_xacts</primary>
7211 The view <structname>pg_prepared_xacts</structname> displays
7212 information about transactions that are currently prepared for two-phase
7213 commit (see <xref linkend="sql-prepare-transaction"> for details).
7217 <structname>pg_prepared_xacts</structname> contains one row per prepared
7218 transaction. An entry is removed when the transaction is committed or
7223 <title><structname>pg_prepared_xacts</> Columns</title>
7230 <entry>References</entry>
7231 <entry>Description</entry>
7236 <entry><structfield>transaction</structfield></entry>
7237 <entry><type>xid</type></entry>
7240 Numeric transaction identifier of the prepared transaction
7244 <entry><structfield>gid</structfield></entry>
7245 <entry><type>text</type></entry>
7248 Global transaction identifier that was assigned to the transaction
7252 <entry><structfield>prepared</structfield></entry>
7253 <entry><type>timestamp with time zone</type></entry>
7256 Time at which the transaction was prepared for commit
7260 <entry><structfield>owner</structfield></entry>
7261 <entry><type>name</type></entry>
7262 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.rolname</literal></entry>
7264 Name of the user that executed the transaction
7268 <entry><structfield>database</structfield></entry>
7269 <entry><type>name</type></entry>
7270 <entry><literal><link linkend="catalog-pg-database"><structname>pg_database</structname></link>.datname</literal></entry>
7272 Name of the database in which the transaction was executed
7280 When the <structname>pg_prepared_xacts</structname> view is accessed, the
7281 internal transaction manager data structures are momentarily locked, and
7282 a copy is made for the view to display. This ensures that the
7283 view produces a consistent set of results, while not blocking
7284 normal operations longer than necessary. Nonetheless
7285 there could be some impact on database performance if this view is
7286 frequently accessed.
7291 <sect1 id="view-pg-roles">
7292 <title><structname>pg_roles</structname></title>
7294 <indexterm zone="view-pg-roles">
7295 <primary>pg_roles</primary>
7299 The view <structname>pg_roles</structname> provides access to
7300 information about database roles. This is simply a publicly
7302 <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>
7303 that blanks out the password field.
7307 This view explicitly exposes the OID column of the underlying table,
7308 since that is needed to do joins to other catalogs.
7312 <title><structname>pg_roles</> Columns</title>
7319 <entry>References</entry>
7320 <entry>Description</entry>
7326 <entry><structfield>rolname</structfield></entry>
7327 <entry><type>name</type></entry>
7329 <entry>Role name</entry>
7333 <entry><structfield>rolsuper</structfield></entry>
7334 <entry><type>bool</type></entry>
7336 <entry>Role has superuser privileges</entry>
7340 <entry><structfield>rolinherit</structfield></entry>
7341 <entry><type>bool</type></entry>
7343 <entry>Role automatically inherits privileges of roles it is a
7348 <entry><structfield>rolcreaterole</structfield></entry>
7349 <entry><type>bool</type></entry>
7351 <entry>Role can create more roles</entry>
7355 <entry><structfield>rolcreatedb</structfield></entry>
7356 <entry><type>bool</type></entry>
7358 <entry>Role can create databases</entry>
7362 <entry><structfield>rolcatupdate</structfield></entry>
7363 <entry><type>bool</type></entry>
7366 Role can update system catalogs directly. (Even a superuser cannot do
7367 this unless this column is true.)
7372 <entry><structfield>rolcanlogin</structfield></entry>
7373 <entry><type>bool</type></entry>
7376 Role can log in. That is, this role can be given as the initial
7377 session authorization identifier
7382 <entry><structfield>rolconnlimit</structfield></entry>
7383 <entry><type>int4</type></entry>
7386 For roles that can log in, this sets maximum number of concurrent
7387 connections this role can make. -1 means no limit.
7392 <entry><structfield>rolpassword</structfield></entry>
7393 <entry><type>text</type></entry>
7395 <entry>Not the password (always reads as <literal>********</>)</entry>
7399 <entry><structfield>rolvaliduntil</structfield></entry>
7400 <entry><type>timestamptz</type></entry>
7402 <entry>Password expiry time (only used for password authentication);
7403 null if no expiration</entry>
7407 <entry><structfield>oid</structfield></entry>
7408 <entry><type>oid</type></entry>
7409 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
7410 <entry>ID of role</entry>
7418 <sect1 id="view-pg-rules">
7419 <title><structname>pg_rules</structname></title>
7421 <indexterm zone="view-pg-rules">
7422 <primary>pg_rules</primary>
7426 The view <structname>pg_rules</structname> provides access to
7427 useful information about query rewrite rules.
7431 <title><structname>pg_rules</> Columns</title>
7438 <entry>References</entry>
7439 <entry>Description</entry>
7444 <entry><structfield>schemaname</structfield></entry>
7445 <entry><type>name</type></entry>
7446 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.nspname</literal></entry>
7447 <entry>Name of schema containing table</entry>
7450 <entry><structfield>tablename</structfield></entry>
7451 <entry><type>name</type></entry>
7452 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relname</literal></entry>
7453 <entry>Name of table the rule is for</entry>
7456 <entry><structfield>rulename</structfield></entry>
7457 <entry><type>name</type></entry>
7458 <entry><literal><link linkend="catalog-pg-rewrite"><structname>pg_rewrite</structname></link>.rulename</literal></entry>
7459 <entry>Name of rule</entry>
7462 <entry><structfield>definition</structfield></entry>
7463 <entry><type>text</type></entry>
7465 <entry>Rule definition (a reconstructed creation command)</entry>
7472 The <structname>pg_rules</> view excludes the <literal>ON SELECT</> rules
7473 of views; those can be seen in <structname>pg_views</>.
7478 <sect1 id="view-pg-seclabels">
7479 <title><structname>pg_seclabels</structname></title>
7481 <indexterm zone="view-pg-seclabels">
7482 <primary>pg_seclabels</primary>
7486 The view <structname>pg_seclabels</structname> provides information about
7487 security labels. It as an easier-to-query version of the
7488 <link linkend="catalog-pg-seclabel"><structname>pg_seclabel</></> catalog.
7492 <title><structname>pg_seclabels</> Columns</title>
7499 <entry>References</entry>
7500 <entry>Description</entry>
7505 <entry><structfield>objoid</structfield></entry>
7506 <entry><type>oid</type></entry>
7507 <entry>any OID column</entry>
7508 <entry>The OID of the object this security label pertains to</entry>
7511 <entry><structfield>classoid</structfield></entry>
7512 <entry><type>oid</type></entry>
7513 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
7514 <entry>The OID of the system catalog this object appears in</entry>
7517 <entry><structfield>objsubid</structfield></entry>
7518 <entry><type>int4</type></entry>
7521 For a security label on a table column, this is the column number (the
7522 <structfield>objoid</> and <structfield>classoid</> refer to
7523 the table itself). For all other object types, this column is
7528 <entry><structfield>objtype</structfield></entry>
7529 <entry><type>text</type></entry>
7532 The type of object to which this label applies, as text.
7536 <entry><structfield>objnamespace</structfield></entry>
7537 <entry><type>oid</type></entry>
7538 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
7540 The OID of the namespace for this object, if applicable;
7545 <entry><structfield>objname</structfield></entry>
7546 <entry><type>text</type></entry>
7549 The name of the object to which this label applies, as text.
7553 <entry><structfield>provider</structfield></entry>
7554 <entry><type>text</type></entry>
7555 <entry><literal><link linkend="catalog-pg-seclabel"><structname>pg_seclabel</structname></link>.provider</literal></entry>
7556 <entry>The label provider associated with this label.</entry>
7559 <entry><structfield>label</structfield></entry>
7560 <entry><type>text</type></entry>
7561 <entry><literal><link linkend="catalog-pg-seclabel"><structname>pg_seclabel</structname></link>.label</literal></entry>
7562 <entry>The security label applied to this object.</entry>
7569 <sect1 id="view-pg-settings">
7570 <title><structname>pg_settings</structname></title>
7572 <indexterm zone="view-pg-settings">
7573 <primary>pg_settings</primary>
7577 The view <structname>pg_settings</structname> provides access to
7578 run-time parameters of the server. It is essentially an alternative
7579 interface to the <xref linkend="sql-show">
7580 and <xref linkend="sql-set"> commands.
7581 It also provides access to some facts about each parameter that are
7582 not directly available from <command>SHOW</>, such as minimum and
7587 <title><structname>pg_settings</> Columns</title>
7594 <entry>Description</entry>
7599 <entry><structfield>name</structfield></entry>
7600 <entry><type>text</type></entry>
7601 <entry>Run-time configuration parameter name</entry>
7604 <entry><structfield>setting</structfield></entry>
7605 <entry><type>text</type></entry>
7606 <entry>Current value of the parameter</entry>
7609 <entry><structfield>unit</structfield></entry>
7610 <entry><type>text</type></entry>
7611 <entry>Implicit unit of the parameter</entry>
7614 <entry><structfield>category</structfield></entry>
7615 <entry><type>text</type></entry>
7616 <entry>Logical group of the parameter</entry>
7619 <entry><structfield>short_desc</structfield></entry>
7620 <entry><type>text</type></entry>
7621 <entry>A brief description of the parameter</entry>
7624 <entry><structfield>extra_desc</structfield></entry>
7625 <entry><type>text</type></entry>
7626 <entry>Additional, more detailed, description of the parameter</entry>
7629 <entry><structfield>context</structfield></entry>
7630 <entry><type>text</type></entry>
7631 <entry>Context required to set the parameter's value (see below)</entry>
7634 <entry><structfield>vartype</structfield></entry>
7635 <entry><type>text</type></entry>
7636 <entry>Parameter type (<literal>bool</>, <literal>enum</>,
7637 <literal>integer</>, <literal>real</>, or <literal>string</>)
7641 <entry><structfield>source</structfield></entry>
7642 <entry><type>text</type></entry>
7643 <entry>Source of the current parameter value</entry>
7646 <entry><structfield>min_val</structfield></entry>
7647 <entry><type>text</type></entry>
7648 <entry>Minimum allowed value of the parameter (null for non-numeric
7652 <entry><structfield>max_val</structfield></entry>
7653 <entry><type>text</type></entry>
7654 <entry>Maximum allowed value of the parameter (null for non-numeric
7658 <entry><structfield>enumvals</structfield></entry>
7659 <entry><type>text[]</type></entry>
7660 <entry>Allowed values of an enum parameter (null for non-enum
7664 <entry><structfield>boot_val</structfield></entry>
7665 <entry><type>text</type></entry>
7666 <entry>Parameter value assumed at server startup if the parameter is
7667 not otherwise set</entry>
7670 <entry><structfield>reset_val</structfield></entry>
7671 <entry><type>text</type></entry>
7672 <entry>Value that <command>RESET</command> would reset the parameter to
7673 in the current session</entry>
7676 <entry><structfield>sourcefile</structfield></entry>
7677 <entry><type>text</type></entry>
7678 <entry>Configuration file the current value was set in (null for
7679 values set from sources other than configuration files, or when
7680 examined by a non-superuser);
7681 helpful when using <literal>include</> directives in configuration files</entry>
7684 <entry><structfield>sourceline</structfield></entry>
7685 <entry><type>integer</type></entry>
7686 <entry>Line number within the configuration file the current value was
7687 set at (null for values set from sources other than configuration files,
7688 or when examined by a non-superuser)
7696 There are several possible values of <structfield>context</structfield>.
7697 In order of decreasing difficulty of changing the setting, they are:
7702 <term><literal>internal</literal></term>
7705 These settings cannot be changed directly; they reflect internally
7706 determined values. Some of them may be adjustable by rebuilding the
7707 server with different configuration options, or by changing options
7708 supplied to <command>initdb</command>.
7713 <term><literal>postmaster</literal></term>
7716 These settings can only be applied when the server starts, so any change
7717 requires restarting the server. Values for these settings are typically
7718 stored in the <filename>postgresql.conf</filename> file, or passed on
7719 the command line when starting the server. Of course, settings with any
7720 of the lower <structfield>context</structfield> types can also be
7721 set at server start time.
7726 <term><literal>sighup</literal></term>
7729 Changes to these settings can be made in
7730 <filename>postgresql.conf</filename> without restarting the server.
7731 Send a <systemitem>SIGHUP</systemitem> signal to the postmaster to
7732 cause it to re-read <filename>postgresql.conf</filename> and apply
7733 the changes. The postmaster will also forward the
7734 <systemitem>SIGHUP</systemitem> signal to its child processes so that
7735 they all pick up the new value.
7740 <term><literal>backend</literal></term>
7743 Changes to these settings can be made in
7744 <filename>postgresql.conf</filename> without restarting the server;
7745 they can also be set for a particular session in the connection request
7746 packet (for example, via <application>libpq</>'s <literal>PGOPTIONS</>
7747 environment variable). However, these settings never change in a
7748 session after it is started. If you change them in
7749 <filename>postgresql.conf</filename>, send a
7750 <systemitem>SIGHUP</systemitem> signal to the postmaster to cause it to
7751 re-read <filename>postgresql.conf</filename>. The new values will only
7752 affect subsequently-launched sessions.
7757 <term><literal>superuser</literal></term>
7760 These settings can be set from <filename>postgresql.conf</filename>,
7761 or within a session via the <command>SET</> command; but only superusers
7762 can change them via <command>SET</>. Changes in
7763 <filename>postgresql.conf</filename> will affect existing sessions
7764 only if no session-local value has been established with <command>SET</>.
7769 <term><literal>user</literal></term>
7772 These settings can be set from <filename>postgresql.conf</filename>,
7773 or within a session via the <command>SET</> command. Any user is
7774 allowed to change his session-local value. Changes in
7775 <filename>postgresql.conf</filename> will affect existing sessions
7776 only if no session-local value has been established with <command>SET</>.
7783 See <xref linkend="config-setting"> for more information about the various
7784 ways to change these parameters.
7788 The <structname>pg_settings</structname> view cannot be inserted into or
7789 deleted from, but it can be updated. An <command>UPDATE</command> applied
7790 to a row of <structname>pg_settings</structname> is equivalent to executing
7791 the <xref linkend="sql-set"> command on that named
7792 parameter. The change only affects the value used by the current
7793 session. If an <command>UPDATE</command> is issued within a transaction
7794 that is later aborted, the effects of the <command>UPDATE</command> command
7795 disappear when the transaction is rolled back. Once the surrounding
7796 transaction is committed, the effects will persist until the end of the
7797 session, unless overridden by another <command>UPDATE</command> or
7798 <command>SET</command>.
7803 <sect1 id="view-pg-shadow">
7804 <title><structname>pg_shadow</structname></title>
7806 <indexterm zone="view-pg-shadow">
7807 <primary>pg_shadow</primary>
7811 The view <structname>pg_shadow</structname> exists for backwards
7812 compatibility: it emulates a catalog that existed in
7813 <productname>PostgreSQL</productname> before version 8.1.
7814 It shows properties of all roles that are marked as
7815 <structfield>rolcanlogin</> in
7816 <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.
7820 The name stems from the fact that this table
7821 should not be readable by the public since it contains passwords.
7822 <link linkend="view-pg-user"><structname>pg_user</structname></link>
7823 is a publicly readable view on
7824 <structname>pg_shadow</structname> that blanks out the password field.
7828 <title><structname>pg_shadow</> Columns</title>
7835 <entry>References</entry>
7836 <entry>Description</entry>
7842 <entry><structfield>usename</structfield></entry>
7843 <entry><type>name</type></entry>
7844 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.rolname</literal></entry>
7845 <entry>User name</entry>
7849 <entry><structfield>usesysid</structfield></entry>
7850 <entry><type>oid</type></entry>
7851 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
7852 <entry>ID of this user</entry>
7856 <entry><structfield>usecreatedb</structfield></entry>
7857 <entry><type>bool</type></entry>
7859 <entry>User can create databases</entry>
7863 <entry><structfield>usesuper</structfield></entry>
7864 <entry><type>bool</type></entry>
7866 <entry>User is a superuser</entry>
7870 <entry><structfield>usecatupd</structfield></entry>
7871 <entry><type>bool</type></entry>
7874 User can update system catalogs. (Even a superuser cannot do
7875 this unless this column is true.)
7880 <entry><structfield>passwd</structfield></entry>
7881 <entry><type>text</type></entry>
7883 <entry>Password (possibly encrypted); null if none. See
7884 <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>
7885 for details of how encrypted passwords are stored.</entry>
7889 <entry><structfield>valuntil</structfield></entry>
7890 <entry><type>abstime</type></entry>
7892 <entry>Password expiry time (only used for password authentication)</entry>
7896 <entry><structfield>useconfig</structfield></entry>
7897 <entry><type>text[]</type></entry>
7899 <entry>Session defaults for run-time configuration variables</entry>
7907 <sect1 id="view-pg-stats">
7908 <title><structname>pg_stats</structname></title>
7910 <indexterm zone="view-pg-stats">
7911 <primary>pg_stats</primary>
7915 The view <structname>pg_stats</structname> provides access to
7916 the information stored in the <link
7917 linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>
7918 catalog. This view allows access only to rows of
7919 <structname>pg_statistic</structname> that correspond to tables the
7920 user has permission to read, and therefore it is safe to allow public
7921 read access to this view.
7925 <structname>pg_stats</structname> is also designed to present the
7926 information in a more readable format than the underlying catalog
7927 — at the cost that its schema must be extended whenever new slot types
7928 are defined for <structname>pg_statistic</structname>.
7932 <title><structname>pg_stats</> Columns</title>
7939 <entry>References</entry>
7940 <entry>Description</entry>
7945 <entry><structfield>schemaname</structfield></entry>
7946 <entry><type>name</type></entry>
7947 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.nspname</literal></entry>
7948 <entry>Name of schema containing table</entry>
7952 <entry><structfield>tablename</structfield></entry>
7953 <entry><type>name</type></entry>
7954 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relname</literal></entry>
7955 <entry>Name of table</entry>
7959 <entry><structfield>attname</structfield></entry>
7960 <entry><type>name</type></entry>
7961 <entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attname</literal></entry>
7962 <entry>Name of the column described by this row</entry>
7966 <entry><structfield>inherited</structfield></entry>
7967 <entry><type>bool</type></entry>
7969 <entry>If true, this row includes inheritance child columns, not just the
7970 values in the specified table</entry>
7974 <entry><structfield>null_frac</structfield></entry>
7975 <entry><type>real</type></entry>
7977 <entry>Fraction of column entries that are null</entry>
7981 <entry><structfield>avg_width</structfield></entry>
7982 <entry><type>integer</type></entry>
7984 <entry>Average width in bytes of column's entries</entry>
7988 <entry><structfield>n_distinct</structfield></entry>
7989 <entry><type>real</type></entry>
7992 If greater than zero, the estimated number of distinct values in the
7993 column. If less than zero, the negative of the number of distinct
7994 values divided by the number of rows. (The negated form is used when
7995 <command>ANALYZE</> believes that the number of distinct values is
7996 likely to increase as the table grows; the positive form is used when
7997 the column seems to have a fixed number of possible values.) For
7998 example, -1 indicates a unique column in which the number of distinct
7999 values is the same as the number of rows.
8004 <entry><structfield>most_common_vals</structfield></entry>
8005 <entry><type>anyarray</type></entry>
8008 A list of the most common values in the column. (Null if
8009 no values seem to be more common than any others.)
8010 For some data types such as <type>tsvector</>, this is a list of
8011 the most common element values rather than values of the type itself.
8016 <entry><structfield>most_common_freqs</structfield></entry>
8017 <entry><type>real[]</type></entry>
8020 A list of the frequencies of the most common values or elements,
8021 i.e., number of occurrences of each divided by total number of rows.
8022 (Null when <structfield>most_common_vals</structfield> is.)
8023 For some data types such as <type>tsvector</>, it can also store some
8024 additional information, making it longer than the
8025 <structfield>most_common_vals</> array.
8030 <entry><structfield>histogram_bounds</structfield></entry>
8031 <entry><type>anyarray</type></entry>
8034 A list of values that divide the column's values into groups of
8035 approximately equal population. The values in
8036 <structfield>most_common_vals</>, if present, are omitted from this
8037 histogram calculation. (This column is null if the column data type
8038 does not have a <literal><</> operator or if the
8039 <structfield>most_common_vals</> list accounts for the entire
8045 <entry><structfield>correlation</structfield></entry>
8046 <entry><type>real</type></entry>
8049 Statistical correlation between physical row ordering and
8050 logical ordering of the column values. This ranges from -1 to +1.
8051 When the value is near -1 or +1, an index scan on the column will
8052 be estimated to be cheaper than when it is near zero, due to reduction
8053 of random access to the disk. (This column is null if the column data
8054 type does not have a <literal><</> operator.)
8062 The maximum number of entries in the <structfield>most_common_vals</>
8063 and <structfield>histogram_bounds</> arrays can be set on a
8064 column-by-column basis using the <command>ALTER TABLE SET STATISTICS</>
8065 command, or globally by setting the
8066 <xref linkend="guc-default-statistics-target"> run-time parameter.
8071 <sect1 id="view-pg-tables">
8072 <title><structname>pg_tables</structname></title>
8074 <indexterm zone="view-pg-tables">
8075 <primary>pg_tables</primary>
8079 The view <structname>pg_tables</structname> provides access to
8080 useful information about each table in the database.
8084 <title><structname>pg_tables</> Columns</title>
8091 <entry>References</entry>
8092 <entry>Description</entry>
8097 <entry><structfield>schemaname</structfield></entry>
8098 <entry><type>name</type></entry>
8099 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.nspname</literal></entry>
8100 <entry>Name of schema containing table</entry>
8103 <entry><structfield>tablename</structfield></entry>
8104 <entry><type>name</type></entry>
8105 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relname</literal></entry>
8106 <entry>Name of table</entry>
8109 <entry><structfield>tableowner</structfield></entry>
8110 <entry><type>name</type></entry>
8111 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.rolname</literal></entry>
8112 <entry>Name of table's owner</entry>
8115 <entry><structfield>tablespace</structfield></entry>
8116 <entry><type>name</type></entry>
8117 <entry><literal><link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.spcname</literal></entry>
8118 <entry>Name of tablespace containing table (null if default for database)</entry>
8121 <entry><structfield>hasindexes</structfield></entry>
8122 <entry><type>boolean</type></entry>
8123 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relhasindex</literal></entry>
8124 <entry>True if table has (or recently had) any indexes</entry>
8127 <entry><structfield>hasrules</structfield></entry>
8128 <entry><type>boolean</type></entry>
8129 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relhasrules</literal></entry>
8130 <entry>True if table has (or once had) rules</entry>
8133 <entry><structfield>hastriggers</structfield></entry>
8134 <entry><type>boolean</type></entry>
8135 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relhastriggers</literal></entry>
8136 <entry>True if table has (or once had) triggers</entry>
8144 <sect1 id="view-pg-timezone-abbrevs">
8145 <title><structname>pg_timezone_abbrevs</structname></title>
8147 <indexterm zone="view-pg-timezone-abbrevs">
8148 <primary>pg_timezone_abbrevs</primary>
8152 The view <structname>pg_timezone_abbrevs</structname> provides a list
8153 of time zone abbreviations that are currently recognized by the datetime
8154 input routines. The contents of this view change when the
8155 <xref linkend="guc-timezone-abbreviations"> run-time parameter is modified.
8159 <title><structname>pg_timezone_abbrevs</> Columns</title>
8166 <entry>Description</entry>
8171 <entry><structfield>abbrev</structfield></entry>
8172 <entry><type>text</type></entry>
8173 <entry>Time zone abbreviation</entry>
8176 <entry><structfield>utc_offset</structfield></entry>
8177 <entry><type>interval</type></entry>
8178 <entry>Offset from UTC (positive means east of Greenwich)</entry>
8181 <entry><structfield>is_dst</structfield></entry>
8182 <entry><type>boolean</type></entry>
8183 <entry>True if this is a daylight-savings abbreviation</entry>
8191 <sect1 id="view-pg-timezone-names">
8192 <title><structname>pg_timezone_names</structname></title>
8194 <indexterm zone="view-pg-timezone-names">
8195 <primary>pg_timezone_names</primary>
8199 The view <structname>pg_timezone_names</structname> provides a list
8200 of time zone names that are recognized by <command>SET TIMEZONE</>,
8201 along with their associated abbreviations, UTC offsets,
8202 and daylight-savings status.
8203 Unlike the abbreviations shown in <link
8204 linkend="view-pg-timezone-abbrevs"><structname>pg_timezone_abbrevs</structname></link>, many of these names imply a set of daylight-savings transition
8205 date rules. Therefore, the associated information changes across local DST
8206 boundaries. The displayed information is computed based on the current
8207 value of <function>CURRENT_TIMESTAMP</>.
8211 <title><structname>pg_timezone_names</> Columns</title>
8218 <entry>Description</entry>
8223 <entry><structfield>name</structfield></entry>
8224 <entry><type>text</type></entry>
8225 <entry>Time zone name</entry>
8228 <entry><structfield>abbrev</structfield></entry>
8229 <entry><type>text</type></entry>
8230 <entry>Time zone abbreviation</entry>
8233 <entry><structfield>utc_offset</structfield></entry>
8234 <entry><type>interval</type></entry>
8235 <entry>Offset from UTC (positive means east of Greenwich)</entry>
8238 <entry><structfield>is_dst</structfield></entry>
8239 <entry><type>boolean</type></entry>
8240 <entry>True if currently observing daylight savings</entry>
8248 <sect1 id="view-pg-user">
8249 <title><structname>pg_user</structname></title>
8251 <indexterm zone="view-pg-user">
8252 <primary>pg_user</primary>
8256 The view <structname>pg_user</structname> provides access to
8257 information about database users. This is simply a publicly
8259 <link linkend="view-pg-shadow"><structname>pg_shadow</structname></link>
8260 that blanks out the password field.
8264 <title><structname>pg_user</> Columns</title>
8271 <entry>Description</entry>
8276 <entry><structfield>usename</structfield></entry>
8277 <entry><type>name</type></entry>
8278 <entry>User name</entry>
8282 <entry><structfield>usesysid</structfield></entry>
8283 <entry><type>int4</type></entry>
8284 <entry>User ID (arbitrary number used to reference this user)</entry>
8288 <entry><structfield>usecreatedb</structfield></entry>
8289 <entry><type>bool</type></entry>
8290 <entry>User can create databases</entry>
8294 <entry><structfield>usesuper</structfield></entry>
8295 <entry><type>bool</type></entry>
8296 <entry>User is a superuser</entry>
8300 <entry><structfield>usecatupd</structfield></entry>
8301 <entry><type>bool</type></entry>
8303 User can update system catalogs. (Even a superuser cannot do
8304 this unless this column is true.)
8309 <entry><structfield>passwd</structfield></entry>
8310 <entry><type>text</type></entry>
8311 <entry>Not the password (always reads as <literal>********</>)</entry>
8315 <entry><structfield>valuntil</structfield></entry>
8316 <entry><type>abstime</type></entry>
8317 <entry>Password expiry time (only used for password authentication)</entry>
8321 <entry><structfield>useconfig</structfield></entry>
8322 <entry><type>text[]</type></entry>
8323 <entry>Session defaults for run-time configuration variables</entry>
8331 <sect1 id="view-pg-user-mappings">
8332 <title><structname>pg_user_mappings</structname></title>
8334 <indexterm zone="view-pg-user-mappings">
8335 <primary>pg_user_mappings</primary>
8339 The view <structname>pg_user_mappings</structname> provides access
8340 to information about user mappings. This is essentially a publicly
8342 <link linkend="catalog-pg-user-mapping"><structname>pg_user_mapping</structname></link>
8343 that leaves out the options field if the user has no rights to use
8348 <title><structname>pg_user_mappings</> Columns</title>
8355 <entry>References</entry>
8356 <entry>Description</entry>
8362 <entry><structfield>umid</structfield></entry>
8363 <entry><type>oid</type></entry>
8364 <entry><literal><link linkend="catalog-pg-user-mapping"><structname>pg_user_mapping</structname></link>.oid</literal></entry>
8365 <entry>OID of the user mapping</entry>
8369 <entry><structfield>srvid</structfield></entry>
8370 <entry><type>oid</type></entry>
8371 <entry><literal><link linkend="catalog-pg-foreign-server"><structname>pg_foreign_server</structname></link>.oid</literal></entry>
8373 The OID of the foreign server that contains this mapping
8378 <entry><structfield>srvname</structfield></entry>
8379 <entry><type>text</type></entry>
8382 Name of the foreign server
8387 <entry><structfield>umuser</structfield></entry>
8388 <entry><type>oid</type></entry>
8389 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
8390 <entry>OID of the local role being mapped, 0 if the user mapping is public</entry>
8394 <entry><structfield>usename</structfield></entry>
8395 <entry><type>name</type></entry>
8397 <entry>Name of the local user to be mapped</entry>
8401 <entry><structfield>umoptions</structfield></entry>
8402 <entry><type>text[]</type></entry>
8405 User mapping specific options, as <quote>keyword=value</>
8406 strings, if the current user is the owner of the foreign
8416 <sect1 id="view-pg-views">
8417 <title><structname>pg_views</structname></title>
8419 <indexterm zone="view-pg-views">
8420 <primary>pg_views</primary>
8424 The view <structname>pg_views</structname> provides access to
8425 useful information about each view in the database.
8429 <title><structname>pg_views</> Columns</title>
8436 <entry>References</entry>
8437 <entry>Description</entry>
8442 <entry><structfield>schemaname</structfield></entry>
8443 <entry><type>name</type></entry>
8444 <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.nspname</literal></entry>
8445 <entry>Name of schema containing view</entry>
8448 <entry><structfield>viewname</structfield></entry>
8449 <entry><type>name</type></entry>
8450 <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relname</literal></entry>
8451 <entry>Name of view</entry>
8454 <entry><structfield>viewowner</structfield></entry>
8455 <entry><type>name</type></entry>
8456 <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.rolname</literal></entry>
8457 <entry>Name of view's owner</entry>
8460 <entry><structfield>definition</structfield></entry>
8461 <entry><type>text</type></entry>
8463 <entry>View definition (a reconstructed <command>SELECT</command> query)</entry>