Skip to main content

Schema, table and columns

All objects belong to a SQL schema, which ensures a conflict-free namespace for accessing them and a focus for user authorization. The schema contains tables and views administered. Statistics on columns can be gathered via the ANALYZE command.

sys.schemas
name type references description
"id" INTEGER   The unique internal identifier for the schema.
"name" VARCHAR   The unique SQL name.
"authorization" INTEGER sys.auths.id The identifier associated with the authorization.
"owner" INTEGER sys.auths.id The reference to the owner of this schema.
"system" BOOLEAN   Is it a system schema.
sys.table_types
name type references description
"table_type_id" SMALLINT   The unique internal identifier for the table type.
"table_type_name" VARCHAR   The unique SQL name of this table type.
sys.tables
name type references description
"id" INTEGER   The unique internal identifier for the table.
"name" VARCHAR   The SQL name.
"schema_id" INTEGER sys.schemas.id The schema identifier.
"query" VARCHAR   The query to populate a view.
"type" SMALLINT sys.table_types. table_type_id The table type identifier.
See sys.table_types for meaning of the identifiers.
"system" BOOLEAN   Is it a MonetDB system table?
"commit_action" SMALLINT   Action on commit for temp tables: 0=Not a temp table, 1=Delete rows, 2=Preserve rows, 3=Drop temp table.
"access" SMALLINT   Table access code: 0 = READWRITE, 1 = READ ONLY, 2 = INSERT ONLY.
"temporary" TINYINT   Retention period indicator; 0=persistent, 1=session.
sys.columns
name type references description
"id" INTEGER   The unique internal identifier for the column.
"name" VARCHAR   The SQL name.
"type" VARCHAR sys.types.sqlname The SQL type name.
"type_digits" INTEGER   The number of digits (radix 2) for numeric types or max length for character/binary strings.
"type_scale" INTEGER   The precision after decimal point. Only applicable for decimal/numeric types.
"table_id" INTEGER sys.tables.id The table this column belongs to.
"default" VARCHAR   The default lexical value.
"null" BOOLEAN   Nulls are allowed constraint.
"number" INTEGER   Column position in the underlying table, starting from 0.
"storage" VARCHAR   Internal storage class identifier.
sys.statistics
name type references description
"column_id" INTEGER sys.columns.id The unique internal identifier for the column.
"type" VARCHAR sys.types.sqlname The SQL type name.
"width" INTEGER   The number of bytes for fixed size data types or number of bytes per character for character/binary strings.
"stamp" TIMESTAMP   Timestamp when the statistics for the column was created or last updated.
"sample" BIGINT   The number of sample items/rows on which the statistic is based.
"count" BIGINT   The number of items/rows on which the statistic is based.
"unique" BIGINT   The number of unique value found for this column.
"nils" BIGINT   The number of NULLs found for this column.
"minval" CLOB   The minimum value found for this column.
"maxval" CLOB   The maximum value found for this column.
"sorted" BOOLEAN   Whether the data in this column is sorted in ascending order.
"revsorted" BOOLEAN   Whether the data in this column is sorted in descending order.