2
Documentation of the system catalogs, directed toward PostgreSQL developers
3
$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.95 2005-01-05 23:42:03 tgl Exp $
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-cast"><structname>pg_cast</structname></link></entry>
83
<entry>casts (data type conversions)</entry>
87
<entry><link linkend="catalog-pg-class"><structname>pg_class</structname></link></entry>
88
<entry>tables, indexes, sequences, views (<quote>relations</quote>)</entry>
92
<entry><link linkend="catalog-pg-constraint"><structname>pg_constraint</structname></link></entry>
93
<entry>check constraints, unique constraints, primary key constraints, foreign key constraints</entry>
97
<entry><link linkend="catalog-pg-conversion"><structname>pg_conversion</structname></link></entry>
98
<entry>encoding conversion information</entry>
102
<entry><link linkend="catalog-pg-database"><structname>pg_database</structname></link></entry>
103
<entry>databases within this database cluster</entry>
107
<entry><link linkend="catalog-pg-depend"><structname>pg_depend</structname></link></entry>
108
<entry>dependencies between database objects</entry>
112
<entry><link linkend="catalog-pg-description"><structname>pg_description</structname></link></entry>
113
<entry>descriptions or comments on database objects</entry>
117
<entry><link linkend="catalog-pg-group"><structname>pg_group</structname></link></entry>
118
<entry>groups of database users</entry>
122
<entry><link linkend="catalog-pg-index"><structname>pg_index</structname></link></entry>
123
<entry>additional index information</entry>
127
<entry><link linkend="catalog-pg-inherits"><structname>pg_inherits</structname></link></entry>
128
<entry>table inheritance hierarchy</entry>
132
<entry><link linkend="catalog-pg-language"><structname>pg_language</structname></link></entry>
133
<entry>languages for writing functions</entry>
137
<entry><link linkend="catalog-pg-largeobject"><structname>pg_largeobject</structname></link></entry>
138
<entry>large objects</entry>
142
<entry><link linkend="catalog-pg-listener"><structname>pg_listener</structname></link></entry>
143
<entry>asynchronous notification support</entry>
147
<entry><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link></entry>
148
<entry>schemas</entry>
152
<entry><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link></entry>
153
<entry>index access method operator classes</entry>
157
<entry><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link></entry>
158
<entry>operators</entry>
162
<entry><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link></entry>
163
<entry>functions and procedures</entry>
167
<entry><link linkend="catalog-pg-rewrite"><structname>pg_rewrite</structname></link></entry>
168
<entry>query rewrite rules</entry>
172
<entry><link linkend="catalog-pg-shadow"><structname>pg_shadow</structname></link></entry>
173
<entry>database users</entry>
177
<entry><link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link></entry>
178
<entry>planner statistics</entry>
182
<entry><link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link></entry>
183
<entry>tablespaces within this database cluster</entry>
187
<entry><link linkend="catalog-pg-trigger"><structname>pg_trigger</structname></link></entry>
188
<entry>triggers</entry>
192
<entry><link linkend="catalog-pg-type"><structname>pg_type</structname></link></entry>
193
<entry>data types</entry>
201
<sect1 id="catalog-pg-aggregate">
202
<title><structname>pg_aggregate</structname></title>
204
<indexterm zone="catalog-pg-aggregate">
205
<primary>pg_aggregate</primary>
209
The catalog <structname>pg_aggregate</structname> stores information about
210
aggregate functions. An aggregate function is a function that
211
operates on a set of values (typically one column from each row
212
that matches a query condition) and returns a single value computed
213
from all these values. Typical aggregate functions are
214
<function>sum</function>, <function>count</function>, and
215
<function>max</function>. Each entry in
216
<structname>pg_aggregate</structname> is an extension of an entry
217
in <structname>pg_proc</structname>. The <structname>pg_proc</structname>
218
entry carries the aggregate's name, input and output data types, and
219
other information that is similar to ordinary functions.
223
<title><structname>pg_aggregate</> Columns</title>
230
<entry>References</entry>
231
<entry>Description</entry>
236
<entry><structfield>aggfnoid</structfield></entry>
237
<entry><type>regproc</type></entry>
238
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
239
<entry><structname>pg_proc</structname> OID of the aggregate function</entry>
242
<entry><structfield>aggtransfn</structfield></entry>
243
<entry><type>regproc</type></entry>
244
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
245
<entry>Transition function</entry>
248
<entry><structfield>aggfinalfn</structfield></entry>
249
<entry><type>regproc</type></entry>
250
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
251
<entry>Final function (zero if none)</entry>
254
<entry><structfield>aggtranstype</structfield></entry>
255
<entry><type>oid</type></entry>
256
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
257
<entry>The type of the aggregate function's internal transition (state) data</entry>
260
<entry><structfield>agginitval</structfield></entry>
261
<entry><type>text</type></entry>
264
The initial value of the transition state. This is a text
265
field containing the initial value in its external string
266
representation. If the value is null, the transition state
267
value starts out null.
275
New aggregate functions are registered with the <command>CREATE
276
AGGREGATE</command> command. See <xref linkend="xaggr"> for more
277
information about writing aggregate functions and the meaning of
278
the transition functions, etc.
284
<sect1 id="catalog-pg-am">
285
<title><structname>pg_am</structname></title>
287
<indexterm zone="catalog-pg-am">
288
<primary>pg_am</primary>
292
The catalog <structname>pg_am</structname> stores information about index access
293
methods. There is one row for each index access method supported by
298
<title><structname>pg_am</> Columns</title>
305
<entry>References</entry>
306
<entry>Description</entry>
312
<entry><structfield>amname</structfield></entry>
313
<entry><type>name</type></entry>
315
<entry>Name of the access method</entry>
319
<entry><structfield>amowner</structfield></entry>
320
<entry><type>int4</type></entry>
321
<entry><literal><link linkend="catalog-pg-shadow"><structname>pg_shadow</structname></link>.usesysid</literal></entry>
322
<entry>User ID of the owner (currently not used)</entry>
326
<entry><structfield>amstrategies</structfield></entry>
327
<entry><type>int2</type></entry>
329
<entry>Number of operator strategies for this access method</entry>
333
<entry><structfield>amsupport</structfield></entry>
334
<entry><type>int2</type></entry>
336
<entry>Number of support routines for this access method</entry>
340
<entry><structfield>amorderstrategy</structfield></entry>
341
<entry><type>int2</type></entry>
343
<entry>Zero if the index offers no sort order, otherwise the strategy
344
number of the strategy operator that describes the sort order</entry>
348
<entry><structfield>amcanunique</structfield></entry>
349
<entry><type>bool</type></entry>
351
<entry>Does the access method support unique indexes?</entry>
355
<entry><structfield>amcanmulticol</structfield></entry>
356
<entry><type>bool</type></entry>
358
<entry>Does the access method support multicolumn indexes?</entry>
362
<entry><structfield>amindexnulls</structfield></entry>
363
<entry><type>bool</type></entry>
365
<entry>Does the access method support null index entries?</entry>
369
<entry><structfield>amconcurrent</structfield></entry>
370
<entry><type>bool</type></entry>
372
<entry>Does the access method support concurrent updates?</entry>
376
<entry><structfield>amgettuple</structfield></entry>
377
<entry><type>regproc</type></entry>
378
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
379
<entry><quote>Next valid tuple</quote> function</entry>
383
<entry><structfield>aminsert</structfield></entry>
384
<entry><type>regproc</type></entry>
385
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
386
<entry><quote>Insert this tuple</quote> function</entry>
390
<entry><structfield>ambeginscan</structfield></entry>
391
<entry><type>regproc</type></entry>
392
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
393
<entry><quote>Start new scan</quote> function</entry>
397
<entry><structfield>amrescan</structfield></entry>
398
<entry><type>regproc</type></entry>
399
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
400
<entry><quote>Restart this scan</quote> function</entry>
404
<entry><structfield>amendscan</structfield></entry>
405
<entry><type>regproc</type></entry>
406
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
407
<entry><quote>End this scan</quote> function</entry>
411
<entry><structfield>ammarkpos</structfield></entry>
412
<entry><type>regproc</type></entry>
413
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
414
<entry><quote>Mark current scan position</quote> function</entry>
418
<entry><structfield>amrestrpos</structfield></entry>
419
<entry><type>regproc</type></entry>
420
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
421
<entry><quote>Restore marked scan position</quote> function</entry>
425
<entry><structfield>ambuild</structfield></entry>
426
<entry><type>regproc</type></entry>
427
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
428
<entry><quote>Build new index</quote> function</entry>
432
<entry><structfield>ambulkdelete</structfield></entry>
433
<entry><type>regproc</type></entry>
434
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
435
<entry>Bulk-delete function</entry>
439
<entry><structfield>amvacuumcleanup</structfield></entry>
440
<entry><type>regproc</type></entry>
441
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
442
<entry>Post-<command>VACUUM</command> cleanup function</entry>
446
<entry><structfield>amcostestimate</structfield></entry>
447
<entry><type>regproc</type></entry>
448
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
449
<entry>Function to estimate cost of an index scan</entry>
457
An index access method that supports multiple columns (has
458
<structfield>amcanmulticol</structfield> true) <emphasis>must</>
459
support indexing null values in columns after the first, because the planner
460
will assume the index can be used for queries on just the first
461
column(s). For example, consider an index on (a,b) and a query with
462
<literal>WHERE a = 4</literal>. The system will assume the index can be used to scan for
463
rows with <literal>a = 4</literal>, which is wrong if the index omits rows where <literal>b</> is null.
464
It is, however, OK to omit rows where the first indexed column is null.
465
(GiST currently does so.)
466
<structfield>amindexnulls</structfield> should be set true only if the
467
index access method indexes all rows, including arbitrary combinations of null values.
473
<sect1 id="catalog-pg-amop">
474
<title><structname>pg_amop</structname></title>
476
<indexterm zone="catalog-pg-amop">
477
<primary>pg_amop</primary>
481
The catalog <structname>pg_amop</structname> stores information about operators
482
associated with index access method operator classes. There is one
483
row for each operator that is a member of an operator class.
487
<title><structname>pg_amop</> Columns</title>
494
<entry>References</entry>
495
<entry>Description</entry>
501
<entry><structfield>amopclaid</structfield></entry>
502
<entry><type>oid</type></entry>
503
<entry><literal><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link>.oid</literal></entry>
504
<entry>The index operator class this entry is for</entry>
508
<entry><structfield>amopsubtype</structfield></entry>
509
<entry><type>oid</type></entry>
510
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
511
<entry>Subtype to distinguish multiple entries for one strategy;
512
zero for default</entry>
516
<entry><structfield>amopstrategy</structfield></entry>
517
<entry><type>int2</type></entry>
519
<entry>Operator strategy number</entry>
523
<entry><structfield>amopreqcheck</structfield></entry>
524
<entry><type>bool</type></entry>
526
<entry>Index hit must be rechecked</entry>
530
<entry><structfield>amopopr</structfield></entry>
531
<entry><type>oid</type></entry>
532
<entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</literal></entry>
533
<entry>OID of the operator</entry>
543
<sect1 id="catalog-pg-amproc">
544
<title><structname>pg_amproc</structname></title>
546
<indexterm zone="catalog-pg-amproc">
547
<primary>pg_amproc</primary>
551
The catalog <structname>pg_amproc</structname> stores information about support
553
associated with index access method operator classes. There is one
554
row for each support procedure belonging to an operator class.
558
<title><structname>pg_amproc</structname> Columns</title>
565
<entry>References</entry>
566
<entry>Description</entry>
572
<entry><structfield>amopclaid</structfield></entry>
573
<entry><type>oid</type></entry>
574
<entry><literal><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link>.oid</literal></entry>
575
<entry>The index operator class this entry is for</entry>
579
<entry><structfield>amprocsubtype</structfield></entry>
580
<entry><type>oid</type></entry>
581
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
582
<entry>Subtype, if cross-type routine, else zero</entry>
586
<entry><structfield>amprocnum</structfield></entry>
587
<entry><type>int2</type></entry>
589
<entry>Support procedure number</entry>
593
<entry><structfield>amproc</structfield></entry>
594
<entry><type>regproc</type></entry>
595
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
596
<entry>OID of the procedure</entry>
606
<sect1 id="catalog-pg-attrdef">
607
<title><structname>pg_attrdef</structname></title>
609
<indexterm zone="catalog-pg-attrdef">
610
<primary>pg_attrdef</primary>
614
The catalog <structname>pg_attrdef</structname> stores column default values. The main information
615
about columns is stored in <structname>pg_attribute</structname>
616
(see below). Only columns that explicitly specify a default value
617
(when the table is created or the column is added) will have an
622
<title><structname>pg_attrdef</> Columns</title>
629
<entry>References</entry>
630
<entry>Description</entry>
636
<entry><structfield>adrelid</structfield></entry>
637
<entry><type>oid</type></entry>
638
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
639
<entry>The table this column belongs to</entry>
643
<entry><structfield>adnum</structfield></entry>
644
<entry><type>int2</type></entry>
645
<entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</literal></entry>
646
<entry>The number of the column</entry>
650
<entry><structfield>adbin</structfield></entry>
651
<entry><type>text</type></entry>
653
<entry>The internal representation of the column default value</entry>
657
<entry><structfield>adsrc</structfield></entry>
658
<entry><type>text</type></entry>
660
<entry>A human-readable representation of the default value</entry>
667
The <structfield>adsrc</structfield> field is historical, and is best
668
not used, because it does not track outside changes that might affect
669
the representation of the default value. Reverse-compiling the
670
<structfield>adbin</structfield> field (with <function>pg_get_expr</> for
671
example) is a better way to display the default value.
677
<sect1 id="catalog-pg-attribute">
678
<title><structname>pg_attribute</structname></title>
680
<indexterm zone="catalog-pg-attribute">
681
<primary>pg_attribute</primary>
685
The catalog <structname>pg_attribute</structname> stores information about
686
table columns. There will be exactly one
687
<structname>pg_attribute</structname> row for every column in every
688
table in the database. (There will also be attribute entries for
689
indexes, and indeed all objects that have <structname>pg_class</structname>
694
The term attribute is equivalent to column and is used for
699
<title><structname>pg_attribute</> Columns</title>
706
<entry>References</entry>
707
<entry>Description</entry>
713
<entry><structfield>attrelid</structfield></entry>
714
<entry><type>oid</type></entry>
715
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
716
<entry>The table this column belongs to</entry>
720
<entry><structfield>attname</structfield></entry>
721
<entry><type>name</type></entry>
723
<entry>The column name</entry>
727
<entry><structfield>atttypid</structfield></entry>
728
<entry><type>oid</type></entry>
729
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
730
<entry>The data type of this column</entry>
734
<entry><structfield>attstattarget</structfield></entry>
735
<entry><type>int4</type></entry>
738
<structfield>attstattarget</structfield> controls the level of detail
739
of statistics accumulated for this column by
740
<xref linkend="sql-analyze" endterm="sql-analyze-title">.
741
A zero value indicates that no statistics should be collected.
742
A negative value says to use the system default statistics target.
743
The exact meaning of positive values is data type-dependent.
744
For scalar data types, <structfield>attstattarget</structfield>
745
is both the target number of <quote>most common values</quote>
746
to collect, and the target number of histogram bins to create.
751
<entry><structfield>attlen</structfield></entry>
752
<entry><type>int2</type></entry>
755
A copy of <literal>pg_type.typlen</literal> of this column's
761
<entry><structfield>attnum</structfield></entry>
762
<entry><type>int2</type></entry>
765
The number of the column. Ordinary columns are numbered from 1
766
up. System columns, such as <structfield>oid</structfield>,
767
have (arbitrary) negative numbers.
772
<entry><structfield>attndims</structfield></entry>
773
<entry><type>int4</type></entry>
776
Number of dimensions, if the column is an array type; otherwise 0.
777
(Presently, the number of dimensions of an array is not enforced,
778
so any nonzero value effectively means <quote>it's an array</>.)
783
<entry><structfield>attcacheoff</structfield></entry>
784
<entry><type>int4</type></entry>
787
Always -1 in storage, but when loaded into a row descriptor
788
in memory this may be updated to cache the offset of the attribute
794
<entry><structfield>atttypmod</structfield></entry>
795
<entry><type>int4</type></entry>
798
<structfield>atttypmod</structfield> records type-specific data
799
supplied at table creation time (for example, the maximum
800
length of a <type>varchar</type> column). It is passed to
801
type-specific input functions and length coercion functions.
802
The value will generally be -1 for types that do not need <structfield>atttypmod</>.
807
<entry><structfield>attbyval</structfield></entry>
808
<entry><type>bool</type></entry>
811
A copy of <literal>pg_type.typbyval</> of this column's type
816
<entry><structfield>attstorage</structfield></entry>
817
<entry><type>char</type></entry>
820
Normally a copy of <literal>pg_type.typstorage</> of this
821
column's type. For TOAST-able data types, this can be altered
822
after column creation to control storage policy.
827
<entry><structfield>attalign</structfield></entry>
828
<entry><type>char</type></entry>
831
A copy of <literal>pg_type.typalign</> of this column's type
836
<entry><structfield>attnotnull</structfield></entry>
837
<entry><type>bool</type></entry>
840
This represents a not-null constraint. It is possible to
841
change this column to enable or disable the constraint.
846
<entry><structfield>atthasdef</structfield></entry>
847
<entry><type>bool</type></entry>
850
This column has a default value, in which case there will be a
851
corresponding entry in the <structname>pg_attrdef</structname>
852
catalog that actually defines the value.
857
<entry><structfield>attisdropped</structfield></entry>
858
<entry><type>bool</type></entry>
861
This column has been dropped and is no longer valid. A dropped
862
column is still physically present in the table, but is
863
ignored by the parser and so cannot be accessed via SQL.
868
<entry><structfield>attislocal</structfield></entry>
869
<entry><type>bool</type></entry>
872
This column is defined locally in the relation. Note that a column may
873
be locally defined and inherited simultaneously.
878
<entry><structfield>attinhcount</structfield></entry>
879
<entry><type>int4</type></entry>
882
The number of direct ancestors this column has. A column with a
883
nonzero number of ancestors cannot be dropped nor renamed.
892
In a dropped column's <structname>pg_attribute</structname> entry,
893
<structfield>atttypid</structfield> is reset to zero, but
894
<structfield>attlen</structfield> and the other fields copied from
895
<structname>pg_type</> are still valid. This arrangement is needed
896
to cope with the situation where the dropped column's data type was
897
later dropped, and so there is no <structname>pg_type</> row anymore.
898
<structfield>attlen</structfield> and the other fields can be used
899
to interpret the contents of a row of the table.
904
<sect1 id="catalog-pg-cast">
905
<title><structname>pg_cast</structname></title>
907
<indexterm zone="catalog-pg-cast">
908
<primary>pg_cast</primary>
912
The catalog <structname>pg_cast</structname> stores data type conversion paths,
913
both built-in paths and those defined with <command>CREATE CAST</command>.
917
<title><structfield>pg_cast</> Columns</title>
924
<entry>References</entry>
925
<entry>Description</entry>
931
<entry><structfield>castsource</structfield></entry>
932
<entry><type>oid</type></entry>
933
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
934
<entry>OID of the source data type</entry>
938
<entry><structfield>casttarget</structfield></entry>
939
<entry><type>oid</type></entry>
940
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
941
<entry>OID of the target data type</entry>
945
<entry><structfield>castfunc</structfield></entry>
946
<entry><type>oid</type></entry>
947
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
949
The OID of the function to use to perform this cast. Zero is
950
stored if the data types are binary compatible (that is, no
951
run-time operation is needed to perform the cast).
956
<entry><structfield>castcontext</structfield></entry>
957
<entry><type>char</type></entry>
960
Indicates what contexts the cast may be invoked in.
961
<literal>e</> means only as an explicit cast (using
962
<literal>CAST</> or <literal>::</> syntax).
963
<literal>a</> means implicitly in assignment
964
to a target column, as well as explicitly.
965
<literal>i</> means implicitly in expressions, as well as the
974
The cast functions listed in <structname>pg_cast</structname> must
975
always take the cast source type as their first argument type, and
976
return the cast destination type as their result type. A cast
977
function can have up to three arguments. The second argument,
978
if present, must be type <type>integer</>; it receives the type
979
modifier associated with the destination type, or <literal>-1</>
980
if there is none. The third argument,
981
if present, must be type <type>boolean</>; it receives <literal>true</>
982
if the cast is an explicit cast, <literal>false</> otherwise.
986
It is legitimate to create a <structname>pg_cast</structname> entry
987
in which the source and target types are the same, if the associated
988
function takes more than one argument. Such entries represent
989
<quote>length coercion functions</> that coerce values of the type
990
to be legal for a particular type modifier value. Note however that
991
at present there is no support for associating non-default type
992
modifiers with user-created data types, and so this facility is only
993
of use for the small number of built-in types that have type modifier
994
syntax built into the grammar.
998
When a <structname>pg_cast</structname> entry has different source and
999
target types and a function that takes more than one argument, it
1000
represents converting from one type to another and applying a length
1001
coercion in a single step. When no such entry is available, coercion
1002
to a type that uses a type modifier involves two steps, one to
1003
convert between data types and a second to apply the modifier.
1007
<sect1 id="catalog-pg-class">
1008
<title><structname>pg_class</structname></title>
1010
<indexterm zone="catalog-pg-class">
1011
<primary>pg_class</primary>
1015
The catalog <structname>pg_class</structname> catalogs tables and most
1016
everything else that has columns or is otherwise similar to a
1017
table. This includes indexes (but see also
1018
<structname>pg_index</structname>), sequences, views, composite types,
1019
and some kinds of special relation; see <structfield>relkind</>.
1020
Below, when we mean all of these
1021
kinds of objects we speak of <quote>relations</quote>. Not all
1022
columns are meaningful for all relation types.
1026
<title><structname>pg_class</> Columns</title>
1033
<entry>References</entry>
1034
<entry>Description</entry>
1040
<entry><structfield>relname</structfield></entry>
1041
<entry><type>name</type></entry>
1043
<entry>Name of the table, index, view, etc.</entry>
1047
<entry><structfield>relnamespace</structfield></entry>
1048
<entry><type>oid</type></entry>
1049
<entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
1051
The OID of the namespace that contains this relation
1056
<entry><structfield>reltype</structfield></entry>
1057
<entry><type>oid</type></entry>
1058
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
1060
The OID of the data type that corresponds to this table's row type,
1061
if any (zero for indexes, which have no <structname>pg_type</> entry)
1066
<entry><structfield>relowner</structfield></entry>
1067
<entry><type>int4</type></entry>
1068
<entry><literal><link linkend="catalog-pg-shadow"><structname>pg_shadow</structname></link>.usesysid</literal></entry>
1069
<entry>Owner of the relation</entry>
1073
<entry><structfield>relam</structfield></entry>
1074
<entry><type>oid</type></entry>
1075
<entry><literal><link linkend="catalog-pg-am"><structname>pg_am</structname></link>.oid</literal></entry>
1076
<entry>If this is an index, the access method used (B-tree, hash, etc.)</entry>
1080
<entry><structfield>relfilenode</structfield></entry>
1081
<entry><type>oid</type></entry>
1083
<entry>Name of the on-disk file of this relation; 0 if none</entry>
1087
<entry><structfield>reltablespace</structfield></entry>
1088
<entry><type>oid</type></entry>
1089
<entry><literal><link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.oid</literal></entry>
1091
The tablespace in which this relation is stored. If zero,
1092
the database's default tablespace is implied. (Not meaningful
1093
if the relation has no on-disk file.)
1098
<entry><structfield>relpages</structfield></entry>
1099
<entry><type>int4</type></entry>
1102
Size of the on-disk representation of this table in pages (of size
1103
<symbol>BLCKSZ</symbol>).
1104
This is only an estimate used by the planner.
1105
It is updated by <command>VACUUM</command>,
1106
<command>ANALYZE</command>, and a few DDL commands
1107
such as <command>CREATE INDEX</command>.
1112
<entry><structfield>reltuples</structfield></entry>
1113
<entry><type>float4</type></entry>
1116
Number of rows in the table.
1117
This is only an estimate used by the planner.
1118
It is updated by <command>VACUUM</command>,
1119
<command>ANALYZE</command>, and a few DDL commands
1120
such as <command>CREATE INDEX</command>.
1125
<entry><structfield>reltoastrelid</structfield></entry>
1126
<entry><type>oid</type></entry>
1127
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
1129
OID of the TOAST table associated with this table, 0 if none.
1130
The TOAST table stores large attributes <quote>out of
1131
line</quote> in a secondary table.
1136
<entry><structfield>reltoastidxid</structfield></entry>
1137
<entry><type>oid</type></entry>
1138
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
1140
For a TOAST table, the OID of its index. 0 if not a TOAST table.
1145
<entry><structfield>relhasindex</structfield></entry>
1146
<entry><type>bool</type></entry>
1149
True if this is a table and it has (or recently had) any
1150
indexes. This is set by <command>CREATE INDEX</command>, but
1151
not cleared immediately by <command>DROP INDEX</command>.
1152
<command>VACUUM</command> clears <structfield>relhasindex</> if it finds the
1153
table has no indexes.
1158
<entry><structfield>relisshared</structfield></entry>
1159
<entry><type>bool</type></entry>
1161
<entry>True if this table is shared across all databases in the
1162
cluster. Only certain system catalogs (such as
1163
<structname>pg_database</structname>) are shared.</entry>
1167
<entry><structfield>relkind</structfield></entry>
1168
<entry><type>char</type></entry>
1171
<literal>r</> = ordinary table, <literal>i</> = index,
1172
<literal>S</> = sequence, <literal>v</> = view, <literal>c</> =
1173
composite type, <literal>s</> = special, <literal>t</> = TOAST
1179
<entry><structfield>relnatts</structfield></entry>
1180
<entry><type>int2</type></entry>
1183
Number of user columns in the relation (system columns not
1184
counted). There must be this many corresponding entries in
1185
<structname>pg_attribute</structname>. See also
1186
<literal>pg_attribute.attnum</literal>.
1191
<entry><structfield>relchecks</structfield></entry>
1192
<entry><type>int2</type></entry>
1195
Number of check constraints on the table; see
1196
<structname>pg_constraint</structname> catalog
1201
<entry><structfield>reltriggers</structfield></entry>
1202
<entry><type>int2</type></entry>
1205
Number of triggers on the table; see
1206
<structname>pg_trigger</structname> catalog
1211
<entry><structfield>relukeys</structfield></entry>
1212
<entry><type>int2</type></entry>
1214
<entry>unused (<emphasis>not</emphasis> the number of unique keys)</entry>
1218
<entry><structfield>relfkeys</structfield></entry>
1219
<entry><type>int2</type></entry>
1221
<entry>unused (<emphasis>not</emphasis> the number of foreign keys on the table)</entry>
1225
<entry><structfield>relrefs</structfield></entry>
1226
<entry><type>int2</type></entry>
1228
<entry>unused</entry>
1232
<entry><structfield>relhasoids</structfield></entry>
1233
<entry><type>bool</type></entry>
1236
True if we generate an OID for each row of the relation.
1241
<entry><structfield>relhaspkey</structfield></entry>
1242
<entry><type>bool</type></entry>
1245
True if the table has (or once had) a primary key.
1250
<entry><structfield>relhasrules</structfield></entry>
1251
<entry><type>bool</type></entry>
1253
<entry>True if table has rules; see
1254
<structname>pg_rewrite</structname> catalog.
1259
<entry><structfield>relhassubclass</structfield></entry>
1260
<entry><type>bool</type></entry>
1262
<entry>True if table has (or once had) any inheritance children.</entry>
1266
<entry><structfield>relacl</structfield></entry>
1267
<entry><type>aclitem[]</type></entry>
1270
Access privileges; see
1271
<xref linkend="sql-grant" endterm="sql-grant-title"> and
1272
<xref linkend="sql-revoke" endterm="sql-revoke-title">
1281
<sect1 id="catalog-pg-constraint">
1282
<title><structname>pg_constraint</structname></title>
1284
<indexterm zone="catalog-pg-constraint">
1285
<primary>pg_constraint</primary>
1289
The catalog <structname>pg_constraint</structname> stores check, primary key, unique, and foreign
1290
key constraints on tables. (Column constraints are not treated
1291
specially. Every column constraint is equivalent to some table
1292
constraint.) Not-null constraints are represented in the
1293
<structname>pg_attribute</> catalog.
1297
Check constraints on domains are stored here, too.
1301
<title><structname>pg_constraint</> Columns</title>
1308
<entry>References</entry>
1309
<entry>Description</entry>
1315
<entry><structfield>conname</structfield></entry>
1316
<entry><type>name</type></entry>
1318
<entry>Constraint name (not necessarily unique!)</entry>
1322
<entry><structfield>connamespace</structfield></entry>
1323
<entry><type>oid</type></entry>
1324
<entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
1326
The OID of the namespace that contains this constraint
1331
<entry><structfield>contype</structfield></entry>
1332
<entry><type>char</type></entry>
1335
<literal>c</> = check constraint,
1336
<literal>f</> = foreign key constraint,
1337
<literal>p</> = primary key constraint,
1338
<literal>u</> = unique constraint
1343
<entry><structfield>condeferrable</structfield></entry>
1344
<entry><type>bool</type></entry>
1346
<entry>Is the constraint deferrable?</entry>
1350
<entry><structfield>condeferred</structfield></entry>
1351
<entry><type>bool</type></entry>
1353
<entry>Is the constraint deferred by default?</entry>
1357
<entry><structfield>conrelid</structfield></entry>
1358
<entry><type>oid</type></entry>
1359
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
1360
<entry>The table this constraint is on; 0 if not a table constraint</entry>
1364
<entry><structfield>contypid</structfield></entry>
1365
<entry><type>oid</type></entry>
1366
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
1367
<entry>The domain this constraint is on; 0 if not a domain constraint</entry>
1371
<entry><structfield>confrelid</structfield></entry>
1372
<entry><type>oid</type></entry>
1373
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
1374
<entry>If a foreign key, the referenced table; else 0</entry>
1378
<entry><structfield>confupdtype</structfield></entry>
1379
<entry><type>char</type></entry>
1381
<entry>Foreign key update action code</entry>
1385
<entry><structfield>confdeltype</structfield></entry>
1386
<entry><type>char</type></entry>
1388
<entry>Foreign key deletion action code</entry>
1392
<entry><structfield>confmatchtype</structfield></entry>
1393
<entry><type>char</type></entry>
1395
<entry>Foreign key match type</entry>
1399
<entry><structfield>conkey</structfield></entry>
1400
<entry><type>int2[]</type></entry>
1401
<entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</></entry>
1402
<entry>If a table constraint, list of columns which the constraint constrains</entry>
1406
<entry><structfield>confkey</structfield></entry>
1407
<entry><type>int2[]</type></entry>
1408
<entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</></entry>
1409
<entry>If a foreign key, list of the referenced columns</entry>
1413
<entry><structfield>conbin</structfield></entry>
1414
<entry><type>text</type></entry>
1416
<entry>If a check constraint, an internal representation of the expression</entry>
1420
<entry><structfield>consrc</structfield></entry>
1421
<entry><type>text</type></entry>
1423
<entry>If a check constraint, a human-readable representation of the expression</entry>
1431
<structfield>consrc</structfield> is not updated when referenced objects
1432
change; for example, it won't track renaming of columns. Rather than
1433
relying on this field, it's best to use <function>pg_get_constraintdef()</>
1434
to extract the definition of a check constraint.
1440
<literal>pg_class.relchecks</literal> needs to agree with the
1441
number of check-constraint entries found in this table for the
1448
<sect1 id="catalog-pg-conversion">
1449
<title><structname>pg_conversion</structname></title>
1451
<indexterm zone="catalog-pg-conversion">
1452
<primary>pg_conversion</primary>
1456
The catalog <structname>pg_conversion</structname> describes the
1457
available encoding conversion procedures. See
1458
<xref linkend="sql-createconversion" endterm="sql-createconversion-title">
1459
for more information.
1463
<title><structname>pg_conversion</> Columns</title>
1470
<entry>References</entry>
1471
<entry>Description</entry>
1477
<entry><structfield>conname</structfield></entry>
1478
<entry><type>name</type></entry>
1480
<entry>Conversion name (unique within a namespace)</entry>
1484
<entry><structfield>connamespace</structfield></entry>
1485
<entry><type>oid</type></entry>
1486
<entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
1488
The OID of the namespace that contains this conversion
1493
<entry><structfield>conowner</structfield></entry>
1494
<entry><type>int4</type></entry>
1495
<entry><literal><link linkend="catalog-pg-shadow"><structname>pg_shadow</structname></link>.usesysid</literal></entry>
1496
<entry>Owner of the conversion</entry>
1500
<entry><structfield>conforencoding</structfield></entry>
1501
<entry><type>int4</type></entry>
1503
<entry>Source encoding ID</entry>
1507
<entry><structfield>contoencoding</structfield></entry>
1508
<entry><type>int4</type></entry>
1510
<entry>Destination encoding ID</entry>
1514
<entry><structfield>conproc</structfield></entry>
1515
<entry><type>regproc</type></entry>
1516
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
1517
<entry>Conversion procedure</entry>
1521
<entry><structfield>condefault</structfield></entry>
1522
<entry><type>bool</type></entry>
1524
<entry>True if this is the default conversion</entry>
1533
<sect1 id="catalog-pg-database">
1534
<title><structname>pg_database</structname></title>
1536
<indexterm zone="catalog-pg-database">
1537
<primary>pg_database</primary>
1541
The catalog <structname>pg_database</structname> stores information
1542
about the available databases. Databases are created with the
1543
<command>CREATE DATABASE</command> command. Consult
1544
<xref linkend="managing-databases"> for details about the meaning of some of the
1549
Unlike most system catalogs, <structname>pg_database</structname>
1550
is shared across all databases of a cluster: there is only one
1551
copy of <structname>pg_database</structname> per cluster, not
1556
<title><structname>pg_database</> Columns</title>
1563
<entry>References</entry>
1564
<entry>Description</entry>
1570
<entry><structfield>datname</structfield></entry>
1571
<entry><type>name</type></entry>
1573
<entry>Database name</entry>
1577
<entry><structfield>datdba</structfield></entry>
1578
<entry><type>int4</type></entry>
1579
<entry><literal><link linkend="catalog-pg-shadow"><structname>pg_shadow</structname></link>.usesysid</literal></entry>
1580
<entry>Owner of the database, usually the user who created it</entry>
1584
<entry><structfield>encoding</structfield></entry>
1585
<entry><type>int4</type></entry>
1587
<entry>Character encoding for this database</entry>
1591
<entry><structfield>datistemplate</structfield></entry>
1592
<entry><type>bool</type></entry>
1595
If true then this database can be used in the
1596
<literal>TEMPLATE</literal> clause of <command>CREATE
1597
DATABASE</command> to create a new database as a clone of
1603
<entry><structfield>datallowconn</structfield></entry>
1604
<entry><type>bool</type></entry>
1607
If false then no one can connect to this database. This is
1608
used to protect the <literal>template0</> database from being altered.
1613
<entry><structfield>datlastsysoid</structfield></entry>
1614
<entry><type>oid</type></entry>
1617
Last system OID in the database; useful
1618
particularly to <application>pg_dump</application>
1623
<entry><structfield>datvacuumxid</structfield></entry>
1624
<entry><type>xid</type></entry>
1627
All rows inserted or deleted by transaction IDs before this one
1628
have been marked as known committed or known aborted in this database.
1629
This is used to determine when commit-log space can be recycled.
1634
<entry><structfield>datfrozenxid</structfield></entry>
1635
<entry><type>xid</type></entry>
1638
All rows inserted by transaction IDs before this one have been
1639
relabeled with a permanent (<quote>frozen</>) transaction ID in this
1640
database. This is useful to check whether a database must be vacuumed
1641
soon to avoid transaction ID wrap-around problems.
1646
<entry><structfield>dattablespace</structfield></entry>
1647
<entry><type>oid</type></entry>
1648
<entry><literal><link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.oid</literal></entry>
1650
The default tablespace for the database.
1651
Within this database, all tables for which
1652
<structname>pg_class</>.<structfield>reltablespace</> is zero
1653
will be stored in this tablespace; in particular, all the non-shared
1654
system catalogs will be there.
1659
<entry><structfield>datconfig</structfield></entry>
1660
<entry><type>text[]</type></entry>
1662
<entry>Session defaults for run-time configuration variables</entry>
1666
<entry><structfield>datacl</structfield></entry>
1667
<entry><type>aclitem[]</type></entry>
1670
Access privileges; see
1671
<xref linkend="sql-grant" endterm="sql-grant-title"> and
1672
<xref linkend="sql-revoke" endterm="sql-revoke-title">
1682
<sect1 id="catalog-pg-depend">
1683
<title><structname>pg_depend</structname></title>
1685
<indexterm zone="catalog-pg-depend">
1686
<primary>pg_depend</primary>
1690
The catalog <structname>pg_depend</structname> records the dependency
1691
relationships between database objects. This information allows
1692
<command>DROP</> commands to find which other objects must be dropped
1693
by <command>DROP CASCADE</> or prevent dropping in the <command>DROP
1698
<title><structname>pg_depend</> Columns</title>
1705
<entry>References</entry>
1706
<entry>Description</entry>
1712
<entry><structfield>classid</structfield></entry>
1713
<entry><type>oid</type></entry>
1714
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
1715
<entry>The OID of the system catalog the dependent object is in</entry>
1719
<entry><structfield>objid</structfield></entry>
1720
<entry><type>oid</type></entry>
1721
<entry>any OID column</entry>
1722
<entry>The OID of the specific dependent object</entry>
1726
<entry><structfield>objsubid</structfield></entry>
1727
<entry><type>int4</type></entry>
1730
For a table column, this is the column number (the
1731
<structfield>objid</> and <structfield>classid</> refer to the
1732
table itself). For all other object types, this column is
1738
<entry><structfield>refclassid</structfield></entry>
1739
<entry><type>oid</type></entry>
1740
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
1741
<entry>The OID of the system catalog the referenced object is in</entry>
1745
<entry><structfield>refobjid</structfield></entry>
1746
<entry><type>oid</type></entry>
1747
<entry>any OID column</entry>
1748
<entry>The OID of the specific referenced object</entry>
1752
<entry><structfield>refobjsubid</structfield></entry>
1753
<entry><type>int4</type></entry>
1756
For a table column, this is the column number (the
1757
<structfield>refobjid</> and <structfield>refclassid</> refer
1758
to the table itself). For all other object types, this column
1764
<entry><structfield>deptype</structfield></entry>
1765
<entry><type>char</type></entry>
1768
A code defining the specific semantics of this dependency relationship; see text.
1777
In all cases, a <structname>pg_depend</structname> entry indicates that the
1778
referenced object may not be dropped without also dropping the dependent
1779
object. However, there are several subflavors identified by
1780
<structfield>deptype</>:
1784
<term><symbol>DEPENDENCY_NORMAL</> (<literal>n</>)</term>
1787
A normal relationship between separately-created objects. The
1788
dependent object may be dropped without affecting the
1789
referenced object. The referenced object may only be dropped
1790
by specifying <literal>CASCADE</>, in which case the dependent
1791
object is dropped, too. Example: a table column has a normal
1792
dependency on its data type.
1798
<term><symbol>DEPENDENCY_AUTO</> (<literal>a</>)</term>
1801
The dependent object can be dropped separately from the
1802
referenced object, and should be automatically dropped
1803
(regardless of <literal>RESTRICT</> or <literal>CASCADE</>
1804
mode) if the referenced object is dropped. Example: a named
1805
constraint on a table is made autodependent on the table, so
1806
that it will go away if the table is dropped.
1812
<term><symbol>DEPENDENCY_INTERNAL</> (<literal>i</>)</term>
1815
The dependent object was created as part of creation of the
1816
referenced object, and is really just a part of its internal
1817
implementation. A <command>DROP</> of the dependent object
1818
will be disallowed outright (we'll tell the user to issue a
1819
<command>DROP</> against the referenced object, instead). A
1820
<command>DROP</> of the referenced object will be propagated
1821
through to drop the dependent object whether
1822
<command>CASCADE</> is specified or not. Example: a trigger
1823
that's created to enforce a foreign-key constraint is made
1824
internally dependent on the constraint's
1825
<structname>pg_constraint</> entry.
1831
<term><symbol>DEPENDENCY_PIN</> (<literal>p</>)</term>
1834
There is no dependent object; this type of entry is a signal
1835
that the system itself depends on the referenced object, and so
1836
that object must never be deleted. Entries of this type are
1837
created only by <command>initdb</command>. The columns for the
1838
dependent object contain zeroes.
1844
Other dependency flavors may be needed in future.
1850
<sect1 id="catalog-pg-description">
1851
<title><structname>pg_description</structname></title>
1853
<indexterm zone="catalog-pg-description">
1854
<primary>pg_description</primary>
1858
The catalog <structname>pg_description</> stores optional descriptions
1859
(comments) for each database object. Descriptions can be manipulated
1860
with the <command>COMMENT</command> command and viewed with
1861
<application>psql</application>'s <literal>\d</literal> commands.
1862
Descriptions of many built-in system objects are provided in the initial
1863
contents of <structname>pg_description</structname>.
1867
<title><structname>pg_description</> Columns</title>
1874
<entry>References</entry>
1875
<entry>Description</entry>
1881
<entry><structfield>objoid</structfield></entry>
1882
<entry><type>oid</type></entry>
1883
<entry>any OID column</entry>
1884
<entry>The OID of the object this description pertains to</entry>
1888
<entry><structfield>classoid</structfield></entry>
1889
<entry><type>oid</type></entry>
1890
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
1891
<entry>The OID of the system catalog this object appears in</entry>
1895
<entry><structfield>objsubid</structfield></entry>
1896
<entry><type>int4</type></entry>
1899
For a comment on a table column, this is the column number (the
1900
<structfield>objoid</> and <structfield>classoid</> refer to
1901
the table itself). For all other object types, this column is
1907
<entry><structfield>description</structfield></entry>
1908
<entry><type>text</type></entry>
1910
<entry>Arbitrary text that serves as the description of this object.</entry>
1919
<sect1 id="catalog-pg-group">
1920
<title><structname>pg_group</structname></title>
1922
<indexterm zone="catalog-pg-group">
1923
<primary>pg_group</primary>
1927
The catalog <structname>pg_group</structname> defines groups and stores what users belong to what
1928
groups. Groups are created with the <command>CREATE
1929
GROUP</command> command. Consult <xref linkend="user-manag"> for information
1930
about user privilege management.
1934
Because user and group identities are cluster-wide,
1935
<structname>pg_group</structname>
1936
is shared across all databases of a cluster: there is only one
1937
copy of <structname>pg_group</structname> per cluster, not
1942
<title><structname>pg_group</> Columns</title>
1949
<entry>References</entry>
1950
<entry>Description</entry>
1956
<entry><structfield>groname</structfield></entry>
1957
<entry><type>name</type></entry>
1959
<entry>Name of the group</entry>
1963
<entry><structfield>grosysid</structfield></entry>
1964
<entry><type>int4</type></entry>
1966
<entry>An arbitrary number to identify this group</entry>
1970
<entry><structfield>grolist</structfield></entry>
1971
<entry><type>int4[]</type></entry>
1972
<entry><literal><link linkend="catalog-pg-shadow"><structname>pg_shadow</structname></link>.usesysid</literal></entry>
1973
<entry>An array containing the IDs of the users in this group</entry>
1982
<sect1 id="catalog-pg-index">
1983
<title><structname>pg_index</structname></title>
1985
<indexterm zone="catalog-pg-index">
1986
<primary>pg_index</primary>
1990
The catalog <structname>pg_index</structname> contains part of the information
1991
about indexes. The rest is mostly in
1992
<structname>pg_class</structname>.
1996
<title><structname>pg_index</> Columns</title>
2003
<entry>References</entry>
2004
<entry>Description</entry>
2010
<entry><structfield>indexrelid</structfield></entry>
2011
<entry><type>oid</type></entry>
2012
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
2013
<entry>The OID of the <structname>pg_class</> entry for this index</entry>
2017
<entry><structfield>indrelid</structfield></entry>
2018
<entry><type>oid</type></entry>
2019
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
2020
<entry>The OID of the <structname>pg_class</> entry for the table this index is for</entry>
2024
<entry><structfield>indkey</structfield></entry>
2025
<entry><type>int2vector</type></entry>
2026
<entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</literal></entry>
2028
This is an array of <structfield>indnatts</structfield> (up to
2029
<symbol>INDEX_MAX_KEYS</symbol>) values that indicate which
2030
table columns this index indexes. For example a value of
2031
<literal>1 3</literal> would mean that the first and the third table
2032
columns make up the index key. A zero in this array indicates that the
2033
corresponding index attribute is an expression over the table columns,
2034
rather than a simple column reference.
2039
<entry><structfield>indclass</structfield></entry>
2040
<entry><type>oidvector</type></entry>
2041
<entry><literal><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link>.oid</literal></entry>
2043
For each column in the index key this contains the OID of
2044
the operator class to use. See
2045
<structname>pg_opclass</structname> for details.
2050
<entry><structfield>indnatts</structfield></entry>
2051
<entry><type>int2</type></entry>
2053
<entry>The number of columns in the index (duplicates
2054
<literal>pg_class.relnatts</literal>)</entry>
2058
<entry><structfield>indisunique</structfield></entry>
2059
<entry><type>bool</type></entry>
2061
<entry>If true, this is a unique index.</entry>
2065
<entry><structfield>indisprimary</structfield></entry>
2066
<entry><type>bool</type></entry>
2068
<entry>If true, this index represents the primary key of the table.
2069
(<structfield>indisunique</> should always be true when this is true.)</entry>
2073
<entry><structfield>indisclustered</structfield></entry>
2074
<entry><type>bool</type></entry>
2076
<entry>If true, the table was last clustered on this index.</entry>
2080
<entry><structfield>indexprs</structfield></entry>
2081
<entry><type>text</type></entry>
2083
<entry>Expression trees (in <function>nodeToString()</function> representation)
2084
for index attributes that are not simple column references. This is a
2085
list with one element for each zero entry in <structfield>indkey</>.
2086
Null if all index attributes are simple references.</entry>
2090
<entry><structfield>indpred</structfield></entry>
2091
<entry><type>text</type></entry>
2093
<entry>Expression tree (in <function>nodeToString()</function> representation)
2094
for partial index predicate. Null if not a partial index.</entry>
2103
<sect1 id="catalog-pg-inherits">
2104
<title><structname>pg_inherits</structname></title>
2106
<indexterm zone="catalog-pg-inherits">
2107
<primary>pg_inherits</primary>
2111
The catalog <structname>pg_inherits</> records information about
2112
table inheritance hierarchies. There is one entry for each direct
2113
child table in the database. (Indirect inheritance can be determined
2114
by following chains of entries.)
2118
<title><structname>pg_inherits</> Columns</title>
2125
<entry>References</entry>
2126
<entry>Description</entry>
2132
<entry><structfield>inhrelid</structfield></entry>
2133
<entry><type>oid</type></entry>
2134
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
2136
The OID of the child table.
2141
<entry><structfield>inhparent</structfield></entry>
2142
<entry><type>oid</type></entry>
2143
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
2145
The OID of the parent table.
2150
<entry><structfield>inhseqno</structfield></entry>
2151
<entry><type>int4</type></entry>
2154
If there is more than one direct parent for a child table (multiple
2155
inheritance), this number tells the order in which the
2156
inherited columns are to be arranged. The count starts at 1.
2166
<sect1 id="catalog-pg-language">
2167
<title><structname>pg_language</structname></title>
2169
<indexterm zone="catalog-pg-language">
2170
<primary>pg_language</primary>
2174
The catalog <structname>pg_language</structname> registers
2175
languages in which you can write functions or stored procedures.
2176
See <xref linkend="sql-createlanguage" endterm="sql-createlanguage-title">
2177
and <xref linkend="xplang"> for more information about language handlers.
2181
<title><structname>pg_language</> Columns</title>
2188
<entry>References</entry>
2189
<entry>Description</entry>
2195
<entry><structfield>lanname</structfield></entry>
2196
<entry><type>name</type></entry>
2198
<entry>Name of the language</entry>
2202
<entry><structfield>lanispl</structfield></entry>
2203
<entry><type>bool</type></entry>
2206
This is false for internal languages (such as
2207
<acronym>SQL</acronym>) and true for user-defined languages.
2208
Currently, <application>pg_dump</application> still uses this
2209
to determine which languages need to be dumped, but this may be
2210
replaced by a different mechanism sometime.
2215
<entry><structfield>lanpltrusted</structfield></entry>
2216
<entry><type>bool</type></entry>
2219
This is a trusted language. If this is an internal
2220
language (<structfield>lanispl</structfield> is false) then
2221
this column is meaningless.
2226
<entry><structfield>lanplcallfoid</structfield></entry>
2227
<entry><type>oid</type></entry>
2228
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
2230
For noninternal languages this references the language
2231
handler, which is a special function that is responsible for
2232
executing all functions that are written in the particular
2238
<entry><structfield>lanvalidator</structfield></entry>
2239
<entry><type>oid</type></entry>
2240
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
2242
This references a language validator function that is responsible
2243
for checking the syntax and validity of new functions when they
2244
are created. Zero if no validator is provided.
2249
<entry><structfield>lanacl</structfield></entry>
2250
<entry><type>aclitem[]</type></entry>
2253
Access privileges; see
2254
<xref linkend="sql-grant" endterm="sql-grant-title"> and
2255
<xref linkend="sql-revoke" endterm="sql-revoke-title">
2266
<sect1 id="catalog-pg-largeobject">
2267
<title><structname>pg_largeobject</structname></title>
2269
<indexterm zone="catalog-pg-largeobject">
2270
<primary>pg_largeobject</primary>
2274
The catalog <structname>pg_largeobject</structname> holds the data making up
2275
<quote>large objects</quote>. A large object is identified by an
2276
OID assigned when it is created. Each large object is broken into
2277
segments or <quote>pages</> small enough to be conveniently stored as rows
2278
in <structname>pg_largeobject</structname>.
2279
The amount of data per page is defined to be <literal>LOBLKSIZE</> (which is currently
2280
<literal>BLCKSZ/4</>, or typically 2 kB).
2284
<title><structname>pg_largeobject</> Columns</title>
2291
<entry>References</entry>
2292
<entry>Description</entry>
2298
<entry><structfield>loid</structfield></entry>
2299
<entry><type>oid</type></entry>
2301
<entry>Identifier of the large object that includes this page</entry>
2305
<entry><structfield>pageno</structfield></entry>
2306
<entry><type>int4</type></entry>
2308
<entry>Page number of this page within its large object
2309
(counting from zero)</entry>
2313
<entry><structfield>data</structfield></entry>
2314
<entry><type>bytea</type></entry>
2317
Actual data stored in the large object.
2318
This will never be more than <symbol>LOBLKSIZE</> bytes and may be less.
2326
Each row of <structname>pg_largeobject</structname> holds data
2327
for one page of a large object, beginning at
2328
byte offset (<literal>pageno * LOBLKSIZE</>) within the object. The implementation
2329
allows sparse storage: pages may be missing, and may be shorter than
2330
<literal>LOBLKSIZE</> bytes even if they are not the last page of the object.
2331
Missing regions within a large object read as zeroes.
2337
<sect1 id="catalog-pg-listener">
2338
<title><structname>pg_listener</structname></title>
2340
<indexterm zone="catalog-pg-listener">
2341
<primary>pg_listener</primary>
2345
The catalog <structname>pg_listener</structname> supports the
2346
<xref linkend="sql-listen" endterm="sql-listen-title"> and
2347
<xref linkend="sql-notify" endterm="sql-notify-title">
2348
commands. A listener creates an entry in
2349
<structname>pg_listener</structname> for each notification name
2350
it is listening for. A notifier scans <structname>pg_listener</structname>
2351
and updates each matching entry to show that a notification has occurred.
2352
The notifier also sends a signal (using the PID recorded in the table)
2353
to awaken the listener from sleep.
2357
<title><structname>pg_listener</> Columns</title>
2364
<entry>References</entry>
2365
<entry>Description</entry>
2371
<entry><structfield>relname</structfield></entry>
2372
<entry><type>name</type></entry>
2374
<entry>Notify condition name. (The name need not match any actual
2375
relation in the database; the name <structfield>relname</> is historical.)
2380
<entry><structfield>listenerpid</structfield></entry>
2381
<entry><type>int4</type></entry>
2383
<entry>PID of the server process that created this entry.</entry>
2387
<entry><structfield>notification</structfield></entry>
2388
<entry><type>int4</type></entry>
2391
Zero if no event is pending for this listener. If an event is
2392
pending, the PID of the server process that sent the notification.
2402
<sect1 id="catalog-pg-namespace">
2403
<title><structname>pg_namespace</structname></title>
2405
<indexterm zone="catalog-pg-namespace">
2406
<primary>pg_namespace</primary>
2410
The catalog <structname>pg_namespace</> stores namespaces.
2411
A namespace is the structure underlying SQL schemas: each namespace
2412
can have a separate collection of relations, types, etc. without name
2417
<title><structname>pg_namespace</> Columns</title>
2424
<entry>References</entry>
2425
<entry>Description</entry>
2431
<entry><structfield>nspname</structfield></entry>
2432
<entry><type>name</type></entry>
2434
<entry>Name of the namespace</entry>
2438
<entry><structfield>nspowner</structfield></entry>
2439
<entry><type>int4</type></entry>
2440
<entry><literal><link linkend="catalog-pg-shadow"><structname>pg_shadow</structname></link>.usesysid</literal></entry>
2441
<entry>Owner of the namespace</entry>
2445
<entry><structfield>nspacl</structfield></entry>
2446
<entry><type>aclitem[]</type></entry>
2449
Access privileges; see
2450
<xref linkend="sql-grant" endterm="sql-grant-title"> and
2451
<xref linkend="sql-revoke" endterm="sql-revoke-title">
2462
<sect1 id="catalog-pg-opclass">
2463
<title><structname>pg_opclass</structname></title>
2465
<indexterm zone="catalog-pg-opclass">
2466
<primary>pg_opclass</primary>
2470
The catalog <structname>pg_opclass</structname> defines
2471
index access method operator classes. Each operator class defines
2472
semantics for index columns of a particular data type and a particular
2473
index access method. Note that there can be multiple operator classes
2474
for a given data type/access method combination, thus supporting multiple
2479
Operator classes are described at length in <xref linkend="xindex">.
2483
<title><structname>pg_opclass</> Columns</title>
2490
<entry>References</entry>
2491
<entry>Description</entry>
2497
<entry><structfield>opcamid</structfield></entry>
2498
<entry><type>oid</type></entry>
2499
<entry><literal><link linkend="catalog-pg-am"><structname>pg_am</structname></link>.oid</literal></entry>
2500
<entry>Index access method operator class is for</entry>
2504
<entry><structfield>opcname</structfield></entry>
2505
<entry><type>name</type></entry>
2507
<entry>Name of this operator class</entry>
2511
<entry><structfield>opcnamespace</structfield></entry>
2512
<entry><type>oid</type></entry>
2513
<entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
2514
<entry>Namespace of this operator class</entry>
2518
<entry><structfield>opcowner</structfield></entry>
2519
<entry><type>int4</type></entry>
2520
<entry><literal><link linkend="catalog-pg-shadow"><structname>pg_shadow</structname></link>.usesysid</literal></entry>
2521
<entry>Operator class owner</entry>
2525
<entry><structfield>opcintype</structfield></entry>
2526
<entry><type>oid</type></entry>
2527
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
2528
<entry>Data type that the operator class indexes</entry>
2532
<entry><structfield>opcdefault</structfield></entry>
2533
<entry><type>bool</type></entry>
2535
<entry>True if this operator class is the default for <structfield>opcintype</></entry>
2539
<entry><structfield>opckeytype</structfield></entry>
2540
<entry><type>oid</type></entry>
2541
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
2542
<entry>Type of data stored in index, or zero if same as <structfield>opcintype</></entry>
2550
The majority of the information defining an operator class is actually
2551
not in its <structname>pg_opclass</structname> row, but in the associated
2552
rows in <structname>pg_amop</structname> and
2553
<structname>pg_amproc</structname>. Those rows are considered to be
2554
part of the operator class definition — this is not unlike the way
2555
that a relation is defined by a single <structname>pg_class</structname>
2556
row plus associated rows in <structname>pg_attribute</structname> and
2563
<sect1 id="catalog-pg-operator">
2564
<title><structname>pg_operator</structname></title>
2566
<indexterm zone="catalog-pg-operator">
2567
<primary>pg_operator</primary>
2571
The catalog <structname>pg_operator</> stores information about operators.
2572
See <xref linkend="sql-createoperator" endterm="sql-createoperator-title">
2573
and <xref linkend="xoper"> for more information.
2577
<title><structname>pg_operator</> Columns</title>
2584
<entry>References</entry>
2585
<entry>Description</entry>
2591
<entry><structfield>oprname</structfield></entry>
2592
<entry><type>name</type></entry>
2594
<entry>Name of the operator</entry>
2598
<entry><structfield>oprnamespace</structfield></entry>
2599
<entry><type>oid</type></entry>
2600
<entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
2602
The OID of the namespace that contains this operator
2607
<entry><structfield>oprowner</structfield></entry>
2608
<entry><type>int4</type></entry>
2609
<entry><literal><link linkend="catalog-pg-shadow"><structname>pg_shadow</structname></link>.usesysid</literal></entry>
2610
<entry>Owner of the operator</entry>
2614
<entry><structfield>oprkind</structfield></entry>
2615
<entry><type>char</type></entry>
2618
<literal>b</> = infix (<quote>both</quote>), <literal>l</> = prefix
2619
(<quote>left</quote>), <literal>r</> = postfix (<quote>right</quote>)
2624
<entry><structfield>oprcanhash</structfield></entry>
2625
<entry><type>bool</type></entry>
2627
<entry>This operator supports hash joins</entry>
2631
<entry><structfield>oprleft</structfield></entry>
2632
<entry><type>oid</type></entry>
2633
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
2634
<entry>Type of the left operand</entry>
2638
<entry><structfield>oprright</structfield></entry>
2639
<entry><type>oid</type></entry>
2640
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
2641
<entry>Type of the right operand</entry>
2645
<entry><structfield>oprresult</structfield></entry>
2646
<entry><type>oid</type></entry>
2647
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
2648
<entry>Type of the result</entry>
2652
<entry><structfield>oprcom</structfield></entry>
2653
<entry><type>oid</type></entry>
2654
<entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</literal></entry>
2655
<entry>Commutator of this operator, if any</entry>
2659
<entry><structfield>oprnegate</structfield></entry>
2660
<entry><type>oid</type></entry>
2661
<entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</literal></entry>
2662
<entry>Negator of this operator, if any</entry>
2666
<entry><structfield>oprlsortop</structfield></entry>
2667
<entry><type>oid</type></entry>
2668
<entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</literal></entry>
2670
If this operator supports merge joins, the operator that sorts
2671
the type of the left-hand operand (<literal>L<L</>)
2676
<entry><structfield>oprrsortop</structfield></entry>
2677
<entry><type>oid</type></entry>
2678
<entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</literal></entry>
2680
If this operator supports merge joins, the operator that sorts
2681
the type of the right-hand operand (<literal>R<R</>)
2686
<entry><structfield>oprltcmpop</structfield></entry>
2687
<entry><type>oid</type></entry>
2688
<entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</literal></entry>
2690
If this operator supports merge joins, the less-than operator that
2691
compares the left and right operand types (<literal>L<R</>)
2696
<entry><structfield>oprgtcmpop</structfield></entry>
2697
<entry><type>oid</type></entry>
2698
<entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</literal></entry>
2700
If this operator supports merge joins, the greater-than operator that
2701
compares the left and right operand types (<literal>L>R</>)
2706
<entry><structfield>oprcode</structfield></entry>
2707
<entry><type>regproc</type></entry>
2708
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
2709
<entry>Function that implements this operator</entry>
2713
<entry><structfield>oprrest</structfield></entry>
2714
<entry><type>regproc</type></entry>
2715
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
2716
<entry>Restriction selectivity estimation function for this operator</entry>
2720
<entry><structfield>oprjoin</structfield></entry>
2721
<entry><type>regproc</type></entry>
2722
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
2723
<entry>Join selectivity estimation function for this operator</entry>
2730
Unused column contain zeroes, for example <structfield>oprleft</structfield> is zero for a
2737
<sect1 id="catalog-pg-proc">
2738
<title><structname>pg_proc</structname></title>
2740
<indexterm zone="catalog-pg-proc">
2741
<primary>pg_proc</primary>
2745
The catalog <structname>pg_proc</> stores information about functions (or procedures).
2746
See <xref linkend="sql-createfunction" endterm="sql-createfunction-title">
2747
and <xref linkend="xfunc"> for more information.
2751
The table contains data for aggregate functions as well as plain functions.
2752
If <structfield>proisagg</structfield> is true, there should be a matching
2753
row in <structfield>pg_aggregate</structfield>.
2757
<title><structname>pg_proc</> Columns</title>
2764
<entry>References</entry>
2765
<entry>Description</entry>
2771
<entry><structfield>proname</structfield></entry>
2772
<entry><type>name</type></entry>
2774
<entry>Name of the function</entry>
2778
<entry><structfield>pronamespace</structfield></entry>
2779
<entry><type>oid</type></entry>
2780
<entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
2782
The OID of the namespace that contains this function
2787
<entry><structfield>proowner</structfield></entry>
2788
<entry><type>int4</type></entry>
2789
<entry><literal><link linkend="catalog-pg-shadow"><structname>pg_shadow</structname></link>.usesysid</literal></entry>
2790
<entry>Owner of the function</entry>
2794
<entry><structfield>prolang</structfield></entry>
2795
<entry><type>oid</type></entry>
2796
<entry><literal><link linkend="catalog-pg-language"><structname>pg_language</structname></link>.oid</literal></entry>
2797
<entry>Implementation language or call interface of this function</entry>
2801
<entry><structfield>proisagg</structfield></entry>
2802
<entry><type>bool</type></entry>
2804
<entry>Function is an aggregate function</entry>
2808
<entry><structfield>prosecdef</structfield></entry>
2809
<entry><type>bool</type></entry>
2811
<entry>Function is a security definer (i.e., a <quote>setuid</>
2816
<entry><structfield>proisstrict</structfield></entry>
2817
<entry><type>bool</type></entry>
2820
Function returns null if any call argument is null. In that
2821
case the function won't actually be called at all. Functions
2822
that are not <quote>strict</quote> must be prepared to handle
2828
<entry><structfield>proretset</structfield></entry>
2829
<entry><type>bool</type></entry>
2831
<entry>Function returns a set (i.e., multiple values of the specified
2836
<entry><structfield>provolatile</structfield></entry>
2837
<entry><type>char</type></entry>
2840
<structfield>provolatile</structfield> tells whether the function's
2841
result depends only on its input arguments, or is affected by outside
2843
It is <literal>i</literal> for <quote>immutable</> functions,
2844
which always deliver the same result for the same inputs.
2845
It is <literal>s</literal> for <quote>stable</> functions,
2846
whose results (for fixed inputs) do not change within a scan.
2847
It is <literal>v</literal> for <quote>volatile</> functions,
2848
whose results may change at any time. (Use <literal>v</literal> also
2849
for functions with side-effects, so that calls to them cannot get
2855
<entry><structfield>pronargs</structfield></entry>
2856
<entry><type>int2</type></entry>
2858
<entry>Number of arguments</entry>
2862
<entry><structfield>prorettype</structfield></entry>
2863
<entry><type>oid</type></entry>
2864
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
2865
<entry>Data type of the return value</entry>
2869
<entry><structfield>proargtypes</structfield></entry>
2870
<entry><type>oidvector</type></entry>
2871
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
2872
<entry>An array with the data types of the function arguments</entry>
2876
<entry><structfield>proargnames</structfield></entry>
2877
<entry><type>text[]</type></entry>
2880
An array with the names of the function arguments.
2881
Arguments without a name are set to empty strings in the array.
2882
If none of the arguments have a name, this field may be null.
2887
<entry><structfield>prosrc</structfield></entry>
2888
<entry><type>text</type></entry>
2891
This tells the function handler how to invoke the function. It
2892
might be the actual source code of the function for interpreted
2893
languages, a link symbol, a file name, or just about anything
2894
else, depending on the implementation language/call convention.
2899
<entry><structfield>probin</structfield></entry>
2900
<entry><type>bytea</type></entry>
2902
<entry>Additional information about how to invoke the function.
2903
Again, the interpretation is language-specific.
2908
<entry><structfield>proacl</structfield></entry>
2909
<entry><type>aclitem[]</type></entry>
2912
Access privileges; see
2913
<xref linkend="sql-grant" endterm="sql-grant-title"> and
2914
<xref linkend="sql-revoke" endterm="sql-revoke-title">
2923
For compiled functions, both built-in and dynamically loaded,
2924
<structfield>prosrc</structfield> contains the function's C-language
2925
name (link symbol). For all other currently-known language types,
2926
<structfield>prosrc</structfield> contains the function's source
2927
text. <structfield>probin</structfield> is unused except for
2928
dynamically-loaded C functions, for which it gives the name of the
2929
shared library file containing the function.
2934
<sect1 id="catalog-pg-rewrite">
2935
<title><structname>pg_rewrite</structname></title>
2937
<indexterm zone="catalog-pg-rewrite">
2938
<primary>pg_rewrite</primary>
2942
The catalog <structname>pg_rewrite</structname> stores rewrite rules for tables and views.
2946
<title><structname>pg_rewrite</> Columns</title>
2953
<entry>References</entry>
2954
<entry>Description</entry>
2960
<entry><structfield>rulename</structfield></entry>
2961
<entry><type>name</type></entry>
2963
<entry>Rule name</entry>
2967
<entry><structfield>ev_class</structfield></entry>
2968
<entry><type>oid</type></entry>
2969
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
2970
<entry>The table this rule is for</entry>
2974
<entry><structfield>ev_attr</structfield></entry>
2975
<entry><type>int2</type></entry>
2977
<entry>The column this rule is for (currently, always zero to
2978
indicate the whole table)</entry>
2982
<entry><structfield>ev_type</structfield></entry>
2983
<entry><type>char</type></entry>
2986
Event type that the rule is for: 1 = <command>SELECT</>, 2 =
2987
<command>UPDATE</>, 3 = <command>INSERT</>, 4 =
2993
<entry><structfield>is_instead</structfield></entry>
2994
<entry><type>bool</type></entry>
2996
<entry>True if the rule is an <literal>INSTEAD</literal> rule</entry>
3000
<entry><structfield>ev_qual</structfield></entry>
3001
<entry><type>text</type></entry>
3004
Expression tree (in the form of a
3005
<function>nodeToString()</function> representation) for the
3006
rule's qualifying condition
3011
<entry><structfield>ev_action</structfield></entry>
3012
<entry><type>text</type></entry>
3015
Query tree (in the form of a
3016
<function>nodeToString()</function> representation) for the
3026
<literal>pg_class.relhasrules</literal>
3027
must be true if a table has any rules in this catalog.
3034
<sect1 id="catalog-pg-shadow">
3035
<title><structname>pg_shadow</structname></title>
3037
<indexterm zone="catalog-pg-shadow">
3038
<primary>pg_shadow</primary>
3042
The catalog <structname>pg_shadow</structname> contains information about
3043
database users. The name stems from the fact that this table
3044
should not be readable by the public since it contains passwords.
3045
<link linkend="view-pg-user"><structname>pg_user</structname></link>
3046
is a publicly readable view on
3047
<structname>pg_shadow</structname> that blanks out the password field.
3051
<xref linkend="user-manag"> contains detailed information about user and
3052
privilege management.
3056
Because user identities are cluster-wide,
3057
<structname>pg_shadow</structname>
3058
is shared across all databases of a cluster: there is only one
3059
copy of <structname>pg_shadow</structname> per cluster, not
3064
<title><structname>pg_shadow</> Columns</title>
3071
<entry>References</entry>
3072
<entry>Description</entry>
3078
<entry><structfield>usename</structfield></entry>
3079
<entry><type>name</type></entry>
3081
<entry>User name</entry>
3085
<entry><structfield>usesysid</structfield></entry>
3086
<entry><type>int4</type></entry>
3088
<entry>User ID (arbitrary number used to reference this user)</entry>
3092
<entry><structfield>usecreatedb</structfield></entry>
3093
<entry><type>bool</type></entry>
3095
<entry>User may create databases</entry>
3099
<entry><structfield>usesuper</structfield></entry>
3100
<entry><type>bool</type></entry>
3102
<entry>User is a superuser</entry>
3106
<entry><structfield>usecatupd</structfield></entry>
3107
<entry><type>bool</type></entry>
3110
User may update system catalogs. (Even a superuser may not do
3111
this unless this column is true.)
3116
<entry><structfield>passwd</structfield></entry>
3117
<entry><type>text</type></entry>
3119
<entry>Password (possibly encrypted)</entry>
3123
<entry><structfield>valuntil</structfield></entry>
3124
<entry><type>abstime</type></entry>
3126
<entry>Password expiry time (only used for password authentication)</entry>
3130
<entry><structfield>useconfig</structfield></entry>
3131
<entry><type>text[]</type></entry>
3133
<entry>Session defaults for run-time configuration variables</entry>
3142
<sect1 id="catalog-pg-statistic">
3143
<title><structname>pg_statistic</structname></title>
3145
<indexterm zone="catalog-pg-statistic">
3146
<primary>pg_statistic</primary>
3150
The catalog <structname>pg_statistic</structname> stores statistical data
3151
about the contents of the database. Entries are created by
3152
<command>ANALYZE</command> and subsequently used by the query planner.
3153
There is one entry for each table column that has been analyzed.
3154
Note that all the statistical data is inherently approximate,
3155
even assuming that it is up-to-date.
3159
<structname>pg_statistic</structname> also stores statistical data about
3160
the values of index expressions. These are described as if they were
3161
actual data columns; in particular, <structfield>starelid</structfield>
3162
references the index. No entry is made for an ordinary non-expression
3163
index column, however, since it would be redundant with the entry
3164
for the underlying table column.
3168
Since different kinds of statistics may be appropriate for different
3169
kinds of data, <structname>pg_statistic</structname> is designed not
3170
to assume very much about what sort of statistics it stores. Only
3171
extremely general statistics (such as nullness) are given dedicated
3172
columns in <structname>pg_statistic</structname>. Everything else
3173
is stored in <quote>slots</quote>, which are groups of associated columns
3174
whose content is identified by a code number in one of the slot's columns.
3175
For more information see
3176
<filename>src/include/catalog/pg_statistic.h</filename>.
3180
<structname>pg_statistic</structname> should not be readable by the
3181
public, since even statistical information about a table's contents
3182
may be considered sensitive. (Example: minimum and maximum values
3183
of a salary column might be quite interesting.)
3184
<link linkend="view-pg-stats"><structname>pg_stats</structname></link>
3185
is a publicly readable view on
3186
<structname>pg_statistic</structname> that only exposes information
3187
about those tables that are readable by the current user.
3191
<title><structname>pg_statistic</> Columns</title>
3198
<entry>References</entry>
3199
<entry>Description</entry>
3205
<entry><structfield>starelid</structfield></entry>
3206
<entry><type>oid</type></entry>
3207
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
3208
<entry>The table or index that the described column belongs to</entry>
3212
<entry><structfield>staattnum</structfield></entry>
3213
<entry><type>int2</type></entry>
3214
<entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</literal></entry>
3215
<entry>The number of the described column</entry>
3219
<entry><structfield>stanullfrac</structfield></entry>
3220
<entry><type>float4</type></entry>
3222
<entry>The fraction of the column's entries that are null</entry>
3226
<entry><structfield>stawidth</structfield></entry>
3227
<entry><type>int4</type></entry>
3229
<entry>The average stored width, in bytes, of nonnull entries</entry>
3233
<entry><structfield>stadistinct</structfield></entry>
3234
<entry><type>float4</type></entry>
3236
<entry>The number of distinct nonnull data values in the column.
3237
A value greater than zero is the actual number of distinct values.
3238
A value less than zero is the negative of a fraction of the number
3239
of rows in the table (for example, a column in which values appear about
3240
twice on the average could be represented by <structfield>stadistinct</> = -0.5).
3241
A zero value means the number of distinct values is unknown.
3246
<entry><structfield>stakind<replaceable>N</></structfield></entry>
3247
<entry><type>int2</type></entry>
3250
A code number indicating the kind of statistics stored in the
3251
<replaceable>N</>th <quote>slot</quote> of the
3252
<structname>pg_statistic</structname> row.
3257
<entry><structfield>staop<replaceable>N</></structfield></entry>
3258
<entry><type>oid</type></entry>
3259
<entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</literal></entry>
3261
An operator used to derive the statistics stored in the
3262
<replaceable>N</>th <quote>slot</quote>. For example, a
3263
histogram slot would show the <literal><</literal> operator
3264
that defines the sort order of the data.
3269
<entry><structfield>stanumbers<replaceable>N</></structfield></entry>
3270
<entry><type>float4[]</type></entry>
3273
Numerical statistics of the appropriate kind for the
3274
<replaceable>N</>th <quote>slot</quote>, or null if the slot
3275
kind does not involve numerical values.
3280
<entry><structfield>stavalues<replaceable>N</></structfield></entry>
3281
<entry><type>anyarray</type></entry>
3284
Column data values of the appropriate kind for the
3285
<replaceable>N</>th <quote>slot</quote>, or null if the slot
3286
kind does not store any data values. Each array's element
3287
values are actually of the specific column's data type, so there
3288
is no way to define these columns' type more specifically than
3299
<sect1 id="catalog-pg-tablespace">
3300
<title><structname>pg_tablespace</structname></title>
3302
<indexterm zone="catalog-pg-tablespace">
3303
<primary>pg_tablespace</primary>
3307
The catalog <structname>pg_tablespace</structname> stores information
3308
about the available tablespaces. Tables can be placed in particular
3309
tablespaces to aid administration of disk layout.
3313
Unlike most system catalogs, <structname>pg_tablespace</structname>
3314
is shared across all databases of a cluster: there is only one
3315
copy of <structname>pg_tablespace</structname> per cluster, not
3320
<title><structname>pg_tablespace</> Columns</title>
3327
<entry>References</entry>
3328
<entry>Description</entry>
3334
<entry><structfield>spcname</structfield></entry>
3335
<entry><type>name</type></entry>
3337
<entry>Tablespace name</entry>
3341
<entry><structfield>spcowner</structfield></entry>
3342
<entry><type>int4</type></entry>
3343
<entry><literal><link linkend="catalog-pg-shadow"><structname>pg_shadow</structname></link>.usesysid</literal></entry>
3344
<entry>Owner of the tablespace, usually the user who created it</entry>
3348
<entry><structfield>spclocation</structfield></entry>
3349
<entry><type>text</type></entry>
3351
<entry>Location (directory path) of the tablespace</entry>
3355
<entry><structfield>spcacl</structfield></entry>
3356
<entry><type>aclitem[]</type></entry>
3359
Access privileges; see
3360
<xref linkend="sql-grant" endterm="sql-grant-title"> and
3361
<xref linkend="sql-revoke" endterm="sql-revoke-title">
3371
<sect1 id="catalog-pg-trigger">
3372
<title><structname>pg_trigger</structname></title>
3374
<indexterm zone="catalog-pg-trigger">
3375
<primary>pg_trigger</primary>
3379
The catalog <structname>pg_trigger</structname> stores triggers on tables.
3380
See <xref linkend="sql-createtrigger" endterm="sql-createtrigger-title">
3381
for more information.
3385
<title><structname>pg_trigger</> Columns</title>
3392
<entry>References</entry>
3393
<entry>Description</entry>
3399
<entry><structfield>tgrelid</structfield></entry>
3400
<entry><type>oid</type></entry>
3401
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
3402
<entry>The table this trigger is on</entry>
3406
<entry><structfield>tgname</structfield></entry>
3407
<entry><type>name</type></entry>
3409
<entry>Trigger name (must be unique among triggers of same table)</entry>
3413
<entry><structfield>tgfoid</structfield></entry>
3414
<entry><type>oid</type></entry>
3415
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
3416
<entry>The function to be called</entry>
3420
<entry><structfield>tgtype</structfield></entry>
3421
<entry><type>int2</type></entry>
3423
<entry>Bit mask identifying trigger conditions</entry>
3427
<entry><structfield>tgenabled</structfield></entry>
3428
<entry><type>bool</type></entry>
3430
<entry>True if trigger is enabled (not presently checked everywhere
3431
it should be, so disabling a trigger by setting this false does not
3432
work reliably)</entry>
3436
<entry><structfield>tgisconstraint</structfield></entry>
3437
<entry><type>bool</type></entry>
3439
<entry>True if trigger implements a referential integrity constraint</entry>
3443
<entry><structfield>tgconstrname</structfield></entry>
3444
<entry><type>name</type></entry>
3446
<entry>Referential integrity constraint name</entry>
3450
<entry><structfield>tgconstrrelid</structfield></entry>
3451
<entry><type>oid</type></entry>
3452
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
3453
<entry>The table referenced by an referential integrity constraint</entry>
3457
<entry><structfield>tgdeferrable</structfield></entry>
3458
<entry><type>bool</type></entry>
3460
<entry>True if deferrable</entry>
3464
<entry><structfield>tginitdeferred</structfield></entry>
3465
<entry><type>bool</type></entry>
3467
<entry>True if initially deferred</entry>
3471
<entry><structfield>tgnargs</structfield></entry>
3472
<entry><type>int2</type></entry>
3474
<entry>Number of argument strings passed to trigger function</entry>
3478
<entry><structfield>tgattr</structfield></entry>
3479
<entry><type>int2vector</type></entry>
3481
<entry>Currently unused</entry>
3485
<entry><structfield>tgargs</structfield></entry>
3486
<entry><type>bytea</type></entry>
3488
<entry>Argument strings to pass to trigger, each null-terminated</entry>
3496
<literal>pg_class.reltriggers</literal> needs to agree with the
3497
number of triggers found in this table for the given relation.
3504
<sect1 id="catalog-pg-type">
3505
<title><structname>pg_type</structname></title>
3507
<indexterm zone="catalog-pg-type">
3508
<primary>pg_type</primary>
3512
The catalog <structname>pg_type</structname> stores information about data
3513
types. Base types (scalar types) are created with
3514
<xref linkend="sql-createtype" endterm="sql-createtype-title">, and
3516
<xref linkend="sql-createdomain" endterm="sql-createdomain-title">.
3517
A composite type is automatically created for each table in the database, to
3518
represent the row structure of the table. It is also possible to create
3519
composite types with <command>CREATE TYPE AS</command>.
3523
<title><structname>pg_type</> Columns</title>
3530
<entry>References</entry>
3531
<entry>Description</entry>
3537
<entry><structfield>typname</structfield></entry>
3538
<entry><type>name</type></entry>
3540
<entry>Data type name</entry>
3544
<entry><structfield>typnamespace</structfield></entry>
3545
<entry><type>oid</type></entry>
3546
<entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
3548
The OID of the namespace that contains this type
3553
<entry><structfield>typowner</structfield></entry>
3554
<entry><type>int4</type></entry>
3555
<entry><literal><link linkend="catalog-pg-shadow"><structname>pg_shadow</structname></link>.usesysid</literal></entry>
3556
<entry>Owner of the type</entry>
3560
<entry><structfield>typlen</structfield></entry>
3561
<entry><type>int2</type></entry>
3564
For a fixed-size type, <structfield>typlen</structfield> is the number
3565
of bytes in the internal representation of the type. But for a
3566
variable-length type, <structfield>typlen</structfield> is negative.
3567
-1 indicates a <quote>varlena</> type (one that has a length word),
3568
-2 indicates a null-terminated C string.
3573
<entry><structfield>typbyval</structfield></entry>
3574
<entry><type>bool</type></entry>
3577
<structfield>typbyval</structfield> determines whether internal
3578
routines pass a value of this type by value or by reference.
3579
<structfield>typbyval</structfield> had better be false if
3580
<structfield>typlen</structfield> is not 1, 2, or 4 (or 8 on machines
3581
where Datum is 8 bytes).
3582
Variable-length types are always passed by reference. Note that
3583
<structfield>typbyval</structfield> can be false even if the
3584
length would allow pass-by-value; this is currently true for
3585
type <type>float4</type>, for example.
3590
<entry><structfield>typtype</structfield></entry>
3591
<entry><type>char</type></entry>
3594
<structfield>typtype</structfield> is <literal>b</literal> for
3595
a base type, <literal>c</literal> for a composite type (e.g., a
3596
table's row type), <literal>d</literal> for a domain, or
3597
<literal>p</literal> for a pseudo-type. See also
3598
<structfield>typrelid</structfield> and
3599
<structfield>typbasetype</structfield>.
3604
<entry><structfield>typisdefined</structfield></entry>
3605
<entry><type>bool</type></entry>
3608
True if the type is defined, false if this is a placeholder
3609
entry for a not-yet-defined type. When
3610
<structfield>typisdefined</structfield> is false, nothing
3611
except the type name, namespace, and OID can be relied on.
3616
<entry><structfield>typdelim</structfield></entry>
3617
<entry><type>char</type></entry>
3619
<entry>Character that separates two values of this type when parsing
3620
array input. Note that the delimiter is associated with the array
3621
element data type, not the array data type.</entry>
3625
<entry><structfield>typrelid</structfield></entry>
3626
<entry><type>oid</type></entry>
3627
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
3629
If this is a composite type (see
3630
<structfield>typtype</structfield>), then this column points to
3631
the <structname>pg_class</structname> entry that defines the
3632
corresponding table. (For a free-standing composite type, the
3633
<structname>pg_class</structname> entry doesn't really represent
3634
a table, but it is needed anyway for the type's
3635
<structname>pg_attribute</structname> entries to link to.)
3636
Zero for non-composite types.
3641
<entry><structfield>typelem</structfield></entry>
3642
<entry><type>oid</type></entry>
3643
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
3645
If <structfield>typelem</structfield> is not 0 then it
3646
identifies another row in <structname>pg_type</structname>.
3647
The current type can then be subscripted like an array yielding
3648
values of type <structfield>typelem</structfield>. A
3649
<quote>true</quote> array type is variable length
3650
(<structfield>typlen</structfield> = -1),
3651
but some fixed-length (<structfield>typlen</structfield> > 0) types
3652
also have nonzero <structfield>typelem</structfield>, for example
3653
<type>name</type> and <type>oidvector</type>.
3654
If a fixed-length type has a <structfield>typelem</structfield> then
3655
its internal representation must be some number of values of the
3656
<structfield>typelem</structfield> data type with no other data.
3657
Variable-length array types have a header defined by the array
3663
<entry><structfield>typinput</structfield></entry>
3664
<entry><type>regproc</type></entry>
3665
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
3666
<entry>Input conversion function (text format)</entry>
3670
<entry><structfield>typoutput</structfield></entry>
3671
<entry><type>regproc</type></entry>
3672
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
3673
<entry>Output conversion function (text format)</entry>
3677
<entry><structfield>typreceive</structfield></entry>
3678
<entry><type>regproc</type></entry>
3679
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
3680
<entry>Input conversion function (binary format), or 0 if none</entry>
3684
<entry><structfield>typsend</structfield></entry>
3685
<entry><type>regproc</type></entry>
3686
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
3687
<entry>Output conversion function (binary format), or 0 if none</entry>
3691
<entry><structfield>typanalyze</structfield></entry>
3692
<entry><type>regproc</type></entry>
3693
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
3694
<entry>Custom ANALYZE function, or 0 to use the standard function</entry>
3698
<entry><structfield>typalign</structfield></entry>
3699
<entry><type>char</type></entry>
3703
<structfield>typalign</structfield> is the alignment required
3704
when storing a value of this type. It applies to storage on
3705
disk as well as most representations of the value inside
3706
<productname>PostgreSQL</>.
3707
When multiple values are stored consecutively, such
3708
as in the representation of a complete row on disk, padding is
3709
inserted before a datum of this type so that it begins on the
3710
specified boundary. The alignment reference is the beginning
3711
of the first datum in the sequence.
3713
Possible values are:
3716
<para><literal>c</> = <type>char</type> alignment, i.e., no alignment needed.</para>
3719
<para><literal>s</> = <type>short</type> alignment (2 bytes on most machines).</para>
3722
<para><literal>i</> = <type>int</type> alignment (4 bytes on most machines).</para>
3725
<para><literal>d</> = <type>double</type> alignment (8 bytes on many machines, but by no means all).</para>
3730
For types used in system tables, it is critical that the size
3731
and alignment defined in <structname>pg_type</structname>
3732
agree with the way that the compiler will lay out the column in
3733
a structure representing a table row.
3739
<entry><structfield>typstorage</structfield></entry>
3740
<entry><type>char</type></entry>
3743
<structfield>typstorage</structfield> tells for varlena
3744
types (those with <structfield>typlen</structfield> = -1) if
3745
the type is prepared for toasting and what the default strategy
3746
for attributes of this type should be.
3750
<para><literal>p</>: Value must always be stored plain.</para>
3754
<literal>e</>: Value can be stored in a <quote>secondary</quote>
3755
relation (if relation has one, see
3756
<literal>pg_class.reltoastrelid</literal>).
3760
<para><literal>m</>: Value can be stored compressed inline.</para>
3763
<para><literal>x</>: Value can be stored compressed inline or stored in <quote>secondary</quote> storage.</para>
3766
Note that <literal>m</> columns can also be moved out to secondary
3767
storage, but only as a last resort (<literal>e</> and <literal>x</> columns are
3773
<entry><structfield>typnotnull</structfield></entry>
3774
<entry><type>bool</type></entry>
3777
<structfield>typnotnull</structfield> represents a not-null
3778
constraint on a type. Used for domains only.
3783
<entry><structfield>typbasetype</structfield></entry>
3784
<entry><type>oid</type></entry>
3785
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
3787
If this is a domain (see <structfield>typtype</structfield>),
3788
then <structfield>typbasetype</structfield> identifies
3789
the type that this one is based on. Zero if not a domain.
3794
<entry><structfield>typtypmod</structfield></entry>
3795
<entry><type>int4</type></entry>
3798
Domains use <structfield>typtypmod</structfield> to record the <literal>typmod</>
3799
to be applied to their base type (-1 if base type does not use a
3800
<literal>typmod</>). -1 if this type is not a domain.
3805
<entry><structfield>typndims</structfield></entry>
3806
<entry><type>int4</type></entry>
3809
<structfield>typndims</structfield> is the number of array dimensions
3810
for a domain that is an array (that is, <structfield>typbasetype</> is an array type;
3811
the domain's <structfield>typelem</> will match the base type's <structfield>typelem</structfield>).
3812
Zero for types other than array domains.
3817
<entry><structfield>typdefaultbin</structfield></entry>
3818
<entry><type>text</type></entry>
3821
If <structfield>typdefaultbin</> is not null, it is the <function>nodeToString()</function>
3822
representation of a default expression for the type. This is
3823
only used for domains.
3828
<entry><structfield>typdefault</structfield></entry>
3829
<entry><type>text</type></entry>
3832
<structfield>typdefault</> is null if the type has no associated
3833
default value. If <structfield>typdefaultbin</> is not null,
3834
<structfield>typdefault</> must contain a human-readable version of the
3835
default expression represented by <structfield>typdefaultbin</>. If
3836
<structfield>typdefaultbin</> is null and <structfield>typdefault</> is
3837
not, then <structfield>typdefault</> is the external representation of
3838
the type's default value, which may be fed to the type's input
3839
converter to produce a constant.
3847
<sect1 id="views-overview">
3848
<title>System Views</title>
3851
In addition to the system catalogs, <productname>PostgreSQL</productname>
3852
provides a number of built-in views. Some system views provide convenient
3853
access to some commonly used queries on the system catalogs. Other views
3854
provide access to internal server state.
3858
The information schema (<xref linkend="information-schema">) provides
3859
an alternative set of views which overlap the functionality of the system
3860
views. Since the information schema is SQL-standard whereas the views
3861
described here are <productname>PostgreSQL</productname>-specific,
3862
it's usually better to use the information schema if it provides all
3863
the information you need.
3867
<xref linkend="view-table"> lists the system views described here.
3868
More detailed documentation of each view follows below.
3869
There are some additional views that provide access to the results of
3870
the statistics collector; they are described in <xref
3871
linkend="monitoring-stats-views-table">.
3875
Except where noted, all the views described here are read-only.
3878
<table id="view-table">
3879
<title>System Views</title>
3884
<entry>View Name</entry>
3885
<entry>Purpose</entry>
3891
<entry><link linkend="view-pg-indexes"><structname>pg_indexes</structname></link></entry>
3892
<entry>indexes</entry>
3896
<entry><link linkend="view-pg-locks"><structname>pg_locks</structname></link></entry>
3897
<entry>currently held locks</entry>
3901
<entry><link linkend="view-pg-rules"><structname>pg_rules</structname></link></entry>
3902
<entry>rules</entry>
3906
<entry><link linkend="view-pg-settings"><structname>pg_settings</structname></link></entry>
3907
<entry>parameter settings</entry>
3911
<entry><link linkend="view-pg-stats"><structname>pg_stats</structname></link></entry>
3912
<entry>planner statistics</entry>
3916
<entry><link linkend="view-pg-tables"><structname>pg_tables</structname></link></entry>
3917
<entry>tables</entry>
3921
<entry><link linkend="view-pg-user"><structname>pg_user</structname></link></entry>
3922
<entry>database users</entry>
3926
<entry><link linkend="view-pg-views"><structname>pg_views</structname></link></entry>
3927
<entry>views</entry>
3935
<sect1 id="view-pg-indexes">
3936
<title><structname>pg_indexes</structname></title>
3938
<indexterm zone="view-pg-indexes">
3939
<primary>pg_indexes</primary>
3943
The view <structname>pg_indexes</structname> provides access to
3944
useful information about each index in the database.
3948
<title><structname>pg_indexes</> Columns</title>
3955
<entry>References</entry>
3956
<entry>Description</entry>
3961
<entry><structfield>schemaname</structfield></entry>
3962
<entry><type>name</type></entry>
3963
<entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.nspname</literal></entry>
3964
<entry>name of schema containing table and index</entry>
3967
<entry><structfield>tablename</structfield></entry>
3968
<entry><type>name</type></entry>
3969
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relname</literal></entry>
3970
<entry>name of table the index is for</entry>
3973
<entry><structfield>indexname</structfield></entry>
3974
<entry><type>name</type></entry>
3975
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relname</literal></entry>
3976
<entry>name of index</entry>
3979
<entry><structfield>tablespace</structfield></entry>
3980
<entry><type>name</type></entry>
3981
<entry><literal><link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.spcname</literal></entry>
3982
<entry>name of tablespace containing index (NULL if default for database)</entry>
3985
<entry><structfield>indexdef</structfield></entry>
3986
<entry><type>text</type></entry>
3988
<entry>index definition (a reconstructed creation command)</entry>
3996
<sect1 id="view-pg-locks">
3997
<title><structname>pg_locks</structname></title>
3999
<indexterm zone="view-pg-locks">
4000
<primary>pg_locks</primary>
4004
The view <structname>pg_locks</structname> provides access to
4005
information about the locks held by open transactions within the
4006
database server. See <xref linkend="mvcc"> for more discussion
4011
<structname>pg_locks</structname> contains one row per active lockable
4012
object, requested lock mode, and relevant transaction. Thus, the same
4014
appear many times, if multiple transactions are holding or waiting
4015
for locks on it. However, an object that currently has no locks on it
4016
will not appear at all. A lockable object is either a relation (e.g., a
4017
table) or a transaction ID.
4021
Note that this view includes only table-level
4022
locks, not row-level ones. If a transaction is waiting for a
4023
row-level lock, it will appear in the view as waiting for the
4024
transaction ID of the current holder of that row lock.
4028
<title><structname>pg_locks</> Columns</title>
4035
<entry>References</entry>
4036
<entry>Description</entry>
4041
<entry><structfield>relation</structfield></entry>
4042
<entry><type>oid</type></entry>
4043
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
4045
OID of the locked relation, or NULL if the lockable object
4050
<entry><structfield>database</structfield></entry>
4051
<entry><type>oid</type></entry>
4052
<entry><literal><link linkend="catalog-pg-database"><structname>pg_database</structname></link>.oid</literal></entry>
4054
OID of the database in which the locked relation exists, or
4055
zero if the locked relation is a globally-shared table, or
4056
NULL if the lockable object is a transaction ID
4060
<entry><structfield>transaction</structfield></entry>
4061
<entry><type>xid</type></entry>
4064
ID of a transaction, or NULL if the lockable object is a relation
4068
<entry><structfield>pid</structfield></entry>
4069
<entry><type>integer</type></entry>
4071
<entry>process ID of a server process holding or awaiting this
4075
<entry><structfield>mode</structfield></entry>
4076
<entry><type>text</type></entry>
4078
<entry>name of the lock mode held or desired by this process (see <xref
4079
linkend="locking-tables">)</entry>
4082
<entry><structfield>granted</structfield></entry>
4083
<entry><type>boolean</type></entry>
4085
<entry>true if lock is held, false if lock is awaited</entry>
4092
<structfield>granted</structfield> is true in a row representing a lock
4093
held by the indicated session. False indicates that this session is
4094
currently waiting to acquire this lock, which implies that some other
4095
session is holding a conflicting lock mode on the same lockable object.
4096
The waiting session will sleep until the other lock is released (or a
4097
deadlock situation is detected). A single session can be waiting to acquire
4098
at most one lock at a time.
4102
Every transaction holds an exclusive lock on its transaction ID for its
4103
entire duration. If one transaction finds it necessary to wait specifically
4104
for another transaction, it does so by attempting to acquire share lock on
4105
the other transaction ID. That will succeed only when the other transaction
4106
terminates and releases its locks.
4110
When the <structname>pg_locks</structname> view is accessed, the
4111
internal lock manager data structures are momentarily locked, and
4112
a copy is made for the view to display. This ensures that the
4113
view produces a consistent set of results, while not blocking
4114
normal lock manager operations longer than necessary. Nonetheless
4115
there could be some impact on database performance if this view is
4120
<structname>pg_locks</structname> provides a global view of all locks
4121
in the database cluster, not only those relevant to the current database.
4122
Although its <structfield>relation</structfield> column can be joined
4123
against <structname>pg_class</>.<structfield>oid</> to identify locked
4124
relations, this will only work correctly for relations in the current
4125
database (those for which the <structfield>database</structfield> column
4126
is either the current database's OID or zero).
4130
If you have enabled the statistics collector, the
4131
<structfield>pid</structfield> column can be joined to the
4132
<structfield>procpid</structfield> column of the
4133
<structname>pg_stat_activity</structname> view to get more
4134
information on the session holding or waiting to hold the lock.
4139
<sect1 id="view-pg-rules">
4140
<title><structname>pg_rules</structname></title>
4142
<indexterm zone="view-pg-rules">
4143
<primary>pg_rules</primary>
4147
The view <structname>pg_rules</structname> provides access to
4148
useful information about query rewrite rules.
4152
<title><structname>pg_rules</> Columns</title>
4159
<entry>References</entry>
4160
<entry>Description</entry>
4165
<entry><structfield>schemaname</structfield></entry>
4166
<entry><type>name</type></entry>
4167
<entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.nspname</literal></entry>
4168
<entry>name of schema containing table</entry>
4171
<entry><structfield>tablename</structfield></entry>
4172
<entry><type>name</type></entry>
4173
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relname</literal></entry>
4174
<entry>name of table the rule is for</entry>
4177
<entry><structfield>rulename</structfield></entry>
4178
<entry><type>name</type></entry>
4179
<entry><literal><link linkend="catalog-pg-rewrite"><structname>pg_rewrite</structname></link>.rulename</literal></entry>
4180
<entry>name of rule</entry>
4183
<entry><structfield>definition</structfield></entry>
4184
<entry><type>text</type></entry>
4186
<entry>rule definition (a reconstructed creation command)</entry>
4193
The <structname>pg_rules</structname> view excludes the ON SELECT rules of
4194
views; those can be seen in <structname>pg_views</structname>.
4199
<sect1 id="view-pg-settings">
4200
<title><structname>pg_settings</structname></title>
4202
<indexterm zone="view-pg-settings">
4203
<primary>pg_settings</primary>
4207
The view <structname>pg_settings</structname> provides access to
4208
run-time parameters of the server. It is essentially an alternative
4209
interface to the <command>SHOW</> and <command>SET</> commands.
4210
It also provides access to some facts about each parameter that are
4211
not directly available from <command>SHOW</>, such as minimum and
4216
<title><structname>pg_settings</> Columns</title>
4223
<entry>References</entry>
4224
<entry>Description</entry>
4229
<entry><structfield>name</structfield></entry>
4230
<entry><type>text</type></entry>
4232
<entry>run-time configuration parameter name</entry>
4235
<entry><structfield>setting</structfield></entry>
4236
<entry><type>text</type></entry>
4238
<entry>current value of the parameter</entry>
4241
<entry><structfield>category</structfield></entry>
4242
<entry><type>text</type></entry>
4244
<entry>logical group of the parameter</entry>
4247
<entry><structfield>short_desc</structfield></entry>
4248
<entry><type>text</type></entry>
4250
<entry>a brief description of the parameter</entry>
4253
<entry><structfield>extra_desc</structfield></entry>
4254
<entry><type>text</type></entry>
4256
<entry>additional, more detailed, information about the parameter</entry>
4259
<entry><structfield>context</structfield></entry>
4260
<entry><type>text</type></entry>
4262
<entry>context required to set the parameter's value</entry>
4265
<entry><structfield>vartype</structfield></entry>
4266
<entry><type>text</type></entry>
4268
<entry>parameter type (<literal>bool</>, <literal>integer</>,
4269
<literal>real</>, or <literal>string</>)
4273
<entry><structfield>source</structfield></entry>
4274
<entry><type>text</type></entry>
4276
<entry>source of the current parameter value</entry>
4279
<entry><structfield>min_val</structfield></entry>
4280
<entry><type>text</type></entry>
4282
<entry>minimum allowed value of the parameter (NULL for nonnumeric
4286
<entry><structfield>max_val</structfield></entry>
4287
<entry><type>text</type></entry>
4289
<entry>maximum allowed value of the parameter (NULL for nonnumeric
4297
The <structname>pg_settings</structname> view cannot be inserted into or
4298
deleted from, but it can be updated. An <command>UPDATE</command> applied
4299
to a row of <structname>pg_settings</structname> is equivalent to executing
4300
the <xref linkend="SQL-SET" endterm="SQL-SET-title"> command on that named
4301
parameter. The change only affects the value used by the current
4302
session. If an <command>UPDATE</command> is issued within a transaction
4303
that is later aborted, the effects of the <command>UPDATE</command> command
4304
disappear when the transaction is rolled back. Once the surrounding
4305
transaction is committed, the effects will persist until the end of the
4306
session, unless overridden by another <command>UPDATE</command> or
4307
<command>SET</command>.
4312
<sect1 id="view-pg-stats">
4313
<title><structname>pg_stats</structname></title>
4315
<indexterm zone="view-pg-stats">
4316
<primary>pg_stats</primary>
4320
The view <structname>pg_stats</structname> provides access to
4321
the information stored in the <link
4322
linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>
4323
catalog. This view allows access only to rows of
4324
<structname>pg_statistic</structname> that correspond to tables the
4325
user has permission to read, and therefore it is safe to allow public
4326
read access to this view.
4330
<structname>pg_stats</structname> is also designed to present the
4331
information in a more readable format than the underlying catalog
4332
— at the cost that its schema must be extended whenever new slot types
4333
are defined for <structname>pg_statistic</structname>.
4337
<title><structname>pg_stats</> Columns</title>
4344
<entry>References</entry>
4345
<entry>Description</entry>
4350
<entry><structfield>schemaname</structfield></entry>
4351
<entry><type>name</type></entry>
4352
<entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.nspname</literal></entry>
4353
<entry>name of schema containing table</entry>
4357
<entry><structfield>tablename</structfield></entry>
4358
<entry><type>name</type></entry>
4359
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relname</literal></entry>
4360
<entry>name of table</entry>
4364
<entry><structfield>attname</structfield></entry>
4365
<entry><type>name</type></entry>
4366
<entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attname</literal></entry>
4367
<entry>name of the column described by this row</entry>
4371
<entry><structfield>null_frac</structfield></entry>
4372
<entry><type>real</type></entry>
4374
<entry>fraction of column entries that are null</entry>
4378
<entry><structfield>avg_width</structfield></entry>
4379
<entry><type>integer</type></entry>
4381
<entry>average width in bytes of column's entries</entry>
4385
<entry><structfield>n_distinct</structfield></entry>
4386
<entry><type>real</type></entry>
4388
<entry>If greater than zero, the estimated number of distinct values
4389
in the column. If less than zero, the negative of the number of
4390
distinct values divided by the number of rows. (The negated form
4391
is used when <command>ANALYZE</> believes that the number of distinct
4393
is likely to increase as the table grows; the positive form is used
4394
when the column seems to have a fixed number of possible values.)
4395
For example, -1 indicates a unique column in which the number of
4396
distinct values is the same as the number of rows.
4401
<entry><structfield>most_common_vals</structfield></entry>
4402
<entry><type>anyarray</type></entry>
4404
<entry>A list of the most common values in the column. (NULL if
4405
no values seem to be more common than any others.)</entry>
4409
<entry><structfield>most_common_freqs</structfield></entry>
4410
<entry><type>real[]</type></entry>
4412
<entry>A list of the frequencies of the most common values,
4413
i.e., number of occurrences of each divided by total number of rows.
4414
(NULL when <structfield>most_common_vals</structfield> is.)
4419
<entry><structfield>histogram_bounds</structfield></entry>
4420
<entry><type>anyarray</type></entry>
4422
<entry>A list of values that divide the column's values into
4423
groups of approximately equal population. The values in
4424
<structfield>most_common_vals</>, if present, are omitted from this
4425
histogram calculation. (This column is NULL if the column data type
4426
does not have a <literal><</> operator or if the
4427
<structfield>most_common_vals</> list accounts for the entire
4433
<entry><structfield>correlation</structfield></entry>
4434
<entry><type>real</type></entry>
4436
<entry>Statistical correlation between physical row ordering and
4437
logical ordering of the column values. This ranges from -1 to +1.
4438
When the value is near -1 or +1, an index scan on the column will
4439
be estimated to be cheaper than when it is near zero, due to reduction
4440
of random access to the disk. (This column is NULL if the column data
4441
type does not have a <literal><</> operator.)
4449
The maximum number of entries in the <structfield>most_common_vals</>
4450
and <structfield>histogram_bounds</> arrays can be set on a
4451
column-by-column basis using the <command>ALTER TABLE SET STATISTICS</>
4452
command, or globally by setting the
4453
<xref linkend="guc-default-statistics-target"> runtime parameter.
4458
<sect1 id="view-pg-tables">
4459
<title><structname>pg_tables</structname></title>
4461
<indexterm zone="view-pg-tables">
4462
<primary>pg_tables</primary>
4466
The view <structname>pg_tables</structname> provides access to
4467
useful information about each table in the database.
4471
<title><structname>pg_tables</> Columns</title>
4478
<entry>References</entry>
4479
<entry>Description</entry>
4484
<entry><structfield>schemaname</structfield></entry>
4485
<entry><type>name</type></entry>
4486
<entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.nspname</literal></entry>
4487
<entry>name of schema containing table</entry>
4490
<entry><structfield>tablename</structfield></entry>
4491
<entry><type>name</type></entry>
4492
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relname</literal></entry>
4493
<entry>name of table</entry>
4496
<entry><structfield>tableowner</structfield></entry>
4497
<entry><type>name</type></entry>
4498
<entry><literal><link linkend="catalog-pg-shadow"><structname>pg_shadow</structname></link>.usename</literal></entry>
4499
<entry>name of table's owner</entry>
4502
<entry><structfield>tablespace</structfield></entry>
4503
<entry><type>name</type></entry>
4504
<entry><literal><link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.spcname</literal></entry>
4505
<entry>name of tablespace containing table (NULL if default for database)</entry>
4508
<entry><structfield>hasindexes</structfield></entry>
4509
<entry><type>boolean</type></entry>
4510
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relhasindex</literal></entry>
4511
<entry>true if table has (or recently had) any indexes</entry>
4514
<entry><structfield>hasrules</structfield></entry>
4515
<entry><type>boolean</type></entry>
4516
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relhasrules</literal></entry>
4517
<entry>true if table has rules</entry>
4520
<entry><structfield>hastriggers</structfield></entry>
4521
<entry><type>boolean</type></entry>
4522
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.reltriggers</literal></entry>
4523
<entry>true if table has triggers</entry>
4531
<sect1 id="view-pg-user">
4532
<title><structname>pg_user</structname></title>
4534
<indexterm zone="view-pg-user">
4535
<primary>pg_user</primary>
4539
The view <structname>pg_user</structname> provides access to
4540
information about database users. This is simply a publicly
4542
<link linkend="catalog-pg-shadow"><structname>pg_shadow</structname></link>
4543
that blanks out the password field.
4547
<title><structname>pg_user</> Columns</title>
4554
<entry>References</entry>
4555
<entry>Description</entry>
4560
<entry><structfield>usename</structfield></entry>
4561
<entry><type>name</type></entry>
4563
<entry>User name</entry>
4567
<entry><structfield>usesysid</structfield></entry>
4568
<entry><type>int4</type></entry>
4570
<entry>User ID (arbitrary number used to reference this user)</entry>
4574
<entry><structfield>usecreatedb</structfield></entry>
4575
<entry><type>bool</type></entry>
4577
<entry>User may create databases</entry>
4581
<entry><structfield>usesuper</structfield></entry>
4582
<entry><type>bool</type></entry>
4584
<entry>User is a superuser</entry>
4588
<entry><structfield>usecatupd</structfield></entry>
4589
<entry><type>bool</type></entry>
4592
User may update system catalogs. (Even a superuser may not do
4593
this unless this column is true.)
4598
<entry><structfield>passwd</structfield></entry>
4599
<entry><type>text</type></entry>
4601
<entry>Not the password (always reads as <literal>********</>)</entry>
4605
<entry><structfield>valuntil</structfield></entry>
4606
<entry><type>abstime</type></entry>
4608
<entry>Password expiry time (only used for password authentication)</entry>
4612
<entry><structfield>useconfig</structfield></entry>
4613
<entry><type>text[]</type></entry>
4615
<entry>Session defaults for run-time configuration variables</entry>
4623
<sect1 id="view-pg-views">
4624
<title><structname>pg_views</structname></title>
4626
<indexterm zone="view-pg-views">
4627
<primary>pg_views</primary>
4631
The view <structname>pg_views</structname> provides access to
4632
useful information about each view in the database.
4636
<title><structname>pg_views</> Columns</title>
4643
<entry>References</entry>
4644
<entry>Description</entry>
4649
<entry><structfield>schemaname</structfield></entry>
4650
<entry><type>name</type></entry>
4651
<entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.nspname</literal></entry>
4652
<entry>name of schema containing view</entry>
4655
<entry><structfield>viewname</structfield></entry>
4656
<entry><type>name</type></entry>
4657
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relname</literal></entry>
4658
<entry>name of view</entry>
4661
<entry><structfield>viewowner</structfield></entry>
4662
<entry><type>name</type></entry>
4663
<entry><literal><link linkend="catalog-pg-shadow"><structname>pg_shadow</structname></link>.usename</literal></entry>
4664
<entry>name of view's owner</entry>
4667
<entry><structfield>definition</structfield></entry>
4668
<entry><type>text</type></entry>
4670
<entry>view definition (a reconstructed SELECT query)</entry>
4680
<!-- Keep this comment at the end of the file
4685
sgml-minimize-attributes:nil
4686
sgml-always-quote-attributes:t
4688
sgml-indent-tabs-mode:nil
4690
sgml-parent-document:nil
4691
sgml-default-dtd-file:"./reference.ced"
4692
sgml-exposed-tags:nil
4693
sgml-local-catalogs:("/usr/share/sgml/catalog")
4694
sgml-local-ecat-files:nil
added
removed
doc/src/sgml/catalogs.sgml
