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.

sys.schemas

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. Query 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 or view this column belongs to.
"default"VARCHARThe default literal value or expression.
"null"BOOLEANWhether NULLs are allowed. When false it denotes the NOT NULL 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"VARCHARsys.schemas.nameThe SQL column's schema name. Names are case sensitive.
"table"VARCHARsys.tables.nameThe SQL column's table name. Names are case sensitive.
"column"VARCHARsys.columns.nameThe SQL column name. Names are case sensitive.
"type"VARCHARsys.types.sqlnameThe SQL type name.
"width"INTEGERThe number of bytes for fixed size data types or number of encoding bytes (1, 2 or 4) needed for character/binary strings.
"count"BIGINTThe approximate number of items/rows in this column.
"unique"BOOLEANWhether the data in this column contains only unique values.
"nils"BOOLEANWhether the data in this column contains any NULLs (so missing values).
"minval"CLOBThe minimum value found for this column or NULL.
"maxval"CLOBThe maximum value found for this column or NULL.
"sorted"BOOLEANWhether the data in this column is sorted in ascending order.
"revsorted"BOOLEANWhether the data in this column is sorted in descending order.

Statistics information can be updated via the ANALYZE statement.