# Databases : PostgreSQL : Introspection : information_schema ## Relations — Details #### administrable_role_authorizations ``` grantee : sql_identifier role_name : sql_identifier is_grantable : yes_or_no ``` #### applicable_roles ``` grantee : sql_identifier role_name : sql_identifier is_grantable : yes_or_no ``` #### check_constraint_routine_usage ``` constraint_catalog : sql_identifier constraint_schema : sql_identifier constraint_name : sql_identifier specific_catalog : sql_identifier specific_schema : sql_identifier specific_name : sql_identifier ``` #### check_constraints ``` constraint_catalog : sql_identifier # always the current database constraint_schema : sql_identifier constraint_name : sql_identifier check_clause : character_data # the check expression ``` The SQL standard considers not-null constraints to be check constraints with a CHECK (column_name IS NOT NULL) expression. #### column_column_usage ``` table_catalog : sql_identifier # always the current database table_schema : sql_identifier table_name : sql_identifier column_name : sql_identifier # base column dependent_column : sql_identifier # generated column ``` #### columns ``` table_catalog : sql_identifier # always the current database table_schema : sql_identifier table_name : sql_identifier column_name : sql_identifier 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) ``` #### constraint_column_usage ``` 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 ``` #### constraint_table_usage ``` table_catalog : sql_identifier # Name of the database that contains the table that is used by some constraint (always the current database) table_schema : sql_identifier # Name of the schema that contains the table that is used by some constraint table_name : sql_identifier # Name of the table 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 ``` #### information_schema_catalog_name ``` catalog_name : sql_identifier ``` #### key_column_usage ``` 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 that contains the column that is restricted by this constraint (always the current database) table_schema : sql_identifier # Name of the schema that contains the table that contains the column that is restricted by this constraint table_name : sql_identifier # Name of the table that contains the column that is restricted by this constraint column_name : sql_identifier # Name of the column that is restricted by this constraint ordinal_position : cardinal_number # Ordinal position of the column within the constraint key (count starts at 1) position_in_unique_constraint : cardinal_number # For a foreign-key constraint, ordinal position of the referenced column within its unique constraint (count starts at 1); otherwise null ``` #### referential_constraints ``` 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. ``` #### schemata ``` 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 ``` #### sequences ``` 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 ``` #### table_constraints ``` 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 (includes not-null constraints), 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) nulls_distinct : yes_or_no # If the constraint is a unique constraint, then YES if the constraint treats nulls as distinct # or NO if it treats nulls as not distinct, otherwise null for other types of constraints. ``` #### tables ``` 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 table_type : character_data # Type of the table: BASE TABLE for a persistent base table (the normal table type), # VIEW for a view, FOREIGN for a foreign table, or LOCAL TEMPORARY for a temporary table self_referencing_column_name : sql_identifier # Applies to a feature not available in PostgreSQL reference_generation : character_data # Applies to a feature not available in PostgreSQL user_defined_type_catalog : sql_identifier # If the table is a typed table, the name of the database that contains # the underlying data type (always the current database), else null. user_defined_type_schema : sql_identifier # If the table is a typed table, the name of the schema that contains the underlying data type, else null. user_defined_type_name : sql_identifier # If the table is a typed table, the name of the underlying data type, else null. is_insertable_into : yes_or_no # YES if the table is insertable into, NO if not (Base tables are always insertable into, views not necessarily.) is_typed : yes_or_no # YES if the table is a typed table, NO if not commit_action : character_data # Not yet implemented ``` #### view_column_usage ``` 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 ``` #### view_table_usage ``` 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 ``` #### views ``` 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 ``` ## Relations — Descriptions - `administrable_role_authorizations` — roles the current user has admin on - `applicable_roles` — roles whose privileges the current user can use - `attributes` — attributes of composite data types - `character_sets` - `check_constraint_routine_usage` — routines (functions and procedures) used by check constraints - `check_constraints` — check constraints (table, domain) - `collation_character_set_applicability` - `collations` - `column_column_usage` — generated columns based on a column in the same table - `column_domain_usage` — table/view columns that use a domain - `column_options` - `column_privileges` - `column_udt_usage` - `columns` — table/view columns, not including system columns (like ctid) - `constraint_column_usage` — columns used by some constraint - `constraint_table_usage` — tables used by some constraint - `data_type_privileges` - `domain_constraints` - `domains` - `domain_udt_usage` - `element_types` - `enabled_roles` - `foreign_data_wrapper_options` - `foreign_data_wrappers` - `foreign_server_options` - `foreign_servers` - `foreign_table_options` - `foreign_tables` - `information_schema_catalog_name` — one row w/ one column containing current database name - `key_column_usage` — columns restricted by unique, PK, or FK constraints - `parameters` — the parameters (arguments) of all functions - `_pg_foreign_data_wrappers` - `_pg_foreign_servers` - `_pg_foreign_table_columns` - `_pg_foreign_tables` - `_pg_user_mappings` - `referential_constraints` — referential (foreign key) constraints - `role_column_grants` - `role_routine_grants` - `role_table_grants` - `role_udt_grants` - `role_usage_grants` - `routine_column_usage` - `routine_privileges` - `routine_routine_usage` - `routines` - `routine_sequence_usage` - `routine_table_usage` - `schemata` - `sequences` - `sql_features` - `sql_implementation_info` - `sql_parts` - `sql_sizing` - `table_constraints` - `table_privileges` - `tables` - `transforms` - `triggered_update_columns` - `triggers` - `udt_privileges` - `usage_privileges` - `user_defined_types` - `user_mapping_options` - `user_mappings` - `view_column_usage` — columns used in view query expressions - `view_routine_usage` - `view_table_usage` — tables used in view query expressions - `views`