1 <!-- doc/src/sgml/information_schema.sgml -->
3 <chapter id="information-schema">
4 <title>The Information Schema</title>
6 <indexterm zone="information-schema">
7 <primary>information schema</primary>
11 The information schema consists of a set of views that contain
12 information about the objects defined in the current database. The
13 information schema is defined in the SQL standard and can therefore
14 be expected to be portable and remain stable — unlike the system
15 catalogs, which are specific to
16 <productname>PostgreSQL</productname> and are modelled after
17 implementation concerns. The information schema views do not,
18 however, contain information about
19 <productname>PostgreSQL</productname>-specific features; to inquire
20 about those you need to query the system catalogs or other
21 <productname>PostgreSQL</productname>-specific views.
26 When querying the database for constraint information, it is possible
27 for a standard-compliant query that expects to return one row to
28 return several. This is because the SQL standard requires constraint
29 names to be unique within a schema, but
30 <productname>PostgreSQL</productname> does not enforce this
31 restriction. <productname>PostgreSQL</productname>
32 automatically-generated constraint names avoid duplicates in the
33 same schema, but users can specify such duplicate names.
37 This problem can appear when querying information schema views such
38 as <literal>check_constraint_routine_usage</>,
39 <literal>check_constraints</>, <literal>domain_constraints</>, and
40 <literal>referential_constraints</>. Some other views have similar
41 issues but contain the table name to help distinguish duplicate
42 rows, e.g., <literal>constraint_column_usage</>,
43 <literal>constraint_table_usage</>, <literal>table_constraints</>.
48 <sect1 id="infoschema-schema">
49 <title>The Schema</title>
52 The information schema itself is a schema named
53 <literal>information_schema</literal>. This schema automatically
54 exists in all databases. The owner of this schema is the initial
55 database user in the cluster, and that user naturally has all the
56 privileges on this schema, including the ability to drop it (but
57 the space savings achieved by that are minuscule).
61 By default, the information schema is not in the schema search
62 path, so you need to access all objects in it through qualified
63 names. Since the names of some of the objects in the information
64 schema are generic names that might occur in user applications, you
65 should be careful if you want to put the information schema in the
70 <sect1 id="infoschema-datatypes">
71 <title>Data Types</title>
74 The columns of the information schema views use special data types
75 that are defined in the information schema. These are defined as
76 simple domains over ordinary built-in types. You should not use
77 these types for work outside the information schema, but your
78 applications must be prepared for them if they select from the
87 <term><type>cardinal_number</type></term>
90 A nonnegative integer.
96 <term><type>character_data</type></term>
99 A character string (without specific maximum length).
105 <term><type>sql_identifier</type></term>
108 A character string. This type is used for SQL identifiers, the
109 type <type>character_data</type> is used for any other kind of
116 <term><type>time_stamp</type></term>
119 A domain over the type <type>timestamp with time zone</type>
125 <term><type>yes_or_no</type></term>
128 A character string domain that contains
129 either <literal>YES</literal> or <literal>NO</literal>. This
130 is used to represent Boolean (true/false) data in the
131 information schema. (The information schema was invented
132 before the type <type>boolean</type> was added to the SQL
133 standard, so this convention is necessary to keep the
134 information schema backward compatible.)
140 Every column in the information schema has one of these five types.
144 <sect1 id="infoschema-information-schema-catalog-name">
145 <title><literal>information_schema_catalog_name</literal></title>
148 <literal>information_schema_catalog_name</literal> is a table that
149 always contains one row and one column containing the name of the
150 current database (current catalog, in SQL terminology).
154 <title><literal>information_schema_catalog_name</literal> Columns</title>
160 <entry>Data Type</entry>
161 <entry>Description</entry>
167 <entry><literal>catalog_name</literal></entry>
168 <entry><type>sql_identifier</type></entry>
169 <entry>Name of the database that contains this information schema</entry>
176 <sect1 id="infoschema-administrable-role-authorizations">
177 <title><literal>administrable_role_authorizations</literal></title>
180 The view <literal>administrable_role_authorizations</literal>
181 identifies all roles that the current user has the admin option
186 <title><literal>administrable_role_authorizations</literal> Columns</title>
192 <entry>Data Type</entry>
193 <entry>Description</entry>
199 <entry><literal>grantee</literal></entry>
200 <entry><type>sql_identifier</type></entry>
202 Name of the role to which this role membership was granted (can
203 be the current user, or a different role in case of nested role
209 <entry><literal>role_name</literal></entry>
210 <entry><type>sql_identifier</type></entry>
211 <entry>Name of a role</entry>
215 <entry><literal>is_grantable</literal></entry>
216 <entry><type>yes_or_no</type></entry>
217 <entry>Always <literal>YES</literal></entry>
224 <sect1 id="infoschema-applicable-roles">
225 <title><literal>applicable_roles</literal></title>
228 The view <literal>applicable_roles</literal> identifies all roles
229 whose privileges the current user can use. This means there is
230 some chain of role grants from the current user to the role in
231 question. The current user itself is also an applicable role. The
232 set of applicable roles is generally used for permission checking.
233 <indexterm><primary>applicable role</primary></indexterm>
234 <indexterm><primary>role</primary><secondary>applicable</secondary></indexterm>
238 <title><literal>applicable_roles</literal> Columns</title>
244 <entry>Data Type</entry>
245 <entry>Description</entry>
251 <entry><literal>grantee</literal></entry>
252 <entry><type>sql_identifier</type></entry>
254 Name of the role to which this role membership was granted (can
255 be the current user, or a different role in case of nested role
261 <entry><literal>role_name</literal></entry>
262 <entry><type>sql_identifier</type></entry>
263 <entry>Name of a role</entry>
267 <entry><literal>is_grantable</literal></entry>
268 <entry><type>yes_or_no</type></entry>
270 <literal>YES</literal> if the grantee has the admin option on
271 the role, <literal>NO</literal> if not
279 <sect1 id="infoschema-attributes">
280 <title><literal>attributes</literal></title>
283 The view <literal>attributes</literal> contains information about
284 the attributes of composite data types defined in the database.
285 (Note that the view does not give information about table columns,
286 which are sometimes called attributes in PostgreSQL contexts.)
290 <title><literal>attributes</literal> Columns</title>
296 <entry>Data Type</entry>
297 <entry>Description</entry>
303 <entry><literal>udt_catalog</literal></entry>
304 <entry><type>sql_identifier</type></entry>
305 <entry>Name of the database containing the data type (always the current database)</entry>
309 <entry><literal>udt_schema</literal></entry>
310 <entry><type>sql_identifier</type></entry>
311 <entry>Name of the schema containing the data type</entry>
315 <entry><literal>udt_name</literal></entry>
316 <entry><type>sql_identifier</type></entry>
317 <entry>Name of the data type</entry>
321 <entry><literal>attribute_name</literal></entry>
322 <entry><type>sql_identifier</type></entry>
323 <entry>Name of the attribute</entry>
327 <entry><literal>ordinal_position</literal></entry>
328 <entry><type>cardinal_number</type></entry>
329 <entry>Ordinal position of the attribute within the data type (count starts at 1)</entry>
333 <entry><literal>attribute_default</literal></entry>
334 <entry><type>character_data</type></entry>
335 <entry>Default expression of the attribute</entry>
339 <entry><literal>is_nullable</literal></entry>
340 <entry><type>yes_or_no</type></entry>
342 <literal>YES</literal> if the attribute is possibly nullable,
343 <literal>NO</literal> if it is known not nullable.
348 <entry><literal>data_type</literal></entry>
349 <entry><type>character_data</type></entry>
351 Data type of the attribute, if it is a built-in type, or
352 <literal>ARRAY</literal> if it is some array (in that case, see
353 the view <literal>element_types</literal>), else
354 <literal>USER-DEFINED</literal> (in that case, the type is
355 identified in <literal>attribute_udt_name</literal> and
361 <entry><literal>character_maximum_length</literal></entry>
362 <entry><type>cardinal_number</type></entry>
364 If <literal>data_type</literal> identifies a character or bit
365 string type, the declared maximum length; null for all other
366 data types or if no maximum length was declared.
371 <entry><literal>character_octet_length</literal></entry>
372 <entry><type>cardinal_number</type></entry>
374 If <literal>data_type</literal> identifies a character type,
375 the maximum possible length in octets (bytes) of a datum; null
376 for all other data types. The maximum octet length depends on
377 the declared character maximum length (see above) and the
383 <entry><literal>character_set_catalog</literal></entry>
384 <entry><type>sql_identifier</type></entry>
385 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
389 <entry><literal>character_set_schema</literal></entry>
390 <entry><type>sql_identifier</type></entry>
391 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
395 <entry><literal>character_set_name</literal></entry>
396 <entry><type>sql_identifier</type></entry>
397 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
401 <entry><literal>collation_catalog</literal></entry>
402 <entry><type>sql_identifier</type></entry>
404 Name of the database containing the collation of the attribute
405 (always the current database), null if default or the data type
406 of the attribute is not collatable
411 <entry><literal>collation_schema</literal></entry>
412 <entry><type>sql_identifier</type></entry>
414 Name of the schema containing the collation of the attribute,
415 null if default or the data type of the attribute is not
421 <entry><literal>collation_name</literal></entry>
422 <entry><type>sql_identifier</type></entry>
424 Name of the collation of the attribute, null if default or the
425 data type of the attribute is not collatable
430 <entry><literal>numeric_precision</literal></entry>
431 <entry><type>cardinal_number</type></entry>
433 If <literal>data_type</literal> identifies a numeric type, this
434 column contains the (declared or implicit) precision of the
435 type for this attribute. The precision indicates the number of
436 significant digits. It can be expressed in decimal (base 10)
437 or binary (base 2) terms, as specified in the column
438 <literal>numeric_precision_radix</literal>. For all other data
439 types, this column is null.
444 <entry><literal>numeric_precision_radix</literal></entry>
445 <entry><type>cardinal_number</type></entry>
447 If <literal>data_type</literal> identifies a numeric type, this
448 column indicates in which base the values in the columns
449 <literal>numeric_precision</literal> and
450 <literal>numeric_scale</literal> are expressed. The value is
451 either 2 or 10. For all other data types, this column is null.
456 <entry><literal>numeric_scale</literal></entry>
457 <entry><type>cardinal_number</type></entry>
459 If <literal>data_type</literal> identifies an exact numeric
460 type, this column contains the (declared or implicit) scale of
461 the type for this attribute. The scale indicates the number of
462 significant digits to the right of the decimal point. It can
463 be expressed in decimal (base 10) or binary (base 2) terms, as
464 specified in the column
465 <literal>numeric_precision_radix</literal>. For all other data
466 types, this column is null.
471 <entry><literal>datetime_precision</literal></entry>
472 <entry><type>cardinal_number</type></entry>
474 If <literal>data_type</literal> identifies a date, time,
475 timestamp, or interval type, this column contains the (declared
476 or implicit) fractional seconds precision of the type for this
477 attribute, that is, the number of decimal digits maintained
478 following the decimal point in the seconds value. For all
479 other data types, this column is null.
484 <entry><literal>interval_type</literal></entry>
485 <entry><type>character_data</type></entry>
487 If <literal>data_type</literal> identifies an interval type,
488 this column contains the specification which fields the
489 intervals include for this attribute, e.g., <literal>YEAR TO
490 MONTH</literal>, <literal>DAY TO SECOND</literal>, etc. If no
491 field restrictions were specified (that is, the interval
492 accepts all fields), and for all other data types, this field
498 <entry><literal>interval_precision</literal></entry>
499 <entry><type>cardinal_number</type></entry>
501 Applies to a feature not available
502 in <productname>PostgreSQL</productname>
503 (see <literal>datetime_precision</literal> for the fractional
504 seconds precision of interval type attributes)
509 <entry><literal>attribute_udt_catalog</literal></entry>
510 <entry><type>sql_identifier</type></entry>
512 Name of the database that the attribute data type is defined in
513 (always the current database)
518 <entry><literal>attribute_udt_schema</literal></entry>
519 <entry><type>sql_identifier</type></entry>
521 Name of the schema that the attribute data type is defined in
526 <entry><literal>attribute_udt_name</literal></entry>
527 <entry><type>sql_identifier</type></entry>
529 Name of the attribute data type
534 <entry><literal>scope_catalog</literal></entry>
535 <entry><type>sql_identifier</type></entry>
536 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
540 <entry><literal>scope_schema</literal></entry>
541 <entry><type>sql_identifier</type></entry>
542 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
546 <entry><literal>scope_name</literal></entry>
547 <entry><type>sql_identifier</type></entry>
548 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
552 <entry><literal>maximum_cardinality</literal></entry>
553 <entry><type>cardinal_number</type></entry>
554 <entry>Always null, because arrays always have unlimited maximum cardinality in <productname>PostgreSQL</></entry>
558 <entry><literal>dtd_identifier</literal></entry>
559 <entry><type>sql_identifier</type></entry>
561 An identifier of the data type descriptor of the column, unique
562 among the data type descriptors pertaining to the table. This
563 is mainly useful for joining with other instances of such
564 identifiers. (The specific format of the identifier is not
565 defined and not guaranteed to remain the same in future
571 <entry><literal>is_derived_reference_attribute</literal></entry>
572 <entry><type>yes_or_no</type></entry>
573 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
580 See also under <xref linkend="infoschema-columns">, a similarly
581 structured view, for further information on some of the columns.
585 <sect1 id="infoschema-character-sets">
586 <title><literal>character_sets</literal></title>
589 The view <literal>character_sets</literal> identifies the character
590 sets available in the current database. Since PostgreSQL does not
591 support multiple character sets within one database, this view only
592 shows one, which is the database encoding.
596 Take note of how the following terms are used in the SQL standard:
599 <term>character repertoire</term>
602 An abstract collection of characters, for
603 example <literal>UNICODE</literal>, <literal>UCS</literal>, or
604 <literal>LATIN1</literal>. Not exposed as an SQL object, but
605 visible in this view.
611 <term>character encoding form</term>
614 An encoding of some character repertoire. Most older character
615 repertoires only use one encoding form, and so there are no
616 separate names for them (e.g., <literal>LATIN1</literal> is an
617 encoding form applicable to the <literal>LATIN1</literal>
618 repertoire). But for example Unicode has the encoding forms
619 <literal>UTF8</literal>, <literal>UTF16</literal>, etc. (not
620 all supported by PostgreSQL). Encoding forms are not exposed
621 as an SQL object, but are visible in this view.
627 <term>character set</term>
630 A named SQL object that identifies a character repertoire, a
631 character encoding, and a default collation. A predefined
632 character set would typically have the same name as an encoding
633 form, but users could define other names. For example, the
634 character set <literal>UTF8</literal> would typically identify
635 the character repertoire <literal>UCS</literal>, encoding
636 form <literal>UTF8</literal>, and some default collation.
642 You can think of an <quote>encoding</quote> in PostgreSQL either as
643 a character set or a character encoding form. They will have the
644 same name, and there can only be one in one database.
648 <title><literal>character_sets</literal> Columns</title>
654 <entry>Data Type</entry>
655 <entry>Description</entry>
661 <entry><literal>character_set_catalog</literal></entry>
662 <entry><literal>sql_identifier</literal></entry>
663 <entry>Character sets are currently not implemented as schema objects, so this column is null.</entry>
667 <entry><literal>character_set_schema</literal></entry>
668 <entry><literal>sql_identifier</literal></entry>
669 <entry>Character sets are currently not implemented as schema objects, so this column is null.</entry>
673 <entry><literal>character_set_name</literal></entry>
674 <entry><literal>sql_identifier</literal></entry>
675 <entry>Name of the character set, currently implemented as showing the name of the database encoding</entry>
679 <entry><literal>character_repertoire</literal></entry>
680 <entry><literal>sql_identifier</literal></entry>
681 <entry>Character repertoire, showing <literal>UCS</literal> if the encoding is <literal>UTF8</literal>, else just the encoding name</entry>
685 <entry><literal>form_of_use</literal></entry>
686 <entry><literal>sql_identifier</literal></entry>
687 <entry>Character encoding form, same as the database encoding</entry>
691 <entry><literal>default_collate_catalog</literal></entry>
692 <entry><literal>sql_identifier</literal></entry>
693 <entry>Name of the database containing the default collation (always the current database, if any collation is identified)</entry>
697 <entry><literal>default_collate_schema</literal></entry>
698 <entry><literal>sql_identifier</literal></entry>
699 <entry>Name of the schema containing the default collation</entry>
703 <entry><literal>default_collate_name</literal></entry>
704 <entry><literal>sql_identifier</literal></entry>
706 Name of the default collation. The default collation is
707 identified as the collation that matches
708 the <literal>COLLATE</literal> and <literal>CTYPE</literal>
709 settings of the current database. If there is no such
710 collation, then this column and the associated schema and
711 catalog columns are null.
719 <sect1 id="infoschema-check-constraint-routine-usage">
720 <title><literal>check_constraint_routine_usage</literal></title>
723 The view <literal>check_constraint_routine_usage</literal>
724 identifies routines (functions and procedures) that are used by a
725 check constraint. Only those routines are shown that are owned by
726 a currently enabled role.
730 <title><literal>check_constraint_routine_usage</literal> Columns</title>
736 <entry>Data Type</entry>
737 <entry>Description</entry>
743 <entry><literal>constraint_catalog</literal></entry>
744 <entry><literal>sql_identifier</literal></entry>
745 <entry>Name of the database containing the constraint (always the current database)</entry>
749 <entry><literal>constraint_schema</literal></entry>
750 <entry><literal>sql_identifier</literal></entry>
751 <entry>Name of the schema containing the constraint</entry>
755 <entry><literal>constraint_name</literal></entry>
756 <entry><literal>sql_identifier</literal></entry>
757 <entry>Name of the constraint</entry>
761 <entry><literal>specific_catalog</literal></entry>
762 <entry><literal>sql_identifier</literal></entry>
763 <entry>Name of the database containing the function (always the current database)</entry>
767 <entry><literal>specific_schema</literal></entry>
768 <entry><literal>sql_identifier</literal></entry>
769 <entry>Name of the schema containing the function</entry>
773 <entry><literal>specific_name</literal></entry>
774 <entry><literal>sql_identifier</literal></entry>
776 The <quote>specific name</quote> of the function. See <xref
777 linkend="infoschema-routines"> for more information.
785 <sect1 id="infoschema-check-constraints">
786 <title><literal>check_constraints</literal></title>
789 The view <literal>check_constraints</literal> contains all check
790 constraints, either defined on a table or on a domain, that are
791 owned by a currently enabled role. (The owner of the table or
792 domain is the owner of the constraint.)
796 <title><literal>check_constraints</literal> Columns</title>
802 <entry>Data Type</entry>
803 <entry>Description</entry>
809 <entry><literal>constraint_catalog</literal></entry>
810 <entry><literal>sql_identifier</literal></entry>
811 <entry>Name of the database containing the constraint (always the current database)</entry>
815 <entry><literal>constraint_schema</literal></entry>
816 <entry><literal>sql_identifier</literal></entry>
817 <entry>Name of the schema containing the constraint</entry>
821 <entry><literal>constraint_name</literal></entry>
822 <entry><literal>sql_identifier</literal></entry>
823 <entry>Name of the constraint</entry>
827 <entry><literal>check_clause</literal></entry>
828 <entry><literal>character_data</literal></entry>
829 <entry>The check expression of the check constraint</entry>
836 <sect1 id="infoschema-collations">
837 <title><literal>collations</literal></title>
840 The view <literal>collations</literal> contains the collations
841 available in the current database.
845 <title><literal>collations</literal> Columns</title>
851 <entry>Data Type</entry>
852 <entry>Description</entry>
858 <entry><literal>collation_catalog</literal></entry>
859 <entry><literal>sql_identifier</literal></entry>
860 <entry>Name of the database containing the collation (always the current database)</entry>
864 <entry><literal>collation_schema</literal></entry>
865 <entry><literal>sql_identifier</literal></entry>
866 <entry>Name of the schema containing the collation</entry>
870 <entry><literal>collation_name</literal></entry>
871 <entry><literal>sql_identifier</literal></entry>
872 <entry>Name of the default collation</entry>
876 <entry><literal>pad_attribute</literal></entry>
877 <entry><literal>character_data</literal></entry>
879 Always <literal>NO PAD</literal> (The alternative <literal>PAD
880 SPACE</literal> is not supported by PostgreSQL.)
888 <sect1 id="infoschema-collation-character-set-applicab"> <!-- max 44 characters -->
889 <title><literal>collation_character_set_applicability</literal></title>
892 The view <literal>collation_character_set_applicability</literal>
893 identifies which character set the available collations are
894 applicable to. In PostgreSQL, there is only one character set per
895 database (see explanation
896 in <xref linkend="infoschema-character-sets">), so this view does
897 not provide much useful information.
901 <title><literal>collation_character_set_applicability</literal> Columns</title>
907 <entry>Data Type</entry>
908 <entry>Description</entry>
914 <entry><literal>collation_catalog</literal></entry>
915 <entry><literal>sql_identifier</literal></entry>
916 <entry>Name of the database containing the collation (always the current database)</entry>
920 <entry><literal>collation_schema</literal></entry>
921 <entry><literal>sql_identifier</literal></entry>
922 <entry>Name of the schema containing the collation</entry>
926 <entry><literal>collation_name</literal></entry>
927 <entry><literal>sql_identifier</literal></entry>
928 <entry>Name of the default collation</entry>
932 <entry><literal>character_set_catalog</literal></entry>
933 <entry><literal>sql_identifier</literal></entry>
934 <entry>Character sets are currently not implemented as schema objects, so this column is null</entry>
938 <entry><literal>character_set_schema</literal></entry>
939 <entry><literal>sql_identifier</literal></entry>
940 <entry>Character sets are currently not implemented as schema objects, so this column is null</entry>
944 <entry><literal>character_set_name</literal></entry>
945 <entry><literal>sql_identifier</literal></entry>
946 <entry>Name of the character set</entry>
953 <sect1 id="infoschema-column-domain-usage">
954 <title><literal>column_domain_usage</literal></title>
957 The view <literal>column_domain_usage</literal> identifies all
958 columns (of a table or a view) that make use of some domain defined
959 in the current database and owned by a currently enabled role.
963 <title><literal>column_domain_usage</literal> Columns</title>
969 <entry>Data Type</entry>
970 <entry>Description</entry>
976 <entry><literal>domain_catalog</literal></entry>
977 <entry><type>sql_identifier</type></entry>
978 <entry>Name of the database containing the domain (always the current database)</entry>
982 <entry><literal>domain_schema</literal></entry>
983 <entry><type>sql_identifier</type></entry>
984 <entry>Name of the schema containing the domain</entry>
988 <entry><literal>domain_name</literal></entry>
989 <entry><type>sql_identifier</type></entry>
990 <entry>Name of the domain</entry>
994 <entry><literal>table_catalog</literal></entry>
995 <entry><type>sql_identifier</type></entry>
996 <entry>Name of the database containing the table (always the current database)</entry>
1000 <entry><literal>table_schema</literal></entry>
1001 <entry><type>sql_identifier</type></entry>
1002 <entry>Name of the schema containing the table</entry>
1006 <entry><literal>table_name</literal></entry>
1007 <entry><type>sql_identifier</type></entry>
1008 <entry>Name of the table</entry>
1012 <entry><literal>column_name</literal></entry>
1013 <entry><type>sql_identifier</type></entry>
1014 <entry>Name of the column</entry>
1021 <sect1 id="infoschema-column-options">
1022 <title><literal>column_options</literal></title>
1025 The view <literal>column_options</literal> contains all the
1026 options defined for foreign table columns in the current database. Only
1027 those foreign table columns are shown that the current user has access to
1028 (by way of being the owner or having some privilege).
1032 <title><literal>column_options</literal> Columns</title>
1038 <entry>Data Type</entry>
1039 <entry>Description</entry>
1045 <entry><literal>table_catalog</literal></entry>
1046 <entry><type>sql_identifier</type></entry>
1047 <entry>Name of the database that contains the foreign table (always the current database)</entry>
1051 <entry><literal>table_schema</literal></entry>
1052 <entry><type>sql_identifier</type></entry>
1053 <entry>Name of the schema that contains the foreign table</entry>
1057 <entry><literal>table_name</literal></entry>
1058 <entry><type>sql_identifier</type></entry>
1059 <entry>Name of the foreign table</entry>
1063 <entry><literal>column_name</literal></entry>
1064 <entry><type>sql_identifier</type></entry>
1065 <entry>Name of the column</entry>
1069 <entry><literal>option_name</literal></entry>
1070 <entry><type>sql_identifier</type></entry>
1071 <entry>Name of an option</entry>
1075 <entry><literal>option_value</literal></entry>
1076 <entry><type>character_data</type></entry>
1077 <entry>Value of the option</entry>
1084 <sect1 id="infoschema-column-privileges">
1085 <title><literal>column_privileges</literal></title>
1088 The view <literal>column_privileges</literal> identifies all
1089 privileges granted on columns to a currently enabled role or by a
1090 currently enabled role. There is one row for each combination of
1091 column, grantor, and grantee.
1095 If a privilege has been granted on an entire table, it will show up in
1096 this view as a grant for each column, but only for the
1097 privilege types where column granularity is possible:
1098 <literal>SELECT</literal>, <literal>INSERT</literal>,
1099 <literal>UPDATE</literal>, <literal>REFERENCES</literal>.
1103 <title><literal>column_privileges</literal> Columns</title>
1109 <entry>Data Type</entry>
1110 <entry>Description</entry>
1116 <entry><literal>grantor</literal></entry>
1117 <entry><type>sql_identifier</type></entry>
1118 <entry>Name of the role that granted the privilege</entry>
1122 <entry><literal>grantee</literal></entry>
1123 <entry><type>sql_identifier</type></entry>
1124 <entry>Name of the role that the privilege was granted to</entry>
1128 <entry><literal>table_catalog</literal></entry>
1129 <entry><type>sql_identifier</type></entry>
1130 <entry>Name of the database that contains the table that contains the column (always the current database)</entry>
1134 <entry><literal>table_schema</literal></entry>
1135 <entry><type>sql_identifier</type></entry>
1136 <entry>Name of the schema that contains the table that contains the column</entry>
1140 <entry><literal>table_name</literal></entry>
1141 <entry><type>sql_identifier</type></entry>
1142 <entry>Name of the table that contains the column</entry>
1146 <entry><literal>column_name</literal></entry>
1147 <entry><type>sql_identifier</type></entry>
1148 <entry>Name of the column</entry>
1152 <entry><literal>privilege_type</literal></entry>
1153 <entry><type>character_data</type></entry>
1155 Type of the privilege: <literal>SELECT</literal>,
1156 <literal>INSERT</literal>, <literal>UPDATE</literal>, or
1157 <literal>REFERENCES</literal>
1162 <entry><literal>is_grantable</literal></entry>
1163 <entry><type>yes_or_no</type></entry>
1164 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
1171 <sect1 id="infoschema-column-udt-usage">
1172 <title><literal>column_udt_usage</literal></title>
1175 The view <literal>column_udt_usage</literal> identifies all columns
1176 that use data types owned by a currently enabled role. Note that in
1177 <productname>PostgreSQL</productname>, built-in data types behave
1178 like user-defined types, so they are included here as well. See
1179 also <xref linkend="infoschema-columns"> for details.
1183 <title><literal>column_udt_usage</literal> Columns</title>
1189 <entry>Data Type</entry>
1190 <entry>Description</entry>
1196 <entry><literal>udt_catalog</literal></entry>
1197 <entry><type>sql_identifier</type></entry>
1199 Name of the database that the column data type (the underlying
1200 type of the domain, if applicable) is defined in (always the
1206 <entry><literal>udt_schema</literal></entry>
1207 <entry><type>sql_identifier</type></entry>
1209 Name of the schema that the column data type (the underlying
1210 type of the domain, if applicable) is defined in
1215 <entry><literal>udt_name</literal></entry>
1216 <entry><type>sql_identifier</type></entry>
1218 Name of the column data type (the underlying type of the
1219 domain, if applicable)
1224 <entry><literal>table_catalog</literal></entry>
1225 <entry><type>sql_identifier</type></entry>
1226 <entry>Name of the database containing the table (always the current database)</entry>
1230 <entry><literal>table_schema</literal></entry>
1231 <entry><type>sql_identifier</type></entry>
1232 <entry>Name of the schema containing the table</entry>
1236 <entry><literal>table_name</literal></entry>
1237 <entry><type>sql_identifier</type></entry>
1238 <entry>Name of the table</entry>
1242 <entry><literal>column_name</literal></entry>
1243 <entry><type>sql_identifier</type></entry>
1244 <entry>Name of the column</entry>
1251 <sect1 id="infoschema-columns">
1252 <title><literal>columns</literal></title>
1255 The view <literal>columns</literal> contains information about all
1256 table columns (or view columns) in the database. System columns
1257 (<literal>oid</>, etc.) are not included. Only those columns are
1258 shown that the current user has access to (by way of being the
1259 owner or having some privilege).
1263 <title><literal>columns</literal> Columns</title>
1269 <entry>Data Type</entry>
1270 <entry>Description</entry>
1276 <entry><literal>table_catalog</literal></entry>
1277 <entry><type>sql_identifier</type></entry>
1278 <entry>Name of the database containing the table (always the current database)</entry>
1282 <entry><literal>table_schema</literal></entry>
1283 <entry><type>sql_identifier</type></entry>
1284 <entry>Name of the schema containing the table</entry>
1288 <entry><literal>table_name</literal></entry>
1289 <entry><type>sql_identifier</type></entry>
1290 <entry>Name of the table</entry>
1294 <entry><literal>column_name</literal></entry>
1295 <entry><type>sql_identifier</type></entry>
1296 <entry>Name of the column</entry>
1300 <entry><literal>ordinal_position</literal></entry>
1301 <entry><type>cardinal_number</type></entry>
1302 <entry>Ordinal position of the column within the table (count starts at 1)</entry>
1306 <entry><literal>column_default</literal></entry>
1307 <entry><type>character_data</type></entry>
1308 <entry>Default expression of the column</entry>
1312 <entry><literal>is_nullable</literal></entry>
1313 <entry><type>yes_or_no</type></entry>
1315 <literal>YES</literal> if the column is possibly nullable,
1316 <literal>NO</literal> if it is known not nullable. A not-null
1317 constraint is one way a column can be known not nullable, but
1318 there can be others.
1323 <entry><literal>data_type</literal></entry>
1324 <entry><type>character_data</type></entry>
1326 Data type of the column, if it is a built-in type, or
1327 <literal>ARRAY</literal> if it is some array (in that case, see
1328 the view <literal>element_types</literal>), else
1329 <literal>USER-DEFINED</literal> (in that case, the type is
1330 identified in <literal>udt_name</literal> and associated
1331 columns). If the column is based on a domain, this column
1332 refers to the type underlying the domain (and the domain is
1333 identified in <literal>domain_name</literal> and associated
1339 <entry><literal>character_maximum_length</literal></entry>
1340 <entry><type>cardinal_number</type></entry>
1342 If <literal>data_type</literal> identifies a character or bit
1343 string type, the declared maximum length; null for all other
1344 data types or if no maximum length was declared.
1349 <entry><literal>character_octet_length</literal></entry>
1350 <entry><type>cardinal_number</type></entry>
1352 If <literal>data_type</literal> identifies a character type,
1353 the maximum possible length in octets (bytes) of a datum; null
1354 for all other data types. The maximum octet length depends on
1355 the declared character maximum length (see above) and the
1361 <entry><literal>numeric_precision</literal></entry>
1362 <entry><type>cardinal_number</type></entry>
1364 If <literal>data_type</literal> identifies a numeric type, this
1365 column contains the (declared or implicit) precision of the
1366 type for this column. The precision indicates the number of
1367 significant digits. It can be expressed in decimal (base 10)
1368 or binary (base 2) terms, as specified in the column
1369 <literal>numeric_precision_radix</literal>. For all other data
1370 types, this column is null.
1375 <entry><literal>numeric_precision_radix</literal></entry>
1376 <entry><type>cardinal_number</type></entry>
1378 If <literal>data_type</literal> identifies a numeric type, this
1379 column indicates in which base the values in the columns
1380 <literal>numeric_precision</literal> and
1381 <literal>numeric_scale</literal> are expressed. The value is
1382 either 2 or 10. For all other data types, this column is null.
1387 <entry><literal>numeric_scale</literal></entry>
1388 <entry><type>cardinal_number</type></entry>
1390 If <literal>data_type</literal> identifies an exact numeric
1391 type, this column contains the (declared or implicit) scale of
1392 the type for this column. The scale indicates the number of
1393 significant digits to the right of the decimal point. It can
1394 be expressed in decimal (base 10) or binary (base 2) terms, as
1395 specified in the column
1396 <literal>numeric_precision_radix</literal>. For all other data
1397 types, this column is null.
1402 <entry><literal>datetime_precision</literal></entry>
1403 <entry><type>cardinal_number</type></entry>
1405 If <literal>data_type</literal> identifies a date, time,
1406 timestamp, or interval type, this column contains the (declared
1407 or implicit) fractional seconds precision of the type for this
1408 column, that is, the number of decimal digits maintained
1409 following the decimal point in the seconds value. For all
1410 other data types, this column is null.
1415 <entry><literal>interval_type</literal></entry>
1416 <entry><type>character_data</type></entry>
1418 If <literal>data_type</literal> identifies an interval type,
1419 this column contains the specification which fields the
1420 intervals include for this column, e.g., <literal>YEAR TO
1421 MONTH</literal>, <literal>DAY TO SECOND</literal>, etc. If no
1422 field restrictions were specified (that is, the interval
1423 accepts all fields), and for all other data types, this field
1429 <entry><literal>interval_precision</literal></entry>
1430 <entry><type>cardinal_number</type></entry>
1432 Applies to a feature not available
1433 in <productname>PostgreSQL</productname>
1434 (see <literal>datetime_precision</literal> for the fractional
1435 seconds precision of interval type columns)
1440 <entry><literal>character_set_catalog</literal></entry>
1441 <entry><type>sql_identifier</type></entry>
1442 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1446 <entry><literal>character_set_schema</literal></entry>
1447 <entry><type>sql_identifier</type></entry>
1448 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1452 <entry><literal>character_set_name</literal></entry>
1453 <entry><type>sql_identifier</type></entry>
1454 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1458 <entry><literal>collation_catalog</literal></entry>
1459 <entry><type>sql_identifier</type></entry>
1461 Name of the database containing the collation of the column
1462 (always the current database), null if default or the data type
1463 of the column is not collatable
1468 <entry><literal>collation_schema</literal></entry>
1469 <entry><type>sql_identifier</type></entry>
1471 Name of the schema containing the collation of the column, null
1472 if default or the data type of the column is not collatable
1477 <entry><literal>collation_name</literal></entry>
1478 <entry><type>sql_identifier</type></entry>
1480 Name of the collation of the column, null if default or the
1481 data type of the column is not collatable
1486 <entry><literal>domain_catalog</literal></entry>
1487 <entry><type>sql_identifier</type></entry>
1489 If the column has a domain type, the name of the database that
1490 the domain is defined in (always the current database), else
1496 <entry><literal>domain_schema</literal></entry>
1497 <entry><type>sql_identifier</type></entry>
1499 If the column has a domain type, the name of the schema that
1500 the domain is defined in, else null.
1505 <entry><literal>domain_name</literal></entry>
1506 <entry><type>sql_identifier</type></entry>
1507 <entry>If the column has a domain type, the name of the domain, else null.</entry>
1511 <entry><literal>udt_catalog</literal></entry>
1512 <entry><type>sql_identifier</type></entry>
1514 Name of the database that the column data type (the underlying
1515 type of the domain, if applicable) is defined in (always the
1521 <entry><literal>udt_schema</literal></entry>
1522 <entry><type>sql_identifier</type></entry>
1524 Name of the schema that the column data type (the underlying
1525 type of the domain, if applicable) is defined in
1530 <entry><literal>udt_name</literal></entry>
1531 <entry><type>sql_identifier</type></entry>
1533 Name of the column data type (the underlying type of the
1534 domain, if applicable)
1539 <entry><literal>scope_catalog</literal></entry>
1540 <entry><type>sql_identifier</type></entry>
1541 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1545 <entry><literal>scope_schema</literal></entry>
1546 <entry><type>sql_identifier</type></entry>
1547 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1551 <entry><literal>scope_name</literal></entry>
1552 <entry><type>sql_identifier</type></entry>
1553 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1557 <entry><literal>maximum_cardinality</literal></entry>
1558 <entry><type>cardinal_number</type></entry>
1559 <entry>Always null, because arrays always have unlimited maximum cardinality in <productname>PostgreSQL</></entry>
1563 <entry><literal>dtd_identifier</literal></entry>
1564 <entry><type>sql_identifier</type></entry>
1566 An identifier of the data type descriptor of the column, unique
1567 among the data type descriptors pertaining to the table. This
1568 is mainly useful for joining with other instances of such
1569 identifiers. (The specific format of the identifier is not
1570 defined and not guaranteed to remain the same in future
1576 <entry><literal>is_self_referencing</literal></entry>
1577 <entry><type>yes_or_no</type></entry>
1578 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1582 <entry><literal>is_identity</literal></entry>
1583 <entry><type>yes_or_no</type></entry>
1584 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1588 <entry><literal>identity_generation</literal></entry>
1589 <entry><type>character_data</type></entry>
1590 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1594 <entry><literal>identity_start</literal></entry>
1595 <entry><type>character_data</type></entry>
1596 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1600 <entry><literal>identity_increment</literal></entry>
1601 <entry><type>character_data</type></entry>
1602 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1606 <entry><literal>identity_maximum</literal></entry>
1607 <entry><type>character_data</type></entry>
1608 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1612 <entry><literal>identity_minimum</literal></entry>
1613 <entry><type>character_data</type></entry>
1614 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1618 <entry><literal>identity_cycle</literal></entry>
1619 <entry><type>yes_or_no</type></entry>
1620 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1624 <entry><literal>is_generated</literal></entry>
1625 <entry><type>character_data</type></entry>
1626 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1630 <entry><literal>generation_expression</literal></entry>
1631 <entry><type>character_data</type></entry>
1632 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1636 <entry><literal>is_updatable</literal></entry>
1637 <entry><type>yes_or_no</type></entry>
1639 <literal>YES</literal> if the column is updatable,
1640 <literal>NO</literal> if not (Columns in base tables are always
1641 updatable, columns in views not necessarily)
1649 Since data types can be defined in a variety of ways in SQL, and
1650 <productname>PostgreSQL</productname> contains additional ways to
1651 define data types, their representation in the information schema
1652 can be somewhat difficult. The column <literal>data_type</literal>
1653 is supposed to identify the underlying built-in type of the column.
1654 In <productname>PostgreSQL</productname>, this means that the type
1655 is defined in the system catalog schema
1656 <literal>pg_catalog</literal>. This column might be useful if the
1657 application can handle the well-known built-in types specially (for
1658 example, format the numeric types differently or use the data in
1659 the precision columns). The columns <literal>udt_name</literal>,
1660 <literal>udt_schema</literal>, and <literal>udt_catalog</literal>
1661 always identify the underlying data type of the column, even if the
1662 column is based on a domain. (Since
1663 <productname>PostgreSQL</productname> treats built-in types like
1664 user-defined types, built-in types appear here as well. This is an
1665 extension of the SQL standard.) These columns should be used if an
1666 application wants to process data differently according to the
1667 type, because in that case it wouldn't matter if the column is
1668 really based on a domain. If the column is based on a domain, the
1669 identity of the domain is stored in the columns
1670 <literal>domain_name</literal>, <literal>domain_schema</literal>,
1671 and <literal>domain_catalog</literal>. If you want to pair up
1672 columns with their associated data types and treat domains as
1673 separate types, you could write <literal>coalesce(domain_name,
1674 udt_name)</literal>, etc.
1678 <sect1 id="infoschema-constraint-column-usage">
1679 <title><literal>constraint_column_usage</literal></title>
1682 The view <literal>constraint_column_usage</literal> identifies all
1683 columns in the current database that are used by some constraint.
1684 Only those columns are shown that are contained in a table owned by
1685 a currently enabled role. For a check constraint, this view
1686 identifies the columns that are used in the check expression. For
1687 a foreign key constraint, this view identifies the columns that the
1688 foreign key references. For a unique or primary key constraint,
1689 this view identifies the constrained columns.
1693 <title><literal>constraint_column_usage</literal> Columns</title>
1699 <entry>Data Type</entry>
1700 <entry>Description</entry>
1706 <entry><literal>table_catalog</literal></entry>
1707 <entry><type>sql_identifier</type></entry>
1709 Name of the database that contains the table that contains the
1710 column that is used by some constraint (always the current
1716 <entry><literal>table_schema</literal></entry>
1717 <entry><type>sql_identifier</type></entry>
1719 Name of the schema that contains the table that contains the
1720 column that is used by some constraint
1725 <entry><literal>table_name</literal></entry>
1726 <entry><type>sql_identifier</type></entry>
1728 Name of the table that contains the column that is used by some
1734 <entry><literal>column_name</literal></entry>
1735 <entry><type>sql_identifier</type></entry>
1737 Name of the column that is used by some constraint
1742 <entry><literal>constraint_catalog</literal></entry>
1743 <entry><type>sql_identifier</type></entry>
1744 <entry>Name of the database that contains the constraint (always the current database)</entry>
1748 <entry><literal>constraint_schema</literal></entry>
1749 <entry><type>sql_identifier</type></entry>
1750 <entry>Name of the schema that contains the constraint</entry>
1754 <entry><literal>constraint_name</literal></entry>
1755 <entry><type>sql_identifier</type></entry>
1756 <entry>Name of the constraint</entry>
1763 <sect1 id="infoschema-constraint-table-usage">
1764 <title><literal>constraint_table_usage</literal></title>
1767 The view <literal>constraint_table_usage</literal> identifies all
1768 tables in the current database that are used by some constraint and
1769 are owned by a currently enabled role. (This is different from the
1770 view <literal>table_constraints</literal>, which identifies all
1771 table constraints along with the table they are defined on.) For a
1772 foreign key constraint, this view identifies the table that the
1773 foreign key references. For a unique or primary key constraint,
1774 this view simply identifies the table the constraint belongs to.
1775 Check constraints and not-null constraints are not included in this
1780 <title><literal>constraint_table_usage</literal> Columns</title>
1786 <entry>Data Type</entry>
1787 <entry>Description</entry>
1793 <entry><literal>table_catalog</literal></entry>
1794 <entry><type>sql_identifier</type></entry>
1796 Name of the database that contains the table that is used by
1797 some constraint (always the current database)
1802 <entry><literal>table_schema</literal></entry>
1803 <entry><type>sql_identifier</type></entry>
1805 Name of the schema that contains the table that is used by some
1811 <entry><literal>table_name</literal></entry>
1812 <entry><type>sql_identifier</type></entry>
1813 <entry>Name of the table that is used by some constraint</entry>
1817 <entry><literal>constraint_catalog</literal></entry>
1818 <entry><type>sql_identifier</type></entry>
1819 <entry>Name of the database that contains the constraint (always the current database)</entry>
1823 <entry><literal>constraint_schema</literal></entry>
1824 <entry><type>sql_identifier</type></entry>
1825 <entry>Name of the schema that contains the constraint</entry>
1829 <entry><literal>constraint_name</literal></entry>
1830 <entry><type>sql_identifier</type></entry>
1831 <entry>Name of the constraint</entry>
1838 <sect1 id="infoschema-data-type-privileges">
1839 <title><literal>data_type_privileges</literal></title>
1842 The view <literal>data_type_privileges</literal> identifies all
1843 data type descriptors that the current user has access to, by way
1844 of being the owner of the described object or having some privilege
1845 for it. A data type descriptor is generated whenever a data type
1846 is used in the definition of a table column, a domain, or a
1847 function (as parameter or return type) and stores some information
1848 about how the data type is used in that instance (for example, the
1849 declared maximum length, if applicable). Each data type
1850 descriptor is assigned an arbitrary identifier that is unique
1851 among the data type descriptor identifiers assigned for one object
1852 (table, domain, function). This view is probably not useful for
1853 applications, but it is used to define some other views in the
1858 <title><literal>data_type_privileges</literal> Columns</title>
1864 <entry>Data Type</entry>
1865 <entry>Description</entry>
1871 <entry><literal>object_catalog</literal></entry>
1872 <entry><type>sql_identifier</type></entry>
1873 <entry>Name of the database that contains the described object (always the current database)</entry>
1877 <entry><literal>object_schema</literal></entry>
1878 <entry><type>sql_identifier</type></entry>
1879 <entry>Name of the schema that contains the described object</entry>
1883 <entry><literal>object_name</literal></entry>
1884 <entry><type>sql_identifier</type></entry>
1885 <entry>Name of the described object</entry>
1889 <entry><literal>object_type</literal></entry>
1890 <entry><type>character_data</type></entry>
1892 The type of the described object: one of
1893 <literal>TABLE</literal> (the data type descriptor pertains to
1894 a column of that table), <literal>DOMAIN</literal> (the data
1895 type descriptors pertains to that domain),
1896 <literal>ROUTINE</literal> (the data type descriptor pertains
1897 to a parameter or the return data type of that function).
1902 <entry><literal>dtd_identifier</literal></entry>
1903 <entry><type>sql_identifier</type></entry>
1905 The identifier of the data type descriptor, which is unique
1906 among the data type descriptors for that same object.
1914 <sect1 id="infoschema-domain-constraints">
1915 <title><literal>domain_constraints</literal></title>
1918 The view <literal>domain_constraints</literal> contains all
1919 constraints belonging to domains defined in the current database.
1923 <title><literal>domain_constraints</literal> Columns</title>
1929 <entry>Data Type</entry>
1930 <entry>Description</entry>
1936 <entry><literal>constraint_catalog</literal></entry>
1937 <entry><type>sql_identifier</type></entry>
1938 <entry>Name of the database that contains the constraint (always the current database)</entry>
1942 <entry><literal>constraint_schema</literal></entry>
1943 <entry><type>sql_identifier</type></entry>
1944 <entry>Name of the schema that contains the constraint</entry>
1948 <entry><literal>constraint_name</literal></entry>
1949 <entry><type>sql_identifier</type></entry>
1950 <entry>Name of the constraint</entry>
1954 <entry><literal>domain_catalog</literal></entry>
1955 <entry><type>sql_identifier</type></entry>
1956 <entry>Name of the database that contains the domain (always the current database)</entry>
1960 <entry><literal>domain_schema</literal></entry>
1961 <entry><type>sql_identifier</type></entry>
1962 <entry>Name of the schema that contains the domain</entry>
1966 <entry><literal>domain_name</literal></entry>
1967 <entry><type>sql_identifier</type></entry>
1968 <entry>Name of the domain</entry>
1972 <entry><literal>is_deferrable</literal></entry>
1973 <entry><type>yes_or_no</type></entry>
1974 <entry><literal>YES</literal> if the constraint is deferrable, <literal>NO</literal> if not</entry>
1978 <entry><literal>initially_deferred</literal></entry>
1979 <entry><type>yes_or_no</type></entry>
1980 <entry><literal>YES</literal> if the constraint is deferrable and initially deferred, <literal>NO</literal> if not</entry>
1987 <sect1 id="infoschema-domain-udt-usage">
1988 <title><literal>domain_udt_usage</literal></title>
1991 The view <literal>domain_udt_usage</literal> identifies all domains
1992 that are based on data types owned by a currently enabled role.
1993 Note that in <productname>PostgreSQL</productname>, built-in data
1994 types behave like user-defined types, so they are included here as
1999 <title><literal>domain_udt_usage</literal> Columns</title>
2005 <entry>Data Type</entry>
2006 <entry>Description</entry>
2012 <entry><literal>udt_catalog</literal></entry>
2013 <entry><type>sql_identifier</type></entry>
2014 <entry>Name of the database that the domain data type is defined in (always the current database)</entry>
2018 <entry><literal>udt_schema</literal></entry>
2019 <entry><type>sql_identifier</type></entry>
2020 <entry>Name of the schema that the domain data type is defined in</entry>
2024 <entry><literal>udt_name</literal></entry>
2025 <entry><type>sql_identifier</type></entry>
2026 <entry>Name of the domain data type</entry>
2030 <entry><literal>domain_catalog</literal></entry>
2031 <entry><type>sql_identifier</type></entry>
2032 <entry>Name of the database that contains the domain (always the current database)</entry>
2036 <entry><literal>domain_schema</literal></entry>
2037 <entry><type>sql_identifier</type></entry>
2038 <entry>Name of the schema that contains the domain</entry>
2042 <entry><literal>domain_name</literal></entry>
2043 <entry><type>sql_identifier</type></entry>
2044 <entry>Name of the domain</entry>
2051 <sect1 id="infoschema-domains">
2052 <title><literal>domains</literal></title>
2055 The view <literal>domains</literal> contains all domains defined in
2056 the current database.
2060 <title><literal>domains</literal> Columns</title>
2066 <entry>Data Type</entry>
2067 <entry>Description</entry>
2073 <entry><literal>domain_catalog</literal></entry>
2074 <entry><type>sql_identifier</type></entry>
2075 <entry>Name of the database that contains the domain (always the current database)</entry>
2079 <entry><literal>domain_schema</literal></entry>
2080 <entry><type>sql_identifier</type></entry>
2081 <entry>Name of the schema that contains the domain</entry>
2085 <entry><literal>domain_name</literal></entry>
2086 <entry><type>sql_identifier</type></entry>
2087 <entry>Name of the domain</entry>
2091 <entry><literal>data_type</literal></entry>
2092 <entry><type>character_data</type></entry>
2094 Data type of the domain, if it is a built-in type, or
2095 <literal>ARRAY</literal> if it is some array (in that case, see
2096 the view <literal>element_types</literal>), else
2097 <literal>USER-DEFINED</literal> (in that case, the type is
2098 identified in <literal>udt_name</literal> and associated
2104 <entry><literal>character_maximum_length</literal></entry>
2105 <entry><type>cardinal_number</type></entry>
2107 If the domain has a character or bit string type, the declared
2108 maximum length; null for all other data types or if no maximum
2109 length was declared.
2114 <entry><literal>character_octet_length</literal></entry>
2115 <entry><type>cardinal_number</type></entry>
2117 If the domain has a character type, the maximum possible length
2118 in octets (bytes) of a datum; null for all other data types.
2119 The maximum octet length depends on the declared character
2120 maximum length (see above) and the server encoding.
2125 <entry><literal>character_set_catalog</literal></entry>
2126 <entry><type>sql_identifier</type></entry>
2127 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2131 <entry><literal>character_set_schema</literal></entry>
2132 <entry><type>sql_identifier</type></entry>
2133 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2137 <entry><literal>character_set_name</literal></entry>
2138 <entry><type>sql_identifier</type></entry>
2139 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2143 <entry><literal>collation_catalog</literal></entry>
2144 <entry><type>sql_identifier</type></entry>
2146 Name of the database containing the collation of the domain
2147 (always the current database), null if default or the data type
2148 of the domain is not collatable
2153 <entry><literal>collation_schema</literal></entry>
2154 <entry><type>sql_identifier</type></entry>
2156 Name of the schema containing the collation of the domain, null
2157 if default or the data type of the domain is not collatable
2162 <entry><literal>collation_name</literal></entry>
2163 <entry><type>sql_identifier</type></entry>
2165 Name of the collation of the column, null if default or the
2166 data type of the domain is not collatable
2171 <entry><literal>numeric_precision</literal></entry>
2172 <entry><type>cardinal_number</type></entry>
2174 If the domain has a numeric type, this column contains the
2175 (declared or implicit) precision of the type for this domain.
2176 The precision indicates the number of significant digits. It
2177 can be expressed in decimal (base 10) or binary (base 2) terms,
2178 as specified in the column
2179 <literal>numeric_precision_radix</literal>. For all other data
2180 types, this column is null.
2185 <entry><literal>numeric_precision_radix</literal></entry>
2186 <entry><type>cardinal_number</type></entry>
2188 If the domain has a numeric type, this column indicates in
2189 which base the values in the columns
2190 <literal>numeric_precision</literal> and
2191 <literal>numeric_scale</literal> are expressed. The value is
2192 either 2 or 10. For all other data types, this column is null.
2197 <entry><literal>numeric_scale</literal></entry>
2198 <entry><type>cardinal_number</type></entry>
2200 If the domain has an exact numeric type, this column contains
2201 the (declared or implicit) scale of the type for this domain.
2202 The scale indicates the number of significant digits to the
2203 right of the decimal point. It can be expressed in decimal
2204 (base 10) or binary (base 2) terms, as specified in the column
2205 <literal>numeric_precision_radix</literal>. For all other data
2206 types, this column is null.
2211 <entry><literal>datetime_precision</literal></entry>
2212 <entry><type>cardinal_number</type></entry>
2214 If <literal>data_type</literal> identifies a date, time,
2215 timestamp, or interval type, this column contains the (declared
2216 or implicit) fractional seconds precision of the type for this
2217 domain, that is, the number of decimal digits maintained
2218 following the decimal point in the seconds value. For all
2219 other data types, this column is null.
2224 <entry><literal>interval_type</literal></entry>
2225 <entry><type>character_data</type></entry>
2227 If <literal>data_type</literal> identifies an interval type,
2228 this column contains the specification which fields the
2229 intervals include for this domain, e.g., <literal>YEAR TO
2230 MONTH</literal>, <literal>DAY TO SECOND</literal>, etc. If no
2231 field restrictions were specified (that is, the interval
2232 accepts all fields), and for all other data types, this field
2238 <entry><literal>interval_precision</literal></entry>
2239 <entry><type>cardinal_number</type></entry>
2241 Applies to a feature not available
2242 in <productname>PostgreSQL</productname>
2243 (see <literal>datetime_precision</literal> for the fractional
2244 seconds precision of interval type domains)
2249 <entry><literal>domain_default</literal></entry>
2250 <entry><type>character_data</type></entry>
2251 <entry>Default expression of the domain</entry>
2255 <entry><literal>udt_catalog</literal></entry>
2256 <entry><type>sql_identifier</type></entry>
2257 <entry>Name of the database that the domain data type is defined in (always the current database)</entry>
2261 <entry><literal>udt_schema</literal></entry>
2262 <entry><type>sql_identifier</type></entry>
2263 <entry>Name of the schema that the domain data type is defined in</entry>
2267 <entry><literal>udt_name</literal></entry>
2268 <entry><type>sql_identifier</type></entry>
2269 <entry>Name of the domain data type</entry>
2273 <entry><literal>scope_catalog</literal></entry>
2274 <entry><type>sql_identifier</type></entry>
2275 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2279 <entry><literal>scope_schema</literal></entry>
2280 <entry><type>sql_identifier</type></entry>
2281 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2285 <entry><literal>scope_name</literal></entry>
2286 <entry><type>sql_identifier</type></entry>
2287 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2291 <entry><literal>maximum_cardinality</literal></entry>
2292 <entry><type>cardinal_number</type></entry>
2293 <entry>Always null, because arrays always have unlimited maximum cardinality in <productname>PostgreSQL</></entry>
2297 <entry><literal>dtd_identifier</literal></entry>
2298 <entry><type>sql_identifier</type></entry>
2300 An identifier of the data type descriptor of the domain, unique
2301 among the data type descriptors pertaining to the domain (which
2302 is trivial, because a domain only contains one data type
2303 descriptor). This is mainly useful for joining with other
2304 instances of such identifiers. (The specific format of the
2305 identifier is not defined and not guaranteed to remain the same
2306 in future versions.)
2314 <sect1 id="infoschema-element-types">
2315 <title><literal>element_types</literal></title>
2318 The view <literal>element_types</literal> contains the data type
2319 descriptors of the elements of arrays. When a table column, composite-type attribute,
2320 domain, function parameter, or function return value is defined to
2321 be of an array type, the respective information schema view only
2322 contains <literal>ARRAY</literal> in the column
2323 <literal>data_type</literal>. To obtain information on the element
2324 type of the array, you can join the respective view with this view.
2325 For example, to show the columns of a table with data types and
2326 array element types, if applicable, you could do:
2328 SELECT c.column_name, c.data_type, e.data_type AS element_type
2329 FROM information_schema.columns c LEFT JOIN information_schema.element_types e
2330 ON ((c.table_catalog, c.table_schema, c.table_name, 'TABLE', c.dtd_identifier)
2331 = (e.object_catalog, e.object_schema, e.object_name, e.object_type, e.collection_type_identifier))
2332 WHERE c.table_schema = '...' AND c.table_name = '...'
2333 ORDER BY c.ordinal_position;
2335 This view only includes objects that the current user has access
2336 to, by way of being the owner or having some privilege.
2340 <title><literal>element_types</literal> Columns</title>
2346 <entry>Data Type</entry>
2347 <entry>Description</entry>
2353 <entry><literal>object_catalog</literal></entry>
2354 <entry><type>sql_identifier</type></entry>
2356 Name of the database that contains the object that uses the
2357 array being described (always the current database)
2362 <entry><literal>object_schema</literal></entry>
2363 <entry><type>sql_identifier</type></entry>
2365 Name of the schema that contains the object that uses the array
2371 <entry><literal>object_name</literal></entry>
2372 <entry><type>sql_identifier</type></entry>
2374 Name of the object that uses the array being described
2379 <entry><literal>object_type</literal></entry>
2380 <entry><type>character_data</type></entry>
2382 The type of the object that uses the array being described: one
2383 of <literal>TABLE</literal> (the array is used by a column of
2384 that table), <literal>USER-DEFINED TYPE</literal> (the array is
2385 used by an attribute of that composite type),
2386 <literal>DOMAIN</literal> (the array is used by that domain),
2387 <literal>ROUTINE</literal> (the array is used by a parameter or
2388 the return data type of that function).
2393 <entry><literal>collection_type_identifier</literal></entry>
2394 <entry><type>sql_identifier</type></entry>
2396 The identifier of the data type descriptor of the array being
2397 described. Use this to join with the
2398 <literal>dtd_identifier</literal> columns of other information
2404 <entry><literal>data_type</literal></entry>
2405 <entry><type>character_data</type></entry>
2407 Data type of the array elements, if it is a built-in type, else
2408 <literal>USER-DEFINED</literal> (in that case, the type is
2409 identified in <literal>udt_name</literal> and associated
2415 <entry><literal>character_maximum_length</literal></entry>
2416 <entry><type>cardinal_number</type></entry>
2417 <entry>Always null, since this information is not applied to array element data types in <productname>PostgreSQL</></entry>
2421 <entry><literal>character_octet_length</literal></entry>
2422 <entry><type>cardinal_number</type></entry>
2423 <entry>Always null, since this information is not applied to array element data types in <productname>PostgreSQL</></entry>
2427 <entry><literal>character_set_catalog</literal></entry>
2428 <entry><type>sql_identifier</type></entry>
2429 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2433 <entry><literal>character_set_schema</literal></entry>
2434 <entry><type>sql_identifier</type></entry>
2435 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2439 <entry><literal>character_set_name</literal></entry>
2440 <entry><type>sql_identifier</type></entry>
2441 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2445 <entry><literal>collation_catalog</literal></entry>
2446 <entry><type>sql_identifier</type></entry>
2448 Name of the database containing the collation of the element
2449 type (always the current database), null if default or the data
2450 type of the element is not collatable
2455 <entry><literal>collation_schema</literal></entry>
2456 <entry><type>sql_identifier</type></entry>
2458 Name of the schema containing the collation of the element
2459 type, null if default or the data type of the element is not
2465 <entry><literal>collation_name</literal></entry>
2466 <entry><type>sql_identifier</type></entry>
2468 Name of the collation of the element type, null if default or
2469 the data type of the element is not collatable
2474 <entry><literal>numeric_precision</literal></entry>
2475 <entry><type>cardinal_number</type></entry>
2476 <entry>Always null, since this information is not applied to array element data types in <productname>PostgreSQL</></entry>
2480 <entry><literal>numeric_precision_radix</literal></entry>
2481 <entry><type>cardinal_number</type></entry>
2482 <entry>Always null, since this information is not applied to array element data types in <productname>PostgreSQL</></entry>
2486 <entry><literal>numeric_scale</literal></entry>
2487 <entry><type>cardinal_number</type></entry>
2488 <entry>Always null, since this information is not applied to array element data types in <productname>PostgreSQL</></entry>
2492 <entry><literal>datetime_precision</literal></entry>
2493 <entry><type>cardinal_number</type></entry>
2494 <entry>Always null, since this information is not applied to array element data types in <productname>PostgreSQL</></entry>
2498 <entry><literal>interval_type</literal></entry>
2499 <entry><type>character_data</type></entry>
2500 <entry>Always null, since this information is not applied to array element data types in <productname>PostgreSQL</></entry>
2504 <entry><literal>interval_precision</literal></entry>
2505 <entry><type>cardinal_number</type></entry>
2506 <entry>Always null, since this information is not applied to array element data types in <productname>PostgreSQL</></entry>
2510 <entry><literal>domain_default</literal></entry>
2511 <entry><type>character_data</type></entry>
2512 <entry>Not yet implemented</entry>
2516 <entry><literal>udt_catalog</literal></entry>
2517 <entry><type>sql_identifier</type></entry>
2519 Name of the database that the data type of the elements is
2520 defined in (always the current database)
2525 <entry><literal>udt_schema</literal></entry>
2526 <entry><type>sql_identifier</type></entry>
2528 Name of the schema that the data type of the elements is
2534 <entry><literal>udt_name</literal></entry>
2535 <entry><type>sql_identifier</type></entry>
2537 Name of the data type of the elements
2542 <entry><literal>scope_catalog</literal></entry>
2543 <entry><type>sql_identifier</type></entry>
2544 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2548 <entry><literal>scope_schema</literal></entry>
2549 <entry><type>sql_identifier</type></entry>
2550 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2554 <entry><literal>scope_name</literal></entry>
2555 <entry><type>sql_identifier</type></entry>
2556 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2560 <entry><literal>maximum_cardinality</literal></entry>
2561 <entry><type>cardinal_number</type></entry>
2562 <entry>Always null, because arrays always have unlimited maximum cardinality in <productname>PostgreSQL</></entry>
2566 <entry><literal>dtd_identifier</literal></entry>
2567 <entry><type>sql_identifier</type></entry>
2569 An identifier of the data type descriptor of the element. This
2570 is currently not useful.
2578 <sect1 id="infoschema-enabled-roles">
2579 <title><literal>enabled_roles</literal></title>
2582 The view <literal>enabled_roles</literal> identifies the currently
2583 <quote>enabled roles</quote>. The enabled roles are recursively
2584 defined as the current user together with all roles that have been
2585 granted to the enabled roles with automatic inheritance. In other
2586 words, these are all roles that the current user has direct or
2587 indirect, automatically inheriting membership in.
2588 <indexterm><primary>enabled role</primary></indexterm>
2589 <indexterm><primary>role</primary><secondary>enabled</secondary></indexterm>
2593 For permission checking, the set of <quote>applicable roles</quote>
2594 is applied, which can be broader than the set of enabled roles. So
2595 generally, it is better to use the view
2596 <literal>applicable_roles</literal> instead of this one; see also
2601 <title><literal>enabled_roles</literal> Columns</title>
2607 <entry>Data Type</entry>
2608 <entry>Description</entry>
2614 <entry><literal>role_name</literal></entry>
2615 <entry><type>sql_identifier</type></entry>
2616 <entry>Name of a role</entry>
2623 <sect1 id="infoschema-foreign-data-wrapper-options">
2624 <title><literal>foreign_data_wrapper_options</literal></title>
2627 The view <literal>foreign_data_wrapper_options</literal> contains
2628 all the options defined for foreign-data wrappers in the current
2629 database. Only those foreign-data wrappers are shown that the
2630 current user has access to (by way of being the owner or having
2635 <title><literal>foreign_data_wrapper_options</literal> Columns</title>
2641 <entry>Data Type</entry>
2642 <entry>Description</entry>
2648 <entry><literal>foreign_data_wrapper_catalog</literal></entry>
2649 <entry><type>sql_identifier</type></entry>
2650 <entry>Name of the database that the foreign-data wrapper is defined in (always the current database)</entry>
2654 <entry><literal>foreign_data_wrapper_name</literal></entry>
2655 <entry><type>sql_identifier</type></entry>
2656 <entry>Name of the foreign-data wrapper</entry>
2660 <entry><literal>option_name</literal></entry>
2661 <entry><type>sql_identifier</type></entry>
2662 <entry>Name of an option</entry>
2666 <entry><literal>option_value</literal></entry>
2667 <entry><type>character_data</type></entry>
2668 <entry>Value of the option</entry>
2675 <sect1 id="infoschema-foreign-data-wrappers">
2676 <title><literal>foreign_data_wrappers</literal></title>
2679 The view <literal>foreign_data_wrappers</literal> contains all
2680 foreign-data wrappers defined in the current database. Only those
2681 foreign-data wrappers are shown that the current user has access to
2682 (by way of being the owner or having some privilege).
2686 <title><literal>foreign_data_wrappers</literal> Columns</title>
2692 <entry>Data Type</entry>
2693 <entry>Description</entry>
2699 <entry><literal>foreign_data_wrapper_catalog</literal></entry>
2700 <entry><type>sql_identifier</type></entry>
2701 <entry>Name of the database that contains the foreign-data
2702 wrapper (always the current database)</entry>
2706 <entry><literal>foreign_data_wrapper_name</literal></entry>
2707 <entry><type>sql_identifier</type></entry>
2708 <entry>Name of the foreign-data wrapper</entry>
2712 <entry><literal>authorization_identifier</literal></entry>
2713 <entry><type>sql_identifier</type></entry>
2714 <entry>Name of the owner of the foreign server</entry>
2718 <entry><literal>library_name</literal></entry>
2719 <entry><type>character_data</type></entry>
2720 <entry>File name of the library that implementing this foreign-data wrapper</entry>
2724 <entry><literal>foreign_data_wrapper_language</literal></entry>
2725 <entry><type>character_data</type></entry>
2726 <entry>Language used to implement this foreign-data wrapper</entry>
2733 <sect1 id="infoschema-foreign-server-options">
2734 <title><literal>foreign_server_options</literal></title>
2737 The view <literal>foreign_server_options</literal> contains all the
2738 options defined for foreign servers in the current database. Only
2739 those foreign servers are shown that the current user has access to
2740 (by way of being the owner or having some privilege).
2744 <title><literal>foreign_server_options</literal> Columns</title>
2750 <entry>Data Type</entry>
2751 <entry>Description</entry>
2757 <entry><literal>foreign_server_catalog</literal></entry>
2758 <entry><type>sql_identifier</type></entry>
2759 <entry>Name of the database that the foreign server is defined in (always the current database)</entry>
2763 <entry><literal>foreign_server_name</literal></entry>
2764 <entry><type>sql_identifier</type></entry>
2765 <entry>Name of the foreign server</entry>
2769 <entry><literal>option_name</literal></entry>
2770 <entry><type>sql_identifier</type></entry>
2771 <entry>Name of an option</entry>
2775 <entry><literal>option_value</literal></entry>
2776 <entry><type>character_data</type></entry>
2777 <entry>Value of the option</entry>
2784 <sect1 id="infoschema-foreign-servers">
2785 <title><literal>foreign_servers</literal></title>
2788 The view <literal>foreign_servers</literal> contains all foreign
2789 servers defined in the current database. Only those foreign
2790 servers are shown that the current user has access to (by way of
2791 being the owner or having some privilege).
2795 <title><literal>foreign_servers</literal> Columns</title>
2801 <entry>Data Type</entry>
2802 <entry>Description</entry>
2808 <entry><literal>foreign_server_catalog</literal></entry>
2809 <entry><type>sql_identifier</type></entry>
2810 <entry>Name of the database that the foreign server is defined in (always the current database)</entry>
2814 <entry><literal>foreign_server_name</literal></entry>
2815 <entry><type>sql_identifier</type></entry>
2816 <entry>Name of the foreign server</entry>
2820 <entry><literal>foreign_data_wrapper_catalog</literal></entry>
2821 <entry><type>sql_identifier</type></entry>
2822 <entry>Name of the database that contains the foreign-data
2823 wrapper used by the foreign server (always the current database)</entry>
2827 <entry><literal>foreign_data_wrapper_name</literal></entry>
2828 <entry><type>sql_identifier</type></entry>
2829 <entry>Name of the foreign-data wrapper used by the foreign server</entry>
2833 <entry><literal>foreign_server_type</literal></entry>
2834 <entry><type>character_data</type></entry>
2835 <entry>Foreign server type information, if specified upon creation</entry>
2839 <entry><literal>foreign_server_version</literal></entry>
2840 <entry><type>character_data</type></entry>
2841 <entry>Foreign server version information, if specified upon creation</entry>
2845 <entry><literal>authorization_identifier</literal></entry>
2846 <entry><type>sql_identifier</type></entry>
2847 <entry>Name of the owner of the foreign server</entry>
2854 <sect1 id="infoschema-foreign-table-options">
2855 <title><literal>foreign_table_options</literal></title>
2858 The view <literal>foreign_table_options</literal> contains all the
2859 options defined for foreign tables in the current database. Only
2860 those foreign tables are shown that the current user has access to
2861 (by way of being the owner or having some privilege).
2865 <title><literal>foreign_table_options</literal> Columns</title>
2871 <entry>Data Type</entry>
2872 <entry>Description</entry>
2878 <entry><literal>foreign_table_catalog</literal></entry>
2879 <entry><type>sql_identifier</type></entry>
2880 <entry>Name of the database that contains the foreign table (always the current database)</entry>
2884 <entry><literal>foreign_table_schema</literal></entry>
2885 <entry><type>sql_identifier</type></entry>
2886 <entry>Name of the schema that contains the foreign table</entry>
2890 <entry><literal>foreign_table_name</literal></entry>
2891 <entry><type>sql_identifier</type></entry>
2892 <entry>Name of the foreign table</entry>
2896 <entry><literal>foreign_server_catalog</literal></entry>
2897 <entry><type>sql_identifier</type></entry>
2898 <entry>Name of the database that the foreign server is defined in (always the current database)</entry>
2902 <entry><literal>foreign_server_name</literal></entry>
2903 <entry><type>sql_identifier</type></entry>
2904 <entry>Name of the foreign server</entry>
2908 <entry><literal>option_name</literal></entry>
2909 <entry><type>sql_identifier</type></entry>
2910 <entry>Name of an option</entry>
2914 <entry><literal>option_value</literal></entry>
2915 <entry><type>character_data</type></entry>
2916 <entry>Value of the option</entry>
2923 <sect1 id="infoschema-foreign-tables">
2924 <title><literal>foreign_tables</literal></title>
2927 The view <literal>foreign_tables</literal> contains all foreign
2928 tables defined in the current database. Only those foreign
2929 tables are shown that the current user has access to (by way of
2930 being the owner or having some privilege).
2934 <title><literal>foreign_tables</literal> Columns</title>
2940 <entry>Data Type</entry>
2941 <entry>Description</entry>
2947 <entry><literal>foreign_table_catalog</literal></entry>
2948 <entry><type>sql_identifier</type></entry>
2949 <entry>Name of the database that the foreign table is defined in (always the current database)</entry>
2953 <entry><literal>foreign_table_schema</literal></entry>
2954 <entry><type>sql_identifier</type></entry>
2955 <entry>Name of the schema that contains the foreign table</entry>
2959 <entry><literal>foreign_table_name</literal></entry>
2960 <entry><type>sql_identifier</type></entry>
2961 <entry>Name of the foreign table</entry>
2965 <entry><literal>foreign_server_catalog</literal></entry>
2966 <entry><type>sql_identifier</type></entry>
2967 <entry>Name of the database that the foreign server is defined in (always the current database)</entry>
2971 <entry><literal>foreign_server_name</literal></entry>
2972 <entry><type>sql_identifier</type></entry>
2973 <entry>Name of the foreign server</entry>
2980 <sect1 id="infoschema-key-column-usage">
2981 <title><literal>key_column_usage</literal></title>
2984 The view <literal>key_column_usage</literal> identifies all columns
2985 in the current database that are restricted by some unique, primary
2986 key, or foreign key constraint. Check constraints are not included
2987 in this view. Only those columns are shown that the current user
2988 has access to, by way of being the owner or having some privilege.
2992 <title><literal>key_column_usage</literal> Columns</title>
2998 <entry>Data Type</entry>
2999 <entry>Description</entry>
3005 <entry><literal>constraint_catalog</literal></entry>
3006 <entry><type>sql_identifier</type></entry>
3007 <entry>Name of the database that contains the constraint (always the current database)</entry>
3011 <entry><literal>constraint_schema</literal></entry>
3012 <entry><type>sql_identifier</type></entry>
3013 <entry>Name of the schema that contains the constraint</entry>
3017 <entry><literal>constraint_name</literal></entry>
3018 <entry><type>sql_identifier</type></entry>
3019 <entry>Name of the constraint</entry>
3023 <entry><literal>table_catalog</literal></entry>
3024 <entry><type>sql_identifier</type></entry>
3026 Name of the database that contains the table that contains the
3027 column that is restricted by this constraint (always the
3033 <entry><literal>table_schema</literal></entry>
3034 <entry><type>sql_identifier</type></entry>
3036 Name of the schema that contains the table that contains the
3037 column that is restricted by this constraint
3042 <entry><literal>table_name</literal></entry>
3043 <entry><type>sql_identifier</type></entry>
3045 Name of the table that contains the column that is restricted
3051 <entry><literal>column_name</literal></entry>
3052 <entry><type>sql_identifier</type></entry>
3054 Name of the column that is restricted by this constraint
3059 <entry><literal>ordinal_position</literal></entry>
3060 <entry><type>cardinal_number</type></entry>
3062 Ordinal position of the column within the constraint key (count
3068 <entry><literal>position_in_unique_constraint</literal></entry>
3069 <entry><type>cardinal_number</type></entry>
3071 For a foreign-key constraint, ordinal position of the referenced
3072 column within its unique constraint (count starts at 1);
3081 <sect1 id="infoschema-parameters">
3082 <title><literal>parameters</literal></title>
3085 The view <literal>parameters</literal> contains information about
3086 the parameters (arguments) of all functions in the current database.
3087 Only those functions are shown that the current user has access to
3088 (by way of being the owner or having some privilege).
3092 <title><literal>parameters</literal> Columns</title>
3098 <entry>Data Type</entry>
3099 <entry>Description</entry>
3105 <entry><literal>specific_catalog</literal></entry>
3106 <entry><type>sql_identifier</type></entry>
3107 <entry>Name of the database containing the function (always the current database)</entry>
3111 <entry><literal>specific_schema</literal></entry>
3112 <entry><type>sql_identifier</type></entry>
3113 <entry>Name of the schema containing the function</entry>
3117 <entry><literal>specific_name</literal></entry>
3118 <entry><type>sql_identifier</type></entry>
3120 The <quote>specific name</quote> of the function. See <xref
3121 linkend="infoschema-routines"> for more information.
3126 <entry><literal>ordinal_position</literal></entry>
3127 <entry><type>cardinal_number</type></entry>
3129 Ordinal position of the parameter in the argument list of the
3130 function (count starts at 1)
3135 <entry><literal>parameter_mode</literal></entry>
3136 <entry><type>character_data</type></entry>
3138 <literal>IN</literal> for input parameter,
3139 <literal>OUT</literal> for output parameter,
3140 and <literal>INOUT</literal> for input/output parameter.
3145 <entry><literal>is_result</literal></entry>
3146 <entry><type>yes_or_no</type></entry>
3147 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3151 <entry><literal>as_locator</literal></entry>
3152 <entry><type>yes_or_no</type></entry>
3153 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3157 <entry><literal>parameter_name</literal></entry>
3158 <entry><type>sql_identifier</type></entry>
3159 <entry>Name of the parameter, or null if the parameter has no name</entry>
3163 <entry><literal>data_type</literal></entry>
3164 <entry><type>character_data</type></entry>
3166 Data type of the parameter, if it is a built-in type, or
3167 <literal>ARRAY</literal> if it is some array (in that case, see
3168 the view <literal>element_types</literal>), else
3169 <literal>USER-DEFINED</literal> (in that case, the type is
3170 identified in <literal>udt_name</literal> and associated
3176 <entry><literal>character_maximum_length</literal></entry>
3177 <entry><type>cardinal_number</type></entry>
3178 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3182 <entry><literal>character_octet_length</literal></entry>
3183 <entry><type>cardinal_number</type></entry>
3184 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3188 <entry><literal>character_set_catalog</literal></entry>
3189 <entry><type>sql_identifier</type></entry>
3190 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3194 <entry><literal>character_set_schema</literal></entry>
3195 <entry><type>sql_identifier</type></entry>
3196 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3200 <entry><literal>character_set_name</literal></entry>
3201 <entry><type>sql_identifier</type></entry>
3202 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3206 <entry><literal>collation_catalog</literal></entry>
3207 <entry><type>sql_identifier</type></entry>
3208 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3212 <entry><literal>collation_schema</literal></entry>
3213 <entry><type>sql_identifier</type></entry>
3214 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3218 <entry><literal>collation_name</literal></entry>
3219 <entry><type>sql_identifier</type></entry>
3220 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3224 <entry><literal>numeric_precision</literal></entry>
3225 <entry><type>cardinal_number</type></entry>
3226 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3230 <entry><literal>numeric_precision_radix</literal></entry>
3231 <entry><type>cardinal_number</type></entry>
3232 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3236 <entry><literal>numeric_scale</literal></entry>
3237 <entry><type>cardinal_number</type></entry>
3238 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3242 <entry><literal>datetime_precision</literal></entry>
3243 <entry><type>cardinal_number</type></entry>
3244 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3248 <entry><literal>interval_type</literal></entry>
3249 <entry><type>character_data</type></entry>
3250 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3254 <entry><literal>interval_precision</literal></entry>
3255 <entry><type>cardinal_number</type></entry>
3256 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3260 <entry><literal>udt_catalog</literal></entry>
3261 <entry><type>sql_identifier</type></entry>
3263 Name of the database that the data type of the parameter is
3264 defined in (always the current database)
3269 <entry><literal>udt_schema</literal></entry>
3270 <entry><type>sql_identifier</type></entry>
3272 Name of the schema that the data type of the parameter is
3278 <entry><literal>udt_name</literal></entry>
3279 <entry><type>sql_identifier</type></entry>
3281 Name of the data type of the parameter
3286 <entry><literal>scope_catalog</literal></entry>
3287 <entry><type>sql_identifier</type></entry>
3288 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3292 <entry><literal>scope_schema</literal></entry>
3293 <entry><type>sql_identifier</type></entry>
3294 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3298 <entry><literal>scope_name</literal></entry>
3299 <entry><type>sql_identifier</type></entry>
3300 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3304 <entry><literal>maximum_cardinality</literal></entry>
3305 <entry><type>cardinal_number</type></entry>
3306 <entry>Always null, because arrays always have unlimited maximum cardinality in <productname>PostgreSQL</></entry>
3310 <entry><literal>dtd_identifier</literal></entry>
3311 <entry><type>sql_identifier</type></entry>
3313 An identifier of the data type descriptor of the parameter,
3314 unique among the data type descriptors pertaining to the
3315 function. This is mainly useful for joining with other
3316 instances of such identifiers. (The specific format of the
3317 identifier is not defined and not guaranteed to remain the same
3318 in future versions.)
3326 <sect1 id="infoschema-referential-constraints">
3327 <title><literal>referential_constraints</literal></title>
3330 The view <literal>referential_constraints</literal> contains all
3331 referential (foreign key) constraints in the current database.
3332 Only those constraints are shown for which the current user has
3333 write access to the referencing table (by way of being the
3334 owner or having some privilege other than <literal>SELECT</literal>).
3338 <title><literal>referential_constraints</literal> Columns</title>
3344 <entry>Data Type</entry>
3345 <entry>Description</entry>
3351 <entry><literal>constraint_catalog</literal></entry>
3352 <entry><literal>sql_identifier</literal></entry>
3353 <entry>Name of the database containing the constraint (always the current database)</entry>
3357 <entry><literal>constraint_schema</literal></entry>
3358 <entry><literal>sql_identifier</literal></entry>
3359 <entry>Name of the schema containing the constraint</entry>
3363 <entry><literal>constraint_name</literal></entry>
3364 <entry><literal>sql_identifier</literal></entry>
3365 <entry>Name of the constraint</entry>
3369 <entry><literal>unique_constraint_catalog</literal></entry>
3370 <entry><literal>sql_identifier</literal></entry>
3372 Name of the database that contains the unique or primary key
3373 constraint that the foreign key constraint references (always
3374 the current database)
3379 <entry><literal>unique_constraint_schema</literal></entry>
3380 <entry><literal>sql_identifier</literal></entry>
3382 Name of the schema that contains the unique or primary key
3383 constraint that the foreign key constraint references
3388 <entry><literal>unique_constraint_name</literal></entry>
3389 <entry><literal>sql_identifier</literal></entry>
3391 Name of the unique or primary key constraint that the foreign
3392 key constraint references
3397 <entry><literal>match_option</literal></entry>
3398 <entry><literal>character_data</literal></entry>
3400 Match option of the foreign key constraint:
3401 <literal>FULL</literal>, <literal>PARTIAL</literal>, or
3402 <literal>NONE</literal>.
3407 <entry><literal>update_rule</literal></entry>
3408 <entry><literal>character_data</literal></entry>
3410 Update rule of the foreign key constraint:
3411 <literal>CASCADE</literal>, <literal>SET NULL</literal>,
3412 <literal>SET DEFAULT</literal>, <literal>RESTRICT</literal>, or
3413 <literal>NO ACTION</literal>.
3418 <entry><literal>delete_rule</literal></entry>
3419 <entry><literal>character_data</literal></entry>
3421 Delete rule of the foreign key constraint:
3422 <literal>CASCADE</literal>, <literal>SET NULL</literal>,
3423 <literal>SET DEFAULT</literal>, <literal>RESTRICT</literal>, or
3424 <literal>NO ACTION</literal>.
3433 <sect1 id="infoschema-role-column-grants">
3434 <title><literal>role_column_grants</literal></title>
3437 The view <literal>role_column_grants</literal> identifies all
3438 privileges granted on columns where the grantor or grantee is a
3439 currently enabled role. Further information can be found under
3440 <literal>column_privileges</literal>. The only effective
3441 difference between this view
3442 and <literal>column_privileges</literal> is that this view omits
3443 columns that have been made accessible to the current user by way
3444 of a grant to <literal>PUBLIC</literal>.
3448 <title><literal>role_column_grants</literal> Columns</title>
3454 <entry>Data Type</entry>
3455 <entry>Description</entry>
3461 <entry><literal>grantor</literal></entry>
3462 <entry><type>sql_identifier</type></entry>
3463 <entry>Name of the role that granted the privilege</entry>
3467 <entry><literal>grantee</literal></entry>
3468 <entry><type>sql_identifier</type></entry>
3469 <entry>Name of the role that the privilege was granted to</entry>
3473 <entry><literal>table_catalog</literal></entry>
3474 <entry><type>sql_identifier</type></entry>
3475 <entry>Name of the database that contains the table that contains the column (always the current database)</entry>
3479 <entry><literal>table_schema</literal></entry>
3480 <entry><type>sql_identifier</type></entry>
3481 <entry>Name of the schema that contains the table that contains the column</entry>
3485 <entry><literal>table_name</literal></entry>
3486 <entry><type>sql_identifier</type></entry>
3487 <entry>Name of the table that contains the column</entry>
3491 <entry><literal>column_name</literal></entry>
3492 <entry><type>sql_identifier</type></entry>
3493 <entry>Name of the column</entry>
3497 <entry><literal>privilege_type</literal></entry>
3498 <entry><type>character_data</type></entry>
3500 Type of the privilege: <literal>SELECT</literal>,
3501 <literal>INSERT</literal>, <literal>UPDATE</literal>, or
3502 <literal>REFERENCES</literal>
3507 <entry><literal>is_grantable</literal></entry>
3508 <entry><type>yes_or_no</type></entry>
3509 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
3516 <sect1 id="infoschema-role-routine-grants">
3517 <title><literal>role_routine_grants</literal></title>
3520 The view <literal>role_routine_grants</literal> identifies all
3521 privileges granted on functions where the grantor or grantee is a
3522 currently enabled role. Further information can be found under
3523 <literal>routine_privileges</literal>. The only effective
3524 difference between this view
3525 and <literal>routine_privileges</literal> is that this view omits
3526 functions that have been made accessible to the current user by way
3527 of a grant to <literal>PUBLIC</literal>.
3531 <title><literal>role_routine_grants</literal> Columns</title>
3537 <entry>Data Type</entry>
3538 <entry>Description</entry>
3544 <entry><literal>grantor</literal></entry>
3545 <entry><type>sql_identifier</type></entry>
3546 <entry>Name of the role that granted the privilege</entry>
3550 <entry><literal>grantee</literal></entry>
3551 <entry><type>sql_identifier</type></entry>
3552 <entry>Name of the role that the privilege was granted to</entry>
3556 <entry><literal>specific_catalog</literal></entry>
3557 <entry><type>sql_identifier</type></entry>
3558 <entry>Name of the database containing the function (always the current database)</entry>
3562 <entry><literal>specific_schema</literal></entry>
3563 <entry><type>sql_identifier</type></entry>
3564 <entry>Name of the schema containing the function</entry>
3568 <entry><literal>specific_name</literal></entry>
3569 <entry><type>sql_identifier</type></entry>
3571 The <quote>specific name</quote> of the function. See <xref
3572 linkend="infoschema-routines"> for more information.
3577 <entry><literal>routine_catalog</literal></entry>
3578 <entry><type>sql_identifier</type></entry>
3579 <entry>Name of the database containing the function (always the current database)</entry>
3583 <entry><literal>routine_schema</literal></entry>
3584 <entry><type>sql_identifier</type></entry>
3585 <entry>Name of the schema containing the function</entry>
3589 <entry><literal>routine_name</literal></entry>
3590 <entry><type>sql_identifier</type></entry>
3591 <entry>Name of the function (might be duplicated in case of overloading)</entry>
3595 <entry><literal>privilege_type</literal></entry>
3596 <entry><type>character_data</type></entry>
3597 <entry>Always <literal>EXECUTE</literal> (the only privilege type for functions)</entry>
3601 <entry><literal>is_grantable</literal></entry>
3602 <entry><type>yes_or_no</type></entry>
3603 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
3610 <sect1 id="infoschema-role-table-grants">
3611 <title><literal>role_table_grants</literal></title>
3614 The view <literal>role_table_grants</literal> identifies all
3615 privileges granted on tables or views where the grantor or grantee
3616 is a currently enabled role. Further information can be found
3617 under <literal>table_privileges</literal>. The only effective
3618 difference between this view
3619 and <literal>table_privileges</literal> is that this view omits
3620 tables that have been made accessible to the current user by way of
3621 a grant to <literal>PUBLIC</literal>.
3625 <title><literal>role_table_grants</literal> Columns</title>
3631 <entry>Data Type</entry>
3632 <entry>Description</entry>
3638 <entry><literal>grantor</literal></entry>
3639 <entry><type>sql_identifier</type></entry>
3640 <entry>Name of the role that granted the privilege</entry>
3644 <entry><literal>grantee</literal></entry>
3645 <entry><type>sql_identifier</type></entry>
3646 <entry>Name of the role that the privilege was granted to</entry>
3650 <entry><literal>table_catalog</literal></entry>
3651 <entry><type>sql_identifier</type></entry>
3652 <entry>Name of the database that contains the table (always the current database)</entry>
3656 <entry><literal>table_schema</literal></entry>
3657 <entry><type>sql_identifier</type></entry>
3658 <entry>Name of the schema that contains the table</entry>
3662 <entry><literal>table_name</literal></entry>
3663 <entry><type>sql_identifier</type></entry>
3664 <entry>Name of the table</entry>
3668 <entry><literal>privilege_type</literal></entry>
3669 <entry><type>character_data</type></entry>
3671 Type of the privilege: <literal>SELECT</literal>,
3672 <literal>INSERT</literal>, <literal>UPDATE</literal>,
3673 <literal>DELETE</literal>, <literal>TRUNCATE</literal>,
3674 <literal>REFERENCES</literal>, or <literal>TRIGGER</literal>
3679 <entry><literal>is_grantable</literal></entry>
3680 <entry><type>yes_or_no</type></entry>
3681 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
3685 <entry><literal>with_hierarchy</literal></entry>
3686 <entry><type>yes_or_no</type></entry>
3688 In the SQL standard, <literal>WITH HIERARCHY OPTION</literal>
3689 is a separate (sub-)privilege allowing certain operations on
3690 table inheritance hierarchies. In PostgreSQL, this is included
3691 in the <literal>SELECT</literal> privilege, so this column
3692 shows <literal>YES</literal> if the privilege
3693 is <literal>SELECT</literal>, else <literal>NO</literal>.
3701 <sect1 id="infoschema-role-udt-grants">
3702 <title><literal>role_udt_grants</literal></title>
3705 The view <literal>role_udt_grants</literal> is intended to identify
3706 <literal>USAGE</literal> privileges granted on user-defined types
3707 where the grantor or grantee is a currently enabled role. Further
3708 information can be found under
3709 <literal>udt_privileges</literal>. The only effective difference
3710 between this view and <literal>udt_privileges</literal> is that
3711 this view omits objects that have been made accessible to the
3712 current user by way of a grant to <literal>PUBLIC</literal>. Since
3713 data types do not have real privileges in PostgreSQL, but only an
3714 implicit grant to <literal>PUBLIC</literal>, this view is empty.
3718 <title><literal>role_udt_grants</literal> Columns</title>
3724 <entry>Data Type</entry>
3725 <entry>Description</entry>
3731 <entry><literal>grantor</literal></entry>
3732 <entry><type>sql_identifier</type></entry>
3733 <entry>The name of the role that granted the privilege</entry>
3737 <entry><literal>grantee</literal></entry>
3738 <entry><type>sql_identifier</type></entry>
3739 <entry>The name of the role that the privilege was granted to</entry>
3743 <entry><literal>udt_catalog</literal></entry>
3744 <entry><type>sql_identifier</type></entry>
3745 <entry>Name of the database containing the type (always the current database)</entry>
3749 <entry><literal>udt_schema</literal></entry>
3750 <entry><type>sql_identifier</type></entry>
3751 <entry>Name of the schema containing the type</entry>
3755 <entry><literal>udt_name</literal></entry>
3756 <entry><type>sql_identifier</type></entry>
3757 <entry>Name of the type</entry>
3761 <entry><literal>privilege_type</literal></entry>
3762 <entry><type>character_data</type></entry>
3763 <entry>Always <literal>TYPE USAGE</literal></entry>
3767 <entry><literal>is_grantable</literal></entry>
3768 <entry><type>yes_or_no</type></entry>
3769 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
3776 <sect1 id="infoschema-role-usage-grants">
3777 <title><literal>role_usage_grants</literal></title>
3780 The view <literal>role_usage_grants</literal> identifies
3781 <literal>USAGE</literal> privileges granted on various kinds of
3782 objects where the grantor or grantee is a currently enabled role.
3783 Further information can be found under
3784 <literal>usage_privileges</literal>. The only effective difference
3785 between this view and <literal>usage_privileges</literal> is that
3786 this view omits objects that have been made accessible to the
3787 current user by way of a grant to <literal>PUBLIC</literal>.
3791 <title><literal>role_usage_grants</literal> Columns</title>
3797 <entry>Data Type</entry>
3798 <entry>Description</entry>
3804 <entry><literal>grantor</literal></entry>
3805 <entry><type>sql_identifier</type></entry>
3806 <entry>The name of the role that granted the privilege</entry>
3810 <entry><literal>grantee</literal></entry>
3811 <entry><type>sql_identifier</type></entry>
3812 <entry>The name of the role that the privilege was granted to</entry>
3816 <entry><literal>object_catalog</literal></entry>
3817 <entry><type>sql_identifier</type></entry>
3818 <entry>Name of the database containing the object (always the current database)</entry>
3822 <entry><literal>object_schema</literal></entry>
3823 <entry><type>sql_identifier</type></entry>
3824 <entry>Name of the schema containing the object, if applicable,
3825 else an empty string</entry>
3829 <entry><literal>object_name</literal></entry>
3830 <entry><type>sql_identifier</type></entry>
3831 <entry>Name of the object</entry>
3835 <entry><literal>object_type</literal></entry>
3836 <entry><type>character_data</type></entry>
3837 <entry><literal>COLLATION</literal> or <literal>DOMAIN</literal> or <literal>FOREIGN DATA WRAPPER</literal> or <literal>FOREIGN SERVER</literal></entry>
3841 <entry><literal>privilege_type</literal></entry>
3842 <entry><type>character_data</type></entry>
3843 <entry>Always <literal>USAGE</literal></entry>
3847 <entry><literal>is_grantable</literal></entry>
3848 <entry><type>yes_or_no</type></entry>
3849 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
3856 <sect1 id="infoschema-routine-privileges">
3857 <title><literal>routine_privileges</literal></title>
3860 The view <literal>routine_privileges</literal> identifies all
3861 privileges granted on functions to a currently enabled role or by a
3862 currently enabled role. There is one row for each combination of function,
3863 grantor, and grantee.
3867 <title><literal>routine_privileges</literal> Columns</title>
3873 <entry>Data Type</entry>
3874 <entry>Description</entry>
3880 <entry><literal>grantor</literal></entry>
3881 <entry><type>sql_identifier</type></entry>
3882 <entry>Name of the role that granted the privilege</entry>
3886 <entry><literal>grantee</literal></entry>
3887 <entry><type>sql_identifier</type></entry>
3888 <entry>Name of the role that the privilege was granted to</entry>
3892 <entry><literal>specific_catalog</literal></entry>
3893 <entry><type>sql_identifier</type></entry>
3894 <entry>Name of the database containing the function (always the current database)</entry>
3898 <entry><literal>specific_schema</literal></entry>
3899 <entry><type>sql_identifier</type></entry>
3900 <entry>Name of the schema containing the function</entry>
3904 <entry><literal>specific_name</literal></entry>
3905 <entry><type>sql_identifier</type></entry>
3907 The <quote>specific name</quote> of the function. See <xref
3908 linkend="infoschema-routines"> for more information.
3913 <entry><literal>routine_catalog</literal></entry>
3914 <entry><type>sql_identifier</type></entry>
3915 <entry>Name of the database containing the function (always the current database)</entry>
3919 <entry><literal>routine_schema</literal></entry>
3920 <entry><type>sql_identifier</type></entry>
3921 <entry>Name of the schema containing the function</entry>
3925 <entry><literal>routine_name</literal></entry>
3926 <entry><type>sql_identifier</type></entry>
3927 <entry>Name of the function (might be duplicated in case of overloading)</entry>
3931 <entry><literal>privilege_type</literal></entry>
3932 <entry><type>character_data</type></entry>
3933 <entry>Always <literal>EXECUTE</literal> (the only privilege type for functions)</entry>
3937 <entry><literal>is_grantable</literal></entry>
3938 <entry><type>yes_or_no</type></entry>
3939 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
3946 <sect1 id="infoschema-routines">
3947 <title><literal>routines</literal></title>
3950 The view <literal>routines</literal> contains all functions in the
3951 current database. Only those functions are shown that the current
3952 user has access to (by way of being the owner or having some
3957 <title><literal>routines</literal> Columns</title>
3963 <entry>Data Type</entry>
3964 <entry>Description</entry>
3970 <entry><literal>specific_catalog</literal></entry>
3971 <entry><type>sql_identifier</type></entry>
3972 <entry>Name of the database containing the function (always the current database)</entry>
3976 <entry><literal>specific_schema</literal></entry>
3977 <entry><type>sql_identifier</type></entry>
3978 <entry>Name of the schema containing the function</entry>
3982 <entry><literal>specific_name</literal></entry>
3983 <entry><type>sql_identifier</type></entry>
3985 The <quote>specific name</quote> of the function. This is a
3986 name that uniquely identifies the function in the schema, even
3987 if the real name of the function is overloaded. The format of
3988 the specific name is not defined, it should only be used to
3989 compare it to other instances of specific routine names.
3994 <entry><literal>routine_catalog</literal></entry>
3995 <entry><type>sql_identifier</type></entry>
3996 <entry>Name of the database containing the function (always the current database)</entry>
4000 <entry><literal>routine_schema</literal></entry>
4001 <entry><type>sql_identifier</type></entry>
4002 <entry>Name of the schema containing the function</entry>
4006 <entry><literal>routine_name</literal></entry>
4007 <entry><type>sql_identifier</type></entry>
4008 <entry>Name of the function (might be duplicated in case of overloading)</entry>
4012 <entry><literal>routine_type</literal></entry>
4013 <entry><type>character_data</type></entry>
4015 Always <literal>FUNCTION</literal> (In the future there might
4016 be other types of routines.)
4021 <entry><literal>module_catalog</literal></entry>
4022 <entry><type>sql_identifier</type></entry>
4023 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4027 <entry><literal>module_schema</literal></entry>
4028 <entry><type>sql_identifier</type></entry>
4029 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4033 <entry><literal>module_name</literal></entry>
4034 <entry><type>sql_identifier</type></entry>
4035 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4039 <entry><literal>udt_catalog</literal></entry>
4040 <entry><type>sql_identifier</type></entry>
4041 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4045 <entry><literal>udt_schema</literal></entry>
4046 <entry><type>sql_identifier</type></entry>
4047 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4051 <entry><literal>udt_name</literal></entry>
4052 <entry><type>sql_identifier</type></entry>
4053 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4057 <entry><literal>data_type</literal></entry>
4058 <entry><type>character_data</type></entry>
4060 Return data type of the function, if it is a built-in type, or
4061 <literal>ARRAY</literal> if it is some array (in that case, see
4062 the view <literal>element_types</literal>), else
4063 <literal>USER-DEFINED</literal> (in that case, the type is
4064 identified in <literal>type_udt_name</literal> and associated
4070 <entry><literal>character_maximum_length</literal></entry>
4071 <entry><type>cardinal_number</type></entry>
4072 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4076 <entry><literal>character_octet_length</literal></entry>
4077 <entry><type>cardinal_number</type></entry>
4078 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4082 <entry><literal>character_set_catalog</literal></entry>
4083 <entry><type>sql_identifier</type></entry>
4084 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4088 <entry><literal>character_set_schema</literal></entry>
4089 <entry><type>sql_identifier</type></entry>
4090 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4094 <entry><literal>character_set_name</literal></entry>
4095 <entry><type>sql_identifier</type></entry>
4096 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4100 <entry><literal>collation_catalog</literal></entry>
4101 <entry><type>sql_identifier</type></entry>
4102 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4106 <entry><literal>collation_schema</literal></entry>
4107 <entry><type>sql_identifier</type></entry>
4108 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4112 <entry><literal>collation_name</literal></entry>
4113 <entry><type>sql_identifier</type></entry>
4114 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4118 <entry><literal>numeric_precision</literal></entry>
4119 <entry><type>cardinal_number</type></entry>
4120 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4124 <entry><literal>numeric_precision_radix</literal></entry>
4125 <entry><type>cardinal_number</type></entry>
4126 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4130 <entry><literal>numeric_scale</literal></entry>
4131 <entry><type>cardinal_number</type></entry>
4132 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4136 <entry><literal>datetime_precision</literal></entry>
4137 <entry><type>cardinal_number</type></entry>
4138 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4142 <entry><literal>interval_type</literal></entry>
4143 <entry><type>character_data</type></entry>
4144 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4148 <entry><literal>interval_precision</literal></entry>
4149 <entry><type>cardinal_number</type></entry>
4150 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4154 <entry><literal>type_udt_catalog</literal></entry>
4155 <entry><type>sql_identifier</type></entry>
4157 Name of the database that the return data type of the function
4158 is defined in (always the current database)
4163 <entry><literal>type_udt_schema</literal></entry>
4164 <entry><type>sql_identifier</type></entry>
4166 Name of the schema that the return data type of the function is
4172 <entry><literal>type_udt_name</literal></entry>
4173 <entry><type>sql_identifier</type></entry>
4175 Name of the return data type of the function
4180 <entry><literal>scope_catalog</literal></entry>
4181 <entry><type>sql_identifier</type></entry>
4182 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4186 <entry><literal>scope_schema</literal></entry>
4187 <entry><type>sql_identifier</type></entry>
4188 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4192 <entry><literal>scope_name</literal></entry>
4193 <entry><type>sql_identifier</type></entry>
4194 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4198 <entry><literal>maximum_cardinality</literal></entry>
4199 <entry><type>cardinal_number</type></entry>
4200 <entry>Always null, because arrays always have unlimited maximum cardinality in <productname>PostgreSQL</></entry>
4204 <entry><literal>dtd_identifier</literal></entry>
4205 <entry><type>sql_identifier</type></entry>
4207 An identifier of the data type descriptor of the return data
4208 type of this function, unique among the data type descriptors
4209 pertaining to the function. This is mainly useful for joining
4210 with other instances of such identifiers. (The specific format
4211 of the identifier is not defined and not guaranteed to remain
4212 the same in future versions.)
4217 <entry><literal>routine_body</literal></entry>
4218 <entry><type>character_data</type></entry>
4220 If the function is an SQL function, then
4221 <literal>SQL</literal>, else <literal>EXTERNAL</literal>.
4226 <entry><literal>routine_definition</literal></entry>
4227 <entry><type>character_data</type></entry>
4229 The source text of the function (null if the function is not
4230 owned by a currently enabled role). (According to the SQL
4231 standard, this column is only applicable if
4232 <literal>routine_body</literal> is <literal>SQL</literal>, but
4233 in <productname>PostgreSQL</productname> it will contain
4234 whatever source text was specified when the function was
4240 <entry><literal>external_name</literal></entry>
4241 <entry><type>character_data</type></entry>
4243 If this function is a C function, then the external name (link
4244 symbol) of the function; else null. (This works out to be the
4245 same value that is shown in
4246 <literal>routine_definition</literal>.)
4251 <entry><literal>external_language</literal></entry>
4252 <entry><type>character_data</type></entry>
4253 <entry>The language the function is written in</entry>
4257 <entry><literal>parameter_style</literal></entry>
4258 <entry><type>character_data</type></entry>
4260 Always <literal>GENERAL</literal> (The SQL standard defines
4261 other parameter styles, which are not available in <productname>PostgreSQL</>.)
4266 <entry><literal>is_deterministic</literal></entry>
4267 <entry><type>yes_or_no</type></entry>
4269 If the function is declared immutable (called deterministic in
4270 the SQL standard), then <literal>YES</literal>, else
4271 <literal>NO</literal>. (You cannot query the other volatility
4272 levels available in <productname>PostgreSQL</> through the information schema.)
4277 <entry><literal>sql_data_access</literal></entry>
4278 <entry><type>character_data</type></entry>
4280 Always <literal>MODIFIES</literal>, meaning that the function
4281 possibly modifies SQL data. This information is not useful for
4282 <productname>PostgreSQL</>.
4287 <entry><literal>is_null_call</literal></entry>
4288 <entry><type>yes_or_no</type></entry>
4290 If the function automatically returns null if any of its
4291 arguments are null, then <literal>YES</literal>, else
4292 <literal>NO</literal>.
4297 <entry><literal>sql_path</literal></entry>
4298 <entry><type>character_data</type></entry>
4299 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4303 <entry><literal>schema_level_routine</literal></entry>
4304 <entry><type>yes_or_no</type></entry>
4306 Always <literal>YES</literal> (The opposite would be a method
4307 of a user-defined type, which is a feature not available in
4308 <productname>PostgreSQL</>.)
4313 <entry><literal>max_dynamic_result_sets</literal></entry>
4314 <entry><type>cardinal_number</type></entry>
4315 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4319 <entry><literal>is_user_defined_cast</literal></entry>
4320 <entry><type>yes_or_no</type></entry>
4321 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4325 <entry><literal>is_implicitly_invocable</literal></entry>
4326 <entry><type>yes_or_no</type></entry>
4327 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4331 <entry><literal>security_type</literal></entry>
4332 <entry><type>character_data</type></entry>
4334 If the function runs with the privileges of the current user,
4335 then <literal>INVOKER</literal>, if the function runs with the
4336 privileges of the user who defined it, then
4337 <literal>DEFINER</literal>.
4342 <entry><literal>to_sql_specific_catalog</literal></entry>
4343 <entry><type>sql_identifier</type></entry>
4344 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4348 <entry><literal>to_sql_specific_schema</literal></entry>
4349 <entry><type>sql_identifier</type></entry>
4350 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4354 <entry><literal>to_sql_specific_name</literal></entry>
4355 <entry><type>sql_identifier</type></entry>
4356 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4360 <entry><literal>as_locator</literal></entry>
4361 <entry><type>yes_or_no</type></entry>
4362 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4366 <entry><literal>created</literal></entry>
4367 <entry><type>time_stamp</type></entry>
4368 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4372 <entry><literal>last_altered</literal></entry>
4373 <entry><type>time_stamp</type></entry>
4374 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4378 <entry><literal>new_savepoint_level</literal></entry>
4379 <entry><type>yes_or_no</type></entry>
4380 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4384 <entry><literal>is_udt_dependent</literal></entry>
4385 <entry><type>yes_or_no</type></entry>
4387 Currently always <literal>NO</literal>. The alternative
4388 <literal>YES</literal> applies to a feature not available in
4389 <productname>PostgreSQL</>.
4394 <entry><literal>result_cast_from_data_type</literal></entry>
4395 <entry><type>character_data</type></entry>
4396 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4400 <entry><literal>result_cast_as_locator</literal></entry>
4401 <entry><type>yes_or_no</type></entry>
4402 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4406 <entry><literal>result_cast_char_max_length</literal></entry>
4407 <entry><type>cardinal_number</type></entry>
4408 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4412 <entry><literal>result_cast_char_octet_length</literal></entry>
4413 <entry><type>character_data</type></entry>
4414 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4418 <entry><literal>result_cast_char_set_catalog</literal></entry>
4419 <entry><type>sql_identifier</type></entry>
4420 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4424 <entry><literal>result_cast_char_set_schema</literal></entry>
4425 <entry><type>sql_identifier</type></entry>
4426 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4430 <entry><literal>result_cast_char_set_name</literal></entry>
4431 <entry><type>sql_identifier</type></entry>
4432 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4436 <entry><literal>result_cast_collation_catalog</literal></entry>
4437 <entry><type>sql_identifier</type></entry>
4438 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4442 <entry><literal>result_cast_collation_schema</literal></entry>
4443 <entry><type>sql_identifier</type></entry>
4444 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4448 <entry><literal>result_cast_collation_name</literal></entry>
4449 <entry><type>sql_identifier</type></entry>
4450 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4454 <entry><literal>result_cast_numeric_precision</literal></entry>
4455 <entry><type>cardinal_number</type></entry>
4456 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4460 <entry><literal>result_cast_numeric_precision_radix</literal></entry>
4461 <entry><type>cardinal_number</type></entry>
4462 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4466 <entry><literal>result_cast_numeric_scale</literal></entry>
4467 <entry><type>cardinal_number</type></entry>
4468 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4472 <entry><literal>result_cast_datetime_precision</literal></entry>
4473 <entry><type>character_data</type></entry>
4474 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4478 <entry><literal>result_cast_interval_type</literal></entry>
4479 <entry><type>character_data</type></entry>
4480 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4484 <entry><literal>result_cast_interval_precision</literal></entry>
4485 <entry><type>cardinal_number</type></entry>
4486 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4490 <entry><literal>result_cast_type_udt_catalog</literal></entry>
4491 <entry><type>sql_identifier</type></entry>
4492 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4496 <entry><literal>result_cast_type_udt_schema</literal></entry>
4497 <entry><type>sql_identifier</type></entry>
4498 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4502 <entry><literal>result_cast_type_udt_name</literal></entry>
4503 <entry><type>sql_identifier</type></entry>
4504 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4508 <entry><literal>result_cast_scope_catalog</literal></entry>
4509 <entry><type>sql_identifier</type></entry>
4510 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4514 <entry><literal>result_cast_scope_schema</literal></entry>
4515 <entry><type>sql_identifier</type></entry>
4516 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4520 <entry><literal>result_cast_scope_name</literal></entry>
4521 <entry><type>sql_identifier</type></entry>
4522 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4526 <entry><literal>result_cast_maximum_cardinality</literal></entry>
4527 <entry><type>cardinal_number</type></entry>
4528 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4532 <entry><literal>result_cast_dtd_identifier</literal></entry>
4533 <entry><type>sql_identifier</type></entry>
4534 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4541 <sect1 id="infoschema-schemata">
4542 <title><literal>schemata</literal></title>
4545 The view <literal>schemata</literal> contains all schemas in the
4546 current database that are owned by a currently enabled role.
4550 <title><literal>schemata</literal> Columns</title>
4556 <entry>Data Type</entry>
4557 <entry>Description</entry>
4563 <entry><literal>catalog_name</literal></entry>
4564 <entry><type>sql_identifier</type></entry>
4565 <entry>Name of the database that the schema is contained in (always the current database)</entry>
4569 <entry><literal>schema_name</literal></entry>
4570 <entry><type>sql_identifier</type></entry>
4571 <entry>Name of the schema</entry>
4575 <entry><literal>schema_owner</literal></entry>
4576 <entry><type>sql_identifier</type></entry>
4577 <entry>Name of the owner of the schema</entry>
4581 <entry><literal>default_character_set_catalog</literal></entry>
4582 <entry><type>sql_identifier</type></entry>
4583 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4587 <entry><literal>default_character_set_schema</literal></entry>
4588 <entry><type>sql_identifier</type></entry>
4589 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4593 <entry><literal>default_character_set_name</literal></entry>
4594 <entry><type>sql_identifier</type></entry>
4595 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4599 <entry><literal>sql_path</literal></entry>
4600 <entry><type>character_data</type></entry>
4601 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4608 <sect1 id="infoschema-sequences">
4609 <title><literal>sequences</literal></title>
4612 The view <literal>sequences</literal> contains all sequences
4613 defined in the current database. Only those sequences are shown
4614 that the current user has access to (by way of being the owner or
4615 having some privilege).
4619 <title><literal>sequences</literal> Columns</title>
4625 <entry>Data Type</entry>
4626 <entry>Description</entry>
4632 <entry><literal>sequence_catalog</literal></entry>
4633 <entry><type>sql_identifier</type></entry>
4634 <entry>Name of the database that contains the sequence (always the current database)</entry>
4638 <entry><literal>sequence_schema</literal></entry>
4639 <entry><type>sql_identifier</type></entry>
4640 <entry>Name of the schema that contains the sequence</entry>
4644 <entry><literal>sequence_name</literal></entry>
4645 <entry><type>sql_identifier</type></entry>
4646 <entry>Name of the sequence</entry>
4650 <entry><literal>data_type</literal></entry>
4651 <entry><type>character_data</type></entry>
4653 The data type of the sequence. In
4654 <productname>PostgreSQL</productname>, this is currently always
4655 <literal>bigint</literal>.
4660 <entry><literal>numeric_precision</literal></entry>
4661 <entry><type>cardinal_number</type></entry>
4663 This column contains the (declared or implicit) precision of
4664 the sequence data type (see above). The precision indicates
4665 the number of significant digits. It can be expressed in
4666 decimal (base 10) or binary (base 2) terms, as specified in the
4667 column <literal>numeric_precision_radix</literal>.
4672 <entry><literal>numeric_precision_radix</literal></entry>
4673 <entry><type>cardinal_number</type></entry>
4675 This column indicates in which base the values in the columns
4676 <literal>numeric_precision</literal> and
4677 <literal>numeric_scale</literal> are expressed. The value is
4683 <entry><literal>numeric_scale</literal></entry>
4684 <entry><type>cardinal_number</type></entry>
4686 This column contains the (declared or implicit) scale of the
4687 sequence data type (see above). The scale indicates the number
4688 of significant digits to the right of the decimal point. It
4689 can be expressed in decimal (base 10) or binary (base 2) terms,
4690 as specified in the column
4691 <literal>numeric_precision_radix</literal>.
4696 <entry><literal>start_value</literal></entry>
4697 <entry><type>character_data</type></entry>
4698 <entry>The start value of the sequence</entry>
4702 <entry><literal>minimum_value</literal></entry>
4703 <entry><type>character_data</type></entry>
4704 <entry>The minimum value of the sequence</entry>
4708 <entry><literal>maximum_value</literal></entry>
4709 <entry><type>character_data</type></entry>
4710 <entry>The maximum value of the sequence</entry>
4714 <entry><literal>increment</literal></entry>
4715 <entry><type>character_data</type></entry>
4716 <entry>The increment of the sequence</entry>
4720 <entry><literal>cycle_option</literal></entry>
4721 <entry><type>yes_or_no</type></entry>
4722 <entry><literal>YES</literal> if the sequence cycles, else <literal>NO</literal></entry>
4729 Note that in accordance with the SQL standard, the start, minimum,
4730 maximum, and increment values are returned as character strings.
4734 <sect1 id="infoschema-sql-features">
4735 <title><literal>sql_features</literal></title>
4738 The table <literal>sql_features</literal> contains information
4739 about which formal features defined in the SQL standard are
4740 supported by <productname>PostgreSQL</productname>. This is the
4741 same information that is presented in <xref linkend="features">.
4742 There you can also find some additional background information.
4746 <title><literal>sql_features</literal> Columns</title>
4752 <entry>Data Type</entry>
4753 <entry>Description</entry>
4759 <entry><literal>feature_id</literal></entry>
4760 <entry><type>character_data</type></entry>
4761 <entry>Identifier string of the feature</entry>
4765 <entry><literal>feature_name</literal></entry>
4766 <entry><type>character_data</type></entry>
4767 <entry>Descriptive name of the feature</entry>
4771 <entry><literal>sub_feature_id</literal></entry>
4772 <entry><type>character_data</type></entry>
4773 <entry>Identifier string of the subfeature, or a zero-length string if not a subfeature</entry>
4777 <entry><literal>sub_feature_name</literal></entry>
4778 <entry><type>character_data</type></entry>
4779 <entry>Descriptive name of the subfeature, or a zero-length string if not a subfeature</entry>
4783 <entry><literal>is_supported</literal></entry>
4784 <entry><type>yes_or_no</type></entry>
4786 <literal>YES</literal> if the feature is fully supported by the
4787 current version of <productname>PostgreSQL</>, <literal>NO</literal> if not
4792 <entry><literal>is_verified_by</literal></entry>
4793 <entry><type>character_data</type></entry>
4795 Always null, since the <productname>PostgreSQL</> development group does not
4796 perform formal testing of feature conformance
4801 <entry><literal>comments</literal></entry>
4802 <entry><type>character_data</type></entry>
4803 <entry>Possibly a comment about the supported status of the feature</entry>
4810 <sect1 id="infoschema-sql-implementation-info">
4811 <title><literal>sql_implementation_info</literal></title>
4814 The table <literal>sql_implementation_info</literal> contains
4815 information about various aspects that are left
4816 implementation-defined by the SQL standard. This information is
4817 primarily intended for use in the context of the ODBC interface;
4818 users of other interfaces will probably find this information to be
4819 of little use. For this reason, the individual implementation
4820 information items are not described here; you will find them in the
4821 description of the ODBC interface.
4825 <title><literal>sql_implementation_info</literal> Columns</title>
4831 <entry>Data Type</entry>
4832 <entry>Description</entry>
4838 <entry><literal>implementation_info_id</literal></entry>
4839 <entry><type>character_data</type></entry>
4840 <entry>Identifier string of the implementation information item</entry>
4844 <entry><literal>implementation_info_name</literal></entry>
4845 <entry><type>character_data</type></entry>
4846 <entry>Descriptive name of the implementation information item</entry>
4850 <entry><literal>integer_value</literal></entry>
4851 <entry><type>cardinal_number</type></entry>
4853 Value of the implementation information item, or null if the
4854 value is contained in the column
4855 <literal>character_value</literal>
4860 <entry><literal>character_value</literal></entry>
4861 <entry><type>character_data</type></entry>
4863 Value of the implementation information item, or null if the
4864 value is contained in the column
4865 <literal>integer_value</literal>
4870 <entry><literal>comments</literal></entry>
4871 <entry><type>character_data</type></entry>
4872 <entry>Possibly a comment pertaining to the implementation information item</entry>
4879 <sect1 id="infoschema-sql-languages">
4880 <title><literal>sql_languages</literal></title>
4883 The table <literal>sql_languages</literal> contains one row for
4884 each SQL language binding that is supported by
4885 <productname>PostgreSQL</productname>.
4886 <productname>PostgreSQL</productname> supports direct SQL and
4887 embedded SQL in C; that is all you will learn from this table.
4891 This table was removed from the SQL standard in SQL:2008, so there
4892 are no entries referring to standards later than SQL:2003.
4896 <title><literal>sql_languages</literal> Columns</title>
4902 <entry>Data Type</entry>
4903 <entry>Description</entry>
4909 <entry><literal>sql_language_source</literal></entry>
4910 <entry><type>character_data</type></entry>
4912 The name of the source of the language definition; always
4913 <literal>ISO 9075</literal>, that is, the SQL standard
4918 <entry><literal>sql_language_year</literal></entry>
4919 <entry><type>character_data</type></entry>
4921 The year the standard referenced in
4922 <literal>sql_language_source</literal> was approved.
4927 <entry><literal>sql_language_conformance</literal></entry>
4928 <entry><type>character_data</type></entry>
4930 The standard conformance level for the language binding. For
4931 ISO 9075:2003 this is always <literal>CORE</literal>.
4936 <entry><literal>sql_language_integrity</literal></entry>
4937 <entry><type>character_data</type></entry>
4938 <entry>Always null (This value is relevant to an earlier version of the SQL standard.)</entry>
4942 <entry><literal>sql_language_implementation</literal></entry>
4943 <entry><type>character_data</type></entry>
4944 <entry>Always null</entry>
4948 <entry><literal>sql_language_binding_style</literal></entry>
4949 <entry><type>character_data</type></entry>
4951 The language binding style, either <literal>DIRECT</literal> or
4952 <literal>EMBEDDED</literal>
4957 <entry><literal>sql_language_programming_language</literal></entry>
4958 <entry><type>character_data</type></entry>
4960 The programming language, if the binding style is
4961 <literal>EMBEDDED</literal>, else null. <productname>PostgreSQL</> only
4962 supports the language C.
4970 <sect1 id="infoschema-sql-packages">
4971 <title><literal>sql_packages</literal></title>
4974 The table <literal>sql_packages</literal> contains information
4975 about which feature packages defined in the SQL standard are
4976 supported by <productname>PostgreSQL</productname>. Refer to <xref
4977 linkend="features"> for background information on feature packages.
4981 <title><literal>sql_packages</literal> Columns</title>
4987 <entry>Data Type</entry>
4988 <entry>Description</entry>
4994 <entry><literal>feature_id</literal></entry>
4995 <entry><type>character_data</type></entry>
4996 <entry>Identifier string of the package</entry>
5000 <entry><literal>feature_name</literal></entry>
5001 <entry><type>character_data</type></entry>
5002 <entry>Descriptive name of the package</entry>
5006 <entry><literal>is_supported</literal></entry>
5007 <entry><type>yes_or_no</type></entry>
5009 <literal>YES</literal> if the package is fully supported by the
5010 current version of <productname>PostgreSQL</>, <literal>NO</literal> if not
5015 <entry><literal>is_verified_by</literal></entry>
5016 <entry><type>character_data</type></entry>
5018 Always null, since the <productname>PostgreSQL</> development group does not
5019 perform formal testing of feature conformance
5024 <entry><literal>comments</literal></entry>
5025 <entry><type>character_data</type></entry>
5026 <entry>Possibly a comment about the supported status of the package</entry>
5033 <sect1 id="infoschema-sql-parts">
5034 <title><literal>sql_parts</literal></title>
5037 The table <literal>sql_parts</literal> contains information about
5038 which of the several parts of the SQL standard are supported by
5039 <productname>PostgreSQL</productname>.
5043 <title><literal>sql_parts</literal> Columns</title>
5049 <entry>Data Type</entry>
5050 <entry>Description</entry>
5056 <entry><literal>feature_id</literal></entry>
5057 <entry><type>character_data</type></entry>
5058 <entry>An identifier string containing the number of the part</entry>
5062 <entry><literal>feature_name</literal></entry>
5063 <entry><type>character_data</type></entry>
5064 <entry>Descriptive name of the part</entry>
5068 <entry><literal>is_supported</literal></entry>
5069 <entry><type>yes_or_no</type></entry>
5071 <literal>YES</literal> if the part is fully supported by the
5072 current version of <productname>PostgreSQL</>,
5073 <literal>NO</literal> if not
5078 <entry><literal>is_verified_by</literal></entry>
5079 <entry><type>character_data</type></entry>
5081 Always null, since the <productname>PostgreSQL</> development group does not
5082 perform formal testing of feature conformance
5087 <entry><literal>comments</literal></entry>
5088 <entry><type>character_data</type></entry>
5089 <entry>Possibly a comment about the supported status of the part</entry>
5096 <sect1 id="infoschema-sql-sizing">
5097 <title><literal>sql_sizing</literal></title>
5100 The table <literal>sql_sizing</literal> contains information about
5101 various size limits and maximum values in
5102 <productname>PostgreSQL</productname>. This information is
5103 primarily intended for use in the context of the ODBC interface;
5104 users of other interfaces will probably find this information to be
5105 of little use. For this reason, the individual sizing items are
5106 not described here; you will find them in the description of the
5111 <title><literal>sql_sizing</literal> Columns</title>
5117 <entry>Data Type</entry>
5118 <entry>Description</entry>
5124 <entry><literal>sizing_id</literal></entry>
5125 <entry><type>cardinal_number</type></entry>
5126 <entry>Identifier of the sizing item</entry>
5130 <entry><literal>sizing_name</literal></entry>
5131 <entry><type>character_data</type></entry>
5132 <entry>Descriptive name of the sizing item</entry>
5136 <entry><literal>supported_value</literal></entry>
5137 <entry><type>cardinal_number</type></entry>
5139 Value of the sizing item, or 0 if the size is unlimited or
5140 cannot be determined, or null if the features for which the
5141 sizing item is applicable are not supported
5146 <entry><literal>comments</literal></entry>
5147 <entry><type>character_data</type></entry>
5148 <entry>Possibly a comment pertaining to the sizing item</entry>
5155 <sect1 id="infoschema-sql-sizing-profiles">
5156 <title><literal>sql_sizing_profiles</literal></title>
5159 The table <literal>sql_sizing_profiles</literal> contains
5160 information about the <literal>sql_sizing</literal> values that are
5161 required by various profiles of the SQL standard. <productname>PostgreSQL</> does
5162 not track any SQL profiles, so this table is empty.
5166 <title><literal>sql_sizing_profiles</literal> Columns</title>
5172 <entry>Data Type</entry>
5173 <entry>Description</entry>
5179 <entry><literal>sizing_id</literal></entry>
5180 <entry><type>cardinal_number</type></entry>
5181 <entry>Identifier of the sizing item</entry>
5185 <entry><literal>sizing_name</literal></entry>
5186 <entry><type>character_data</type></entry>
5187 <entry>Descriptive name of the sizing item</entry>
5191 <entry><literal>profile_id</literal></entry>
5192 <entry><type>character_data</type></entry>
5193 <entry>Identifier string of a profile</entry>
5197 <entry><literal>required_value</literal></entry>
5198 <entry><type>cardinal_number</type></entry>
5200 The value required by the SQL profile for the sizing item, or 0
5201 if the profile places no limit on the sizing item, or null if
5202 the profile does not require any of the features for which the
5203 sizing item is applicable
5208 <entry><literal>comments</literal></entry>
5209 <entry><type>character_data</type></entry>
5210 <entry>Possibly a comment pertaining to the sizing item within the profile</entry>
5217 <sect1 id="infoschema-table-constraints">
5218 <title><literal>table_constraints</literal></title>
5221 The view <literal>table_constraints</literal> contains all
5222 constraints belonging to tables that the current user owns or has
5223 some privilege other than <literal>SELECT</literal> on.
5227 <title><literal>table_constraints</literal> Columns</title>
5233 <entry>Data Type</entry>
5234 <entry>Description</entry>
5240 <entry><literal>constraint_catalog</literal></entry>
5241 <entry><type>sql_identifier</type></entry>
5242 <entry>Name of the database that contains the constraint (always the current database)</entry>
5246 <entry><literal>constraint_schema</literal></entry>
5247 <entry><type>sql_identifier</type></entry>
5248 <entry>Name of the schema that contains the constraint</entry>
5252 <entry><literal>constraint_name</literal></entry>
5253 <entry><type>sql_identifier</type></entry>
5254 <entry>Name of the constraint</entry>
5258 <entry><literal>table_catalog</literal></entry>
5259 <entry><type>sql_identifier</type></entry>
5260 <entry>Name of the database that contains the table (always the current database)</entry>
5264 <entry><literal>table_schema</literal></entry>
5265 <entry><type>sql_identifier</type></entry>
5266 <entry>Name of the schema that contains the table</entry>
5270 <entry><literal>table_name</literal></entry>
5271 <entry><type>sql_identifier</type></entry>
5272 <entry>Name of the table</entry>
5276 <entry><literal>constraint_type</literal></entry>
5277 <entry><type>character_data</type></entry>
5279 Type of the constraint: <literal>CHECK</literal>,
5280 <literal>FOREIGN KEY</literal>, <literal>PRIMARY KEY</literal>,
5281 or <literal>UNIQUE</literal>
5286 <entry><literal>is_deferrable</literal></entry>
5287 <entry><type>yes_or_no</type></entry>
5288 <entry><literal>YES</literal> if the constraint is deferrable, <literal>NO</literal> if not</entry>
5292 <entry><literal>initially_deferred</literal></entry>
5293 <entry><type>yes_or_no</type></entry>
5294 <entry><literal>YES</literal> if the constraint is deferrable and initially deferred, <literal>NO</literal> if not</entry>
5301 <sect1 id="infoschema-table-privileges">
5302 <title><literal>table_privileges</literal></title>
5305 The view <literal>table_privileges</literal> identifies all
5306 privileges granted on tables or views to a currently enabled role
5307 or by a currently enabled role. There is one row for each
5308 combination of table, grantor, and grantee.
5312 <title><literal>table_privileges</literal> Columns</title>
5318 <entry>Data Type</entry>
5319 <entry>Description</entry>
5325 <entry><literal>grantor</literal></entry>
5326 <entry><type>sql_identifier</type></entry>
5327 <entry>Name of the role that granted the privilege</entry>
5331 <entry><literal>grantee</literal></entry>
5332 <entry><type>sql_identifier</type></entry>
5333 <entry>Name of the role that the privilege was granted to</entry>
5337 <entry><literal>table_catalog</literal></entry>
5338 <entry><type>sql_identifier</type></entry>
5339 <entry>Name of the database that contains the table (always the current database)</entry>
5343 <entry><literal>table_schema</literal></entry>
5344 <entry><type>sql_identifier</type></entry>
5345 <entry>Name of the schema that contains the table</entry>
5349 <entry><literal>table_name</literal></entry>
5350 <entry><type>sql_identifier</type></entry>
5351 <entry>Name of the table</entry>
5355 <entry><literal>privilege_type</literal></entry>
5356 <entry><type>character_data</type></entry>
5358 Type of the privilege: <literal>SELECT</literal>,
5359 <literal>INSERT</literal>, <literal>UPDATE</literal>,
5360 <literal>DELETE</literal>, <literal>TRUNCATE</literal>,
5361 <literal>REFERENCES</literal>, or <literal>TRIGGER</literal>
5366 <entry><literal>is_grantable</literal></entry>
5367 <entry><type>yes_or_no</type></entry>
5368 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
5372 <entry><literal>with_hierarchy</literal></entry>
5373 <entry><type>yes_or_no</type></entry>
5375 In the SQL standard, <literal>WITH HIERARCHY OPTION</literal>
5376 is a separate (sub-)privilege allowing certain operations on
5377 table inheritance hierarchies. In PostgreSQL, this is included
5378 in the <literal>SELECT</literal> privilege, so this column
5379 shows <literal>YES</literal> if the privilege
5380 is <literal>SELECT</literal>, else <literal>NO</literal>.
5388 <sect1 id="infoschema-tables">
5389 <title><literal>tables</literal></title>
5392 The view <literal>tables</literal> contains all tables and views
5393 defined in the current database. Only those tables and views are
5394 shown that the current user has access to (by way of being the
5395 owner or having some privilege).
5399 <title><literal>tables</literal> Columns</title>
5405 <entry>Data Type</entry>
5406 <entry>Description</entry>
5412 <entry><literal>table_catalog</literal></entry>
5413 <entry><type>sql_identifier</type></entry>
5414 <entry>Name of the database that contains the table (always the current database)</entry>
5418 <entry><literal>table_schema</literal></entry>
5419 <entry><type>sql_identifier</type></entry>
5420 <entry>Name of the schema that contains the table</entry>
5424 <entry><literal>table_name</literal></entry>
5425 <entry><type>sql_identifier</type></entry>
5426 <entry>Name of the table</entry>
5430 <entry><literal>table_type</literal></entry>
5431 <entry><type>character_data</type></entry>
5433 Type of the table: <literal>BASE TABLE</literal> for a
5434 persistent base table (the normal table type),
5435 <literal>VIEW</literal> for a view, <literal>FOREIGN TABLE</literal>
5436 for a foreign table, or
5437 <literal>LOCAL TEMPORARY</literal> for a temporary table
5442 <entry><literal>self_referencing_column_name</literal></entry>
5443 <entry><type>sql_identifier</type></entry>
5444 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5448 <entry><literal>reference_generation</literal></entry>
5449 <entry><type>character_data</type></entry>
5450 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5454 <entry><literal>user_defined_type_catalog</literal></entry>
5455 <entry><type>sql_identifier</type></entry>
5457 If the table is a typed table, the name of the database that
5458 contains the underlying data type (always the current
5459 database), else null.
5464 <entry><literal>user_defined_type_schema</literal></entry>
5465 <entry><type>sql_identifier</type></entry>
5467 If the table is a typed table, the name of the schema that
5468 contains the underlying data type, else null.
5473 <entry><literal>user_defined_type_name</literal></entry>
5474 <entry><type>sql_identifier</type></entry>
5476 If the table is a typed table, the name of the underlying data
5482 <entry><literal>is_insertable_into</literal></entry>
5483 <entry><type>yes_or_no</type></entry>
5485 <literal>YES</literal> if the table is insertable into,
5486 <literal>NO</literal> if not (Base tables are always insertable
5487 into, views not necessarily.)
5492 <entry><literal>is_typed</literal></entry>
5493 <entry><type>yes_or_no</type></entry>
5494 <entry><literal>YES</literal> if the table is a typed table, <literal>NO</literal> if not</entry>
5498 <entry><literal>commit_action</literal></entry>
5499 <entry><type>character_data</type></entry>
5500 <entry>Not yet implemented</entry>
5507 <sect1 id="infoschema-triggered-update-columns">
5508 <title><literal>triggered_update_columns</literal></title>
5511 For triggers in the current database that specify a column list
5512 (like <literal>UPDATE OF column1, column2</literal>), the
5513 view <literal>triggered_update_columns</literal> identifies these
5514 columns. Triggers that do not specify a column list are not
5515 included in this view. Only those columns are shown that the
5516 current user owns or has some privilege other than
5517 <literal>SELECT</literal> on.
5521 <title><literal>triggered_update_columns</literal> Columns</title>
5527 <entry>Data Type</entry>
5528 <entry>Description</entry>
5534 <entry><literal>trigger_catalog</literal></entry>
5535 <entry><type>sql_identifier</type></entry>
5536 <entry>Name of the database that contains the trigger (always the current database)</entry>
5540 <entry><literal>trigger_schema</literal></entry>
5541 <entry><type>sql_identifier</type></entry>
5542 <entry>Name of the schema that contains the trigger</entry>
5546 <entry><literal>trigger_name</literal></entry>
5547 <entry><type>sql_identifier</type></entry>
5548 <entry>Name of the trigger</entry>
5552 <entry><literal>event_object_catalog</literal></entry>
5553 <entry><type>sql_identifier</type></entry>
5555 Name of the database that contains the table that the trigger
5556 is defined on (always the current database)
5561 <entry><literal>event_object_schema</literal></entry>
5562 <entry><type>sql_identifier</type></entry>
5563 <entry>Name of the schema that contains the table that the trigger is defined on</entry>
5567 <entry><literal>event_object_table</literal></entry>
5568 <entry><type>sql_identifier</type></entry>
5569 <entry>Name of the table that the trigger is defined on</entry>
5573 <entry><literal>event_object_column</literal></entry>
5574 <entry><type>sql_identifier</type></entry>
5575 <entry>Name of the column that the trigger is defined on</entry>
5582 <sect1 id="infoschema-triggers">
5583 <title><literal>triggers</literal></title>
5586 The view <literal>triggers</literal> contains all triggers defined
5587 in the current database on tables and views that the current user owns
5588 or has some privilege other than <literal>SELECT</literal> on.
5592 <title><literal>triggers</literal> Columns</title>
5598 <entry>Data Type</entry>
5599 <entry>Description</entry>
5605 <entry><literal>trigger_catalog</literal></entry>
5606 <entry><type>sql_identifier</type></entry>
5607 <entry>Name of the database that contains the trigger (always the current database)</entry>
5611 <entry><literal>trigger_schema</literal></entry>
5612 <entry><type>sql_identifier</type></entry>
5613 <entry>Name of the schema that contains the trigger</entry>
5617 <entry><literal>trigger_name</literal></entry>
5618 <entry><type>sql_identifier</type></entry>
5619 <entry>Name of the trigger</entry>
5623 <entry><literal>event_manipulation</literal></entry>
5624 <entry><type>character_data</type></entry>
5626 Event that fires the trigger (<literal>INSERT</literal>,
5627 <literal>UPDATE</literal>, or <literal>DELETE</literal>)
5632 <entry><literal>event_object_catalog</literal></entry>
5633 <entry><type>sql_identifier</type></entry>
5635 Name of the database that contains the table that the trigger
5636 is defined on (always the current database)
5641 <entry><literal>event_object_schema</literal></entry>
5642 <entry><type>sql_identifier</type></entry>
5643 <entry>Name of the schema that contains the table that the trigger is defined on</entry>
5647 <entry><literal>event_object_table</literal></entry>
5648 <entry><type>sql_identifier</type></entry>
5649 <entry>Name of the table that the trigger is defined on</entry>
5653 <entry><literal>action_order</literal></entry>
5654 <entry><type>cardinal_number</type></entry>
5655 <entry>Not yet implemented</entry>
5659 <entry><literal>action_condition</literal></entry>
5660 <entry><type>character_data</type></entry>
5662 <literal>WHEN</literal> condition of the trigger, null if none
5663 (also null if the table is not owned by a currently enabled
5669 <entry><literal>action_statement</literal></entry>
5670 <entry><type>character_data</type></entry>
5672 Statement that is executed by the trigger (currently always
5673 <literal>EXECUTE PROCEDURE
5674 <replaceable>function</replaceable>(...)</literal>)
5679 <entry><literal>action_orientation</literal></entry>
5680 <entry><type>character_data</type></entry>
5682 Identifies whether the trigger fires once for each processed
5683 row or once for each statement (<literal>ROW</literal> or
5684 <literal>STATEMENT</literal>)
5689 <entry><literal>action_timing</literal></entry>
5690 <entry><type>character_data</type></entry>
5692 Time at which the trigger fires (<literal>BEFORE</literal>,
5693 <literal>AFTER</literal>, or <literal>INSTEAD OF</literal>)
5698 <entry><literal>action_reference_old_table</literal></entry>
5699 <entry><type>sql_identifier</type></entry>
5700 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5704 <entry><literal>action_reference_new_table</literal></entry>
5705 <entry><type>sql_identifier</type></entry>
5706 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5710 <entry><literal>action_reference_old_row</literal></entry>
5711 <entry><type>sql_identifier</type></entry>
5712 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5716 <entry><literal>action_reference_new_row</literal></entry>
5717 <entry><type>sql_identifier</type></entry>
5718 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5722 <entry><literal>created</literal></entry>
5723 <entry><type>time_stamp</type></entry>
5724 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5731 Triggers in <productname>PostgreSQL</productname> have two
5732 incompatibilities with the SQL standard that affect the
5733 representation in the information schema. First, trigger names are
5734 local to each table in <productname>PostgreSQL</productname>, rather
5735 than being independent schema objects. Therefore there can be duplicate
5736 trigger names defined in one schema, so long as they belong to
5737 different tables. (<literal>trigger_catalog</literal> and
5738 <literal>trigger_schema</literal> are really the values pertaining
5739 to the table that the trigger is defined on.) Second, triggers can
5740 be defined to fire on multiple events in
5741 <productname>PostgreSQL</productname> (e.g., <literal>ON INSERT OR
5742 UPDATE</literal>), whereas the SQL standard only allows one. If a
5743 trigger is defined to fire on multiple events, it is represented as
5744 multiple rows in the information schema, one for each type of
5745 event. As a consequence of these two issues, the primary key of
5746 the view <literal>triggers</literal> is really
5747 <literal>(trigger_catalog, trigger_schema, event_object_table,
5748 trigger_name, event_manipulation)</literal> instead of
5749 <literal>(trigger_catalog, trigger_schema, trigger_name)</literal>,
5750 which is what the SQL standard specifies. Nonetheless, if you
5751 define your triggers in a manner that conforms with the SQL
5752 standard (trigger names unique in the schema and only one event
5753 type per trigger), this will not affect you.
5758 Prior to <productname>PostgreSQL</> 9.1, this view's columns
5759 <structfield>action_timing</structfield>,
5760 <structfield>action_reference_old_table</structfield>,
5761 <structfield>action_reference_new_table</structfield>,
5762 <structfield>action_reference_old_row</structfield>, and
5763 <structfield>action_reference_new_row</structfield>
5765 <structfield>condition_timing</structfield>,
5766 <structfield>condition_reference_old_table</structfield>,
5767 <structfield>condition_reference_new_table</structfield>,
5768 <structfield>condition_reference_old_row</structfield>, and
5769 <structfield>condition_reference_new_row</structfield>
5771 That was how they were named in the SQL:1999 standard.
5772 The new naming conforms to SQL:2003 and later.
5777 <sect1 id="infoschema-udt-privileges">
5778 <title><literal>udt_privileges</literal></title>
5781 The view <literal>udt_privileges</literal> is intended to identify
5782 <literal>USAGE</literal> privileges granted on user-defined types
5783 to a currently enabled role or by a currently enabled role. Since
5784 data types do not have real privileges
5785 in <productname>PostgreSQL</productname>, this view shows implicit
5786 non-grantable <literal>USAGE</literal> privileges granted by the
5787 owner to <literal>PUBLIC</literal> for all types, including
5788 built-in ones (except domains,
5789 see <xref linkend="infoschema-usage-privileges"> for that).
5793 <title><literal>udt_privileges</literal> Columns</title>
5799 <entry>Data Type</entry>
5800 <entry>Description</entry>
5806 <entry><literal>grantor</literal></entry>
5807 <entry><type>sql_identifier</type></entry>
5808 <entry>Name of the role that granted the privilege</entry>
5812 <entry><literal>grantee</literal></entry>
5813 <entry><type>sql_identifier</type></entry>
5814 <entry>Name of the role that the privilege was granted to</entry>
5818 <entry><literal>udt_catalog</literal></entry>
5819 <entry><type>sql_identifier</type></entry>
5820 <entry>Name of the database containing the type (always the current database)</entry>
5824 <entry><literal>udt_schema</literal></entry>
5825 <entry><type>sql_identifier</type></entry>
5826 <entry>Name of the schema containing the type</entry>
5830 <entry><literal>udt_name</literal></entry>
5831 <entry><type>sql_identifier</type></entry>
5832 <entry>Name of the type</entry>
5836 <entry><literal>privilege_type</literal></entry>
5837 <entry><type>character_data</type></entry>
5838 <entry>Always <literal>TYPE USAGE</literal></entry>
5842 <entry><literal>is_grantable</literal></entry>
5843 <entry><type>yes_or_no</type></entry>
5844 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
5851 <sect1 id="infoschema-usage-privileges">
5852 <title><literal>usage_privileges</literal></title>
5855 The view <literal>usage_privileges</literal> identifies
5856 <literal>USAGE</literal> privileges granted on various kinds of
5857 objects to a currently enabled role or by a currently enabled role.
5858 In <productname>PostgreSQL</productname>, this currently applies to
5859 collations, domains, foreign-data wrappers, and foreign servers. There is one
5860 row for each combination of object, grantor, and grantee.
5864 Since collations and domains do not have real privileges
5865 in <productname>PostgreSQL</productname>, this view shows implicit
5866 non-grantable <literal>USAGE</literal> privileges granted by the
5867 owner to <literal>PUBLIC</literal> for all collations and domains. The other
5868 object types, however, show real privileges.
5872 <title><literal>usage_privileges</literal> Columns</title>
5878 <entry>Data Type</entry>
5879 <entry>Description</entry>
5885 <entry><literal>grantor</literal></entry>
5886 <entry><type>sql_identifier</type></entry>
5887 <entry>Name of the role that granted the privilege</entry>
5891 <entry><literal>grantee</literal></entry>
5892 <entry><type>sql_identifier</type></entry>
5893 <entry>Name of the role that the privilege was granted to</entry>
5897 <entry><literal>object_catalog</literal></entry>
5898 <entry><type>sql_identifier</type></entry>
5899 <entry>Name of the database containing the object (always the current database)</entry>
5903 <entry><literal>object_schema</literal></entry>
5904 <entry><type>sql_identifier</type></entry>
5905 <entry>Name of the schema containing the object, if applicable,
5906 else an empty string</entry>
5910 <entry><literal>object_name</literal></entry>
5911 <entry><type>sql_identifier</type></entry>
5912 <entry>Name of the object</entry>
5916 <entry><literal>object_type</literal></entry>
5917 <entry><type>character_data</type></entry>
5918 <entry><literal>COLLATION</literal> or <literal>DOMAIN</literal> or <literal>FOREIGN DATA WRAPPER</literal> or <literal>FOREIGN SERVER</literal></entry>
5922 <entry><literal>privilege_type</literal></entry>
5923 <entry><type>character_data</type></entry>
5924 <entry>Always <literal>USAGE</literal></entry>
5928 <entry><literal>is_grantable</literal></entry>
5929 <entry><type>yes_or_no</type></entry>
5930 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
5937 <sect1 id="infoschema-user-defined-types">
5938 <title><literal>user_defined_types</literal></title>
5941 The view <literal>user_defined_types</literal> currently contains
5942 all composite types defined in the current database.
5946 SQL knows about two kinds of user-defined types: structured types
5947 (also known as composite types
5948 in <productname>PostgreSQL</productname>) and distinct types (not
5949 implemented in <productname>PostgreSQL</productname>). To be
5950 future-proof, use the
5951 column <literal>user_defined_type_category</literal> to
5952 differentiate between these. Other user-defined types such as base
5953 types and enums, which are <productname>PostgreSQL</productname>
5954 extensions, are not shown here. For domains,
5955 see <xref linkend="infoschema-domains"> instead.
5959 <title><literal>user_defined_types</literal> Columns</title>
5965 <entry>Data Type</entry>
5966 <entry>Description</entry>
5972 <entry><literal>user_defined_type_catalog</literal></entry>
5973 <entry><type>sql_identifier</type></entry>
5974 <entry>Name of the database that contains the type (always the current database)</entry>
5978 <entry><literal>user_defined_type_schema</literal></entry>
5979 <entry><type>sql_identifier</type></entry>
5980 <entry>Name of the schema that contains the type</entry>
5984 <entry><literal>user_defined_type_name</literal></entry>
5985 <entry><type>sql_identifier</type></entry>
5986 <entry>Name of the type</entry>
5990 <entry><literal>user_defined_type_category</literal></entry>
5991 <entry><type>character_data</type></entry>
5993 Currently always <literal>STRUCTURED</literal>
5998 <entry><literal>is_instantiable</literal></entry>
5999 <entry><type>yes_or_no</type></entry>
6000 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6004 <entry><literal>is_final</literal></entry>
6005 <entry><type>yes_or_no</type></entry>
6006 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6010 <entry><literal>ordering_form</literal></entry>
6011 <entry><type>character_data</type></entry>
6012 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6016 <entry><literal>ordering_category</literal></entry>
6017 <entry><type>character_data</type></entry>
6018 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6022 <entry><literal>ordering_routine_catalog</literal></entry>
6023 <entry><type>sql_identifier</type></entry>
6024 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6028 <entry><literal>ordering_routine_schema</literal></entry>
6029 <entry><type>sql_identifier</type></entry>
6030 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6034 <entry><literal>ordering_routine_name</literal></entry>
6035 <entry><type>sql_identifier</type></entry>
6036 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6040 <entry><literal>reference_type</literal></entry>
6041 <entry><type>character_data</type></entry>
6042 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6046 <entry><literal>data_type</literal></entry>
6047 <entry><type>character_data</type></entry>
6048 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6052 <entry><literal>character_maximum_length</literal></entry>
6053 <entry><type>cardinal_number</type></entry>
6054 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6058 <entry><literal>character_octet_length</literal></entry>
6059 <entry><type>cardinal_number</type></entry>
6060 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6064 <entry><literal>character_set_catalog</literal></entry>
6065 <entry><type>sql_identifier</type></entry>
6066 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6070 <entry><literal>character_set_schema</literal></entry>
6071 <entry><type>sql_identifier</type></entry>
6072 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6076 <entry><literal>character_set_name</literal></entry>
6077 <entry><type>sql_identifier</type></entry>
6078 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6082 <entry><literal>collation_catalog</literal></entry>
6083 <entry><type>sql_identifier</type></entry>
6084 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6088 <entry><literal>collation_schema</literal></entry>
6089 <entry><type>sql_identifier</type></entry>
6090 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6094 <entry><literal>collation_name</literal></entry>
6095 <entry><type>sql_identifier</type></entry>
6096 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6100 <entry><literal>numeric_precision</literal></entry>
6101 <entry><type>cardinal_number</type></entry>
6102 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6106 <entry><literal>numeric_precision_radix</literal></entry>
6107 <entry><type>cardinal_number</type></entry>
6108 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6112 <entry><literal>numeric_scale</literal></entry>
6113 <entry><type>cardinal_number</type></entry>
6114 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6118 <entry><literal>datetime_precision</literal></entry>
6119 <entry><type>cardinal_number</type></entry>
6120 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6124 <entry><literal>interval_type</literal></entry>
6125 <entry><type>character_data</type></entry>
6126 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6130 <entry><literal>interval_precision</literal></entry>
6131 <entry><type>cardinal_number</type></entry>
6132 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6136 <entry><literal>source_dtd_identifier</literal></entry>
6137 <entry><type>sql_identifier</type></entry>
6138 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6142 <entry><literal>ref_dtd_identifier</literal></entry>
6143 <entry><type>sql_identifier</type></entry>
6144 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6151 <sect1 id="infoschema-user-mapping-options">
6152 <title><literal>user_mapping_options</literal></title>
6155 The view <literal>user_mapping_options</literal> contains all the
6156 options defined for user mappings in the current database. Only
6157 those user mappings are shown where the current user has access to
6158 the corresponding foreign server (by way of being the owner or
6159 having some privilege).
6163 <title><literal>user_mapping_options</literal> Columns</title>
6169 <entry>Data Type</entry>
6170 <entry>Description</entry>
6176 <entry><literal>authorization_identifier</literal></entry>
6177 <entry><type>sql_identifier</type></entry>
6178 <entry>Name of the user being mapped,
6179 or <literal>PUBLIC</literal> if the mapping is public</entry>
6183 <entry><literal>foreign_server_catalog</literal></entry>
6184 <entry><type>sql_identifier</type></entry>
6185 <entry>Name of the database that the foreign server used by this
6186 mapping is defined in (always the current database)</entry>
6190 <entry><literal>foreign_server_name</literal></entry>
6191 <entry><type>sql_identifier</type></entry>
6192 <entry>Name of the foreign server used by this mapping</entry>
6196 <entry><literal>option_name</literal></entry>
6197 <entry><type>sql_identifier</type></entry>
6198 <entry>Name of an option</entry>
6202 <entry><literal>option_value</literal></entry>
6203 <entry><type>character_data</type></entry>
6204 <entry>Value of the option. This column will show as null
6205 unless the current user is the user being mapped, or the mapping
6206 is for <literal>PUBLIC</literal> and the current user is the
6207 server owner, or the current user is a superuser. The intent is
6208 to protect password information stored as user mapping
6216 <sect1 id="infoschema-user-mappings">
6217 <title><literal>user_mappings</literal></title>
6220 The view <literal>user_mappings</literal> contains all user
6221 mappings defined in the current database. Only those user mappings
6222 are shown where the current user has access to the corresponding
6223 foreign server (by way of being the owner or having some
6228 <title><literal>user_mappings</literal> Columns</title>
6234 <entry>Data Type</entry>
6235 <entry>Description</entry>
6241 <entry><literal>authorization_identifier</literal></entry>
6242 <entry><type>sql_identifier</type></entry>
6243 <entry>Name of the user being mapped,
6244 or <literal>PUBLIC</literal> if the mapping is public</entry>
6248 <entry><literal>foreign_server_catalog</literal></entry>
6249 <entry><type>sql_identifier</type></entry>
6250 <entry>Name of the database that the foreign server used by this
6251 mapping is defined in (always the current database)</entry>
6255 <entry><literal>foreign_server_name</literal></entry>
6256 <entry><type>sql_identifier</type></entry>
6257 <entry>Name of the foreign server used by this mapping</entry>
6264 <sect1 id="infoschema-view-column-usage">
6265 <title><literal>view_column_usage</literal></title>
6268 The view <literal>view_column_usage</literal> identifies all
6269 columns that are used in the query expression of a view (the
6270 <command>SELECT</command> statement that defines the view). A
6271 column is only included if the table that contains the column is
6272 owned by a currently enabled role.
6277 Columns of system tables are not included. This should be fixed
6283 <title><literal>view_column_usage</literal> Columns</title>
6289 <entry>Data Type</entry>
6290 <entry>Description</entry>
6296 <entry><literal>view_catalog</literal></entry>
6297 <entry><type>sql_identifier</type></entry>
6298 <entry>Name of the database that contains the view (always the current database)</entry>
6302 <entry><literal>view_schema</literal></entry>
6303 <entry><type>sql_identifier</type></entry>
6304 <entry>Name of the schema that contains the view</entry>
6308 <entry><literal>view_name</literal></entry>
6309 <entry><type>sql_identifier</type></entry>
6310 <entry>Name of the view</entry>
6314 <entry><literal>table_catalog</literal></entry>
6315 <entry><type>sql_identifier</type></entry>
6317 Name of the database that contains the table that contains the
6318 column that is used by the view (always the current database)
6323 <entry><literal>table_schema</literal></entry>
6324 <entry><type>sql_identifier</type></entry>
6326 Name of the schema that contains the table that contains the
6327 column that is used by the view
6332 <entry><literal>table_name</literal></entry>
6333 <entry><type>sql_identifier</type></entry>
6335 Name of the table that contains the column that is used by the
6341 <entry><literal>column_name</literal></entry>
6342 <entry><type>sql_identifier</type></entry>
6343 <entry>Name of the column that is used by the view</entry>
6350 <sect1 id="infoschema-view-routine-usage">
6351 <title><literal>view_routine_usage</literal></title>
6354 The view <literal>view_routine_usage</literal> identifies all
6355 routines (functions and procedures) that are used in the query
6356 expression of a view (the <command>SELECT</command> statement that
6357 defines the view). A routine is only included if that routine is
6358 owned by a currently enabled role.
6362 <title><literal>view_routine_usage</literal> Columns</title>
6368 <entry>Data Type</entry>
6369 <entry>Description</entry>
6375 <entry><literal>table_catalog</literal></entry>
6376 <entry><literal>sql_identifier</literal></entry>
6377 <entry>Name of the database containing the view (always the current database)</entry>
6381 <entry><literal>table_schema</literal></entry>
6382 <entry><literal>sql_identifier</literal></entry>
6383 <entry>Name of the schema containing the view</entry>
6387 <entry><literal>table_name</literal></entry>
6388 <entry><literal>sql_identifier</literal></entry>
6389 <entry>Name of the view</entry>
6393 <entry><literal>specific_catalog</literal></entry>
6394 <entry><literal>sql_identifier</literal></entry>
6395 <entry>Name of the database containing the function (always the current database)</entry>
6399 <entry><literal>specific_schema</literal></entry>
6400 <entry><literal>sql_identifier</literal></entry>
6401 <entry>Name of the schema containing the function</entry>
6405 <entry><literal>specific_name</literal></entry>
6406 <entry><literal>sql_identifier</literal></entry>
6408 The <quote>specific name</quote> of the function. See <xref
6409 linkend="infoschema-routines"> for more information.
6417 <sect1 id="infoschema-view-table-usage">
6418 <title><literal>view_table_usage</literal></title>
6421 The view <literal>view_table_usage</literal> identifies all tables
6422 that are used in the query expression of a view (the
6423 <command>SELECT</command> statement that defines the view). A
6424 table is only included if that table is owned by a currently
6430 System tables are not included. This should be fixed sometime.
6435 <title><literal>view_table_usage</literal> Columns</title>
6441 <entry>Data Type</entry>
6442 <entry>Description</entry>
6448 <entry><literal>view_catalog</literal></entry>
6449 <entry><type>sql_identifier</type></entry>
6450 <entry>Name of the database that contains the view (always the current database)</entry>
6454 <entry><literal>view_schema</literal></entry>
6455 <entry><type>sql_identifier</type></entry>
6456 <entry>Name of the schema that contains the view</entry>
6460 <entry><literal>view_name</literal></entry>
6461 <entry><type>sql_identifier</type></entry>
6462 <entry>Name of the view</entry>
6466 <entry><literal>table_catalog</literal></entry>
6467 <entry><type>sql_identifier</type></entry>
6469 Name of the database that contains the table that is
6470 used by the view (always the current database)
6475 <entry><literal>table_schema</literal></entry>
6476 <entry><type>sql_identifier</type></entry>
6478 Name of the schema that contains the table that is used by the
6484 <entry><literal>table_name</literal></entry>
6485 <entry><type>sql_identifier</type></entry>
6487 Name of the table that is used by the view
6495 <sect1 id="infoschema-views">
6496 <title><literal>views</literal></title>
6499 The view <literal>views</literal> contains all views defined in the
6500 current database. Only those views are shown that the current user
6501 has access to (by way of being the owner or having some privilege).
6505 <title><literal>views</literal> Columns</title>
6511 <entry>Data Type</entry>
6512 <entry>Description</entry>
6518 <entry><literal>table_catalog</literal></entry>
6519 <entry><type>sql_identifier</type></entry>
6520 <entry>Name of the database that contains the view (always the current database)</entry>
6524 <entry><literal>table_schema</literal></entry>
6525 <entry><type>sql_identifier</type></entry>
6526 <entry>Name of the schema that contains the view</entry>
6530 <entry><literal>table_name</literal></entry>
6531 <entry><type>sql_identifier</type></entry>
6532 <entry>Name of the view</entry>
6536 <entry><literal>view_definition</literal></entry>
6537 <entry><type>character_data</type></entry>
6539 Query expression defining the view (null if the view is not
6540 owned by a currently enabled role)
6545 <entry><literal>check_option</literal></entry>
6546 <entry><type>character_data</type></entry>
6547 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6551 <entry><literal>is_updatable</literal></entry>
6552 <entry><type>yes_or_no</type></entry>
6554 <literal>YES</literal> if the view is updatable (allows
6555 <command>UPDATE</command> and <command>DELETE</command>),
6556 <literal>NO</literal> if not
6561 <entry><literal>is_insertable_into</literal></entry>
6562 <entry><type>yes_or_no</type></entry>
6564 <literal>YES</literal> if the view is insertable into (allows
6565 <command>INSERT</command>), <literal>NO</literal> if not
6570 <entry><literal>is_trigger_updatable</literal></entry>
6571 <entry><type>yes_or_no</type></entry>
6573 <literal>YES</> if the view has an <literal>INSTEAD OF</>
6574 <command>UPDATE</> trigger defined on it, <literal>NO</> if not
6579 <entry><literal>is_trigger_deletable</literal></entry>
6580 <entry><type>yes_or_no</type></entry>
6582 <literal>YES</> if the view has an <literal>INSTEAD OF</>
6583 <command>DELETE</> trigger defined on it, <literal>NO</> if not
6588 <entry><literal>is_trigger_insertable_into</literal></entry>
6589 <entry><type>yes_or_no</type></entry>
6591 <literal>YES</> if the view has an <literal>INSTEAD OF</>
6592 <command>INSERT</> trigger defined on it, <literal>NO</> if not