Schemas, Tables, 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.

Starting on Jan2022 release, in order to mitigate the I/O required to update the 'statistics' table, this table is no longer persisted. Alternately, it was changed into a computed view every time when queried.

sys.schema

nametypereferencesdescription
"id"INTEGERThe unique internal identifier for the schema.
"name"VARCHARThe unique SQL schema name. Names are case sensitive.
"authorization"INTEGERsys.auths.idThe identifier associated with the authorization.
"owner"INTEGERsys.auths.idThe reference to the owner of this schema.
"system"BOOLEANIs it a MonetDB system schema.

sys.table_types

nametypereferencesdescription
"table_type_id"SMALLINTThe unique internal identifier for the table type.
"table_type_name"VARCHARThe unique SQL name of this table type.

sys.tables

nametypereferencesdescription
"id"INTEGERThe unique internal identifier for the table or view.
"name"VARCHARThe SQL table or view name. Names are case sensitive.
"schema_id"INTEGERsys.schemas.idThe schema identifier.
"query"VARCHARThe query to populate a view.
"type"SMALLINTsys.table_types. table_type_idThe table type identifier.
See sys.table_types for meaning of the identifiers.
"system"BOOLEANIs it a MonetDB system table or view.
"commit_action"SMALLINTAction on commit for temp tables: 0=Not a temp table, 1=Delete rows, 2=Preserve rows, 3=Drop temp table.
"access"SMALLINTTable access code: 0 = READWRITE, 1 = READ ONLY, 2 = INSERT ONLY.
"temporary"TINYINTRetention period indicator; 0=persistent, 1=session.

sys.columns

nametypereferencesdescription
"id"INTEGERThe unique internal identifier for the column.
"name"VARCHARThe SQL column name. Names are case sensitive.
"type"VARCHARsys.types.sqlnameThe SQL type name.
"type_digits"INTEGERThe number of digits (radix 2) for numeric types or max length for character/binary strings.
"type_scale"INTEGERThe precision after decimal point. Only applicable for decimal/numeric types.
"table_id"INTEGERsys.tables.idThe table this column belongs to.
"default"VARCHARThe default lexical value.
"null"BOOLEANNulls are allowed constraint.
"number"INTEGERColumn position in the underlying table, starting from 0.
"storage"VARCHARInternal storage class identifier.

sys.statistics

nametypereferencesdescription
"column_id"INTEGERsys.columns.idThe unique internal identifier for the column.
"schema"VARCHARThe SQL column's schema name. Names are case sensitive. (Available since Jan2022 release)
"table"VARCHARThe SQL column's table name. Names are case sensitive. (Available since Jan2022 release)
"column"VARCHARThe SQL column name. Names are case sensitive. (Available since Jan2022 release)
"type"VARCHARsys.types.sqlnameThe SQL type name.
"width"INTEGERThe number of bytes for fixed size data types or number of bytes per character for character/binary strings.
"count"BIGINTThe number of items/rows on which the statistic is based.
"unique"BIGINTThe number of unique value found for this column.
"nils"BIGINTThe number of NULLs found for this column.
"minval"CLOBThe minimum value found for this column.
"maxval"CLOBThe maximum value found for this column.
"sorted"BOOLEANWhether the data in this column is sorted in ascending order.
"revsorted"BOOLEANWhether the data in this column is sorted in descending order.