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
| name | type | references | description |
|---|
| "id" | INTEGER | | The unique internal identifier for the schema. |
| "name" | VARCHAR | | The unique SQL schema name. Names are case sensitive. |
| "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 MonetDB system schema. |
sys.tables
| name | type | references | description |
|---|
| "id" | INTEGER | | The unique internal identifier for the table or view. |
| "name" | VARCHAR | | The SQL table or view name. Names are case sensitive. |
| "schema_id" | INTEGER | sys.schemas.id | The schema identifier. |
| "query" | VARCHAR | | The view definition when it is a view, the connection string when it is a remote table. |
| "type" | SMALLINT | sys.table_types. table_type_id | The table type identifier. Query sys.table_types for meaning of the identifiers. |
| "system" | BOOLEAN | | Is it a MonetDB system table or view. |
| "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" | SMALLINT | | Retention period indicator; 0 = persistent, 1 = session. |
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.columns
| name | type | references | description |
|---|
| "id" | INTEGER | | The unique internal identifier for the column. |
| "name" | VARCHAR | | The SQL column name. Names are case sensitive. |
| "type" | VARCHAR | sys.types.sqlname | The SQL type name. |
| "type_digits" | INTEGER | | The maximum length for character/json/url/binary string types. The number of digits for decimal/numeric types. The number of second fraction digits +1 for time/timestamp types. The specific interval subtype such as INTERVAL YEAR TO MONTH for interval types. The specific geometry subtype such as POINT or POLYGON for geometry types. |
| "type_scale" | INTEGER | | The precision after decimal point for decimal/numeric types. |
| "table_id" | INTEGER | sys.tables.id | The table or view this column belongs to. |
| "default" | VARCHAR | | The default literal value or expression. |
| "null" | BOOLEAN | | Whether NULLs are allowed. When false it denotes the NOT NULL 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. |
| "schema" | VARCHAR | sys.schemas.name | The SQL column's schema name. Names are case sensitive. |
| "table" | VARCHAR | sys.tables.name | The SQL column's table name. Names are case sensitive. |
| "column" | VARCHAR | sys.columns.name | The SQL column name. Names are case sensitive. |
| "type" | VARCHAR | sys.types.sqlname | The SQL type name. |
| "width" | INTEGER | | The number of bytes for fixed size data types or number of encoding bytes (1, 2 or 4) needed for character/binary strings. |
| "count" | BIGINT | | The approximate number of items/rows in this column. |
| "unique" | BOOLEAN | | Whether the data in this column contains only unique values. |
| "nils" | BOOLEAN | | Whether the data in this column contains any NULLs (so missing values). |
| "minval" | CLOB | | The minimum value found for this column or NULL. |
| "maxval" | CLOB | | The maximum value found for this column or NULL. |
| "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. |
Statistics information can be updated via the ANALYZE statement.