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>
486 <entry>Not yet implemented</entry>
490 <entry><literal>interval_precision</literal></entry>
491 <entry><type>character_data</type></entry>
492 <entry>Not yet implemented</entry>
496 <entry><literal>attribute_udt_catalog</literal></entry>
497 <entry><type>sql_identifier</type></entry>
499 Name of the database that the attribute data type is defined in
500 (always the current database)
505 <entry><literal>attribute_udt_schema</literal></entry>
506 <entry><type>sql_identifier</type></entry>
508 Name of the schema that the attribute data type is defined in
513 <entry><literal>attribute_udt_name</literal></entry>
514 <entry><type>sql_identifier</type></entry>
516 Name of the attribute data type
521 <entry><literal>scope_catalog</literal></entry>
522 <entry><type>sql_identifier</type></entry>
523 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
527 <entry><literal>scope_schema</literal></entry>
528 <entry><type>sql_identifier</type></entry>
529 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
533 <entry><literal>scope_name</literal></entry>
534 <entry><type>sql_identifier</type></entry>
535 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
539 <entry><literal>maximum_cardinality</literal></entry>
540 <entry><type>cardinal_number</type></entry>
541 <entry>Always null, because arrays always have unlimited maximum cardinality in <productname>PostgreSQL</></entry>
545 <entry><literal>dtd_identifier</literal></entry>
546 <entry><type>sql_identifier</type></entry>
548 An identifier of the data type descriptor of the column, unique
549 among the data type descriptors pertaining to the table. This
550 is mainly useful for joining with other instances of such
551 identifiers. (The specific format of the identifier is not
552 defined and not guaranteed to remain the same in future
558 <entry><literal>is_derived_reference_attribute</literal></entry>
559 <entry><type>yes_or_no</type></entry>
560 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
567 See also under <xref linkend="infoschema-columns">, a similarly
568 structured view, for further information on some of the columns.
572 <sect1 id="infoschema-character-sets">
573 <title><literal>character_sets</literal></title>
576 The view <literal>character_sets</literal> identifies the character
577 sets available in the current database. Since PostgreSQL does not
578 support multiple character sets within one database, this view only
579 shows one, which is the database encoding.
583 Take note of how the following terms are used in the SQL standard:
586 <term>character repertoire</term>
589 An abstract collection of characters, for
590 example <literal>UNICODE</literal>, <literal>UCS</literal>, or
591 <literal>LATIN1</literal>. Not exposed as an SQL object, but
592 visible in this view.
598 <term>character encoding form</term>
601 An encoding of some character repertoire. Most older character
602 repertoires only use one encoding form, and so there are no
603 separate names for them (e.g., <literal>LATIN1</literal> is an
604 encoding form applicable to the <literal>LATIN1</literal>
605 repertoire). But for example Unicode has the encoding forms
606 <literal>UTF8</literal>, <literal>UTF16</literal>, etc. (not
607 all supported by PostgreSQL). Encoding forms are not exposed
608 as an SQL object, but are visible in this view.
614 <term>character set</term>
617 A named SQL object that identifies a character repertoire, a
618 character encoding, and a default collation. A predefined
619 character set would typically have the same name as an encoding
620 form, but users could define other names. For example, the
621 character set <literal>UTF8</literal> would typically identify
622 the character repertoire <literal>UCS</literal>, encoding
623 form <literal>UTF8</literal>, and some default collation.
629 You can think of an <quote>encoding</quote> in PostgreSQL either as
630 a character set or a character encoding form. They will have the
631 same name, and there can only be one in one database.
635 <title><literal>character_sets</literal> Columns</title>
641 <entry>Data Type</entry>
642 <entry>Description</entry>
648 <entry><literal>character_set_catalog</literal></entry>
649 <entry><literal>sql_identifier</literal></entry>
650 <entry>Character sets are currently not implemented as schema objects, so this column is null.</entry>
654 <entry><literal>character_set_schema</literal></entry>
655 <entry><literal>sql_identifier</literal></entry>
656 <entry>Character sets are currently not implemented as schema objects, so this column is null.</entry>
660 <entry><literal>character_set_name</literal></entry>
661 <entry><literal>sql_identifier</literal></entry>
662 <entry>Name of the character set, currently implemented as showing the name of the database encoding</entry>
666 <entry><literal>character_repertoire</literal></entry>
667 <entry><literal>sql_identifier</literal></entry>
668 <entry>Character repertoire, showing <literal>UCS</literal> if the encoding is <literal>UTF8</literal>, else just the encoding name</entry>
672 <entry><literal>form_of_use</literal></entry>
673 <entry><literal>sql_identifier</literal></entry>
674 <entry>Character encoding form, same as the database encoding</entry>
678 <entry><literal>default_collate_catalog</literal></entry>
679 <entry><literal>sql_identifier</literal></entry>
680 <entry>Name of the database containing the default collation (always the current database, if any collation is identified)</entry>
684 <entry><literal>default_collate_schema</literal></entry>
685 <entry><literal>sql_identifier</literal></entry>
686 <entry>Name of the schema containing the default collation</entry>
690 <entry><literal>default_collate_name</literal></entry>
691 <entry><literal>sql_identifier</literal></entry>
693 Name of the default collation. The default collation is
694 identified as the collation that matches
695 the <literal>COLLATE</literal> and <literal>CTYPE</literal>
696 settings of the current database. If there is no such
697 collation, then this column and the associated schema and
698 catalog columns are null.
706 <sect1 id="infoschema-check-constraint-routine-usage">
707 <title><literal>check_constraint_routine_usage</literal></title>
710 The view <literal>check_constraint_routine_usage</literal>
711 identifies routines (functions and procedures) that are used by a
712 check constraint. Only those routines are shown that are owned by
713 a currently enabled role.
717 <title><literal>check_constraint_routine_usage</literal> Columns</title>
723 <entry>Data Type</entry>
724 <entry>Description</entry>
730 <entry><literal>constraint_catalog</literal></entry>
731 <entry><literal>sql_identifier</literal></entry>
732 <entry>Name of the database containing the constraint (always the current database)</entry>
736 <entry><literal>constraint_schema</literal></entry>
737 <entry><literal>sql_identifier</literal></entry>
738 <entry>Name of the schema containing the constraint</entry>
742 <entry><literal>constraint_name</literal></entry>
743 <entry><literal>sql_identifier</literal></entry>
744 <entry>Name of the constraint</entry>
748 <entry><literal>specific_catalog</literal></entry>
749 <entry><literal>sql_identifier</literal></entry>
750 <entry>Name of the database containing the function (always the current database)</entry>
754 <entry><literal>specific_schema</literal></entry>
755 <entry><literal>sql_identifier</literal></entry>
756 <entry>Name of the schema containing the function</entry>
760 <entry><literal>specific_name</literal></entry>
761 <entry><literal>sql_identifier</literal></entry>
763 The <quote>specific name</quote> of the function. See <xref
764 linkend="infoschema-routines"> for more information.
772 <sect1 id="infoschema-check-constraints">
773 <title><literal>check_constraints</literal></title>
776 The view <literal>check_constraints</literal> contains all check
777 constraints, either defined on a table or on a domain, that are
778 owned by a currently enabled role. (The owner of the table or
779 domain is the owner of the constraint.)
783 <title><literal>check_constraints</literal> Columns</title>
789 <entry>Data Type</entry>
790 <entry>Description</entry>
796 <entry><literal>constraint_catalog</literal></entry>
797 <entry><literal>sql_identifier</literal></entry>
798 <entry>Name of the database containing the constraint (always the current database)</entry>
802 <entry><literal>constraint_schema</literal></entry>
803 <entry><literal>sql_identifier</literal></entry>
804 <entry>Name of the schema containing the constraint</entry>
808 <entry><literal>constraint_name</literal></entry>
809 <entry><literal>sql_identifier</literal></entry>
810 <entry>Name of the constraint</entry>
814 <entry><literal>check_clause</literal></entry>
815 <entry><literal>character_data</literal></entry>
816 <entry>The check expression of the check constraint</entry>
823 <sect1 id="infoschema-collations">
824 <title><literal>collations</literal></title>
827 The view <literal>collations</literal> contains the collations
828 available in the current database.
832 <title><literal>collations</literal> Columns</title>
838 <entry>Data Type</entry>
839 <entry>Description</entry>
845 <entry><literal>collation_catalog</literal></entry>
846 <entry><literal>sql_identifier</literal></entry>
847 <entry>Name of the database containing the collation (always the current database)</entry>
851 <entry><literal>collation_schema</literal></entry>
852 <entry><literal>sql_identifier</literal></entry>
853 <entry>Name of the schema containing the collation</entry>
857 <entry><literal>collation_name</literal></entry>
858 <entry><literal>sql_identifier</literal></entry>
859 <entry>Name of the default collation</entry>
863 <entry><literal>pad_attribute</literal></entry>
864 <entry><literal>character_data</literal></entry>
866 Always <literal>NO PAD</literal> (The alternative <literal>PAD
867 SPACE</literal> is not supported by PostgreSQL.)
875 <sect1 id="infoschema-collation-character-set-applicab"> <!-- max 44 characters -->
876 <title><literal>collation_character_set_applicability</literal></title>
879 The view <literal>collation_character_set_applicability</literal>
880 identifies which character set the available collations are
881 applicable to. In PostgreSQL, there is only one character set per
882 database (see explanation
883 in <xref linkend="infoschema-character-sets">), so this view does
884 not provide much useful information.
888 <title><literal>collation_character_set_applicability</literal> Columns</title>
894 <entry>Data Type</entry>
895 <entry>Description</entry>
901 <entry><literal>collation_catalog</literal></entry>
902 <entry><literal>sql_identifier</literal></entry>
903 <entry>Name of the database containing the collation (always the current database)</entry>
907 <entry><literal>collation_schema</literal></entry>
908 <entry><literal>sql_identifier</literal></entry>
909 <entry>Name of the schema containing the collation</entry>
913 <entry><literal>collation_name</literal></entry>
914 <entry><literal>sql_identifier</literal></entry>
915 <entry>Name of the default collation</entry>
919 <entry><literal>character_set_catalog</literal></entry>
920 <entry><literal>sql_identifier</literal></entry>
921 <entry>Character sets are currently not implemented as schema objects, so this column is null</entry>
925 <entry><literal>character_set_schema</literal></entry>
926 <entry><literal>sql_identifier</literal></entry>
927 <entry>Character sets are currently not implemented as schema objects, so this column is null</entry>
931 <entry><literal>character_set_name</literal></entry>
932 <entry><literal>sql_identifier</literal></entry>
933 <entry>Name of the character set</entry>
940 <sect1 id="infoschema-column-domain-usage">
941 <title><literal>column_domain_usage</literal></title>
944 The view <literal>column_domain_usage</literal> identifies all
945 columns (of a table or a view) that make use of some domain defined
946 in the current database and owned by a currently enabled role.
950 <title><literal>column_domain_usage</literal> Columns</title>
956 <entry>Data Type</entry>
957 <entry>Description</entry>
963 <entry><literal>domain_catalog</literal></entry>
964 <entry><type>sql_identifier</type></entry>
965 <entry>Name of the database containing the domain (always the current database)</entry>
969 <entry><literal>domain_schema</literal></entry>
970 <entry><type>sql_identifier</type></entry>
971 <entry>Name of the schema containing the domain</entry>
975 <entry><literal>domain_name</literal></entry>
976 <entry><type>sql_identifier</type></entry>
977 <entry>Name of the domain</entry>
981 <entry><literal>table_catalog</literal></entry>
982 <entry><type>sql_identifier</type></entry>
983 <entry>Name of the database containing the table (always the current database)</entry>
987 <entry><literal>table_schema</literal></entry>
988 <entry><type>sql_identifier</type></entry>
989 <entry>Name of the schema containing the table</entry>
993 <entry><literal>table_name</literal></entry>
994 <entry><type>sql_identifier</type></entry>
995 <entry>Name of the table</entry>
999 <entry><literal>column_name</literal></entry>
1000 <entry><type>sql_identifier</type></entry>
1001 <entry>Name of the column</entry>
1008 <sect1 id="infoschema-column-privileges">
1009 <title><literal>column_privileges</literal></title>
1012 The view <literal>column_privileges</literal> identifies all
1013 privileges granted on columns to a currently enabled role or by a
1014 currently enabled role. There is one row for each combination of
1015 column, grantor, and grantee.
1019 If a privilege has been granted on an entire table, it will show up in
1020 this view as a grant for each column, but only for the
1021 privilege types where column granularity is possible:
1022 <literal>SELECT</literal>, <literal>INSERT</literal>,
1023 <literal>UPDATE</literal>, <literal>REFERENCES</literal>.
1027 <title><literal>column_privileges</literal> Columns</title>
1033 <entry>Data Type</entry>
1034 <entry>Description</entry>
1040 <entry><literal>grantor</literal></entry>
1041 <entry><type>sql_identifier</type></entry>
1042 <entry>Name of the role that granted the privilege</entry>
1046 <entry><literal>grantee</literal></entry>
1047 <entry><type>sql_identifier</type></entry>
1048 <entry>Name of the role that the privilege was granted to</entry>
1052 <entry><literal>table_catalog</literal></entry>
1053 <entry><type>sql_identifier</type></entry>
1054 <entry>Name of the database that contains the table that contains the column (always the current database)</entry>
1058 <entry><literal>table_schema</literal></entry>
1059 <entry><type>sql_identifier</type></entry>
1060 <entry>Name of the schema that contains the table that contains the column</entry>
1064 <entry><literal>table_name</literal></entry>
1065 <entry><type>sql_identifier</type></entry>
1066 <entry>Name of the table that contains the column</entry>
1070 <entry><literal>column_name</literal></entry>
1071 <entry><type>sql_identifier</type></entry>
1072 <entry>Name of the column</entry>
1076 <entry><literal>privilege_type</literal></entry>
1077 <entry><type>character_data</type></entry>
1079 Type of the privilege: <literal>SELECT</literal>,
1080 <literal>INSERT</literal>, <literal>UPDATE</literal>, or
1081 <literal>REFERENCES</literal>
1086 <entry><literal>is_grantable</literal></entry>
1087 <entry><type>yes_or_no</type></entry>
1088 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
1095 <sect1 id="infoschema-column-udt-usage">
1096 <title><literal>column_udt_usage</literal></title>
1099 The view <literal>column_udt_usage</literal> identifies all columns
1100 that use data types owned by a currently enabled role. Note that in
1101 <productname>PostgreSQL</productname>, built-in data types behave
1102 like user-defined types, so they are included here as well. See
1103 also <xref linkend="infoschema-columns"> for details.
1107 <title><literal>column_udt_usage</literal> Columns</title>
1113 <entry>Data Type</entry>
1114 <entry>Description</entry>
1120 <entry><literal>udt_catalog</literal></entry>
1121 <entry><type>sql_identifier</type></entry>
1123 Name of the database that the column data type (the underlying
1124 type of the domain, if applicable) is defined in (always the
1130 <entry><literal>udt_schema</literal></entry>
1131 <entry><type>sql_identifier</type></entry>
1133 Name of the schema that the column data type (the underlying
1134 type of the domain, if applicable) is defined in
1139 <entry><literal>udt_name</literal></entry>
1140 <entry><type>sql_identifier</type></entry>
1142 Name of the column data type (the underlying type of the
1143 domain, if applicable)
1148 <entry><literal>table_catalog</literal></entry>
1149 <entry><type>sql_identifier</type></entry>
1150 <entry>Name of the database containing the table (always the current database)</entry>
1154 <entry><literal>table_schema</literal></entry>
1155 <entry><type>sql_identifier</type></entry>
1156 <entry>Name of the schema containing the table</entry>
1160 <entry><literal>table_name</literal></entry>
1161 <entry><type>sql_identifier</type></entry>
1162 <entry>Name of the table</entry>
1166 <entry><literal>column_name</literal></entry>
1167 <entry><type>sql_identifier</type></entry>
1168 <entry>Name of the column</entry>
1175 <sect1 id="infoschema-columns">
1176 <title><literal>columns</literal></title>
1179 The view <literal>columns</literal> contains information about all
1180 table columns (or view columns) in the database. System columns
1181 (<literal>oid</>, etc.) are not included. Only those columns are
1182 shown that the current user has access to (by way of being the
1183 owner or having some privilege).
1187 <title><literal>columns</literal> Columns</title>
1193 <entry>Data Type</entry>
1194 <entry>Description</entry>
1200 <entry><literal>table_catalog</literal></entry>
1201 <entry><type>sql_identifier</type></entry>
1202 <entry>Name of the database containing the table (always the current database)</entry>
1206 <entry><literal>table_schema</literal></entry>
1207 <entry><type>sql_identifier</type></entry>
1208 <entry>Name of the schema containing the table</entry>
1212 <entry><literal>table_name</literal></entry>
1213 <entry><type>sql_identifier</type></entry>
1214 <entry>Name of the table</entry>
1218 <entry><literal>column_name</literal></entry>
1219 <entry><type>sql_identifier</type></entry>
1220 <entry>Name of the column</entry>
1224 <entry><literal>ordinal_position</literal></entry>
1225 <entry><type>cardinal_number</type></entry>
1226 <entry>Ordinal position of the column within the table (count starts at 1)</entry>
1230 <entry><literal>column_default</literal></entry>
1231 <entry><type>character_data</type></entry>
1232 <entry>Default expression of the column</entry>
1236 <entry><literal>is_nullable</literal></entry>
1237 <entry><type>yes_or_no</type></entry>
1239 <literal>YES</literal> if the column is possibly nullable,
1240 <literal>NO</literal> if it is known not nullable. A not-null
1241 constraint is one way a column can be known not nullable, but
1242 there can be others.
1247 <entry><literal>data_type</literal></entry>
1248 <entry><type>character_data</type></entry>
1250 Data type of the column, if it is a built-in type, or
1251 <literal>ARRAY</literal> if it is some array (in that case, see
1252 the view <literal>element_types</literal>), else
1253 <literal>USER-DEFINED</literal> (in that case, the type is
1254 identified in <literal>udt_name</literal> and associated
1255 columns). If the column is based on a domain, this column
1256 refers to the type underlying the domain (and the domain is
1257 identified in <literal>domain_name</literal> and associated
1263 <entry><literal>character_maximum_length</literal></entry>
1264 <entry><type>cardinal_number</type></entry>
1266 If <literal>data_type</literal> identifies a character or bit
1267 string type, the declared maximum length; null for all other
1268 data types or if no maximum length was declared.
1273 <entry><literal>character_octet_length</literal></entry>
1274 <entry><type>cardinal_number</type></entry>
1276 If <literal>data_type</literal> identifies a character type,
1277 the maximum possible length in octets (bytes) of a datum; null
1278 for all other data types. The maximum octet length depends on
1279 the declared character maximum length (see above) and the
1285 <entry><literal>numeric_precision</literal></entry>
1286 <entry><type>cardinal_number</type></entry>
1288 If <literal>data_type</literal> identifies a numeric type, this
1289 column contains the (declared or implicit) precision of the
1290 type for this column. The precision indicates the number of
1291 significant digits. It can be expressed in decimal (base 10)
1292 or binary (base 2) terms, as specified in the column
1293 <literal>numeric_precision_radix</literal>. For all other data
1294 types, this column is null.
1299 <entry><literal>numeric_precision_radix</literal></entry>
1300 <entry><type>cardinal_number</type></entry>
1302 If <literal>data_type</literal> identifies a numeric type, this
1303 column indicates in which base the values in the columns
1304 <literal>numeric_precision</literal> and
1305 <literal>numeric_scale</literal> are expressed. The value is
1306 either 2 or 10. For all other data types, this column is null.
1311 <entry><literal>numeric_scale</literal></entry>
1312 <entry><type>cardinal_number</type></entry>
1314 If <literal>data_type</literal> identifies an exact numeric
1315 type, this column contains the (declared or implicit) scale of
1316 the type for this column. The scale indicates the number of
1317 significant digits to the right of the decimal point. It can
1318 be expressed in decimal (base 10) or binary (base 2) terms, as
1319 specified in the column
1320 <literal>numeric_precision_radix</literal>. For all other data
1321 types, this column is null.
1326 <entry><literal>datetime_precision</literal></entry>
1327 <entry><type>cardinal_number</type></entry>
1329 If <literal>data_type</literal> identifies a date, time,
1330 timestamp, or interval type, this column contains the (declared
1331 or implicit) fractional seconds precision of the type for this
1332 column, that is, the number of decimal digits maintained
1333 following the decimal point in the seconds value. For all
1334 other data types, this column is null.
1339 <entry><literal>interval_type</literal></entry>
1340 <entry><type>character_data</type></entry>
1341 <entry>Not yet implemented</entry>
1345 <entry><literal>interval_precision</literal></entry>
1346 <entry><type>character_data</type></entry>
1347 <entry>Not yet implemented</entry>
1351 <entry><literal>character_set_catalog</literal></entry>
1352 <entry><type>sql_identifier</type></entry>
1353 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1357 <entry><literal>character_set_schema</literal></entry>
1358 <entry><type>sql_identifier</type></entry>
1359 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1363 <entry><literal>character_set_name</literal></entry>
1364 <entry><type>sql_identifier</type></entry>
1365 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1369 <entry><literal>collation_catalog</literal></entry>
1370 <entry><type>sql_identifier</type></entry>
1372 Name of the database containing the collation of the column
1373 (always the current database), null if default or the data type
1374 of the column is not collatable
1379 <entry><literal>collation_schema</literal></entry>
1380 <entry><type>sql_identifier</type></entry>
1382 Name of the schema containing the collation of the column, null
1383 if default or the data type of the column is not collatable
1388 <entry><literal>collation_name</literal></entry>
1389 <entry><type>sql_identifier</type></entry>
1391 Name of the collation of the column, null if default or the
1392 data type of the column is not collatable
1397 <entry><literal>domain_catalog</literal></entry>
1398 <entry><type>sql_identifier</type></entry>
1400 If the column has a domain type, the name of the database that
1401 the domain is defined in (always the current database), else
1407 <entry><literal>domain_schema</literal></entry>
1408 <entry><type>sql_identifier</type></entry>
1410 If the column has a domain type, the name of the schema that
1411 the domain is defined in, else null.
1416 <entry><literal>domain_name</literal></entry>
1417 <entry><type>sql_identifier</type></entry>
1418 <entry>If the column has a domain type, the name of the domain, else null.</entry>
1422 <entry><literal>udt_catalog</literal></entry>
1423 <entry><type>sql_identifier</type></entry>
1425 Name of the database that the column data type (the underlying
1426 type of the domain, if applicable) is defined in (always the
1432 <entry><literal>udt_schema</literal></entry>
1433 <entry><type>sql_identifier</type></entry>
1435 Name of the schema that the column data type (the underlying
1436 type of the domain, if applicable) is defined in
1441 <entry><literal>udt_name</literal></entry>
1442 <entry><type>sql_identifier</type></entry>
1444 Name of the column data type (the underlying type of the
1445 domain, if applicable)
1450 <entry><literal>scope_catalog</literal></entry>
1451 <entry><type>sql_identifier</type></entry>
1452 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1456 <entry><literal>scope_schema</literal></entry>
1457 <entry><type>sql_identifier</type></entry>
1458 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1462 <entry><literal>scope_name</literal></entry>
1463 <entry><type>sql_identifier</type></entry>
1464 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1468 <entry><literal>maximum_cardinality</literal></entry>
1469 <entry><type>cardinal_number</type></entry>
1470 <entry>Always null, because arrays always have unlimited maximum cardinality in <productname>PostgreSQL</></entry>
1474 <entry><literal>dtd_identifier</literal></entry>
1475 <entry><type>sql_identifier</type></entry>
1477 An identifier of the data type descriptor of the column, unique
1478 among the data type descriptors pertaining to the table. This
1479 is mainly useful for joining with other instances of such
1480 identifiers. (The specific format of the identifier is not
1481 defined and not guaranteed to remain the same in future
1487 <entry><literal>is_self_referencing</literal></entry>
1488 <entry><type>yes_or_no</type></entry>
1489 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1493 <entry><literal>is_identity</literal></entry>
1494 <entry><type>yes_or_no</type></entry>
1495 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1499 <entry><literal>identity_generation</literal></entry>
1500 <entry><type>character_data</type></entry>
1501 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1505 <entry><literal>identity_start</literal></entry>
1506 <entry><type>character_data</type></entry>
1507 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1511 <entry><literal>identity_increment</literal></entry>
1512 <entry><type>character_data</type></entry>
1513 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1517 <entry><literal>identity_maximum</literal></entry>
1518 <entry><type>character_data</type></entry>
1519 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1523 <entry><literal>identity_minimum</literal></entry>
1524 <entry><type>character_data</type></entry>
1525 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1529 <entry><literal>identity_cycle</literal></entry>
1530 <entry><type>yes_or_no</type></entry>
1531 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1535 <entry><literal>is_generated</literal></entry>
1536 <entry><type>character_data</type></entry>
1537 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1541 <entry><literal>generation_expression</literal></entry>
1542 <entry><type>character_data</type></entry>
1543 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
1547 <entry><literal>is_updatable</literal></entry>
1548 <entry><type>yes_or_no</type></entry>
1550 <literal>YES</literal> if the column is updatable,
1551 <literal>NO</literal> if not (Columns in base tables are always
1552 updatable, columns in views not necessarily)
1560 Since data types can be defined in a variety of ways in SQL, and
1561 <productname>PostgreSQL</productname> contains additional ways to
1562 define data types, their representation in the information schema
1563 can be somewhat difficult. The column <literal>data_type</literal>
1564 is supposed to identify the underlying built-in type of the column.
1565 In <productname>PostgreSQL</productname>, this means that the type
1566 is defined in the system catalog schema
1567 <literal>pg_catalog</literal>. This column might be useful if the
1568 application can handle the well-known built-in types specially (for
1569 example, format the numeric types differently or use the data in
1570 the precision columns). The columns <literal>udt_name</literal>,
1571 <literal>udt_schema</literal>, and <literal>udt_catalog</literal>
1572 always identify the underlying data type of the column, even if the
1573 column is based on a domain. (Since
1574 <productname>PostgreSQL</productname> treats built-in types like
1575 user-defined types, built-in types appear here as well. This is an
1576 extension of the SQL standard.) These columns should be used if an
1577 application wants to process data differently according to the
1578 type, because in that case it wouldn't matter if the column is
1579 really based on a domain. If the column is based on a domain, the
1580 identity of the domain is stored in the columns
1581 <literal>domain_name</literal>, <literal>domain_schema</literal>,
1582 and <literal>domain_catalog</literal>. If you want to pair up
1583 columns with their associated data types and treat domains as
1584 separate types, you could write <literal>coalesce(domain_name,
1585 udt_name)</literal>, etc.
1589 <sect1 id="infoschema-constraint-column-usage">
1590 <title><literal>constraint_column_usage</literal></title>
1593 The view <literal>constraint_column_usage</literal> identifies all
1594 columns in the current database that are used by some constraint.
1595 Only those columns are shown that are contained in a table owned by
1596 a currently enabled role. For a check constraint, this view
1597 identifies the columns that are used in the check expression. For
1598 a foreign key constraint, this view identifies the columns that the
1599 foreign key references. For a unique or primary key constraint,
1600 this view identifies the constrained columns.
1604 <title><literal>constraint_column_usage</literal> Columns</title>
1610 <entry>Data Type</entry>
1611 <entry>Description</entry>
1617 <entry><literal>table_catalog</literal></entry>
1618 <entry><type>sql_identifier</type></entry>
1620 Name of the database that contains the table that contains the
1621 column that is used by some constraint (always the current
1627 <entry><literal>table_schema</literal></entry>
1628 <entry><type>sql_identifier</type></entry>
1630 Name of the schema that contains the table that contains the
1631 column that is used by some constraint
1636 <entry><literal>table_name</literal></entry>
1637 <entry><type>sql_identifier</type></entry>
1639 Name of the table that contains the column that is used by some
1645 <entry><literal>column_name</literal></entry>
1646 <entry><type>sql_identifier</type></entry>
1648 Name of the column that is used by some constraint
1653 <entry><literal>constraint_catalog</literal></entry>
1654 <entry><type>sql_identifier</type></entry>
1655 <entry>Name of the database that contains the constraint (always the current database)</entry>
1659 <entry><literal>constraint_schema</literal></entry>
1660 <entry><type>sql_identifier</type></entry>
1661 <entry>Name of the schema that contains the constraint</entry>
1665 <entry><literal>constraint_name</literal></entry>
1666 <entry><type>sql_identifier</type></entry>
1667 <entry>Name of the constraint</entry>
1674 <sect1 id="infoschema-constraint-table-usage">
1675 <title><literal>constraint_table_usage</literal></title>
1678 The view <literal>constraint_table_usage</literal> identifies all
1679 tables in the current database that are used by some constraint and
1680 are owned by a currently enabled role. (This is different from the
1681 view <literal>table_constraints</literal>, which identifies all
1682 table constraints along with the table they are defined on.) For a
1683 foreign key constraint, this view identifies the table that the
1684 foreign key references. For a unique or primary key constraint,
1685 this view simply identifies the table the constraint belongs to.
1686 Check constraints and not-null constraints are not included in this
1691 <title><literal>constraint_table_usage</literal> Columns</title>
1697 <entry>Data Type</entry>
1698 <entry>Description</entry>
1704 <entry><literal>table_catalog</literal></entry>
1705 <entry><type>sql_identifier</type></entry>
1707 Name of the database that contains the table that is used by
1708 some constraint (always the current database)
1713 <entry><literal>table_schema</literal></entry>
1714 <entry><type>sql_identifier</type></entry>
1716 Name of the schema that contains the table that is used by some
1722 <entry><literal>table_name</literal></entry>
1723 <entry><type>sql_identifier</type></entry>
1724 <entry>Name of the table that is used by some constraint</entry>
1728 <entry><literal>constraint_catalog</literal></entry>
1729 <entry><type>sql_identifier</type></entry>
1730 <entry>Name of the database that contains the constraint (always the current database)</entry>
1734 <entry><literal>constraint_schema</literal></entry>
1735 <entry><type>sql_identifier</type></entry>
1736 <entry>Name of the schema that contains the constraint</entry>
1740 <entry><literal>constraint_name</literal></entry>
1741 <entry><type>sql_identifier</type></entry>
1742 <entry>Name of the constraint</entry>
1749 <sect1 id="infoschema-data-type-privileges">
1750 <title><literal>data_type_privileges</literal></title>
1753 The view <literal>data_type_privileges</literal> identifies all
1754 data type descriptors that the current user has access to, by way
1755 of being the owner of the described object or having some privilege
1756 for it. A data type descriptor is generated whenever a data type
1757 is used in the definition of a table column, a domain, or a
1758 function (as parameter or return type) and stores some information
1759 about how the data type is used in that instance (for example, the
1760 declared maximum length, if applicable). Each data type
1761 descriptor is assigned an arbitrary identifier that is unique
1762 among the data type descriptor identifiers assigned for one object
1763 (table, domain, function). This view is probably not useful for
1764 applications, but it is used to define some other views in the
1769 <title><literal>data_type_privileges</literal> Columns</title>
1775 <entry>Data Type</entry>
1776 <entry>Description</entry>
1782 <entry><literal>object_catalog</literal></entry>
1783 <entry><type>sql_identifier</type></entry>
1784 <entry>Name of the database that contains the described object (always the current database)</entry>
1788 <entry><literal>object_schema</literal></entry>
1789 <entry><type>sql_identifier</type></entry>
1790 <entry>Name of the schema that contains the described object</entry>
1794 <entry><literal>object_name</literal></entry>
1795 <entry><type>sql_identifier</type></entry>
1796 <entry>Name of the described object</entry>
1800 <entry><literal>object_type</literal></entry>
1801 <entry><type>character_data</type></entry>
1803 The type of the described object: one of
1804 <literal>TABLE</literal> (the data type descriptor pertains to
1805 a column of that table), <literal>DOMAIN</literal> (the data
1806 type descriptors pertains to that domain),
1807 <literal>ROUTINE</literal> (the data type descriptor pertains
1808 to a parameter or the return data type of that function).
1813 <entry><literal>dtd_identifier</literal></entry>
1814 <entry><type>sql_identifier</type></entry>
1816 The identifier of the data type descriptor, which is unique
1817 among the data type descriptors for that same object.
1825 <sect1 id="infoschema-domain-constraints">
1826 <title><literal>domain_constraints</literal></title>
1829 The view <literal>domain_constraints</literal> contains all
1830 constraints belonging to domains defined in the current database.
1834 <title><literal>domain_constraints</literal> Columns</title>
1840 <entry>Data Type</entry>
1841 <entry>Description</entry>
1847 <entry><literal>constraint_catalog</literal></entry>
1848 <entry><type>sql_identifier</type></entry>
1849 <entry>Name of the database that contains the constraint (always the current database)</entry>
1853 <entry><literal>constraint_schema</literal></entry>
1854 <entry><type>sql_identifier</type></entry>
1855 <entry>Name of the schema that contains the constraint</entry>
1859 <entry><literal>constraint_name</literal></entry>
1860 <entry><type>sql_identifier</type></entry>
1861 <entry>Name of the constraint</entry>
1865 <entry><literal>domain_catalog</literal></entry>
1866 <entry><type>sql_identifier</type></entry>
1867 <entry>Name of the database that contains the domain (always the current database)</entry>
1871 <entry><literal>domain_schema</literal></entry>
1872 <entry><type>sql_identifier</type></entry>
1873 <entry>Name of the schema that contains the domain</entry>
1877 <entry><literal>domain_name</literal></entry>
1878 <entry><type>sql_identifier</type></entry>
1879 <entry>Name of the domain</entry>
1883 <entry><literal>is_deferrable</literal></entry>
1884 <entry><type>yes_or_no</type></entry>
1885 <entry><literal>YES</literal> if the constraint is deferrable, <literal>NO</literal> if not</entry>
1889 <entry><literal>initially_deferred</literal></entry>
1890 <entry><type>yes_or_no</type></entry>
1891 <entry><literal>YES</literal> if the constraint is deferrable and initially deferred, <literal>NO</literal> if not</entry>
1898 <sect1 id="infoschema-domain-udt-usage">
1899 <title><literal>domain_udt_usage</literal></title>
1902 The view <literal>domain_udt_usage</literal> identifies all domains
1903 that are based on data types owned by a currently enabled role.
1904 Note that in <productname>PostgreSQL</productname>, built-in data
1905 types behave like user-defined types, so they are included here as
1910 <title><literal>domain_udt_usage</literal> Columns</title>
1916 <entry>Data Type</entry>
1917 <entry>Description</entry>
1923 <entry><literal>udt_catalog</literal></entry>
1924 <entry><type>sql_identifier</type></entry>
1925 <entry>Name of the database that the domain data type is defined in (always the current database)</entry>
1929 <entry><literal>udt_schema</literal></entry>
1930 <entry><type>sql_identifier</type></entry>
1931 <entry>Name of the schema that the domain data type is defined in</entry>
1935 <entry><literal>udt_name</literal></entry>
1936 <entry><type>sql_identifier</type></entry>
1937 <entry>Name of the domain data type</entry>
1941 <entry><literal>domain_catalog</literal></entry>
1942 <entry><type>sql_identifier</type></entry>
1943 <entry>Name of the database that contains the domain (always the current database)</entry>
1947 <entry><literal>domain_schema</literal></entry>
1948 <entry><type>sql_identifier</type></entry>
1949 <entry>Name of the schema that contains the domain</entry>
1953 <entry><literal>domain_name</literal></entry>
1954 <entry><type>sql_identifier</type></entry>
1955 <entry>Name of the domain</entry>
1962 <sect1 id="infoschema-domains">
1963 <title><literal>domains</literal></title>
1966 The view <literal>domains</literal> contains all domains defined in
1967 the current database.
1971 <title><literal>domains</literal> Columns</title>
1977 <entry>Data Type</entry>
1978 <entry>Description</entry>
1984 <entry><literal>domain_catalog</literal></entry>
1985 <entry><type>sql_identifier</type></entry>
1986 <entry>Name of the database that contains the domain (always the current database)</entry>
1990 <entry><literal>domain_schema</literal></entry>
1991 <entry><type>sql_identifier</type></entry>
1992 <entry>Name of the schema that contains the domain</entry>
1996 <entry><literal>domain_name</literal></entry>
1997 <entry><type>sql_identifier</type></entry>
1998 <entry>Name of the domain</entry>
2002 <entry><literal>data_type</literal></entry>
2003 <entry><type>character_data</type></entry>
2005 Data type of the domain, if it is a built-in type, or
2006 <literal>ARRAY</literal> if it is some array (in that case, see
2007 the view <literal>element_types</literal>), else
2008 <literal>USER-DEFINED</literal> (in that case, the type is
2009 identified in <literal>udt_name</literal> and associated
2015 <entry><literal>character_maximum_length</literal></entry>
2016 <entry><type>cardinal_number</type></entry>
2018 If the domain has a character or bit string type, the declared
2019 maximum length; null for all other data types or if no maximum
2020 length was declared.
2025 <entry><literal>character_octet_length</literal></entry>
2026 <entry><type>cardinal_number</type></entry>
2028 If the domain has a character type, the maximum possible length
2029 in octets (bytes) of a datum; null for all other data types.
2030 The maximum octet length depends on the declared character
2031 maximum length (see above) and the server encoding.
2036 <entry><literal>character_set_catalog</literal></entry>
2037 <entry><type>sql_identifier</type></entry>
2038 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2042 <entry><literal>character_set_schema</literal></entry>
2043 <entry><type>sql_identifier</type></entry>
2044 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2048 <entry><literal>character_set_name</literal></entry>
2049 <entry><type>sql_identifier</type></entry>
2050 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2054 <entry><literal>collation_catalog</literal></entry>
2055 <entry><type>sql_identifier</type></entry>
2057 Name of the database containing the collation of the domain
2058 (always the current database), null if default or the data type
2059 of the domain is not collatable
2064 <entry><literal>collation_schema</literal></entry>
2065 <entry><type>sql_identifier</type></entry>
2067 Name of the schema containing the collation of the domain, null
2068 if default or the data type of the domain is not collatable
2073 <entry><literal>collation_name</literal></entry>
2074 <entry><type>sql_identifier</type></entry>
2076 Name of the collation of the column, null if default or the
2077 data type of the domain is not collatable
2082 <entry><literal>numeric_precision</literal></entry>
2083 <entry><type>cardinal_number</type></entry>
2085 If the domain has a numeric type, this column contains the
2086 (declared or implicit) precision of the type for this domain.
2087 The precision indicates the number of significant digits. It
2088 can be expressed in decimal (base 10) or binary (base 2) terms,
2089 as specified in the column
2090 <literal>numeric_precision_radix</literal>. For all other data
2091 types, this column is null.
2096 <entry><literal>numeric_precision_radix</literal></entry>
2097 <entry><type>cardinal_number</type></entry>
2099 If the domain has a numeric type, this column indicates in
2100 which base the values in the columns
2101 <literal>numeric_precision</literal> and
2102 <literal>numeric_scale</literal> are expressed. The value is
2103 either 2 or 10. For all other data types, this column is null.
2108 <entry><literal>numeric_scale</literal></entry>
2109 <entry><type>cardinal_number</type></entry>
2111 If the domain has an exact numeric type, this column contains
2112 the (declared or implicit) scale of the type for this domain.
2113 The scale indicates the number of significant digits to the
2114 right of the decimal point. It can be expressed in decimal
2115 (base 10) or binary (base 2) terms, as specified in the column
2116 <literal>numeric_precision_radix</literal>. For all other data
2117 types, this column is null.
2122 <entry><literal>datetime_precision</literal></entry>
2123 <entry><type>cardinal_number</type></entry>
2125 If <literal>data_type</literal> identifies a date, time,
2126 timestamp, or interval type, this column contains the (declared
2127 or implicit) fractional seconds precision of the type for this
2128 domain, that is, the number of decimal digits maintained
2129 following the decimal point in the seconds value. For all
2130 other data types, this column is null.
2135 <entry><literal>interval_type</literal></entry>
2136 <entry><type>character_data</type></entry>
2137 <entry>Not yet implemented</entry>
2141 <entry><literal>interval_precision</literal></entry>
2142 <entry><type>character_data</type></entry>
2143 <entry>Not yet implemented</entry>
2147 <entry><literal>domain_default</literal></entry>
2148 <entry><type>character_data</type></entry>
2149 <entry>Default expression of the domain</entry>
2153 <entry><literal>udt_catalog</literal></entry>
2154 <entry><type>sql_identifier</type></entry>
2155 <entry>Name of the database that the domain data type is defined in (always the current database)</entry>
2159 <entry><literal>udt_schema</literal></entry>
2160 <entry><type>sql_identifier</type></entry>
2161 <entry>Name of the schema that the domain data type is defined in</entry>
2165 <entry><literal>udt_name</literal></entry>
2166 <entry><type>sql_identifier</type></entry>
2167 <entry>Name of the domain data type</entry>
2171 <entry><literal>scope_catalog</literal></entry>
2172 <entry><type>sql_identifier</type></entry>
2173 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2177 <entry><literal>scope_schema</literal></entry>
2178 <entry><type>sql_identifier</type></entry>
2179 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2183 <entry><literal>scope_name</literal></entry>
2184 <entry><type>sql_identifier</type></entry>
2185 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2189 <entry><literal>maximum_cardinality</literal></entry>
2190 <entry><type>cardinal_number</type></entry>
2191 <entry>Always null, because arrays always have unlimited maximum cardinality in <productname>PostgreSQL</></entry>
2195 <entry><literal>dtd_identifier</literal></entry>
2196 <entry><type>sql_identifier</type></entry>
2198 An identifier of the data type descriptor of the domain, unique
2199 among the data type descriptors pertaining to the domain (which
2200 is trivial, because a domain only contains one data type
2201 descriptor). This is mainly useful for joining with other
2202 instances of such identifiers. (The specific format of the
2203 identifier is not defined and not guaranteed to remain the same
2204 in future versions.)
2212 <sect1 id="infoschema-element-types">
2213 <title><literal>element_types</literal></title>
2216 The view <literal>element_types</literal> contains the data type
2217 descriptors of the elements of arrays. When a table column, composite-type attribute,
2218 domain, function parameter, or function return value is defined to
2219 be of an array type, the respective information schema view only
2220 contains <literal>ARRAY</literal> in the column
2221 <literal>data_type</literal>. To obtain information on the element
2222 type of the array, you can join the respective view with this view.
2223 For example, to show the columns of a table with data types and
2224 array element types, if applicable, you could do:
2226 SELECT c.column_name, c.data_type, e.data_type AS element_type
2227 FROM information_schema.columns c LEFT JOIN information_schema.element_types e
2228 ON ((c.table_catalog, c.table_schema, c.table_name, 'TABLE', c.dtd_identifier)
2229 = (e.object_catalog, e.object_schema, e.object_name, e.object_type, e.collection_type_identifier))
2230 WHERE c.table_schema = '...' AND c.table_name = '...'
2231 ORDER BY c.ordinal_position;
2233 This view only includes objects that the current user has access
2234 to, by way of being the owner or having some privilege.
2238 <title><literal>element_types</literal> Columns</title>
2244 <entry>Data Type</entry>
2245 <entry>Description</entry>
2251 <entry><literal>object_catalog</literal></entry>
2252 <entry><type>sql_identifier</type></entry>
2254 Name of the database that contains the object that uses the
2255 array being described (always the current database)
2260 <entry><literal>object_schema</literal></entry>
2261 <entry><type>sql_identifier</type></entry>
2263 Name of the schema that contains the object that uses the array
2269 <entry><literal>object_name</literal></entry>
2270 <entry><type>sql_identifier</type></entry>
2272 Name of the object that uses the array being described
2277 <entry><literal>object_type</literal></entry>
2278 <entry><type>character_data</type></entry>
2280 The type of the object that uses the array being described: one
2281 of <literal>TABLE</literal> (the array is used by a column of
2282 that table), <literal>USER-DEFINED TYPE</literal> (the array is
2283 used by an attribute of that composite type),
2284 <literal>DOMAIN</literal> (the array is used by that domain),
2285 <literal>ROUTINE</literal> (the array is used by a parameter or
2286 the return data type of that function).
2291 <entry><literal>collection_type_identifier</literal></entry>
2292 <entry><type>sql_identifier</type></entry>
2294 The identifier of the data type descriptor of the array being
2295 described. Use this to join with the
2296 <literal>dtd_identifier</literal> columns of other information
2302 <entry><literal>data_type</literal></entry>
2303 <entry><type>character_data</type></entry>
2305 Data type of the array elements, if it is a built-in type, else
2306 <literal>USER-DEFINED</literal> (in that case, the type is
2307 identified in <literal>udt_name</literal> and associated
2313 <entry><literal>character_maximum_length</literal></entry>
2314 <entry><type>cardinal_number</type></entry>
2315 <entry>Always null, since this information is not applied to array element data types in <productname>PostgreSQL</></entry>
2319 <entry><literal>character_octet_length</literal></entry>
2320 <entry><type>cardinal_number</type></entry>
2321 <entry>Always null, since this information is not applied to array element data types in <productname>PostgreSQL</></entry>
2325 <entry><literal>character_set_catalog</literal></entry>
2326 <entry><type>sql_identifier</type></entry>
2327 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2331 <entry><literal>character_set_schema</literal></entry>
2332 <entry><type>sql_identifier</type></entry>
2333 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2337 <entry><literal>character_set_name</literal></entry>
2338 <entry><type>sql_identifier</type></entry>
2339 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2343 <entry><literal>collation_catalog</literal></entry>
2344 <entry><type>sql_identifier</type></entry>
2346 Name of the database containing the collation of the element
2347 type (always the current database), null if default or the data
2348 type of the element is not collatable
2353 <entry><literal>collation_schema</literal></entry>
2354 <entry><type>sql_identifier</type></entry>
2356 Name of the schema containing the collation of the element
2357 type, null if default or the data type of the element is not
2363 <entry><literal>collation_name</literal></entry>
2364 <entry><type>sql_identifier</type></entry>
2366 Name of the collation of the element type, null if default or
2367 the data type of the element is not collatable
2372 <entry><literal>numeric_precision</literal></entry>
2373 <entry><type>cardinal_number</type></entry>
2374 <entry>Always null, since this information is not applied to array element data types in <productname>PostgreSQL</></entry>
2378 <entry><literal>numeric_precision_radix</literal></entry>
2379 <entry><type>cardinal_number</type></entry>
2380 <entry>Always null, since this information is not applied to array element data types in <productname>PostgreSQL</></entry>
2384 <entry><literal>numeric_scale</literal></entry>
2385 <entry><type>cardinal_number</type></entry>
2386 <entry>Always null, since this information is not applied to array element data types in <productname>PostgreSQL</></entry>
2390 <entry><literal>datetime_precision</literal></entry>
2391 <entry><type>cardinal_number</type></entry>
2392 <entry>Always null, since this information is not applied to array element data types in <productname>PostgreSQL</></entry>
2396 <entry><literal>interval_type</literal></entry>
2397 <entry><type>character_data</type></entry>
2398 <entry>Always null, since this information is not applied to array element data types in <productname>PostgreSQL</></entry>
2402 <entry><literal>interval_precision</literal></entry>
2403 <entry><type>character_data</type></entry>
2404 <entry>Always null, since this information is not applied to array element data types in <productname>PostgreSQL</></entry>
2408 <entry><literal>domain_default</literal></entry>
2409 <entry><type>character_data</type></entry>
2410 <entry>Not yet implemented</entry>
2414 <entry><literal>udt_catalog</literal></entry>
2415 <entry><type>sql_identifier</type></entry>
2417 Name of the database that the data type of the elements is
2418 defined in (always the current database)
2423 <entry><literal>udt_schema</literal></entry>
2424 <entry><type>sql_identifier</type></entry>
2426 Name of the schema that the data type of the elements is
2432 <entry><literal>udt_name</literal></entry>
2433 <entry><type>sql_identifier</type></entry>
2435 Name of the data type of the elements
2440 <entry><literal>scope_catalog</literal></entry>
2441 <entry><type>sql_identifier</type></entry>
2442 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2446 <entry><literal>scope_schema</literal></entry>
2447 <entry><type>sql_identifier</type></entry>
2448 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2452 <entry><literal>scope_name</literal></entry>
2453 <entry><type>sql_identifier</type></entry>
2454 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
2458 <entry><literal>maximum_cardinality</literal></entry>
2459 <entry><type>cardinal_number</type></entry>
2460 <entry>Always null, because arrays always have unlimited maximum cardinality in <productname>PostgreSQL</></entry>
2464 <entry><literal>dtd_identifier</literal></entry>
2465 <entry><type>sql_identifier</type></entry>
2467 An identifier of the data type descriptor of the element. This
2468 is currently not useful.
2476 <sect1 id="infoschema-enabled-roles">
2477 <title><literal>enabled_roles</literal></title>
2480 The view <literal>enabled_roles</literal> identifies the currently
2481 <quote>enabled roles</quote>. The enabled roles are recursively
2482 defined as the current user together with all roles that have been
2483 granted to the enabled roles with automatic inheritance. In other
2484 words, these are all roles that the current user has direct or
2485 indirect, automatically inheriting membership in.
2486 <indexterm><primary>enabled role</primary></indexterm>
2487 <indexterm><primary>role</primary><secondary>enabled</secondary></indexterm>
2491 For permission checking, the set of <quote>applicable roles</quote>
2492 is applied, which can be broader than the set of enabled roles. So
2493 generally, it is better to use the view
2494 <literal>applicable_roles</literal> instead of this one; see also
2499 <title><literal>enabled_roles</literal> Columns</title>
2505 <entry>Data Type</entry>
2506 <entry>Description</entry>
2512 <entry><literal>role_name</literal></entry>
2513 <entry><type>sql_identifier</type></entry>
2514 <entry>Name of a role</entry>
2521 <sect1 id="infoschema-foreign-data-wrapper-options">
2522 <title><literal>foreign_data_wrapper_options</literal></title>
2525 The view <literal>foreign_data_wrapper_options</literal> contains
2526 all the options defined for foreign-data wrappers in the current
2527 database. Only those foreign-data wrappers are shown that the
2528 current user has access to (by way of being the owner or having
2533 <title><literal>foreign_data_wrapper_options</literal> Columns</title>
2539 <entry>Data Type</entry>
2540 <entry>Description</entry>
2546 <entry><literal>foreign_data_wrapper_catalog</literal></entry>
2547 <entry><type>sql_identifier</type></entry>
2548 <entry>Name of the database that the foreign-data wrapper is defined in (always the current database)</entry>
2552 <entry><literal>foreign_data_wrapper_name</literal></entry>
2553 <entry><type>sql_identifier</type></entry>
2554 <entry>Name of the foreign-data wrapper</entry>
2558 <entry><literal>option_name</literal></entry>
2559 <entry><type>sql_identifier</type></entry>
2560 <entry>Name of an option</entry>
2564 <entry><literal>option_value</literal></entry>
2565 <entry><type>character_data</type></entry>
2566 <entry>Value of the option</entry>
2573 <sect1 id="infoschema-foreign-data-wrappers">
2574 <title><literal>foreign_data_wrappers</literal></title>
2577 The view <literal>foreign_data_wrappers</literal> contains all
2578 foreign-data wrappers defined in the current database. Only those
2579 foreign-data wrappers are shown that the current user has access to
2580 (by way of being the owner or having some privilege).
2584 <title><literal>foreign_data_wrappers</literal> Columns</title>
2590 <entry>Data Type</entry>
2591 <entry>Description</entry>
2597 <entry><literal>foreign_data_wrapper_catalog</literal></entry>
2598 <entry><type>sql_identifier</type></entry>
2599 <entry>Name of the database that contains the foreign-data
2600 wrapper (always the current database)</entry>
2604 <entry><literal>foreign_data_wrapper_name</literal></entry>
2605 <entry><type>sql_identifier</type></entry>
2606 <entry>Name of the foreign-data wrapper</entry>
2610 <entry><literal>authorization_identifier</literal></entry>
2611 <entry><type>sql_identifier</type></entry>
2612 <entry>Name of the owner of the foreign server</entry>
2616 <entry><literal>library_name</literal></entry>
2617 <entry><type>character_data</type></entry>
2618 <entry>File name of the library that implementing this foreign-data wrapper</entry>
2622 <entry><literal>foreign_data_wrapper_language</literal></entry>
2623 <entry><type>character_data</type></entry>
2624 <entry>Language used to implement this foreign-data wrapper</entry>
2631 <sect1 id="infoschema-foreign-server-options">
2632 <title><literal>foreign_server_options</literal></title>
2635 The view <literal>foreign_server_options</literal> contains all the
2636 options defined for foreign servers in the current database. Only
2637 those foreign servers are shown that the current user has access to
2638 (by way of being the owner or having some privilege).
2642 <title><literal>foreign_server_options</literal> Columns</title>
2648 <entry>Data Type</entry>
2649 <entry>Description</entry>
2655 <entry><literal>foreign_server_catalog</literal></entry>
2656 <entry><type>sql_identifier</type></entry>
2657 <entry>Name of the database that the foreign server is defined in (always the current database)</entry>
2661 <entry><literal>foreign_server_name</literal></entry>
2662 <entry><type>sql_identifier</type></entry>
2663 <entry>Name of the foreign server</entry>
2667 <entry><literal>option_name</literal></entry>
2668 <entry><type>sql_identifier</type></entry>
2669 <entry>Name of an option</entry>
2673 <entry><literal>option_value</literal></entry>
2674 <entry><type>character_data</type></entry>
2675 <entry>Value of the option</entry>
2682 <sect1 id="infoschema-foreign-servers">
2683 <title><literal>foreign_servers</literal></title>
2686 The view <literal>foreign_servers</literal> contains all foreign
2687 servers defined in the current database. Only those foreign
2688 servers are shown that the current user has access to (by way of
2689 being the owner or having some privilege).
2693 <title><literal>foreign_servers</literal> Columns</title>
2699 <entry>Data Type</entry>
2700 <entry>Description</entry>
2706 <entry><literal>foreign_server_catalog</literal></entry>
2707 <entry><type>sql_identifier</type></entry>
2708 <entry>Name of the database that the foreign server is defined in (always the current database)</entry>
2712 <entry><literal>foreign_server_name</literal></entry>
2713 <entry><type>sql_identifier</type></entry>
2714 <entry>Name of the foreign server</entry>
2718 <entry><literal>foreign_data_wrapper_catalog</literal></entry>
2719 <entry><type>sql_identifier</type></entry>
2720 <entry>Name of the database that contains the foreign-data
2721 wrapper used by the foreign server (always the current database)</entry>
2725 <entry><literal>foreign_data_wrapper_name</literal></entry>
2726 <entry><type>sql_identifier</type></entry>
2727 <entry>Name of the foreign-data wrapper used by the foreign server</entry>
2731 <entry><literal>foreign_server_type</literal></entry>
2732 <entry><type>character_data</type></entry>
2733 <entry>Foreign server type information, if specified upon creation</entry>
2737 <entry><literal>foreign_server_version</literal></entry>
2738 <entry><type>character_data</type></entry>
2739 <entry>Foreign server version information, if specified upon creation</entry>
2743 <entry><literal>authorization_identifier</literal></entry>
2744 <entry><type>sql_identifier</type></entry>
2745 <entry>Name of the owner of the foreign server</entry>
2752 <sect1 id="infoschema-foreign-table-options">
2753 <title><literal>foreign_table_options</literal></title>
2756 The view <literal>foreign_table_options</literal> contains all the
2757 options defined for foreign tables in the current database. Only
2758 those foreign tables are shown that the current user has access to
2759 (by way of being the owner or having some privilege).
2763 <title><literal>foreign_table_options</literal> Columns</title>
2769 <entry>Data Type</entry>
2770 <entry>Description</entry>
2776 <entry><literal>foreign_table_catalog</literal></entry>
2777 <entry><type>sql_identifier</type></entry>
2778 <entry>Name of the database that contains the foreign table (always the current database)</entry>
2782 <entry><literal>foreign_table_schema</literal></entry>
2783 <entry><type>sql_identifier</type></entry>
2784 <entry>Name of the schema that contains the foreign table</entry>
2788 <entry><literal>foreign_table_name</literal></entry>
2789 <entry><type>sql_identifier</type></entry>
2790 <entry>Name of the foreign table</entry>
2794 <entry><literal>foreign_server_catalog</literal></entry>
2795 <entry><type>sql_identifier</type></entry>
2796 <entry>Name of the database that the foreign server is defined in (always the current database)</entry>
2800 <entry><literal>foreign_server_name</literal></entry>
2801 <entry><type>sql_identifier</type></entry>
2802 <entry>Name of the foreign server</entry>
2806 <entry><literal>option_name</literal></entry>
2807 <entry><type>sql_identifier</type></entry>
2808 <entry>Name of an option</entry>
2812 <entry><literal>option_value</literal></entry>
2813 <entry><type>character_data</type></entry>
2814 <entry>Value of the option</entry>
2821 <sect1 id="infoschema-foreign-tables">
2822 <title><literal>foreign_tables</literal></title>
2825 The view <literal>foreign_tables</literal> contains all foreign
2826 tables defined in the current database. Only those foreign
2827 tables are shown that the current user has access to (by way of
2828 being the owner or having some privilege).
2832 <title><literal>foreign_tables</literal> Columns</title>
2838 <entry>Data Type</entry>
2839 <entry>Description</entry>
2845 <entry><literal>foreign_table_catalog</literal></entry>
2846 <entry><type>sql_identifier</type></entry>
2847 <entry>Name of the database that the foreign table is defined in (always the current database)</entry>
2851 <entry><literal>foreign_table_schema</literal></entry>
2852 <entry><type>sql_identifier</type></entry>
2853 <entry>Name of the schema that contains the foreign table</entry>
2857 <entry><literal>foreign_table_name</literal></entry>
2858 <entry><type>sql_identifier</type></entry>
2859 <entry>Name of the foreign table</entry>
2863 <entry><literal>foreign_server_catalog</literal></entry>
2864 <entry><type>sql_identifier</type></entry>
2865 <entry>Name of the database that the foreign server is defined in (always the current database)</entry>
2869 <entry><literal>foreign_server_name</literal></entry>
2870 <entry><type>sql_identifier</type></entry>
2871 <entry>Name of the foreign server</entry>
2878 <sect1 id="infoschema-key-column-usage">
2879 <title><literal>key_column_usage</literal></title>
2882 The view <literal>key_column_usage</literal> identifies all columns
2883 in the current database that are restricted by some unique, primary
2884 key, or foreign key constraint. Check constraints are not included
2885 in this view. Only those columns are shown that the current user
2886 has access to, by way of being the owner or having some privilege.
2890 <title><literal>key_column_usage</literal> Columns</title>
2896 <entry>Data Type</entry>
2897 <entry>Description</entry>
2903 <entry><literal>constraint_catalog</literal></entry>
2904 <entry><type>sql_identifier</type></entry>
2905 <entry>Name of the database that contains the constraint (always the current database)</entry>
2909 <entry><literal>constraint_schema</literal></entry>
2910 <entry><type>sql_identifier</type></entry>
2911 <entry>Name of the schema that contains the constraint</entry>
2915 <entry><literal>constraint_name</literal></entry>
2916 <entry><type>sql_identifier</type></entry>
2917 <entry>Name of the constraint</entry>
2921 <entry><literal>table_catalog</literal></entry>
2922 <entry><type>sql_identifier</type></entry>
2924 Name of the database that contains the table that contains the
2925 column that is restricted by this constraint (always the
2931 <entry><literal>table_schema</literal></entry>
2932 <entry><type>sql_identifier</type></entry>
2934 Name of the schema that contains the table that contains the
2935 column that is restricted by this constraint
2940 <entry><literal>table_name</literal></entry>
2941 <entry><type>sql_identifier</type></entry>
2943 Name of the table that contains the column that is restricted
2949 <entry><literal>column_name</literal></entry>
2950 <entry><type>sql_identifier</type></entry>
2952 Name of the column that is restricted by this constraint
2957 <entry><literal>ordinal_position</literal></entry>
2958 <entry><type>cardinal_number</type></entry>
2960 Ordinal position of the column within the constraint key (count
2966 <entry><literal>position_in_unique_constraint</literal></entry>
2967 <entry><type>cardinal_number</type></entry>
2969 For a foreign-key constraint, ordinal position of the referenced
2970 column within its unique constraint (count starts at 1);
2979 <sect1 id="infoschema-parameters">
2980 <title><literal>parameters</literal></title>
2983 The view <literal>parameters</literal> contains information about
2984 the parameters (arguments) of all functions in the current database.
2985 Only those functions are shown that the current user has access to
2986 (by way of being the owner or having some privilege).
2990 <title><literal>parameters</literal> Columns</title>
2996 <entry>Data Type</entry>
2997 <entry>Description</entry>
3003 <entry><literal>specific_catalog</literal></entry>
3004 <entry><type>sql_identifier</type></entry>
3005 <entry>Name of the database containing the function (always the current database)</entry>
3009 <entry><literal>specific_schema</literal></entry>
3010 <entry><type>sql_identifier</type></entry>
3011 <entry>Name of the schema containing the function</entry>
3015 <entry><literal>specific_name</literal></entry>
3016 <entry><type>sql_identifier</type></entry>
3018 The <quote>specific name</quote> of the function. See <xref
3019 linkend="infoschema-routines"> for more information.
3024 <entry><literal>ordinal_position</literal></entry>
3025 <entry><type>cardinal_number</type></entry>
3027 Ordinal position of the parameter in the argument list of the
3028 function (count starts at 1)
3033 <entry><literal>parameter_mode</literal></entry>
3034 <entry><type>character_data</type></entry>
3036 <literal>IN</literal> for input parameter,
3037 <literal>OUT</literal> for output parameter,
3038 and <literal>INOUT</literal> for input/output parameter.
3043 <entry><literal>is_result</literal></entry>
3044 <entry><type>yes_or_no</type></entry>
3045 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3049 <entry><literal>as_locator</literal></entry>
3050 <entry><type>yes_or_no</type></entry>
3051 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3055 <entry><literal>parameter_name</literal></entry>
3056 <entry><type>sql_identifier</type></entry>
3057 <entry>Name of the parameter, or null if the parameter has no name</entry>
3061 <entry><literal>data_type</literal></entry>
3062 <entry><type>character_data</type></entry>
3064 Data type of the parameter, if it is a built-in type, or
3065 <literal>ARRAY</literal> if it is some array (in that case, see
3066 the view <literal>element_types</literal>), else
3067 <literal>USER-DEFINED</literal> (in that case, the type is
3068 identified in <literal>udt_name</literal> and associated
3074 <entry><literal>character_maximum_length</literal></entry>
3075 <entry><type>cardinal_number</type></entry>
3076 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3080 <entry><literal>character_octet_length</literal></entry>
3081 <entry><type>cardinal_number</type></entry>
3082 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3086 <entry><literal>character_set_catalog</literal></entry>
3087 <entry><type>sql_identifier</type></entry>
3088 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3092 <entry><literal>character_set_schema</literal></entry>
3093 <entry><type>sql_identifier</type></entry>
3094 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3098 <entry><literal>character_set_name</literal></entry>
3099 <entry><type>sql_identifier</type></entry>
3100 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3104 <entry><literal>collation_catalog</literal></entry>
3105 <entry><type>sql_identifier</type></entry>
3106 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3110 <entry><literal>collation_schema</literal></entry>
3111 <entry><type>sql_identifier</type></entry>
3112 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3116 <entry><literal>collation_name</literal></entry>
3117 <entry><type>sql_identifier</type></entry>
3118 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3122 <entry><literal>numeric_precision</literal></entry>
3123 <entry><type>cardinal_number</type></entry>
3124 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3128 <entry><literal>numeric_precision_radix</literal></entry>
3129 <entry><type>cardinal_number</type></entry>
3130 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3134 <entry><literal>numeric_scale</literal></entry>
3135 <entry><type>cardinal_number</type></entry>
3136 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3140 <entry><literal>datetime_precision</literal></entry>
3141 <entry><type>cardinal_number</type></entry>
3142 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3146 <entry><literal>interval_type</literal></entry>
3147 <entry><type>character_data</type></entry>
3148 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3152 <entry><literal>interval_precision</literal></entry>
3153 <entry><type>character_data</type></entry>
3154 <entry>Always null, since this information is not applied to parameter data types in <productname>PostgreSQL</></entry>
3158 <entry><literal>udt_catalog</literal></entry>
3159 <entry><type>sql_identifier</type></entry>
3161 Name of the database that the data type of the parameter is
3162 defined in (always the current database)
3167 <entry><literal>udt_schema</literal></entry>
3168 <entry><type>sql_identifier</type></entry>
3170 Name of the schema that the data type of the parameter is
3176 <entry><literal>udt_name</literal></entry>
3177 <entry><type>sql_identifier</type></entry>
3179 Name of the data type of the parameter
3184 <entry><literal>scope_catalog</literal></entry>
3185 <entry><type>sql_identifier</type></entry>
3186 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3190 <entry><literal>scope_schema</literal></entry>
3191 <entry><type>sql_identifier</type></entry>
3192 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3196 <entry><literal>scope_name</literal></entry>
3197 <entry><type>sql_identifier</type></entry>
3198 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3202 <entry><literal>maximum_cardinality</literal></entry>
3203 <entry><type>cardinal_number</type></entry>
3204 <entry>Always null, because arrays always have unlimited maximum cardinality in <productname>PostgreSQL</></entry>
3208 <entry><literal>dtd_identifier</literal></entry>
3209 <entry><type>sql_identifier</type></entry>
3211 An identifier of the data type descriptor of the parameter,
3212 unique among the data type descriptors pertaining to the
3213 function. This is mainly useful for joining with other
3214 instances of such identifiers. (The specific format of the
3215 identifier is not defined and not guaranteed to remain the same
3216 in future versions.)
3224 <sect1 id="infoschema-referential-constraints">
3225 <title><literal>referential_constraints</literal></title>
3228 The view <literal>referential_constraints</literal> contains all
3229 referential (foreign key) constraints in the current database.
3230 Only those constraints are shown for which the current user has
3231 write access to the referencing table (by way of being the
3232 owner or having some privilege other than SELECT).
3236 <title><literal>referential_constraints</literal> Columns</title>
3242 <entry>Data Type</entry>
3243 <entry>Description</entry>
3249 <entry><literal>constraint_catalog</literal></entry>
3250 <entry><literal>sql_identifier</literal></entry>
3251 <entry>Name of the database containing the constraint (always the current database)</entry>
3255 <entry><literal>constraint_schema</literal></entry>
3256 <entry><literal>sql_identifier</literal></entry>
3257 <entry>Name of the schema containing the constraint</entry>
3261 <entry><literal>constraint_name</literal></entry>
3262 <entry><literal>sql_identifier</literal></entry>
3263 <entry>Name of the constraint</entry>
3267 <entry><literal>unique_constraint_catalog</literal></entry>
3268 <entry><literal>sql_identifier</literal></entry>
3270 Name of the database that contains the unique or primary key
3271 constraint that the foreign key constraint references (always
3272 the current database)
3277 <entry><literal>unique_constraint_schema</literal></entry>
3278 <entry><literal>sql_identifier</literal></entry>
3280 Name of the schema that contains the unique or primary key
3281 constraint that the foreign key constraint references
3286 <entry><literal>unique_constraint_name</literal></entry>
3287 <entry><literal>sql_identifier</literal></entry>
3289 Name of the unique or primary key constraint that the foreign
3290 key constraint references
3295 <entry><literal>match_option</literal></entry>
3296 <entry><literal>character_data</literal></entry>
3298 Match option of the foreign key constraint:
3299 <literal>FULL</literal>, <literal>PARTIAL</literal>, or
3300 <literal>NONE</literal>.
3305 <entry><literal>update_rule</literal></entry>
3306 <entry><literal>character_data</literal></entry>
3308 Update rule of the foreign key constraint:
3309 <literal>CASCADE</literal>, <literal>SET NULL</literal>,
3310 <literal>SET DEFAULT</literal>, <literal>RESTRICT</literal>, or
3311 <literal>NO ACTION</literal>.
3316 <entry><literal>delete_rule</literal></entry>
3317 <entry><literal>character_data</literal></entry>
3319 Delete rule of the foreign key constraint:
3320 <literal>CASCADE</literal>, <literal>SET NULL</literal>,
3321 <literal>SET DEFAULT</literal>, <literal>RESTRICT</literal>, or
3322 <literal>NO ACTION</literal>.
3331 <sect1 id="infoschema-role-column-grants">
3332 <title><literal>role_column_grants</literal></title>
3335 The view <literal>role_column_grants</literal> identifies all
3336 privileges granted on columns where the grantor or grantee is a
3337 currently enabled role. Further information can be found under
3338 <literal>column_privileges</literal>. The only effective
3339 difference between this view
3340 and <literal>column_privileges</literal> is that this view omits
3341 columns that have been made accessible to the current user by way
3342 of a grant to <literal>PUBLIC</literal>.
3346 <title><literal>role_column_grants</literal> Columns</title>
3352 <entry>Data Type</entry>
3353 <entry>Description</entry>
3359 <entry><literal>grantor</literal></entry>
3360 <entry><type>sql_identifier</type></entry>
3361 <entry>Name of the role that granted the privilege</entry>
3365 <entry><literal>grantee</literal></entry>
3366 <entry><type>sql_identifier</type></entry>
3367 <entry>Name of the role that the privilege was granted to</entry>
3371 <entry><literal>table_catalog</literal></entry>
3372 <entry><type>sql_identifier</type></entry>
3373 <entry>Name of the database that contains the table that contains the column (always the current database)</entry>
3377 <entry><literal>table_schema</literal></entry>
3378 <entry><type>sql_identifier</type></entry>
3379 <entry>Name of the schema that contains the table that contains the column</entry>
3383 <entry><literal>table_name</literal></entry>
3384 <entry><type>sql_identifier</type></entry>
3385 <entry>Name of the table that contains the column</entry>
3389 <entry><literal>column_name</literal></entry>
3390 <entry><type>sql_identifier</type></entry>
3391 <entry>Name of the column</entry>
3395 <entry><literal>privilege_type</literal></entry>
3396 <entry><type>character_data</type></entry>
3398 Type of the privilege: <literal>SELECT</literal>,
3399 <literal>INSERT</literal>, <literal>UPDATE</literal>, or
3400 <literal>REFERENCES</literal>
3405 <entry><literal>is_grantable</literal></entry>
3406 <entry><type>yes_or_no</type></entry>
3407 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
3414 <sect1 id="infoschema-role-routine-grants">
3415 <title><literal>role_routine_grants</literal></title>
3418 The view <literal>role_routine_grants</literal> identifies all
3419 privileges granted on functions where the grantor or grantee is a
3420 currently enabled role. Further information can be found under
3421 <literal>routine_privileges</literal>. The only effective
3422 difference between this view
3423 and <literal>routine_privileges</literal> is that this view omits
3424 functions that have been made accessible to the current user by way
3425 of a grant to <literal>PUBLIC</literal>.
3429 <title><literal>role_routine_grants</literal> Columns</title>
3435 <entry>Data Type</entry>
3436 <entry>Description</entry>
3442 <entry><literal>grantor</literal></entry>
3443 <entry><type>sql_identifier</type></entry>
3444 <entry>Name of the role that granted the privilege</entry>
3448 <entry><literal>grantee</literal></entry>
3449 <entry><type>sql_identifier</type></entry>
3450 <entry>Name of the role that the privilege was granted to</entry>
3454 <entry><literal>specific_catalog</literal></entry>
3455 <entry><type>sql_identifier</type></entry>
3456 <entry>Name of the database containing the function (always the current database)</entry>
3460 <entry><literal>specific_schema</literal></entry>
3461 <entry><type>sql_identifier</type></entry>
3462 <entry>Name of the schema containing the function</entry>
3466 <entry><literal>specific_name</literal></entry>
3467 <entry><type>sql_identifier</type></entry>
3469 The <quote>specific name</quote> of the function. See <xref
3470 linkend="infoschema-routines"> for more information.
3475 <entry><literal>routine_catalog</literal></entry>
3476 <entry><type>sql_identifier</type></entry>
3477 <entry>Name of the database containing the function (always the current database)</entry>
3481 <entry><literal>routine_schema</literal></entry>
3482 <entry><type>sql_identifier</type></entry>
3483 <entry>Name of the schema containing the function</entry>
3487 <entry><literal>routine_name</literal></entry>
3488 <entry><type>sql_identifier</type></entry>
3489 <entry>Name of the function (might be duplicated in case of overloading)</entry>
3493 <entry><literal>privilege_type</literal></entry>
3494 <entry><type>character_data</type></entry>
3495 <entry>Always <literal>EXECUTE</literal> (the only privilege type for functions)</entry>
3499 <entry><literal>is_grantable</literal></entry>
3500 <entry><type>yes_or_no</type></entry>
3501 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
3508 <sect1 id="infoschema-role-table-grants">
3509 <title><literal>role_table_grants</literal></title>
3512 The view <literal>role_table_grants</literal> identifies all
3513 privileges granted on tables or views where the grantor or grantee
3514 is a currently enabled role. Further information can be found
3515 under <literal>table_privileges</literal>. The only effective
3516 difference between this view
3517 and <literal>table_privileges</literal> is that this view omits
3518 tables that have been made accessible to the current user by way of
3519 a grant to <literal>PUBLIC</literal>.
3523 <title><literal>role_table_grants</literal> Columns</title>
3529 <entry>Data Type</entry>
3530 <entry>Description</entry>
3536 <entry><literal>grantor</literal></entry>
3537 <entry><type>sql_identifier</type></entry>
3538 <entry>Name of the role that granted the privilege</entry>
3542 <entry><literal>grantee</literal></entry>
3543 <entry><type>sql_identifier</type></entry>
3544 <entry>Name of the role that the privilege was granted to</entry>
3548 <entry><literal>table_catalog</literal></entry>
3549 <entry><type>sql_identifier</type></entry>
3550 <entry>Name of the database that contains the table (always the current database)</entry>
3554 <entry><literal>table_schema</literal></entry>
3555 <entry><type>sql_identifier</type></entry>
3556 <entry>Name of the schema that contains the table</entry>
3560 <entry><literal>table_name</literal></entry>
3561 <entry><type>sql_identifier</type></entry>
3562 <entry>Name of the table</entry>
3566 <entry><literal>privilege_type</literal></entry>
3567 <entry><type>character_data</type></entry>
3569 Type of the privilege: <literal>SELECT</literal>,
3570 <literal>INSERT</literal>, <literal>UPDATE</literal>,
3571 <literal>DELETE</literal>, <literal>TRUNCATE</literal>,
3572 <literal>REFERENCES</literal>, or <literal>TRIGGER</literal>
3577 <entry><literal>is_grantable</literal></entry>
3578 <entry><type>yes_or_no</type></entry>
3579 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
3583 <entry><literal>with_hierarchy</literal></entry>
3584 <entry><type>yes_or_no</type></entry>
3585 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3592 <sect1 id="infoschema-role-udt-grants">
3593 <title><literal>role_udt_grants</literal></title>
3596 The view <literal>role_udt_grants</literal> is intended to identify
3597 <literal>USAGE</literal> privileges granted on user-defined types
3598 where the grantor or grantee is a currently enabled role. Further
3599 information can be found under
3600 <literal>udt_privileges</literal>. The only effective difference
3601 between this view and <literal>udt_privileges</literal> is that
3602 this view omits objects that have been made accessible to the
3603 current user by way of a grant to <literal>PUBLIC</literal>. Since
3604 data types do not have real privileges in PostgreSQL, but only an
3605 implicit grant to <literal>PUBLIC</literal>, this view is empty.
3609 <title><literal>role_udt_grants</literal> Columns</title>
3615 <entry>Data Type</entry>
3616 <entry>Description</entry>
3622 <entry><literal>grantor</literal></entry>
3623 <entry><type>sql_identifier</type></entry>
3624 <entry>The name of the role that granted the privilege</entry>
3628 <entry><literal>grantee</literal></entry>
3629 <entry><type>sql_identifier</type></entry>
3630 <entry>The name of the role that the privilege was granted to</entry>
3634 <entry><literal>udt_catalog</literal></entry>
3635 <entry><type>sql_identifier</type></entry>
3636 <entry>Name of the database containing the type (always the current database)</entry>
3640 <entry><literal>udt_schema</literal></entry>
3641 <entry><type>sql_identifier</type></entry>
3642 <entry>Name of the schema containing the type</entry>
3646 <entry><literal>udt_name</literal></entry>
3647 <entry><type>sql_identifier</type></entry>
3648 <entry>Name of the type</entry>
3652 <entry><literal>privilege_type</literal></entry>
3653 <entry><type>character_data</type></entry>
3654 <entry>Always <literal>TYPE USAGE</literal></entry>
3658 <entry><literal>is_grantable</literal></entry>
3659 <entry><type>yes_or_no</type></entry>
3660 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
3667 <sect1 id="infoschema-role-usage-grants">
3668 <title><literal>role_usage_grants</literal></title>
3671 The view <literal>role_usage_grants</literal> identifies
3672 <literal>USAGE</literal> privileges granted on various kinds of
3673 objects where the grantor or grantee is a currently enabled role.
3674 Further information can be found under
3675 <literal>usage_privileges</literal>. The only effective difference
3676 between this view and <literal>usage_privileges</literal> is that
3677 this view omits objects that have been made accessible to the
3678 current user by way of a grant to <literal>PUBLIC</literal>.
3682 <title><literal>role_usage_grants</literal> Columns</title>
3688 <entry>Data Type</entry>
3689 <entry>Description</entry>
3695 <entry><literal>grantor</literal></entry>
3696 <entry><type>sql_identifier</type></entry>
3697 <entry>The name of the role that granted the privilege</entry>
3701 <entry><literal>grantee</literal></entry>
3702 <entry><type>sql_identifier</type></entry>
3703 <entry>The name of the role that the privilege was granted to</entry>
3707 <entry><literal>object_catalog</literal></entry>
3708 <entry><type>sql_identifier</type></entry>
3709 <entry>Name of the database containing the object (always the current database)</entry>
3713 <entry><literal>object_schema</literal></entry>
3714 <entry><type>sql_identifier</type></entry>
3715 <entry>Name of the schema containing the object, if applicable,
3716 else an empty string</entry>
3720 <entry><literal>object_name</literal></entry>
3721 <entry><type>sql_identifier</type></entry>
3722 <entry>Name of the object</entry>
3726 <entry><literal>object_type</literal></entry>
3727 <entry><type>character_data</type></entry>
3728 <entry><literal>COLLATION</literal> or <literal>DOMAIN</literal> or <literal>FOREIGN DATA WRAPPER</literal> or <literal>FOREIGN SERVER</literal></entry>
3732 <entry><literal>privilege_type</literal></entry>
3733 <entry><type>character_data</type></entry>
3734 <entry>Always <literal>USAGE</literal></entry>
3738 <entry><literal>is_grantable</literal></entry>
3739 <entry><type>yes_or_no</type></entry>
3740 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
3747 <sect1 id="infoschema-routine-privileges">
3748 <title><literal>routine_privileges</literal></title>
3751 The view <literal>routine_privileges</literal> identifies all
3752 privileges granted on functions to a currently enabled role or by a
3753 currently enabled role. There is one row for each combination of function,
3754 grantor, and grantee.
3758 <title><literal>routine_privileges</literal> Columns</title>
3764 <entry>Data Type</entry>
3765 <entry>Description</entry>
3771 <entry><literal>grantor</literal></entry>
3772 <entry><type>sql_identifier</type></entry>
3773 <entry>Name of the role that granted the privilege</entry>
3777 <entry><literal>grantee</literal></entry>
3778 <entry><type>sql_identifier</type></entry>
3779 <entry>Name of the role that the privilege was granted to</entry>
3783 <entry><literal>specific_catalog</literal></entry>
3784 <entry><type>sql_identifier</type></entry>
3785 <entry>Name of the database containing the function (always the current database)</entry>
3789 <entry><literal>specific_schema</literal></entry>
3790 <entry><type>sql_identifier</type></entry>
3791 <entry>Name of the schema containing the function</entry>
3795 <entry><literal>specific_name</literal></entry>
3796 <entry><type>sql_identifier</type></entry>
3798 The <quote>specific name</quote> of the function. See <xref
3799 linkend="infoschema-routines"> for more information.
3804 <entry><literal>routine_catalog</literal></entry>
3805 <entry><type>sql_identifier</type></entry>
3806 <entry>Name of the database containing the function (always the current database)</entry>
3810 <entry><literal>routine_schema</literal></entry>
3811 <entry><type>sql_identifier</type></entry>
3812 <entry>Name of the schema containing the function</entry>
3816 <entry><literal>routine_name</literal></entry>
3817 <entry><type>sql_identifier</type></entry>
3818 <entry>Name of the function (might be duplicated in case of overloading)</entry>
3822 <entry><literal>privilege_type</literal></entry>
3823 <entry><type>character_data</type></entry>
3824 <entry>Always <literal>EXECUTE</literal> (the only privilege type for functions)</entry>
3828 <entry><literal>is_grantable</literal></entry>
3829 <entry><type>yes_or_no</type></entry>
3830 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
3837 <sect1 id="infoschema-routines">
3838 <title><literal>routines</literal></title>
3841 The view <literal>routines</literal> contains all functions in the
3842 current database. Only those functions are shown that the current
3843 user has access to (by way of being the owner or having some
3848 <title><literal>routines</literal> Columns</title>
3854 <entry>Data Type</entry>
3855 <entry>Description</entry>
3861 <entry><literal>specific_catalog</literal></entry>
3862 <entry><type>sql_identifier</type></entry>
3863 <entry>Name of the database containing the function (always the current database)</entry>
3867 <entry><literal>specific_schema</literal></entry>
3868 <entry><type>sql_identifier</type></entry>
3869 <entry>Name of the schema containing the function</entry>
3873 <entry><literal>specific_name</literal></entry>
3874 <entry><type>sql_identifier</type></entry>
3876 The <quote>specific name</quote> of the function. This is a
3877 name that uniquely identifies the function in the schema, even
3878 if the real name of the function is overloaded. The format of
3879 the specific name is not defined, it should only be used to
3880 compare it to other instances of specific routine names.
3885 <entry><literal>routine_catalog</literal></entry>
3886 <entry><type>sql_identifier</type></entry>
3887 <entry>Name of the database containing the function (always the current database)</entry>
3891 <entry><literal>routine_schema</literal></entry>
3892 <entry><type>sql_identifier</type></entry>
3893 <entry>Name of the schema containing the function</entry>
3897 <entry><literal>routine_name</literal></entry>
3898 <entry><type>sql_identifier</type></entry>
3899 <entry>Name of the function (might be duplicated in case of overloading)</entry>
3903 <entry><literal>routine_type</literal></entry>
3904 <entry><type>character_data</type></entry>
3906 Always <literal>FUNCTION</literal> (In the future there might
3907 be other types of routines.)
3912 <entry><literal>module_catalog</literal></entry>
3913 <entry><type>sql_identifier</type></entry>
3914 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3918 <entry><literal>module_schema</literal></entry>
3919 <entry><type>sql_identifier</type></entry>
3920 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3924 <entry><literal>module_name</literal></entry>
3925 <entry><type>sql_identifier</type></entry>
3926 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3930 <entry><literal>udt_catalog</literal></entry>
3931 <entry><type>sql_identifier</type></entry>
3932 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3936 <entry><literal>udt_schema</literal></entry>
3937 <entry><type>sql_identifier</type></entry>
3938 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3942 <entry><literal>udt_name</literal></entry>
3943 <entry><type>sql_identifier</type></entry>
3944 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3948 <entry><literal>data_type</literal></entry>
3949 <entry><type>character_data</type></entry>
3951 Return data type of the function, if it is a built-in type, or
3952 <literal>ARRAY</literal> if it is some array (in that case, see
3953 the view <literal>element_types</literal>), else
3954 <literal>USER-DEFINED</literal> (in that case, the type is
3955 identified in <literal>type_udt_name</literal> and associated
3961 <entry><literal>character_maximum_length</literal></entry>
3962 <entry><type>cardinal_number</type></entry>
3963 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
3967 <entry><literal>character_octet_length</literal></entry>
3968 <entry><type>cardinal_number</type></entry>
3969 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
3973 <entry><literal>character_set_catalog</literal></entry>
3974 <entry><type>sql_identifier</type></entry>
3975 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3979 <entry><literal>character_set_schema</literal></entry>
3980 <entry><type>sql_identifier</type></entry>
3981 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3985 <entry><literal>character_set_name</literal></entry>
3986 <entry><type>sql_identifier</type></entry>
3987 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
3991 <entry><literal>collation_catalog</literal></entry>
3992 <entry><type>sql_identifier</type></entry>
3993 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
3997 <entry><literal>collation_schema</literal></entry>
3998 <entry><type>sql_identifier</type></entry>
3999 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4003 <entry><literal>collation_name</literal></entry>
4004 <entry><type>sql_identifier</type></entry>
4005 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4009 <entry><literal>numeric_precision</literal></entry>
4010 <entry><type>cardinal_number</type></entry>
4011 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4015 <entry><literal>numeric_precision_radix</literal></entry>
4016 <entry><type>cardinal_number</type></entry>
4017 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4021 <entry><literal>numeric_scale</literal></entry>
4022 <entry><type>cardinal_number</type></entry>
4023 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4027 <entry><literal>datetime_precision</literal></entry>
4028 <entry><type>cardinal_number</type></entry>
4029 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4033 <entry><literal>interval_type</literal></entry>
4034 <entry><type>character_data</type></entry>
4035 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4039 <entry><literal>interval_precision</literal></entry>
4040 <entry><type>character_data</type></entry>
4041 <entry>Always null, since this information is not applied to return data types in <productname>PostgreSQL</></entry>
4045 <entry><literal>type_udt_catalog</literal></entry>
4046 <entry><type>sql_identifier</type></entry>
4048 Name of the database that the return data type of the function
4049 is defined in (always the current database)
4054 <entry><literal>type_udt_schema</literal></entry>
4055 <entry><type>sql_identifier</type></entry>
4057 Name of the schema that the return data type of the function is
4063 <entry><literal>type_udt_name</literal></entry>
4064 <entry><type>sql_identifier</type></entry>
4066 Name of the return data type of the function
4071 <entry><literal>scope_catalog</literal></entry>
4072 <entry><type>sql_identifier</type></entry>
4073 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4077 <entry><literal>scope_schema</literal></entry>
4078 <entry><type>sql_identifier</type></entry>
4079 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4083 <entry><literal>scope_name</literal></entry>
4084 <entry><type>sql_identifier</type></entry>
4085 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4089 <entry><literal>maximum_cardinality</literal></entry>
4090 <entry><type>cardinal_number</type></entry>
4091 <entry>Always null, because arrays always have unlimited maximum cardinality in <productname>PostgreSQL</></entry>
4095 <entry><literal>dtd_identifier</literal></entry>
4096 <entry><type>sql_identifier</type></entry>
4098 An identifier of the data type descriptor of the return data
4099 type of this function, unique among the data type descriptors
4100 pertaining to the function. This is mainly useful for joining
4101 with other instances of such identifiers. (The specific format
4102 of the identifier is not defined and not guaranteed to remain
4103 the same in future versions.)
4108 <entry><literal>routine_body</literal></entry>
4109 <entry><type>character_data</type></entry>
4111 If the function is an SQL function, then
4112 <literal>SQL</literal>, else <literal>EXTERNAL</literal>.
4117 <entry><literal>routine_definition</literal></entry>
4118 <entry><type>character_data</type></entry>
4120 The source text of the function (null if the function is not
4121 owned by a currently enabled role). (According to the SQL
4122 standard, this column is only applicable if
4123 <literal>routine_body</literal> is <literal>SQL</literal>, but
4124 in <productname>PostgreSQL</productname> it will contain
4125 whatever source text was specified when the function was
4131 <entry><literal>external_name</literal></entry>
4132 <entry><type>character_data</type></entry>
4134 If this function is a C function, then the external name (link
4135 symbol) of the function; else null. (This works out to be the
4136 same value that is shown in
4137 <literal>routine_definition</literal>.)
4142 <entry><literal>external_language</literal></entry>
4143 <entry><type>character_data</type></entry>
4144 <entry>The language the function is written in</entry>
4148 <entry><literal>parameter_style</literal></entry>
4149 <entry><type>character_data</type></entry>
4151 Always <literal>GENERAL</literal> (The SQL standard defines
4152 other parameter styles, which are not available in <productname>PostgreSQL</>.)
4157 <entry><literal>is_deterministic</literal></entry>
4158 <entry><type>yes_or_no</type></entry>
4160 If the function is declared immutable (called deterministic in
4161 the SQL standard), then <literal>YES</literal>, else
4162 <literal>NO</literal>. (You cannot query the other volatility
4163 levels available in <productname>PostgreSQL</> through the information schema.)
4168 <entry><literal>sql_data_access</literal></entry>
4169 <entry><type>character_data</type></entry>
4171 Always <literal>MODIFIES</literal>, meaning that the function
4172 possibly modifies SQL data. This information is not useful for
4173 <productname>PostgreSQL</>.
4178 <entry><literal>is_null_call</literal></entry>
4179 <entry><type>yes_or_no</type></entry>
4181 If the function automatically returns null if any of its
4182 arguments are null, then <literal>YES</literal>, else
4183 <literal>NO</literal>.
4188 <entry><literal>sql_path</literal></entry>
4189 <entry><type>character_data</type></entry>
4190 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4194 <entry><literal>schema_level_routine</literal></entry>
4195 <entry><type>yes_or_no</type></entry>
4197 Always <literal>YES</literal> (The opposite would be a method
4198 of a user-defined type, which is a feature not available in
4199 <productname>PostgreSQL</>.)
4204 <entry><literal>max_dynamic_result_sets</literal></entry>
4205 <entry><type>cardinal_number</type></entry>
4206 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4210 <entry><literal>is_user_defined_cast</literal></entry>
4211 <entry><type>yes_or_no</type></entry>
4212 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4216 <entry><literal>is_implicitly_invocable</literal></entry>
4217 <entry><type>yes_or_no</type></entry>
4218 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4222 <entry><literal>security_type</literal></entry>
4223 <entry><type>character_data</type></entry>
4225 If the function runs with the privileges of the current user,
4226 then <literal>INVOKER</literal>, if the function runs with the
4227 privileges of the user who defined it, then
4228 <literal>DEFINER</literal>.
4233 <entry><literal>to_sql_specific_catalog</literal></entry>
4234 <entry><type>sql_identifier</type></entry>
4235 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4239 <entry><literal>to_sql_specific_schema</literal></entry>
4240 <entry><type>sql_identifier</type></entry>
4241 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4245 <entry><literal>to_sql_specific_name</literal></entry>
4246 <entry><type>sql_identifier</type></entry>
4247 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4251 <entry><literal>as_locator</literal></entry>
4252 <entry><type>yes_or_no</type></entry>
4253 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4257 <entry><literal>created</literal></entry>
4258 <entry><type>time_stamp</type></entry>
4259 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4263 <entry><literal>last_altered</literal></entry>
4264 <entry><type>time_stamp</type></entry>
4265 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4269 <entry><literal>new_savepoint_level</literal></entry>
4270 <entry><type>yes_or_no</type></entry>
4271 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4275 <entry><literal>is_udt_dependent</literal></entry>
4276 <entry><type>yes_or_no</type></entry>
4277 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4281 <entry><literal>result_cast_from_data_type</literal></entry>
4282 <entry><type>character_data</type></entry>
4283 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4287 <entry><literal>result_cast_as_locator</literal></entry>
4288 <entry><type>yes_or_no</type></entry>
4289 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4293 <entry><literal>result_cast_char_max_length</literal></entry>
4294 <entry><type>cardinal_number</type></entry>
4295 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4299 <entry><literal>result_cast_char_octet_length</literal></entry>
4300 <entry><type>character_data</type></entry>
4301 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4305 <entry><literal>result_cast_char_set_catalog</literal></entry>
4306 <entry><type>sql_identifier</type></entry>
4307 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4311 <entry><literal>result_cast_char_set_schema</literal></entry>
4312 <entry><type>sql_identifier</type></entry>
4313 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4317 <entry><literal>result_cast_char_set_name</literal></entry>
4318 <entry><type>sql_identifier</type></entry>
4319 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4323 <entry><literal>result_cast_collation_catalog</literal></entry>
4324 <entry><type>sql_identifier</type></entry>
4325 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4329 <entry><literal>result_cast_collation_schema</literal></entry>
4330 <entry><type>sql_identifier</type></entry>
4331 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4335 <entry><literal>result_cast_collation_name</literal></entry>
4336 <entry><type>sql_identifier</type></entry>
4337 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4341 <entry><literal>result_cast_numeric_precision</literal></entry>
4342 <entry><type>cardinal_number</type></entry>
4343 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4347 <entry><literal>result_cast_numeric_precision_radix</literal></entry>
4348 <entry><type>cardinal_number</type></entry>
4349 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4353 <entry><literal>result_cast_numeric_scale</literal></entry>
4354 <entry><type>cardinal_number</type></entry>
4355 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4359 <entry><literal>result_cast_datetime_precision</literal></entry>
4360 <entry><type>character_data</type></entry>
4361 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4365 <entry><literal>result_cast_interval_type</literal></entry>
4366 <entry><type>character_data</type></entry>
4367 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4371 <entry><literal>result_cast_interval_precision</literal></entry>
4372 <entry><type>character_data</type></entry>
4373 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4377 <entry><literal>result_cast_type_udt_catalog</literal></entry>
4378 <entry><type>sql_identifier</type></entry>
4379 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4383 <entry><literal>result_cast_type_udt_schema</literal></entry>
4384 <entry><type>sql_identifier</type></entry>
4385 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4389 <entry><literal>result_cast_type_udt_name</literal></entry>
4390 <entry><type>sql_identifier</type></entry>
4391 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4395 <entry><literal>result_cast_scope_catalog</literal></entry>
4396 <entry><type>sql_identifier</type></entry>
4397 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4401 <entry><literal>result_cast_scope_schema</literal></entry>
4402 <entry><type>sql_identifier</type></entry>
4403 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4407 <entry><literal>result_cast_scope_name</literal></entry>
4408 <entry><type>sql_identifier</type></entry>
4409 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4413 <entry><literal>result_cast_maximum_cardinality</literal></entry>
4414 <entry><type>cardinal_number</type></entry>
4415 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4419 <entry><literal>result_cast_dtd_identifier</literal></entry>
4420 <entry><type>sql_identifier</type></entry>
4421 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4428 <sect1 id="infoschema-schemata">
4429 <title><literal>schemata</literal></title>
4432 The view <literal>schemata</literal> contains all schemas in the
4433 current database that are owned by a currently enabled role.
4437 <title><literal>schemata</literal> Columns</title>
4443 <entry>Data Type</entry>
4444 <entry>Description</entry>
4450 <entry><literal>catalog_name</literal></entry>
4451 <entry><type>sql_identifier</type></entry>
4452 <entry>Name of the database that the schema is contained in (always the current database)</entry>
4456 <entry><literal>schema_name</literal></entry>
4457 <entry><type>sql_identifier</type></entry>
4458 <entry>Name of the schema</entry>
4462 <entry><literal>schema_owner</literal></entry>
4463 <entry><type>sql_identifier</type></entry>
4464 <entry>Name of the owner of the schema</entry>
4468 <entry><literal>default_character_set_catalog</literal></entry>
4469 <entry><type>sql_identifier</type></entry>
4470 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4474 <entry><literal>default_character_set_schema</literal></entry>
4475 <entry><type>sql_identifier</type></entry>
4476 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4480 <entry><literal>default_character_set_name</literal></entry>
4481 <entry><type>sql_identifier</type></entry>
4482 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4486 <entry><literal>sql_path</literal></entry>
4487 <entry><type>character_data</type></entry>
4488 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4495 <sect1 id="infoschema-sequences">
4496 <title><literal>sequences</literal></title>
4499 The view <literal>sequences</literal> contains all sequences
4500 defined in the current database. Only those sequences are shown
4501 that the current user has access to (by way of being the owner or
4502 having some privilege).
4506 <title><literal>sequences</literal> Columns</title>
4512 <entry>Data Type</entry>
4513 <entry>Description</entry>
4519 <entry><literal>sequence_catalog</literal></entry>
4520 <entry><type>sql_identifier</type></entry>
4521 <entry>Name of the database that contains the sequence (always the current database)</entry>
4525 <entry><literal>sequence_schema</literal></entry>
4526 <entry><type>sql_identifier</type></entry>
4527 <entry>Name of the schema that contains the sequence</entry>
4531 <entry><literal>sequence_name</literal></entry>
4532 <entry><type>sql_identifier</type></entry>
4533 <entry>Name of the sequence</entry>
4537 <entry><literal>data_type</literal></entry>
4538 <entry><type>character_data</type></entry>
4540 The data type of the sequence. In
4541 <productname>PostgreSQL</productname>, this is currently always
4542 <literal>bigint</literal>.
4547 <entry><literal>numeric_precision</literal></entry>
4548 <entry><type>cardinal_number</type></entry>
4550 This column contains the (declared or implicit) precision of
4551 the sequence data type (see above). The precision indicates
4552 the number of significant digits. It can be expressed in
4553 decimal (base 10) or binary (base 2) terms, as specified in the
4554 column <literal>numeric_precision_radix</literal>.
4559 <entry><literal>numeric_precision_radix</literal></entry>
4560 <entry><type>cardinal_number</type></entry>
4562 This column indicates in which base the values in the columns
4563 <literal>numeric_precision</literal> and
4564 <literal>numeric_scale</literal> are expressed. The value is
4570 <entry><literal>numeric_scale</literal></entry>
4571 <entry><type>cardinal_number</type></entry>
4573 This column contains the (declared or implicit) scale of the
4574 sequence data type (see above). The scale indicates the number
4575 of significant digits to the right of the decimal point. It
4576 can be expressed in decimal (base 10) or binary (base 2) terms,
4577 as specified in the column
4578 <literal>numeric_precision_radix</literal>.
4583 <entry><literal>start_value</literal></entry>
4584 <entry><type>character_data</type></entry>
4585 <entry>The start value of the sequence</entry>
4589 <entry><literal>minimum_value</literal></entry>
4590 <entry><type>character_data</type></entry>
4591 <entry>The minimum value of the sequence</entry>
4595 <entry><literal>maximum_value</literal></entry>
4596 <entry><type>character_data</type></entry>
4597 <entry>The maximum value of the sequence</entry>
4601 <entry><literal>increment</literal></entry>
4602 <entry><type>character_data</type></entry>
4603 <entry>The increment of the sequence</entry>
4607 <entry><literal>cycle_option</literal></entry>
4608 <entry><type>yes_or_no</type></entry>
4609 <entry><literal>YES</literal> if the sequence cycles, else <literal>NO</literal></entry>
4616 Note that in accordance with the SQL standard, the start, minimum,
4617 maximum, and increment values are returned as character strings.
4621 <sect1 id="infoschema-sql-features">
4622 <title><literal>sql_features</literal></title>
4625 The table <literal>sql_features</literal> contains information
4626 about which formal features defined in the SQL standard are
4627 supported by <productname>PostgreSQL</productname>. This is the
4628 same information that is presented in <xref linkend="features">.
4629 There you can also find some additional background information.
4633 <title><literal>sql_features</literal> Columns</title>
4639 <entry>Data Type</entry>
4640 <entry>Description</entry>
4646 <entry><literal>feature_id</literal></entry>
4647 <entry><type>character_data</type></entry>
4648 <entry>Identifier string of the feature</entry>
4652 <entry><literal>feature_name</literal></entry>
4653 <entry><type>character_data</type></entry>
4654 <entry>Descriptive name of the feature</entry>
4658 <entry><literal>sub_feature_id</literal></entry>
4659 <entry><type>character_data</type></entry>
4660 <entry>Identifier string of the subfeature, or a zero-length string if not a subfeature</entry>
4664 <entry><literal>sub_feature_name</literal></entry>
4665 <entry><type>character_data</type></entry>
4666 <entry>Descriptive name of the subfeature, or a zero-length string if not a subfeature</entry>
4670 <entry><literal>is_supported</literal></entry>
4671 <entry><type>yes_or_no</type></entry>
4673 <literal>YES</literal> if the feature is fully supported by the
4674 current version of <productname>PostgreSQL</>, <literal>NO</literal> if not
4679 <entry><literal>is_verified_by</literal></entry>
4680 <entry><type>character_data</type></entry>
4682 Always null, since the <productname>PostgreSQL</> development group does not
4683 perform formal testing of feature conformance
4688 <entry><literal>comments</literal></entry>
4689 <entry><type>character_data</type></entry>
4690 <entry>Possibly a comment about the supported status of the feature</entry>
4697 <sect1 id="infoschema-sql-implementation-info">
4698 <title><literal>sql_implementation_info</literal></title>
4701 The table <literal>sql_implementation_info</literal> contains
4702 information about various aspects that are left
4703 implementation-defined by the SQL standard. This information is
4704 primarily intended for use in the context of the ODBC interface;
4705 users of other interfaces will probably find this information to be
4706 of little use. For this reason, the individual implementation
4707 information items are not described here; you will find them in the
4708 description of the ODBC interface.
4712 <title><literal>sql_implementation_info</literal> Columns</title>
4718 <entry>Data Type</entry>
4719 <entry>Description</entry>
4725 <entry><literal>implementation_info_id</literal></entry>
4726 <entry><type>character_data</type></entry>
4727 <entry>Identifier string of the implementation information item</entry>
4731 <entry><literal>implementation_info_name</literal></entry>
4732 <entry><type>character_data</type></entry>
4733 <entry>Descriptive name of the implementation information item</entry>
4737 <entry><literal>integer_value</literal></entry>
4738 <entry><type>cardinal_number</type></entry>
4740 Value of the implementation information item, or null if the
4741 value is contained in the column
4742 <literal>character_value</literal>
4747 <entry><literal>character_value</literal></entry>
4748 <entry><type>character_data</type></entry>
4750 Value of the implementation information item, or null if the
4751 value is contained in the column
4752 <literal>integer_value</literal>
4757 <entry><literal>comments</literal></entry>
4758 <entry><type>character_data</type></entry>
4759 <entry>Possibly a comment pertaining to the implementation information item</entry>
4766 <sect1 id="infoschema-sql-languages">
4767 <title><literal>sql_languages</literal></title>
4770 The table <literal>sql_languages</literal> contains one row for
4771 each SQL language binding that is supported by
4772 <productname>PostgreSQL</productname>.
4773 <productname>PostgreSQL</productname> supports direct SQL and
4774 embedded SQL in C; that is all you will learn from this table.
4778 <title><literal>sql_languages</literal> Columns</title>
4784 <entry>Data Type</entry>
4785 <entry>Description</entry>
4791 <entry><literal>sql_language_source</literal></entry>
4792 <entry><type>character_data</type></entry>
4794 The name of the source of the language definition; always
4795 <literal>ISO 9075</literal>, that is, the SQL standard
4800 <entry><literal>sql_language_year</literal></entry>
4801 <entry><type>character_data</type></entry>
4803 The year the standard referenced in
4804 <literal>sql_language_source</literal> was approved; currently
4810 <entry><literal>sql_language_conformance</literal></entry>
4811 <entry><type>character_data</type></entry>
4813 The standard conformance level for the language binding. For
4814 ISO 9075:2003 this is always <literal>CORE</literal>.
4819 <entry><literal>sql_language_integrity</literal></entry>
4820 <entry><type>character_data</type></entry>
4821 <entry>Always null (This value is relevant to an earlier version of the SQL standard.)</entry>
4825 <entry><literal>sql_language_implementation</literal></entry>
4826 <entry><type>character_data</type></entry>
4827 <entry>Always null</entry>
4831 <entry><literal>sql_language_binding_style</literal></entry>
4832 <entry><type>character_data</type></entry>
4834 The language binding style, either <literal>DIRECT</literal> or
4835 <literal>EMBEDDED</literal>
4840 <entry><literal>sql_language_programming_language</literal></entry>
4841 <entry><type>character_data</type></entry>
4843 The programming language, if the binding style is
4844 <literal>EMBEDDED</literal>, else null. <productname>PostgreSQL</> only
4845 supports the language C.
4853 <sect1 id="infoschema-sql-packages">
4854 <title><literal>sql_packages</literal></title>
4857 The table <literal>sql_packages</literal> contains information
4858 about which feature packages defined in the SQL standard are
4859 supported by <productname>PostgreSQL</productname>. Refer to <xref
4860 linkend="features"> for background information on feature packages.
4864 <title><literal>sql_packages</literal> Columns</title>
4870 <entry>Data Type</entry>
4871 <entry>Description</entry>
4877 <entry><literal>feature_id</literal></entry>
4878 <entry><type>character_data</type></entry>
4879 <entry>Identifier string of the package</entry>
4883 <entry><literal>feature_name</literal></entry>
4884 <entry><type>character_data</type></entry>
4885 <entry>Descriptive name of the package</entry>
4889 <entry><literal>is_supported</literal></entry>
4890 <entry><type>yes_or_no</type></entry>
4892 <literal>YES</literal> if the package is fully supported by the
4893 current version of <productname>PostgreSQL</>, <literal>NO</literal> if not
4898 <entry><literal>is_verified_by</literal></entry>
4899 <entry><type>character_data</type></entry>
4901 Always null, since the <productname>PostgreSQL</> development group does not
4902 perform formal testing of feature conformance
4907 <entry><literal>comments</literal></entry>
4908 <entry><type>character_data</type></entry>
4909 <entry>Possibly a comment about the supported status of the package</entry>
4916 <sect1 id="infoschema-sql-parts">
4917 <title><literal>sql_parts</literal></title>
4920 The table <literal>sql_parts</literal> contains information about
4921 which of the several parts of the SQL standard are supported by
4922 <productname>PostgreSQL</productname>.
4926 <title><literal>sql_parts</literal> Columns</title>
4932 <entry>Data Type</entry>
4933 <entry>Description</entry>
4939 <entry><literal>feature_id</literal></entry>
4940 <entry><type>character_data</type></entry>
4941 <entry>An identifier string containing the number of the part</entry>
4945 <entry><literal>feature_name</literal></entry>
4946 <entry><type>character_data</type></entry>
4947 <entry>Descriptive name of the part</entry>
4951 <entry><literal>is_supported</literal></entry>
4952 <entry><type>yes_or_no</type></entry>
4954 <literal>YES</literal> if the part is fully supported by the
4955 current version of <productname>PostgreSQL</>,
4956 <literal>NO</literal> if not
4961 <entry><literal>is_verified_by</literal></entry>
4962 <entry><type>character_data</type></entry>
4964 Always null, since the <productname>PostgreSQL</> development group does not
4965 perform formal testing of feature conformance
4970 <entry><literal>comments</literal></entry>
4971 <entry><type>character_data</type></entry>
4972 <entry>Possibly a comment about the supported status of the part</entry>
4979 <sect1 id="infoschema-sql-sizing">
4980 <title><literal>sql_sizing</literal></title>
4983 The table <literal>sql_sizing</literal> contains information about
4984 various size limits and maximum values in
4985 <productname>PostgreSQL</productname>. This information is
4986 primarily intended for use in the context of the ODBC interface;
4987 users of other interfaces will probably find this information to be
4988 of little use. For this reason, the individual sizing items are
4989 not described here; you will find them in the description of the
4994 <title><literal>sql_sizing</literal> Columns</title>
5000 <entry>Data Type</entry>
5001 <entry>Description</entry>
5007 <entry><literal>sizing_id</literal></entry>
5008 <entry><type>cardinal_number</type></entry>
5009 <entry>Identifier of the sizing item</entry>
5013 <entry><literal>sizing_name</literal></entry>
5014 <entry><type>character_data</type></entry>
5015 <entry>Descriptive name of the sizing item</entry>
5019 <entry><literal>supported_value</literal></entry>
5020 <entry><type>cardinal_number</type></entry>
5022 Value of the sizing item, or 0 if the size is unlimited or
5023 cannot be determined, or null if the features for which the
5024 sizing item is applicable are not supported
5029 <entry><literal>comments</literal></entry>
5030 <entry><type>character_data</type></entry>
5031 <entry>Possibly a comment pertaining to the sizing item</entry>
5038 <sect1 id="infoschema-sql-sizing-profiles">
5039 <title><literal>sql_sizing_profiles</literal></title>
5042 The table <literal>sql_sizing_profiles</literal> contains
5043 information about the <literal>sql_sizing</literal> values that are
5044 required by various profiles of the SQL standard. <productname>PostgreSQL</> does
5045 not track any SQL profiles, so this table is empty.
5049 <title><literal>sql_sizing_profiles</literal> Columns</title>
5055 <entry>Data Type</entry>
5056 <entry>Description</entry>
5062 <entry><literal>sizing_id</literal></entry>
5063 <entry><type>cardinal_number</type></entry>
5064 <entry>Identifier of the sizing item</entry>
5068 <entry><literal>sizing_name</literal></entry>
5069 <entry><type>character_data</type></entry>
5070 <entry>Descriptive name of the sizing item</entry>
5074 <entry><literal>profile_id</literal></entry>
5075 <entry><type>character_data</type></entry>
5076 <entry>Identifier string of a profile</entry>
5080 <entry><literal>required_value</literal></entry>
5081 <entry><type>cardinal_number</type></entry>
5083 The value required by the SQL profile for the sizing item, or 0
5084 if the profile places no limit on the sizing item, or null if
5085 the profile does not require any of the features for which the
5086 sizing item is applicable
5091 <entry><literal>comments</literal></entry>
5092 <entry><type>character_data</type></entry>
5093 <entry>Possibly a comment pertaining to the sizing item within the profile</entry>
5100 <sect1 id="infoschema-table-constraints">
5101 <title><literal>table_constraints</literal></title>
5104 The view <literal>table_constraints</literal> contains all
5105 constraints belonging to tables that the current user owns or has
5106 some non-SELECT privilege on.
5110 <title><literal>table_constraints</literal> Columns</title>
5116 <entry>Data Type</entry>
5117 <entry>Description</entry>
5123 <entry><literal>constraint_catalog</literal></entry>
5124 <entry><type>sql_identifier</type></entry>
5125 <entry>Name of the database that contains the constraint (always the current database)</entry>
5129 <entry><literal>constraint_schema</literal></entry>
5130 <entry><type>sql_identifier</type></entry>
5131 <entry>Name of the schema that contains the constraint</entry>
5135 <entry><literal>constraint_name</literal></entry>
5136 <entry><type>sql_identifier</type></entry>
5137 <entry>Name of the constraint</entry>
5141 <entry><literal>table_catalog</literal></entry>
5142 <entry><type>sql_identifier</type></entry>
5143 <entry>Name of the database that contains the table (always the current database)</entry>
5147 <entry><literal>table_schema</literal></entry>
5148 <entry><type>sql_identifier</type></entry>
5149 <entry>Name of the schema that contains the table</entry>
5153 <entry><literal>table_name</literal></entry>
5154 <entry><type>sql_identifier</type></entry>
5155 <entry>Name of the table</entry>
5159 <entry><literal>constraint_type</literal></entry>
5160 <entry><type>character_data</type></entry>
5162 Type of the constraint: <literal>CHECK</literal>,
5163 <literal>FOREIGN KEY</literal>, <literal>PRIMARY KEY</literal>,
5164 or <literal>UNIQUE</literal>
5169 <entry><literal>is_deferrable</literal></entry>
5170 <entry><type>yes_or_no</type></entry>
5171 <entry><literal>YES</literal> if the constraint is deferrable, <literal>NO</literal> if not</entry>
5175 <entry><literal>initially_deferred</literal></entry>
5176 <entry><type>yes_or_no</type></entry>
5177 <entry><literal>YES</literal> if the constraint is deferrable and initially deferred, <literal>NO</literal> if not</entry>
5184 <sect1 id="infoschema-table-privileges">
5185 <title><literal>table_privileges</literal></title>
5188 The view <literal>table_privileges</literal> identifies all
5189 privileges granted on tables or views to a currently enabled role
5190 or by a currently enabled role. There is one row for each
5191 combination of table, grantor, and grantee.
5195 <title><literal>table_privileges</literal> Columns</title>
5201 <entry>Data Type</entry>
5202 <entry>Description</entry>
5208 <entry><literal>grantor</literal></entry>
5209 <entry><type>sql_identifier</type></entry>
5210 <entry>Name of the role that granted the privilege</entry>
5214 <entry><literal>grantee</literal></entry>
5215 <entry><type>sql_identifier</type></entry>
5216 <entry>Name of the role that the privilege was granted to</entry>
5220 <entry><literal>table_catalog</literal></entry>
5221 <entry><type>sql_identifier</type></entry>
5222 <entry>Name of the database that contains the table (always the current database)</entry>
5226 <entry><literal>table_schema</literal></entry>
5227 <entry><type>sql_identifier</type></entry>
5228 <entry>Name of the schema that contains the table</entry>
5232 <entry><literal>table_name</literal></entry>
5233 <entry><type>sql_identifier</type></entry>
5234 <entry>Name of the table</entry>
5238 <entry><literal>privilege_type</literal></entry>
5239 <entry><type>character_data</type></entry>
5241 Type of the privilege: <literal>SELECT</literal>,
5242 <literal>INSERT</literal>, <literal>UPDATE</literal>,
5243 <literal>DELETE</literal>, <literal>TRUNCATE</literal>,
5244 <literal>REFERENCES</literal>, or <literal>TRIGGER</literal>
5249 <entry><literal>is_grantable</literal></entry>
5250 <entry><type>yes_or_no</type></entry>
5251 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
5255 <entry><literal>with_hierarchy</literal></entry>
5256 <entry><type>yes_or_no</type></entry>
5257 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5264 <sect1 id="infoschema-tables">
5265 <title><literal>tables</literal></title>
5268 The view <literal>tables</literal> contains all tables and views
5269 defined in the current database. Only those tables and views are
5270 shown that the current user has access to (by way of being the
5271 owner or having some privilege).
5275 <title><literal>tables</literal> Columns</title>
5281 <entry>Data Type</entry>
5282 <entry>Description</entry>
5288 <entry><literal>table_catalog</literal></entry>
5289 <entry><type>sql_identifier</type></entry>
5290 <entry>Name of the database that contains the table (always the current database)</entry>
5294 <entry><literal>table_schema</literal></entry>
5295 <entry><type>sql_identifier</type></entry>
5296 <entry>Name of the schema that contains the table</entry>
5300 <entry><literal>table_name</literal></entry>
5301 <entry><type>sql_identifier</type></entry>
5302 <entry>Name of the table</entry>
5306 <entry><literal>table_type</literal></entry>
5307 <entry><type>character_data</type></entry>
5309 Type of the table: <literal>BASE TABLE</literal> for a
5310 persistent base table (the normal table type),
5311 <literal>VIEW</literal> for a view, <literal>FOREIGN TABLE</literal>
5312 for a foreign table, or
5313 <literal>LOCAL TEMPORARY</literal> for a temporary table
5318 <entry><literal>self_referencing_column_name</literal></entry>
5319 <entry><type>sql_identifier</type></entry>
5320 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5324 <entry><literal>reference_generation</literal></entry>
5325 <entry><type>character_data</type></entry>
5326 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5330 <entry><literal>user_defined_type_catalog</literal></entry>
5331 <entry><type>sql_identifier</type></entry>
5333 If the table is a typed table, the name of the database that
5334 contains the underlying data type (always the current
5335 database), else null.
5340 <entry><literal>user_defined_type_schema</literal></entry>
5341 <entry><type>sql_identifier</type></entry>
5343 If the table is a typed table, the name of the schema that
5344 contains the underlying data type, else null.
5349 <entry><literal>user_defined_type_name</literal></entry>
5350 <entry><type>sql_identifier</type></entry>
5352 If the table is a typed table, the name of the underlying data
5358 <entry><literal>is_insertable_into</literal></entry>
5359 <entry><type>yes_or_no</type></entry>
5361 <literal>YES</literal> if the table is insertable into,
5362 <literal>NO</literal> if not (Base tables are always insertable
5363 into, views not necessarily.)
5368 <entry><literal>is_typed</literal></entry>
5369 <entry><type>yes_or_no</type></entry>
5370 <entry><literal>YES</literal> if the table is a typed table, <literal>NO</literal> if not</entry>
5374 <entry><literal>commit_action</literal></entry>
5375 <entry><type>character_data</type></entry>
5377 If the table is a temporary table, then
5378 <literal>PRESERVE</literal>, else null. (The SQL standard
5379 defines other commit actions for temporary tables, which are
5380 not supported by <productname>PostgreSQL</>.)
5388 <sect1 id="infoschema-triggered-update-columns">
5389 <title><literal>triggered_update_columns</literal></title>
5392 For triggers in the current database that specify a column list
5393 (like <literal>UPDATE OF column1, column2</literal>), the
5394 view <literal>triggered_update_columns</literal> identifies these
5395 columns. Triggers that do not specify a column list are not
5396 included in this view. Only those columns are shown that the
5397 current user owns or has some non-SELECT privilege on.
5401 <title><literal>triggered_update_columns</literal> Columns</title>
5407 <entry>Data Type</entry>
5408 <entry>Description</entry>
5414 <entry><literal>trigger_catalog</literal></entry>
5415 <entry><type>sql_identifier</type></entry>
5416 <entry>Name of the database that contains the trigger (always the current database)</entry>
5420 <entry><literal>trigger_schema</literal></entry>
5421 <entry><type>sql_identifier</type></entry>
5422 <entry>Name of the schema that contains the trigger</entry>
5426 <entry><literal>trigger_name</literal></entry>
5427 <entry><type>sql_identifier</type></entry>
5428 <entry>Name of the trigger</entry>
5432 <entry><literal>event_object_catalog</literal></entry>
5433 <entry><type>sql_identifier</type></entry>
5435 Name of the database that contains the table that the trigger
5436 is defined on (always the current database)
5441 <entry><literal>event_object_schema</literal></entry>
5442 <entry><type>sql_identifier</type></entry>
5443 <entry>Name of the schema that contains the table that the trigger is defined on</entry>
5447 <entry><literal>event_object_table</literal></entry>
5448 <entry><type>sql_identifier</type></entry>
5449 <entry>Name of the table that the trigger is defined on</entry>
5453 <entry><literal>event_object_column</literal></entry>
5454 <entry><type>sql_identifier</type></entry>
5455 <entry>Name of the column that the trigger is defined on</entry>
5462 <sect1 id="infoschema-triggers">
5463 <title><literal>triggers</literal></title>
5466 The view <literal>triggers</literal> contains all triggers defined
5467 in the current database on tables and views that the current user owns
5468 or has some non-SELECT privilege on.
5472 <title><literal>triggers</literal> Columns</title>
5478 <entry>Data Type</entry>
5479 <entry>Description</entry>
5485 <entry><literal>trigger_catalog</literal></entry>
5486 <entry><type>sql_identifier</type></entry>
5487 <entry>Name of the database that contains the trigger (always the current database)</entry>
5491 <entry><literal>trigger_schema</literal></entry>
5492 <entry><type>sql_identifier</type></entry>
5493 <entry>Name of the schema that contains the trigger</entry>
5497 <entry><literal>trigger_name</literal></entry>
5498 <entry><type>sql_identifier</type></entry>
5499 <entry>Name of the trigger</entry>
5503 <entry><literal>event_manipulation</literal></entry>
5504 <entry><type>character_data</type></entry>
5506 Event that fires the trigger (<literal>INSERT</literal>,
5507 <literal>UPDATE</literal>, or <literal>DELETE</literal>)
5512 <entry><literal>event_object_catalog</literal></entry>
5513 <entry><type>sql_identifier</type></entry>
5515 Name of the database that contains the table that the trigger
5516 is defined on (always the current database)
5521 <entry><literal>event_object_schema</literal></entry>
5522 <entry><type>sql_identifier</type></entry>
5523 <entry>Name of the schema that contains the table that the trigger is defined on</entry>
5527 <entry><literal>event_object_table</literal></entry>
5528 <entry><type>sql_identifier</type></entry>
5529 <entry>Name of the table that the trigger is defined on</entry>
5533 <entry><literal>action_order</literal></entry>
5534 <entry><type>cardinal_number</type></entry>
5535 <entry>Not yet implemented</entry>
5539 <entry><literal>action_condition</literal></entry>
5540 <entry><type>character_data</type></entry>
5542 <literal>WHEN</literal> condition of the trigger, null if none
5543 (also null if the table is not owned by a currently enabled
5549 <entry><literal>action_statement</literal></entry>
5550 <entry><type>character_data</type></entry>
5552 Statement that is executed by the trigger (currently always
5553 <literal>EXECUTE PROCEDURE
5554 <replaceable>function</replaceable>(...)</literal>)
5559 <entry><literal>action_orientation</literal></entry>
5560 <entry><type>character_data</type></entry>
5562 Identifies whether the trigger fires once for each processed
5563 row or once for each statement (<literal>ROW</literal> or
5564 <literal>STATEMENT</literal>)
5569 <entry><literal>action_timing</literal></entry>
5570 <entry><type>character_data</type></entry>
5572 Time at which the trigger fires (<literal>BEFORE</literal>,
5573 <literal>AFTER</literal>, or <literal>INSTEAD OF</literal>)
5578 <entry><literal>action_reference_old_table</literal></entry>
5579 <entry><type>sql_identifier</type></entry>
5580 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5584 <entry><literal>action_reference_new_table</literal></entry>
5585 <entry><type>sql_identifier</type></entry>
5586 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5590 <entry><literal>action_reference_old_row</literal></entry>
5591 <entry><type>sql_identifier</type></entry>
5592 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5596 <entry><literal>action_reference_new_row</literal></entry>
5597 <entry><type>sql_identifier</type></entry>
5598 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5602 <entry><literal>created</literal></entry>
5603 <entry><type>time_stamp</type></entry>
5604 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5611 Triggers in <productname>PostgreSQL</productname> have two
5612 incompatibilities with the SQL standard that affect the
5613 representation in the information schema. First, trigger names are
5614 local to each table in <productname>PostgreSQL</productname>, rather
5615 than being independent schema objects. Therefore there can be duplicate
5616 trigger names defined in one schema, so long as they belong to
5617 different tables. (<literal>trigger_catalog</literal> and
5618 <literal>trigger_schema</literal> are really the values pertaining
5619 to the table that the trigger is defined on.) Second, triggers can
5620 be defined to fire on multiple events in
5621 <productname>PostgreSQL</productname> (e.g., <literal>ON INSERT OR
5622 UPDATE</literal>), whereas the SQL standard only allows one. If a
5623 trigger is defined to fire on multiple events, it is represented as
5624 multiple rows in the information schema, one for each type of
5625 event. As a consequence of these two issues, the primary key of
5626 the view <literal>triggers</literal> is really
5627 <literal>(trigger_catalog, trigger_schema, event_object_table,
5628 trigger_name, event_manipulation)</literal> instead of
5629 <literal>(trigger_catalog, trigger_schema, trigger_name)</literal>,
5630 which is what the SQL standard specifies. Nonetheless, if you
5631 define your triggers in a manner that conforms with the SQL
5632 standard (trigger names unique in the schema and only one event
5633 type per trigger), this will not affect you.
5638 Prior to <productname>PostgreSQL</> 9.1, this view's columns
5639 <structfield>action_timing</structfield>,
5640 <structfield>action_reference_old_table</structfield>,
5641 <structfield>action_reference_new_table</structfield>,
5642 <structfield>action_reference_old_row</structfield>, and
5643 <structfield>action_reference_new_row</structfield>
5645 <structfield>condition_timing</structfield>,
5646 <structfield>condition_reference_old_table</structfield>,
5647 <structfield>condition_reference_new_table</structfield>,
5648 <structfield>condition_reference_old_row</structfield>, and
5649 <structfield>condition_reference_new_row</structfield>
5651 That was how they were named in the SQL:1999 standard.
5652 The new naming conforms to SQL:2003 and later.
5657 <sect1 id="infoschema-udt-privileges">
5658 <title><literal>udt_privileges</literal></title>
5661 The view <literal>udt_privileges</literal> is intended to identify
5662 <literal>USAGE</literal> privileges granted on user-defined types
5663 to a currently enabled role or by a currently enabled role. Since
5664 data types do not have real privileges
5665 in <productname>PostgreSQL</productname>, this view shows implicit
5666 non-grantable <literal>USAGE</literal> privileges granted by the
5667 owner to <literal>PUBLIC</literal> for all types, including
5668 built-in ones (except domains,
5669 see <xref linkend="infoschema-usage-privileges"> for that).
5673 <title><literal>udt_privileges</literal> Columns</title>
5679 <entry>Data Type</entry>
5680 <entry>Description</entry>
5686 <entry><literal>grantor</literal></entry>
5687 <entry><type>sql_identifier</type></entry>
5688 <entry>Name of the role that granted the privilege</entry>
5692 <entry><literal>grantee</literal></entry>
5693 <entry><type>sql_identifier</type></entry>
5694 <entry>Name of the role that the privilege was granted to</entry>
5698 <entry><literal>udt_catalog</literal></entry>
5699 <entry><type>sql_identifier</type></entry>
5700 <entry>Name of the database containing the type (always the current database)</entry>
5704 <entry><literal>udt_schema</literal></entry>
5705 <entry><type>sql_identifier</type></entry>
5706 <entry>Name of the schema containing the type</entry>
5710 <entry><literal>udt_name</literal></entry>
5711 <entry><type>sql_identifier</type></entry>
5712 <entry>Name of the type</entry>
5716 <entry><literal>privilege_type</literal></entry>
5717 <entry><type>character_data</type></entry>
5718 <entry>Always <literal>TYPE USAGE</literal></entry>
5722 <entry><literal>is_grantable</literal></entry>
5723 <entry><type>yes_or_no</type></entry>
5724 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
5731 <sect1 id="infoschema-usage-privileges">
5732 <title><literal>usage_privileges</literal></title>
5735 The view <literal>usage_privileges</literal> identifies
5736 <literal>USAGE</literal> privileges granted on various kinds of
5737 objects to a currently enabled role or by a currently enabled role.
5738 In <productname>PostgreSQL</productname>, this currently applies to
5739 collations, domains, foreign-data wrappers, and foreign servers. There is one
5740 row for each combination of object, grantor, and grantee.
5744 Since collations and domains do not have real privileges
5745 in <productname>PostgreSQL</productname>, this view shows implicit
5746 non-grantable <literal>USAGE</literal> privileges granted by the
5747 owner to <literal>PUBLIC</literal> for all collations and domains. The other
5748 object types, however, show real privileges.
5752 <title><literal>usage_privileges</literal> Columns</title>
5758 <entry>Data Type</entry>
5759 <entry>Description</entry>
5765 <entry><literal>grantor</literal></entry>
5766 <entry><type>sql_identifier</type></entry>
5767 <entry>Name of the role that granted the privilege</entry>
5771 <entry><literal>grantee</literal></entry>
5772 <entry><type>sql_identifier</type></entry>
5773 <entry>Name of the role that the privilege was granted to</entry>
5777 <entry><literal>object_catalog</literal></entry>
5778 <entry><type>sql_identifier</type></entry>
5779 <entry>Name of the database containing the object (always the current database)</entry>
5783 <entry><literal>object_schema</literal></entry>
5784 <entry><type>sql_identifier</type></entry>
5785 <entry>Name of the schema containing the object, if applicable,
5786 else an empty string</entry>
5790 <entry><literal>object_name</literal></entry>
5791 <entry><type>sql_identifier</type></entry>
5792 <entry>Name of the object</entry>
5796 <entry><literal>object_type</literal></entry>
5797 <entry><type>character_data</type></entry>
5798 <entry><literal>COLLATION</literal> or <literal>DOMAIN</literal> or <literal>FOREIGN DATA WRAPPER</literal> or <literal>FOREIGN SERVER</literal></entry>
5802 <entry><literal>privilege_type</literal></entry>
5803 <entry><type>character_data</type></entry>
5804 <entry>Always <literal>USAGE</literal></entry>
5808 <entry><literal>is_grantable</literal></entry>
5809 <entry><type>yes_or_no</type></entry>
5810 <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry>
5817 <sect1 id="infoschema-user-defined-types">
5818 <title><literal>user_defined_types</literal></title>
5821 The view <literal>user_defined_types</literal> currently contains
5822 all composite types defined in the current database.
5826 SQL knows about two kinds of user-defined types: structured types
5827 (also known as composite types
5828 in <productname>PostgreSQL</productname>) and distinct types (not
5829 implemented in <productname>PostgreSQL</productname>). To be
5830 future-proof, use the
5831 column <literal>user_defined_type_category</literal> to
5832 differentiate between these. Other user-defined types such as base
5833 types and enums, which are <productname>PostgreSQL</productname>
5834 extensions, are not shown here. For domains,
5835 see <xref linkend="infoschema-domains"> instead.
5839 <title><literal>user_defined_types</literal> Columns</title>
5845 <entry>Data Type</entry>
5846 <entry>Description</entry>
5852 <entry><literal>user_defined_type_catalog</literal></entry>
5853 <entry><type>sql_identifier</type></entry>
5854 <entry>Name of the database that contains the type (always the current database)</entry>
5858 <entry><literal>user_defined_type_schema</literal></entry>
5859 <entry><type>sql_identifier</type></entry>
5860 <entry>Name of the schema that contains the type</entry>
5864 <entry><literal>user_defined_type_name</literal></entry>
5865 <entry><type>sql_identifier</type></entry>
5866 <entry>Name of the type</entry>
5870 <entry><literal>user_defined_type_category</literal></entry>
5871 <entry><type>character_data</type></entry>
5873 Currently always <literal>STRUCTURED</literal>
5878 <entry><literal>is_instantiable</literal></entry>
5879 <entry><type>yes_or_no</type></entry>
5880 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5884 <entry><literal>is_final</literal></entry>
5885 <entry><type>yes_or_no</type></entry>
5886 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5890 <entry><literal>ordering_form</literal></entry>
5891 <entry><type>character_data</type></entry>
5892 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5896 <entry><literal>ordering_category</literal></entry>
5897 <entry><type>character_data</type></entry>
5898 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5902 <entry><literal>ordering_routine_catalog</literal></entry>
5903 <entry><type>sql_identifier</type></entry>
5904 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5908 <entry><literal>ordering_routine_schema</literal></entry>
5909 <entry><type>sql_identifier</type></entry>
5910 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5914 <entry><literal>ordering_routine_name</literal></entry>
5915 <entry><type>sql_identifier</type></entry>
5916 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5920 <entry><literal>reference_type</literal></entry>
5921 <entry><type>character_data</type></entry>
5922 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5926 <entry><literal>data_type</literal></entry>
5927 <entry><type>character_data</type></entry>
5928 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5932 <entry><literal>character_maximum_length</literal></entry>
5933 <entry><type>cardinal_number</type></entry>
5934 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5938 <entry><literal>character_octet_length</literal></entry>
5939 <entry><type>cardinal_number</type></entry>
5940 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5944 <entry><literal>character_set_catalog</literal></entry>
5945 <entry><type>sql_identifier</type></entry>
5946 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5950 <entry><literal>character_set_schema</literal></entry>
5951 <entry><type>sql_identifier</type></entry>
5952 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5956 <entry><literal>character_set_name</literal></entry>
5957 <entry><type>sql_identifier</type></entry>
5958 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5962 <entry><literal>collation_catalog</literal></entry>
5963 <entry><type>sql_identifier</type></entry>
5964 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5968 <entry><literal>collation_schema</literal></entry>
5969 <entry><type>sql_identifier</type></entry>
5970 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5974 <entry><literal>collation_name</literal></entry>
5975 <entry><type>sql_identifier</type></entry>
5976 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5980 <entry><literal>numeric_precision</literal></entry>
5981 <entry><type>cardinal_number</type></entry>
5982 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5986 <entry><literal>numeric_precision_radix</literal></entry>
5987 <entry><type>cardinal_number</type></entry>
5988 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5992 <entry><literal>numeric_scale</literal></entry>
5993 <entry><type>cardinal_number</type></entry>
5994 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
5998 <entry><literal>datetime_precision</literal></entry>
5999 <entry><type>cardinal_number</type></entry>
6000 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6004 <entry><literal>interval_type</literal></entry>
6005 <entry><type>character_data</type></entry>
6006 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6010 <entry><literal>interval_precision</literal></entry>
6011 <entry><type>character_data</type></entry>
6012 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6016 <entry><literal>source_dtd_identifier</literal></entry>
6017 <entry><type>sql_identifier</type></entry>
6018 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6022 <entry><literal>ref_dtd_identifier</literal></entry>
6023 <entry><type>sql_identifier</type></entry>
6024 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6031 <sect1 id="infoschema-user-mapping-options">
6032 <title><literal>user_mapping_options</literal></title>
6035 The view <literal>user_mapping_options</literal> contains all the
6036 options defined for user mappings in the current database. Only
6037 those user mappings are shown where the current user has access to
6038 the corresponding foreign server (by way of being the owner or
6039 having some privilege).
6043 <title><literal>user_mapping_options</literal> Columns</title>
6049 <entry>Data Type</entry>
6050 <entry>Description</entry>
6056 <entry><literal>authorization_identifier</literal></entry>
6057 <entry><type>sql_identifier</type></entry>
6058 <entry>Name of the user being mapped,
6059 or <literal>PUBLIC</literal> if the mapping is public</entry>
6063 <entry><literal>foreign_server_catalog</literal></entry>
6064 <entry><type>sql_identifier</type></entry>
6065 <entry>Name of the database that the foreign server used by this
6066 mapping is defined in (always the current database)</entry>
6070 <entry><literal>foreign_server_name</literal></entry>
6071 <entry><type>sql_identifier</type></entry>
6072 <entry>Name of the foreign server used by this mapping</entry>
6076 <entry><literal>option_name</literal></entry>
6077 <entry><type>sql_identifier</type></entry>
6078 <entry>Name of an option</entry>
6082 <entry><literal>option_value</literal></entry>
6083 <entry><type>character_data</type></entry>
6084 <entry>Value of the option. This column will show as null
6085 unless the current user is the user being mapped, or the mapping
6086 is for <literal>PUBLIC</literal> and the current user is the
6087 server owner, or the current user is a superuser. The intent is
6088 to protect password information stored as user mapping
6096 <sect1 id="infoschema-user-mappings">
6097 <title><literal>user_mappings</literal></title>
6100 The view <literal>user_mappings</literal> contains all user
6101 mappings defined in the current database. Only those user mappings
6102 are shown where the current user has access to the corresponding
6103 foreign server (by way of being the owner or having some
6108 <title><literal>user_mappings</literal> Columns</title>
6114 <entry>Data Type</entry>
6115 <entry>Description</entry>
6121 <entry><literal>authorization_identifier</literal></entry>
6122 <entry><type>sql_identifier</type></entry>
6123 <entry>Name of the user being mapped,
6124 or <literal>PUBLIC</literal> if the mapping is public</entry>
6128 <entry><literal>foreign_server_catalog</literal></entry>
6129 <entry><type>sql_identifier</type></entry>
6130 <entry>Name of the database that the foreign server used by this
6131 mapping is defined in (always the current database)</entry>
6135 <entry><literal>foreign_server_name</literal></entry>
6136 <entry><type>sql_identifier</type></entry>
6137 <entry>Name of the foreign server used by this mapping</entry>
6144 <sect1 id="infoschema-view-column-usage">
6145 <title><literal>view_column_usage</literal></title>
6148 The view <literal>view_column_usage</literal> identifies all
6149 columns that are used in the query expression of a view (the
6150 <command>SELECT</command> statement that defines the view). A
6151 column is only included if the table that contains the column is
6152 owned by a currently enabled role.
6157 Columns of system tables are not included. This should be fixed
6163 <title><literal>view_column_usage</literal> Columns</title>
6169 <entry>Data Type</entry>
6170 <entry>Description</entry>
6176 <entry><literal>view_catalog</literal></entry>
6177 <entry><type>sql_identifier</type></entry>
6178 <entry>Name of the database that contains the view (always the current database)</entry>
6182 <entry><literal>view_schema</literal></entry>
6183 <entry><type>sql_identifier</type></entry>
6184 <entry>Name of the schema that contains the view</entry>
6188 <entry><literal>view_name</literal></entry>
6189 <entry><type>sql_identifier</type></entry>
6190 <entry>Name of the view</entry>
6194 <entry><literal>table_catalog</literal></entry>
6195 <entry><type>sql_identifier</type></entry>
6197 Name of the database that contains the table that contains the
6198 column that is used by the view (always the current database)
6203 <entry><literal>table_schema</literal></entry>
6204 <entry><type>sql_identifier</type></entry>
6206 Name of the schema that contains the table that contains the
6207 column that is used by the view
6212 <entry><literal>table_name</literal></entry>
6213 <entry><type>sql_identifier</type></entry>
6215 Name of the table that contains the column that is used by the
6221 <entry><literal>column_name</literal></entry>
6222 <entry><type>sql_identifier</type></entry>
6223 <entry>Name of the column that is used by the view</entry>
6230 <sect1 id="infoschema-view-routine-usage">
6231 <title><literal>view_routine_usage</literal></title>
6234 The view <literal>view_routine_usage</literal> identifies all
6235 routines (functions and procedures) that are used in the query
6236 expression of a view (the <command>SELECT</command> statement that
6237 defines the view). A routine is only included if that routine is
6238 owned by a currently enabled role.
6242 <title><literal>view_routine_usage</literal> Columns</title>
6248 <entry>Data Type</entry>
6249 <entry>Description</entry>
6255 <entry><literal>table_catalog</literal></entry>
6256 <entry><literal>sql_identifier</literal></entry>
6257 <entry>Name of the database containing the view (always the current database)</entry>
6261 <entry><literal>table_schema</literal></entry>
6262 <entry><literal>sql_identifier</literal></entry>
6263 <entry>Name of the schema containing the view</entry>
6267 <entry><literal>table_name</literal></entry>
6268 <entry><literal>sql_identifier</literal></entry>
6269 <entry>Name of the view</entry>
6273 <entry><literal>specific_catalog</literal></entry>
6274 <entry><literal>sql_identifier</literal></entry>
6275 <entry>Name of the database containing the function (always the current database)</entry>
6279 <entry><literal>specific_schema</literal></entry>
6280 <entry><literal>sql_identifier</literal></entry>
6281 <entry>Name of the schema containing the function</entry>
6285 <entry><literal>specific_name</literal></entry>
6286 <entry><literal>sql_identifier</literal></entry>
6288 The <quote>specific name</quote> of the function. See <xref
6289 linkend="infoschema-routines"> for more information.
6297 <sect1 id="infoschema-view-table-usage">
6298 <title><literal>view_table_usage</literal></title>
6301 The view <literal>view_table_usage</literal> identifies all tables
6302 that are used in the query expression of a view (the
6303 <command>SELECT</command> statement that defines the view). A
6304 table is only included if that table is owned by a currently
6310 System tables are not included. This should be fixed sometime.
6315 <title><literal>view_table_usage</literal> Columns</title>
6321 <entry>Data Type</entry>
6322 <entry>Description</entry>
6328 <entry><literal>view_catalog</literal></entry>
6329 <entry><type>sql_identifier</type></entry>
6330 <entry>Name of the database that contains the view (always the current database)</entry>
6334 <entry><literal>view_schema</literal></entry>
6335 <entry><type>sql_identifier</type></entry>
6336 <entry>Name of the schema that contains the view</entry>
6340 <entry><literal>view_name</literal></entry>
6341 <entry><type>sql_identifier</type></entry>
6342 <entry>Name of the view</entry>
6346 <entry><literal>table_catalog</literal></entry>
6347 <entry><type>sql_identifier</type></entry>
6349 Name of the database that contains the table that is
6350 used by the view (always the current database)
6355 <entry><literal>table_schema</literal></entry>
6356 <entry><type>sql_identifier</type></entry>
6358 Name of the schema that contains the table that is used by the
6364 <entry><literal>table_name</literal></entry>
6365 <entry><type>sql_identifier</type></entry>
6367 Name of the table that is used by the view
6375 <sect1 id="infoschema-views">
6376 <title><literal>views</literal></title>
6379 The view <literal>views</literal> contains all views defined in the
6380 current database. Only those views are shown that the current user
6381 has access to (by way of being the owner or having some privilege).
6385 <title><literal>views</literal> Columns</title>
6391 <entry>Data Type</entry>
6392 <entry>Description</entry>
6398 <entry><literal>table_catalog</literal></entry>
6399 <entry><type>sql_identifier</type></entry>
6400 <entry>Name of the database that contains the view (always the current database)</entry>
6404 <entry><literal>table_schema</literal></entry>
6405 <entry><type>sql_identifier</type></entry>
6406 <entry>Name of the schema that contains the view</entry>
6410 <entry><literal>table_name</literal></entry>
6411 <entry><type>sql_identifier</type></entry>
6412 <entry>Name of the view</entry>
6416 <entry><literal>view_definition</literal></entry>
6417 <entry><type>character_data</type></entry>
6419 Query expression defining the view (null if the view is not
6420 owned by a currently enabled role)
6425 <entry><literal>check_option</literal></entry>
6426 <entry><type>character_data</type></entry>
6427 <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
6431 <entry><literal>is_updatable</literal></entry>
6432 <entry><type>yes_or_no</type></entry>
6434 <literal>YES</literal> if the view is updatable (allows
6435 <command>UPDATE</command> and <command>DELETE</command>),
6436 <literal>NO</literal> if not
6441 <entry><literal>is_insertable_into</literal></entry>
6442 <entry><type>yes_or_no</type></entry>
6444 <literal>YES</literal> if the view is insertable into (allows
6445 <command>INSERT</command>), <literal>NO</literal> if not
6450 <entry><literal>is_trigger_updatable</literal></entry>
6451 <entry><type>yes_or_no</type></entry>
6453 <literal>YES</> if the view has an <literal>INSTEAD OF</>
6454 <command>UPDATE</> trigger defined on it, <literal>NO</> if not
6459 <entry><literal>is_trigger_deletable</literal></entry>
6460 <entry><type>yes_or_no</type></entry>
6462 <literal>YES</> if the view has an <literal>INSTEAD OF</>
6463 <command>DELETE</> trigger defined on it, <literal>NO</> if not
6468 <entry><literal>is_trigger_insertable_into</literal></entry>
6469 <entry><type>yes_or_no</type></entry>
6471 <literal>YES</> if the view has an <literal>INSTEAD OF</>
6472 <command>INSERT</> trigger defined on it, <literal>NO</> if not