Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Column Type
Description
grantor
sql_identifier
Name of the role that granted the privilege
grantee
sql_identifier
Name of the role that the privilege was granted to
table_catalog
sql_identifier
Name of the database that contains the table (always the current database)
table_schema
sql_identifier
Name of the schema that contains the table
table_name
sql_identifier
Name of the table
privilege_type
character_data
Type of the privilege: SELECT
, INSERT
, UPDATE
, DELETE
, TRUNCATE
, REFERENCES
, or TRIGGER
is_grantable
yes_or_no
YES
if the privilege is grantable, NO
if not
with_hierarchy
yes_or_no
In the SQL standard, WITH HIERARCHY OPTION
is a separate (sub-)privilege allowing certain operations on table inheritance hierarchies. In PostgreSQL, this is included in the SELECT
privilege, so this column shows YES
if the privilege is SELECT
, else NO
.
The information schema consists of a set of views that contain information about the objects defined in the current database. The information schema is defined in the SQL standard and can therefore be expected to be portable and remain stable — unlike the system catalogs, which are specific to PostgreSQL and are modeled after implementation concerns. The information schema views do not, however, contain information about PostgreSQL-specific features; to inquire about those you need to query the system catalogs or other PostgreSQL-specific views.
在資料庫中查詢限制條件資訊時,符合標準的查詢可能預期會回傳一筆資料,到數筆資料。這是因為 SQL 標準要求限制條件名稱在綱要中必須是唯一的,但是 PostgreSQL 不強制執行此限制條件。PostgreSQL 自動產生的限制條件名稱會避免在同一綱要中的重複,但是使用者可以指定重複的名稱。
查詢 information schema 檢視表(如check_constraint_routine_usage,check_constraints,domain_constraints 和 referential_constraints)時,可能會出現此問題。其他一些檢視表也有類似的問題,但是包含資料表名稱用以協助區分重複的資料。例如,constraint_column_usage,constraint_table_usage,table_constraints。
The information schema itself is a schema named information_schema
. This schema automatically exists in all databases. The owner of this schema is the initial database user in the cluster, and that user naturally has all the privileges on this schema, including the ability to drop it (but the space savings achieved by that are minuscule).
By default, the information schema is not in the schema search path, so you need to access all objects in it through qualified names. Since the names of some of the objects in the information schema are generic names that might occur in user applications, you should be careful if you want to put the information schema in the path.
The columns of the information schema views use special data types that are defined in the information schema. These are defined as simple domains over ordinary built-in types. You should not use these types for work outside the information schema, but your applications must be prepared for them if they select from the information schema.
These types are:
cardinal_number
A nonnegative integer.
character_data
A character string (without specific maximum length).
sql_identifier
A character string. This type is used for SQL identifiers, the type character_data
is used for any other kind of text data.
time_stamp
A domain over the type timestamp with time zone
yes_or_no
A character string domain that contains either YES
or NO
. This is used to represent Boolean (true/false) data in the information schema. (The information schema was invented before the type boolean
was added to the SQL standard, so this convention is necessary to keep the information schema backward compatible.)
Every column in the information schema has one of these five types.
Column Type
Description
catalog_name
sql_identifier
Name of the database that contains this information schema
The view applicable_roles
identifies all roles whose privileges the current user can use. This means there is some chain of role grants from the current user to the role in question. The current user itself is also an applicable role. The set of applicable roles is generally used for permission checking.
applicable_roles
ColumnsThe view referential_constraints
contains all referential (foreign key) constraints in the current database. Only those constraints are shown for which the current user has write access to the referencing table (by way of being the owner or having some privilege other than SELECT
).
referential_constraints
ColumnsThe view role_usage_grants
identifies USAGE
privileges granted on various kinds of objects where the grantor or grantee is a currently enabled role. Further information can be found under usage_privileges
. The only effective difference between this view and usage_privileges
is that this view omits objects that have been made accessible to the current user by way of a grant to PUBLIC
.
role_usage_grants
ColumnsThe table sql_implementation_info
contains information about various aspects that are left implementation-defined by the SQL standard. This information is primarily intended for use in the context of the ODBC interface; users of other interfaces will probably find this information to be of little use. For this reason, the individual implementation information items are not described here; you will find them in the description of the ODBC interface.
sql_implementation_info
ColumnsThe view sequences
contains all sequences defined in the current database. Only those sequences are shown that the current user has access to (by way of being the owner or having some privilege).
sequences
ColumnsNote that in accordance with the SQL standard, the start, minimum, maximum, and increment values are returned as character strings.
The view attributes
contains information about the attributes of composite data types defined in the database. (Note that the view does not give information about table columns, which are sometimes called attributes in PostgreSQL contexts.) Only those attributes are shown that the current user has access to (by way of being the owner of or having some privilege on the type).
attributes
ColumnsSee also under Section 37.17, a similarly structured view, for further information on some of the columns.
The view administrable_role_authorizations
identifies all roles that the current user has the admin option for.
Table 37.2. administrable_role_authorizations
Columns
The view view_table_usage
identifies all tables that are used in the query expression of a view (the SELECT
statement that defines the view). A table is only included if that table is owned by a currently enabled role.
System tables are not included. This should be fixed sometime.
Table 37.63. view_table_usage
Columns
Name | Data Type | Description |
---|---|---|
Column Type
Description
grantee
sql_identifier
Name of the role to which this role membership was granted (can be the current user, or a different role in case of nested role memberships)
role_name
sql_identifier
Name of a role
is_grantable
yes_or_no
YES
if the grantee has the admin option on the role, NO
if not
Column Type
Description
grantor
sql_identifier
Name of the role that granted the privilege
grantee
sql_identifier
Name of the role that the privilege was granted to
specific_catalog
sql_identifier
Name of the database containing the function (always the current database)
specific_schema
sql_identifier
Name of the schema containing the function
specific_name
sql_identifier
The “specific name” of the function. See Section 37.45 for more information.
routine_catalog
sql_identifier
Name of the database containing the function (always the current database)
routine_schema
sql_identifier
Name of the schema containing the function
routine_name
sql_identifier
Name of the function (might be duplicated in case of overloading)
privilege_type
character_data
Always EXECUTE
(the only privilege type for functions)
is_grantable
yes_or_no
YES
if the privilege is grantable, NO
if not
constraint_catalog
sql_identifier
Name of the database containing the constraint (always the current database)
constraint_schema
sql_identifier
Name of the schema containing the constraint
constraint_name
sql_identifier
Name of the constraint
unique_constraint_catalog
sql_identifier
Name of the database that contains the unique or primary key constraint that the foreign key constraint references (always the current database)
unique_constraint_schema
sql_identifier
Name of the schema that contains the unique or primary key constraint that the foreign key constraint references
unique_constraint_name
sql_identifier
Name of the unique or primary key constraint that the foreign key constraint references
match_option
character_data
Match option of the foreign key constraint: FULL
, PARTIAL
, or NONE
.
update_rule
character_data
Update rule of the foreign key constraint: CASCADE
, SET NULL
, SET DEFAULT
, RESTRICT
, or NO ACTION
.
delete_rule
character_data
Delete rule of the foreign key constraint: CASCADE
, SET NULL
, SET DEFAULT
, RESTRICT
, or NO ACTION
.
Column Type
Description
grantor
sql_identifier
The name of the role that granted the privilege
grantee
sql_identifier
The name of the role that the privilege was granted to
object_catalog
sql_identifier
Name of the database containing the object (always the current database)
object_schema
sql_identifier
Name of the schema containing the object, if applicable, else an empty string
object_name
sql_identifier
Name of the object
object_type
character_data
COLLATION
or DOMAIN
or FOREIGN DATA WRAPPER
or FOREIGN SERVER
or SEQUENCE
privilege_type
character_data
Always USAGE
is_grantable
yes_or_no
YES
if the privilege is grantable, NO
if not
Column Type
Description
implementation_info_id
character_data
Identifier string of the implementation information item
implementation_info_name
character_data
Descriptive name of the implementation information item
integer_value
cardinal_number
Value of the implementation information item, or null if the value is contained in the column character_value
character_value
character_data
Value of the implementation information item, or null if the value is contained in the column integer_value
comments
character_data
Possibly a comment pertaining to the implementation information item
Column Type
Description
table_catalog
sql_identifier
Name of the database that contains the view (always the current database)
table_schema
sql_identifier
Name of the schema that contains the view
table_name
sql_identifier
Name of the view
view_definition
character_data
Query expression defining the view (null if the view is not owned by a currently enabled role)
check_option
character_data
CASCADED
or LOCAL
if the view has a CHECK OPTION
defined on it, NONE
if not
is_updatable
yes_or_no
YES
if the view is updatable (allows UPDATE
and DELETE
), NO
if not
is_insertable_into
yes_or_no
YES
if the view is insertable into (allows INSERT
), NO
if not
is_trigger_updatable
yes_or_no
YES
if the view has an INSTEAD OF
UPDATE
trigger defined on it, NO
if not
is_trigger_deletable
yes_or_no
YES
if the view has an INSTEAD OF
DELETE
trigger defined on it, NO
if not
is_trigger_insertable_into
yes_or_no
YES
if the view has an INSTEAD OF
INSERT
trigger defined on it, NO
if not
Column Type
Description
sequence_catalog
sql_identifier
Name of the database that contains the sequence (always the current database)
sequence_schema
sql_identifier
Name of the schema that contains the sequence
sequence_name
sql_identifier
Name of the sequence
data_type
character_data
The data type of the sequence.
numeric_precision
cardinal_number
This column contains the (declared or implicit) precision of the sequence data type (see above). The precision indicates the number of significant digits. It can be expressed in decimal (base 10) or binary (base 2) terms, as specified in the column numeric_precision_radix
.
numeric_precision_radix
cardinal_number
This column indicates in which base the values in the columns numeric_precision
and numeric_scale
are expressed. The value is either 2 or 10.
numeric_scale
cardinal_number
This column contains the (declared or implicit) scale of the sequence data type (see above). The scale indicates the number of significant digits to the right of the decimal point. It can be expressed in decimal (base 10) or binary (base 2) terms, as specified in the column numeric_precision_radix
.
start_value
character_data
The start value of the sequence
minimum_value
character_data
The minimum value of the sequence
maximum_value
character_data
The maximum value of the sequence
increment
character_data
The increment of the sequence
cycle_option
yes_or_no
YES
if the sequence cycles, else NO
Column Type
Description
domain_catalog
sql_identifier
Name of the database containing the domain (always the current database)
domain_schema
sql_identifier
Name of the schema containing the domain
domain_name
sql_identifier
Name of the domain
table_catalog
sql_identifier
Name of the database containing the table (always the current database)
table_schema
sql_identifier
Name of the schema containing the table
table_name
sql_identifier
Name of the table
column_name
sql_identifier
Name of the column
Column Type
Description
udt_catalog
sql_identifier
Name of the database containing the data type (always the current database)
udt_schema
sql_identifier
Name of the schema containing the data type
udt_name
sql_identifier
Name of the data type
attribute_name
sql_identifier
Name of the attribute
ordinal_position
cardinal_number
Ordinal position of the attribute within the data type (count starts at 1)
attribute_default
character_data
Default expression of the attribute
is_nullable
yes_or_no
YES
if the attribute is possibly nullable, NO
if it is known not nullable.
data_type
character_data
Data type of the attribute, if it is a built-in type, or ARRAY
if it is some array (in that case, see the view element_types
), else USER-DEFINED
(in that case, the type is identified in attribute_udt_name
and associated columns).
character_maximum_length
cardinal_number
If data_type
identifies a character or bit string type, the declared maximum length; null for all other data types or if no maximum length was declared.
character_octet_length
cardinal_number
If data_type
identifies a character type, the maximum possible length in octets (bytes) of a datum; null for all other data types. The maximum octet length depends on the declared character maximum length (see above) and the server encoding.
character_set_catalog
sql_identifier
Applies to a feature not available in PostgreSQL
character_set_schema
sql_identifier
Applies to a feature not available in PostgreSQL
character_set_name
sql_identifier
Applies to a feature not available in PostgreSQL
collation_catalog
sql_identifier
Name of the database containing the collation of the attribute (always the current database), null if default or the data type of the attribute is not collatable
collation_schema
sql_identifier
Name of the schema containing the collation of the attribute, null if default or the data type of the attribute is not collatable
collation_name
sql_identifier
Name of the collation of the attribute, null if default or the data type of the attribute is not collatable
numeric_precision
cardinal_number
If data_type
identifies a numeric type, this column contains the (declared or implicit) precision of the type for this attribute. The precision indicates the number of significant digits. It can be expressed in decimal (base 10) or binary (base 2) terms, as specified in the column numeric_precision_radix
. For all other data types, this column is null.
numeric_precision_radix
cardinal_number
If data_type
identifies a numeric type, this column indicates in which base the values in the columns numeric_precision
and numeric_scale
are expressed. The value is either 2 or 10. For all other data types, this column is null.
numeric_scale
cardinal_number
If data_type
identifies an exact numeric type, this column contains the (declared or implicit) scale of the type for this attribute. The scale indicates the number of significant digits to the right of the decimal point. It can be expressed in decimal (base 10) or binary (base 2) terms, as specified in the column numeric_precision_radix
. For all other data types, this column is null.
datetime_precision
cardinal_number
If data_type
identifies a date, time, timestamp, or interval type, this column contains the (declared or implicit) fractional seconds precision of the type for this attribute, that is, the number of decimal digits maintained following the decimal point in the seconds value. For all other data types, this column is null.
interval_type
character_data
If data_type
identifies an interval type, this column contains the specification which fields the intervals include for this attribute, e.g., YEAR TO MONTH
, DAY TO SECOND
, etc. If no field restrictions were specified (that is, the interval accepts all fields), and for all other data types, this field is null.
interval_precision
cardinal_number
Applies to a feature not available in PostgreSQL (see datetime_precision
for the fractional seconds precision of interval type attributes)
attribute_udt_catalog
sql_identifier
Name of the database that the attribute data type is defined in (always the current database)
attribute_udt_schema
sql_identifier
Name of the schema that the attribute data type is defined in
attribute_udt_name
sql_identifier
Name of the attribute data type
scope_catalog
sql_identifier
Applies to a feature not available in PostgreSQL
scope_schema
sql_identifier
Applies to a feature not available in PostgreSQL
scope_name
sql_identifier
Applies to a feature not available in PostgreSQL
maximum_cardinality
cardinal_number
Always null, because arrays always have unlimited maximum cardinality in PostgreSQL
dtd_identifier
sql_identifier
An identifier of the data type descriptor of the column, unique among the data type descriptors pertaining to the table. This is mainly useful for joining with other instances of such identifiers. (The specific format of the identifier is not defined and not guaranteed to remain the same in future versions.)
is_derived_reference_attribute
yes_or_no
Applies to a feature not available in PostgreSQL
Column Type
Description
grantee
sql_identifier
Name of the role to which this role membership was granted (can be the current user, or a different role in case of nested role memberships)
role_name
sql_identifier
Name of a role
is_grantable
yes_or_no
Always YES
Column Type
Description
view_catalog
sql_identifier
Name of the database that contains the view (always the current database)
view_schema
sql_identifier
Name of the schema that contains the view
view_name
sql_identifier
Name of the view
table_catalog
sql_identifier
Name of the database that contains the table that is used by the view (always the current database)
table_schema
sql_identifier
Name of the schema that contains the table that is used by the view
table_name
sql_identifier
Name of the table that is used by the view
The view character_sets
identifies the character sets available in the current database. Since PostgreSQL does not support multiple character sets within one database, this view only shows one, which is the database encoding.
Take note of how the following terms are used in the SQL standard:
character repertoire
An abstract collection of characters, for example UNICODE
, UCS
, or LATIN1
. Not exposed as an SQL object, but visible in this view.
character encoding form
An encoding of some character repertoire. Most older character repertoires only use one encoding form, and so there are no separate names for them (e.g., LATIN1
is an encoding form applicable to the LATIN1
repertoire). But for example Unicode has the encoding forms UTF8
, UTF16
, etc. (not all supported by PostgreSQL). Encoding forms are not exposed as an SQL object, but are visible in this view.
character set
A named SQL object that identifies a character repertoire, a character encoding, and a default collation. A predefined character set would typically have the same name as an encoding form, but users could define other names. For example, the character set UTF8
would typically identify the character repertoire UCS
, encoding form UTF8
, and some default collation.
You can think of an “encoding” in PostgreSQL either as a character set or a character encoding form. They will have the same name, and there can only be one in one database.
character_sets
ColumnsThe view collation_character_set_applicability
identifies which character set the available collations are applicable to. In PostgreSQL, there is only one character set per database (see explanation in Section 37.7), so this view does not provide much useful information.
collation_character_set_applicability
ColumnsColumn Type
Description
constraint_catalog
sql_identifier
Name of the database containing the constraint (always the current database)
constraint_schema
sql_identifier
Name of the schema containing the constraint
constraint_name
sql_identifier
Name of the constraint
specific_catalog
sql_identifier
Name of the database containing the function (always the current database)
specific_schema
sql_identifier
Name of the schema containing the function
specific_name
sql_identifier
The “specific name” of the function. See Section 37.45 for more information.
Column Type
Description
constraint_catalog
sql_identifier
Name of the database containing the constraint (always the current database)
constraint_schema
sql_identifier
Name of the schema containing the constraint
constraint_name
sql_identifier
Name of the constraint
check_clause
character_data
The check expression of the check constraint
Column Type
Description
collation_catalog
sql_identifier
Name of the database containing the collation (always the current database)
collation_schema
sql_identifier
Name of the schema containing the collation
collation_name
sql_identifier
Name of the default collation
pad_attribute
character_data
Always NO PAD
(The alternative PAD SPACE
is not supported by PostgreSQL.)
Column Type
Description
character_set_catalog
sql_identifier
Character sets are currently not implemented as schema objects, so this column is null.
character_set_schema
sql_identifier
Character sets are currently not implemented as schema objects, so this column is null.
character_set_name
sql_identifier
Name of the character set, currently implemented as showing the name of the database encoding
character_repertoire
sql_identifier
Character repertoire, showing UCS
if the encoding is UTF8
, else just the encoding name
form_of_use
sql_identifier
Character encoding form, same as the database encoding
default_collate_catalog
sql_identifier
Name of the database containing the default collation (always the current database, if any collation is identified)
default_collate_schema
sql_identifier
Name of the schema containing the default collation
default_collate_name
sql_identifier
Name of the default collation. The default collation is identified as the collation that matches the COLLATE
and CTYPE
settings of the current database. If there is no such collation, then this column and the associated schema and catalog columns are null.
Column Type
Description
table_catalog
sql_identifier
Name of the database containing the table (always the current database)
table_schema
sql_identifier
Name of the schema containing the table
table_name
sql_identifier
Name of the table
column_name
sql_identifier
Name of the base column that a generated column depends on
dependent_column
sql_identifier
Name of the generated column
Column Type
Description
collation_catalog
sql_identifier
Name of the database containing the collation (always the current database)
collation_schema
sql_identifier
Name of the schema containing the collation
collation_name
sql_identifier
Name of the default collation
character_set_catalog
sql_identifier
Character sets are currently not implemented as schema objects, so this column is null
character_set_schema
sql_identifier
Character sets are currently not implemented as schema objects, so this column is null
character_set_name
sql_identifier
Name of the character set
The view column_privileges
identifies all privileges granted on columns to a currently enabled role or by a currently enabled role. There is one row for each combination of column, grantor, and grantee.
If a privilege has been granted on an entire table, it will show up in this view as a grant for each column, but only for the privilege types where column granularity is possible: SELECT
, INSERT
, UPDATE
, REFERENCES
.
column_privileges
ColumnsThe view column_udt_usage
identifies all columns that use data types owned by a currently enabled role. Note that in PostgreSQL, built-in data types behave like user-defined types, so they are included here as well. See also Section 37.17 for details.
column_udt_usage
ColumnsThe view constraint_table_usage
identifies all tables in the current database that are used by some constraint and are owned by a currently enabled role. (This is different from the view table_constraints
, which identifies all table constraints along with the table they are defined on.) For a foreign key constraint, this view identifies the table that the foreign key references. For a unique or primary key constraint, this view simply identifies the table the constraint belongs to. Check constraints and not-null constraints are not included in this view.
constraint_table_usage
ColumnsColumn Type
Description
table_catalog
sql_identifier
Name of the database that contains the foreign table (always the current database)
table_schema
sql_identifier
Name of the schema that contains the foreign table
table_name
sql_identifier
Name of the foreign table
column_name
sql_identifier
Name of the column
option_name
sql_identifier
Name of an option
option_value
character_data
Value of the option
Column Type
Description
grantor
sql_identifier
Name of the role that granted the privilege
grantee
sql_identifier
Name of the role that the privilege was granted to
table_catalog
sql_identifier
Name of the database that contains the table that contains the column (always the current database)
table_schema
sql_identifier
Name of the schema that contains the table that contains the column
table_name
sql_identifier
Name of the table that contains the column
column_name
sql_identifier
Name of the column
privilege_type
character_data
Type of the privilege: SELECT
, INSERT
, UPDATE
, or REFERENCES
is_grantable
yes_or_no
YES
if the privilege is grantable, NO
if not
Column Type
Description
udt_catalog
sql_identifier
Name of the database that the column data type (the underlying type of the domain, if applicable) is defined in (always the current database)
udt_schema
sql_identifier
Name of the schema that the column data type (the underlying type of the domain, if applicable) is defined in
udt_name
sql_identifier
Name of the column data type (the underlying type of the domain, if applicable)
table_catalog
sql_identifier
Name of the database containing the table (always the current database)
table_schema
sql_identifier
Name of the schema containing the table
table_name
sql_identifier
Name of the table
column_name
sql_identifier
Name of the column
Column Type Description |
Name of the database that contains the table that is used by some constraint (always the current database) |
Name of the schema that contains the table that is used by some constraint |
Name of the table that is used by some constraint |
Name of the database that contains the constraint (always the current database) |
Name of the schema that contains the constraint |
Name of the constraint |
The view columns
contains information about all table columns (or view columns) in the database. System columns (ctid
, etc.) are not included. Only those columns are shown that the current user has access to (by way of being the owner or having some privilege).
columns
ColumnsSince data types can be defined in a variety of ways in SQL, and PostgreSQL contains additional ways to define data types, their representation in the information schema can be somewhat difficult. The column data_type
is supposed to identify the underlying built-in type of the column. In PostgreSQL, this means that the type is defined in the system catalog schema pg_catalog
. This column might be useful if the application can handle the well-known built-in types specially (for example, format the numeric types differently or use the data in the precision columns). The columns udt_name
, udt_schema
, and udt_catalog
always identify the underlying data type of the column, even if the column is based on a domain. (Since PostgreSQL treats built-in types like user-defined types, built-in types appear here as well. This is an extension of the SQL standard.) These columns should be used if an application wants to process data differently according to the type, because in that case it wouldn't matter if the column is really based on a domain. If the column is based on a domain, the identity of the domain is stored in the columns domain_name
, domain_schema
, and domain_catalog
. If you want to pair up columns with their associated data types and treat domains as separate types, you could write coalesce(domain_name, udt_name)
, etc.
The view constraint_column_usage
identifies all columns in the current database that are used by some constraint. Only those columns are shown that are contained in a table owned by a currently enabled role. For a check constraint, this view identifies the columns that are used in the check expression. For a foreign key constraint, this view identifies the columns that the foreign key references. For a unique or primary key constraint, this view identifies the constrained columns.
constraint_column_usage
ColumnsThe view data_type_privileges
identifies all data type descriptors that the current user has access to, by way of being the owner of the described object or having some privilege for it. A data type descriptor is generated whenever a data type is used in the definition of a table column, a domain, or a function (as parameter or return type) and stores some information about how the data type is used in that instance (for example, the declared maximum length, if applicable). Each data type descriptor is assigned an arbitrary identifier that is unique among the data type descriptor identifiers assigned for one object (table, domain, function). This view is probably not useful for applications, but it is used to define some other views in the information schema.
data_type_privileges
ColumnsThe view domain_constraints
contains all constraints belonging to domains defined in the current database. Only those domains are shown that the current user has access to (by way of being the owner or having some privilege).
Table 37.19. domain_constraints
Columns
Column Type
Description
table_catalog
sql_identifier
Name of the database containing the table (always the current database)
table_schema
sql_identifier
Name of the schema containing the table
table_name
sql_identifier
Name of the table
column_name
sql_identifier
Name of the column
ordinal_position
cardinal_number
Ordinal position of the column within the table (count starts at 1)
column_default
character_data
Default expression of the column
is_nullable
yes_or_no
YES
if the column is possibly nullable, NO
if it is known not nullable. A not-null constraint is one way a column can be known not nullable, but there can be others.
data_type
character_data
Data type of the column, if it is a built-in type, or ARRAY
if it is some array (in that case, see the view element_types
), else USER-DEFINED
(in that case, the type is identified in udt_name
and associated columns). If the column is based on a domain, this column refers to the type underlying the domain (and the domain is identified in domain_name
and associated columns).
character_maximum_length
cardinal_number
If data_type
identifies a character or bit string type, the declared maximum length; null for all other data types or if no maximum length was declared.
character_octet_length
cardinal_number
If data_type
identifies a character type, the maximum possible length in octets (bytes) of a datum; null for all other data types. The maximum octet length depends on the declared character maximum length (see above) and the server encoding.
numeric_precision
cardinal_number
If data_type
identifies a numeric type, this column contains the (declared or implicit) precision of the type for this column. The precision indicates the number of significant digits. It can be expressed in decimal (base 10) or binary (base 2) terms, as specified in the column numeric_precision_radix
. For all other data types, this column is null.
numeric_precision_radix
cardinal_number
If data_type
identifies a numeric type, this column indicates in which base the values in the columns numeric_precision
and numeric_scale
are expressed. The value is either 2 or 10. For all other data types, this column is null.
numeric_scale
cardinal_number
If data_type
identifies an exact numeric type, this column contains the (declared or implicit) scale of the type for this column. The scale indicates the number of significant digits to the right of the decimal point. It can be expressed in decimal (base 10) or binary (base 2) terms, as specified in the column numeric_precision_radix
. For all other data types, this column is null.
datetime_precision
cardinal_number
If data_type
identifies a date, time, timestamp, or interval type, this column contains the (declared or implicit) fractional seconds precision of the type for this column, that is, the number of decimal digits maintained following the decimal point in the seconds value. For all other data types, this column is null.
interval_type
character_data
If data_type
identifies an interval type, this column contains the specification which fields the intervals include for this column, e.g., YEAR TO MONTH
, DAY TO SECOND
, etc. If no field restrictions were specified (that is, the interval accepts all fields), and for all other data types, this field is null.
interval_precision
cardinal_number
Applies to a feature not available in PostgreSQL (see datetime_precision
for the fractional seconds precision of interval type columns)
character_set_catalog
sql_identifier
Applies to a feature not available in PostgreSQL
character_set_schema
sql_identifier
Applies to a feature not available in PostgreSQL
character_set_name
sql_identifier
Applies to a feature not available in PostgreSQL
collation_catalog
sql_identifier
Name of the database containing the collation of the column (always the current database), null if default or the data type of the column is not collatable
collation_schema
sql_identifier
Name of the schema containing the collation of the column, null if default or the data type of the column is not collatable
collation_name
sql_identifier
Name of the collation of the column, null if default or the data type of the column is not collatable
domain_catalog
sql_identifier
If the column has a domain type, the name of the database that the domain is defined in (always the current database), else null.
domain_schema
sql_identifier
If the column has a domain type, the name of the schema that the domain is defined in, else null.
domain_name
sql_identifier
If the column has a domain type, the name of the domain, else null.
udt_catalog
sql_identifier
Name of the database that the column data type (the underlying type of the domain, if applicable) is defined in (always the current database)
udt_schema
sql_identifier
Name of the schema that the column data type (the underlying type of the domain, if applicable) is defined in
udt_name
sql_identifier
Name of the column data type (the underlying type of the domain, if applicable)
scope_catalog
sql_identifier
Applies to a feature not available in PostgreSQL
scope_schema
sql_identifier
Applies to a feature not available in PostgreSQL
scope_name
sql_identifier
Applies to a feature not available in PostgreSQL
maximum_cardinality
cardinal_number
Always null, because arrays always have unlimited maximum cardinality in PostgreSQL
dtd_identifier
sql_identifier
An identifier of the data type descriptor of the column, unique among the data type descriptors pertaining to the table. This is mainly useful for joining with other instances of such identifiers. (The specific format of the identifier is not defined and not guaranteed to remain the same in future versions.)
is_self_referencing
yes_or_no
Applies to a feature not available in PostgreSQL
is_identity
yes_or_no
If the column is an identity column, then YES
, else NO
.
identity_generation
character_data
If the column is an identity column, then ALWAYS
or BY DEFAULT
, reflecting the definition of the column.
identity_start
character_data
If the column is an identity column, then the start value of the internal sequence, else null.
identity_increment
character_data
If the column is an identity column, then the increment of the internal sequence, else null.
identity_maximum
character_data
If the column is an identity column, then the maximum value of the internal sequence, else null.
identity_minimum
character_data
If the column is an identity column, then the minimum value of the internal sequence, else null.
identity_cycle
yes_or_no
If the column is an identity column, then YES
if the internal sequence cycles or NO
if it does not; otherwise null.
is_generated
character_data
If the column is a generated column, then ALWAYS
, else NEVER
.
generation_expression
character_data
If the column is a generated column, then the generation expression, else null.
is_updatable
yes_or_no
YES
if the column is updatable, NO
if not (Columns in base tables are always updatable, columns in views not necessarily)
Column Type
Description
table_catalog
sql_identifier
Name of the database that contains the table that contains the column that is used by some constraint (always the current database)
table_schema
sql_identifier
Name of the schema that contains the table that contains the column that is used by some constraint
table_name
sql_identifier
Name of the table that contains the column that is used by some constraint
column_name
sql_identifier
Name of the column that is used by some constraint
constraint_catalog
sql_identifier
Name of the database that contains the constraint (always the current database)
constraint_schema
sql_identifier
Name of the schema that contains the constraint
constraint_name
sql_identifier
Name of the constraint
Column Type Description |
Name of the database that contains the described object (always the current database) |
Name of the schema that contains the described object |
Name of the described object |
The type of the described object: one of |
The identifier of the data type descriptor, which is unique among the data type descriptors for that same object. |
Column Type Description |
Name of the database that contains the constraint (always the current database) |
Name of the schema that contains the constraint |
Name of the constraint |
Name of the database that contains the domain (always the current database) |
Name of the schema that contains the domain |
Name of the domain |
|
|
The view role_udt_grants
is intended to identify USAGE
privileges granted on user-defined types where the grantor or grantee is a currently enabled role. Further information can be found under udt_privileges
. The only effective difference between this view and udt_privileges
is that this view omits objects that have been made accessible to the current user by way of a grant to PUBLIC
. Since data types do not have real privileges in PostgreSQL, but only an implicit grant to PUBLIC
, this view is empty.
role_udt_grants
ColumnsName | Data Type | Description |
---|
|
| Name of the database that contains the constraint (always the current database) |
|
| Name of the schema that contains the constraint |
|
| Name of the constraint |
|
| Name of the database that contains the table that contains the column that is restricted by this constraint (always the current database) |
|
| Name of the schema that contains the table that contains the column that is restricted by this constraint |
|
| Name of the table that contains the column that is restricted by this constraint |
|
| Name of the column that is restricted by this constraint |
|
| Ordinal position of the column within the constraint key (count starts at 1) |
|
| For a foreign-key constraint, ordinal position of the referenced column within its unique constraint (count starts at 1); otherwise null |
Column Type Description |
Name of the database containing the function (always the current database) |
Name of the schema containing the function |
The “specific name” of the function. This is a name that uniquely identifies the function in the schema, even if the real name of the function is overloaded. The format of the specific name is not defined, it should only be used to compare it to other instances of specific routine names. |
Name of the database containing the function (always the current database) |
Name of the schema containing the function |
Name of the function (might be duplicated in case of overloading) |
|
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Return data type of the function, if it is a built-in type, or |
Always null, since this information is not applied to return data types in PostgreSQL |
Always null, since this information is not applied to return data types in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Always null, since this information is not applied to return data types in PostgreSQL |
Always null, since this information is not applied to return data types in PostgreSQL |
Always null, since this information is not applied to return data types in PostgreSQL |
Always null, since this information is not applied to return data types in PostgreSQL |
Always null, since this information is not applied to return data types in PostgreSQL |
Always null, since this information is not applied to return data types in PostgreSQL |
Always null, since this information is not applied to return data types in PostgreSQL |
Always null, since this information is not applied to return data types in PostgreSQL |
Always null, since this information is not applied to return data types in PostgreSQL |
Name of the database that the return data type of the function is defined in (always the current database). Null for a procedure. |
Name of the schema that the return data type of the function is defined in. Null for a procedure. |
Name of the return data type of the function. Null for a procedure. |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Always null, because arrays always have unlimited maximum cardinality in PostgreSQL |
An identifier of the data type descriptor of the return data type of this function, unique among the data type descriptors pertaining to the function. This is mainly useful for joining with other instances of such identifiers. (The specific format of the identifier is not defined and not guaranteed to remain the same in future versions.) |
If the function is an SQL function, then |
The source text of the function (null if the function is not owned by a currently enabled role). (According to the SQL standard, this column is only applicable if |
If this function is a C function, then the external name (link symbol) of the function; else null. (This works out to be the same value that is shown in |
The language the function is written in |
Always |
If the function is declared immutable (called deterministic in the SQL standard), then |
Always |
If the function automatically returns null if any of its arguments are null, then |
Applies to a feature not available in PostgreSQL |
Always |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
If the function runs with the privileges of the current user, then |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Currently always |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Column Type Description |
Name of the role that granted the privilege |
Name of the role that the privilege was granted to |
Name of the database that contains the table (always the current database) |
Name of the schema that contains the table |
Name of the table |
Type of the privilege: |
|
In the SQL standard, |
Column Type Description |
The name of the role that granted the privilege |
The name of the role that the privilege was granted to |
Name of the database containing the type (always the current database) |
Name of the schema containing the type |
Name of the type |
Always |
|
Column Type Description |
Name of the database containing the function (always the current database) |
Name of the schema containing the function |
|
Ordinal position of the parameter in the argument list of the function (count starts at 1) |
|
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Name of the parameter, or null if the parameter has no name |
Data type of the parameter, if it is a built-in type, or |
Always null, since this information is not applied to parameter data types in PostgreSQL |
Always null, since this information is not applied to parameter data types in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Always null, since this information is not applied to parameter data types in PostgreSQL |
Always null, since this information is not applied to parameter data types in PostgreSQL |
Always null, since this information is not applied to parameter data types in PostgreSQL |
Always null, since this information is not applied to parameter data types in PostgreSQL |
Always null, since this information is not applied to parameter data types in PostgreSQL |
Always null, since this information is not applied to parameter data types in PostgreSQL |
Always null, since this information is not applied to parameter data types in PostgreSQL |
Always null, since this information is not applied to parameter data types in PostgreSQL |
Always null, since this information is not applied to parameter data types in PostgreSQL |
Name of the database that the data type of the parameter is defined in (always the current database) |
Name of the schema that the data type of the parameter is defined in |
Name of the data type of the parameter |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Applies to a feature not available in PostgreSQL |
Always null, because arrays always have unlimited maximum cardinality in PostgreSQL |
An identifier of the data type descriptor of the parameter, unique among the data type descriptors pertaining to the function. This is mainly useful for joining with other instances of such identifiers. (The specific format of the identifier is not defined and not guaranteed to remain the same in future versions.) |
The default expression of the parameter, or null if none or if the function is not owned by a currently enabled role. |
Column Type Description |
Name of the database containing the function (always the current database) |
Name of the schema containing the function |
|
Name of the database containing the function (always the current database) |
Name of the schema containing the function |
Name of the function (might be duplicated in case of overloading) |
Name of the database that contains the table that is used by the function (always the current database) |
Name of the schema that contains the table that is used by the function |
Name of the table that is used by the function |
Name of the column that is used by the function |
The table sql_parts
contains information about which of the several parts of the SQL standard are supported by PostgreSQL.
Table 37.48. sql_parts
Columns
The “specific name” of the function. See for more information.
The “specific name” of the function. See for more information.
Column Type Description |
An identifier string containing the number of the part |
Descriptive name of the part |
|
Always null, since the PostgreSQL development group does not perform formal testing of feature conformance |
Possibly a comment about the supported status of the part |
The table sql_features
contains information about which formal features defined in the SQL standard are supported by PostgreSQL. This is the same information that is presented in Appendix D. There you can also find some additional background information.
sql_features
ColumnsThe table sql_sizing
contains information about various size limits and maximum values in PostgreSQL. This information is primarily intended for use in the context of the ODBC interface; users of other interfaces will probably find this information to be of little use. For this reason, the individual sizing items are not described here; you will find them in the description of the ODBC interface.
Table 37.49. sql_sizing
Columns
The view view_column_usage
identifies all columns that are used in the query expression of a view (the SELECT
statement that defines the view). A column is only included if the table that contains the column is owned by a currently enabled role.
Columns of system tables are not included. This should be fixed sometime.
view_column_usage
ColumnsName | Data Type | Description |
---|---|---|
Column Type
Description
feature_id
character_data
Identifier string of the feature
feature_name
character_data
Descriptive name of the feature
sub_feature_id
character_data
Identifier string of the subfeature, or a zero-length string if not a subfeature
sub_feature_name
character_data
Descriptive name of the subfeature, or a zero-length string if not a subfeature
is_supported
yes_or_no
YES
if the feature is fully supported by the current version of PostgreSQL, NO
if not
is_verified_by
character_data
Always null, since the PostgreSQL development group does not perform formal testing of feature conformance
comments
character_data
Possibly a comment about the supported status of the feature
Column Type
Description
catalog_name
sql_identifier
Name of the database that the schema is contained in (always the current database)
schema_name
sql_identifier
Name of the schema
schema_owner
sql_identifier
Name of the owner of the schema
default_character_set_catalog
sql_identifier
Applies to a feature not available in PostgreSQL
default_character_set_schema
sql_identifier
Applies to a feature not available in PostgreSQL
default_character_set_name
sql_identifier
Applies to a feature not available in PostgreSQL
sql_path
character_data
Applies to a feature not available in PostgreSQL
Column Type
Description
sizing_id
cardinal_number
Identifier of the sizing item
sizing_name
character_data
Descriptive name of the sizing item
supported_value
cardinal_number
Value of the sizing item, or 0 if the size is unlimited or cannot be determined, or null if the features for which the sizing item is applicable are not supported
comments
character_data
Possibly a comment pertaining to the sizing item
Column Type
Description
view_catalog
sql_identifier
Name of the database that contains the view (always the current database)
view_schema
sql_identifier
Name of the schema that contains the view
view_name
sql_identifier
Name of the view
table_catalog
sql_identifier
Name of the database that contains the table that contains the column that is used by the view (always the current database)
table_schema
sql_identifier
Name of the schema that contains the table that contains the column that is used by the view
table_name
sql_identifier
Name of the table that contains the column that is used by the view
column_name
sql_identifier
Name of the column that is used by the view
constraint_catalog
sql_identifier
Name of the database that contains the constraint (always the current database)
constraint_schema
sql_identifier
Name of the schema that contains the constraint
constraint_name
sql_identifier
Name of the constraint
table_catalog
sql_identifier
Name of the database that contains the table (always the current database)
table_schema
sql_identifier
Name of the schema that contains the table
table_name
sql_identifier
Name of the table
constraint_type
character_data
Type of the constraint: CHECK
, FOREIGN KEY
, PRIMARY KEY
, or UNIQUE
is_deferrable
yes_or_no
YES
if the constraint is deferrable, NO
if not
initially_deferred
yes_or_no
YES
if the constraint is deferrable and initially deferred, NO
if not
enforced
yes_or_no
Applies to a feature not available in PostgreSQL (currently always YES
)
Column Type
Description
table_catalog
sql_identifier
Name of the database containing the view (always the current database)
table_schema
sql_identifier
Name of the schema containing the view
table_name
sql_identifier
Name of the view
specific_catalog
sql_identifier
Name of the database containing the function (always the current database)
specific_schema
sql_identifier
Name of the schema containing the function
specific_name
sql_identifier
The “specific name” of the function. See Section 37.45 for more information.