MonetDB

JSON Data Type

We have some JSON data. We can create a column that would accept string data type and put this JSON inside. If we want more functionality, we can use special JSON data type. In that case only valid JSON could be placed into that column.

We create a JSON column like this. Data type could be JSON, or JSON(100) if we want to limit length.

CREATE TABLE JsonTable (JsonColumn JSON );

We will place our JSON sample in that table.

INSERT INTO JsonTable ( JsonColumn ) VALUES ( 
'{ "store": { "book": [ { "Title": "Door"   ,  "price": 8.95   }
                      , { "Title": "Sword"  ,  "price": 12.99  } ]
            , "bicycle":  { "color": "red", "price": 19.95  }    } 
 , "employee": { "Name": "Runako"  }         }'                    )

We can read from JSON column like from any column with "SELECT * FROM JsonTable;", but if we want to get only some part of JSON code, we have to use JSONpath expressions. JSONpath expressions can define what part of JSON code we want to read. JSONpath expression should be placed as argument in JSON.FILTER function. Result will always be returned inside square brackets. Result is also of JSON data type. JSONpath expressions are case sensitive.

There are other functions for use with JSON, that do not use JSONpath.

There is another JSON function that can create JSON code from DOUBLE or CLOB columns. The returned value will be of data type CLOB.

CREATE TABLE NumberString ( NumberCol DOUBLE, StringCol CLOB ) 

INSERT INTO NumberString ( NumberCol, StringCol ) VALUES ( 1, 'one' ), ( 2, 'two' ), ( 3, 'three' )

In both cases the functions work in the same way. They concatenate all the values into a JSON string.

UUID Data Type

A UUID is a unique value created by an algorithm. The UUID looks like this "65950c76-a2f6-4543-660a-b849cf5f2453". The UUID is represented as 32 hexadecimal digits separated into 5 groups of digits. The first group has 8 digits, then we have three groups of 4 digits, and finally there is one group of 12 digits. Together they define a really big number. Every time we call a function to create a new UUID, it will likely create a new value that was never used, anywhere in the world. Such values are used as a synthetic key that is globally unique.

We can create a new UUID value with this function:

SELECT  SYS.UUID() AS UUID;              65950c76-a2f6-4543-660a-b849cf5f2453

If we want to check whether some value is a valid UUID we can use function SYS.ISAUUID( string ). This function will return TRUE if we have a string formatted like 65950c76–a2f6–4543–660a–b849cf5f2453 or like 65950c76a2f64543660ab849cf5f2453, without hyphens.

SELECT SYS.ISAUUID('e31960fb-dc8b-452d-ab30-b342723e7565');            true

We can transform a string into a UUID data type using the CAST or CONVERT functions, or we can use the UUID unary operator:

select cast( '26d7a80b-7538-4682-a49a-9d0f9676b765' as uuid) as uuid_val;
select convert('83886744-d558-4e41-a361-a40b2765455b', uuid) as uuid_val;
select uuid'AC6E4E8C-81B5-41B5-82DE-9C837C23B40A' as uuid_val;

Serial Data Type

In a table, synthetic (surrogate) key is a column which have a unique value for each row, but its content is not derived from application data, but is generated. If none of the rows is deleted from this table, then this column will usually have numbers as a sequence 1,2,3,4…

There are many advantages in using surrogate keys but this time we will talk only about that how to create them in MonetDB. We can use code like this:

CREATE TABLE SerialTable ( SerialCol SERIAL, Name VARCHAR(10)  )

When we enter data in this column, we do not specified values for SerialCol. Those values will be automatically created because this column is of SERIAL data type.

INSERT INTO SerialTable (Name) VALUES ('Obert')

When we read from this table, this would be result. Number one will be automatically added to SerialCol.

If we repeat this INSERT statement several time, each time we would get next number.

This means that, for this column, there is a database object that is remembering last used number, so that next time it can provide next number in a sequence. We can find that database object in sys.sequences system table. Although this table claims that maxvalue is some huge number ( upper limit for BIGINT, or 9223372036854775807  ), this column will actually be of the type INTEGER. Starting value is one, and increment is also one. That is why we are getting numbers 1,2,3…

It is also possible to use BIGSERIAL instead of the SERIAL. In this case our column would be really of the type BIGINT.

If we want to define what kind of integer to use then we are using syntax like this. Instead of INT we can use any other integer data type like TINYINT, SMALLINT, BIGINT, HUGEINT.

CREATE TABLE AutoIncrementTable ( AutoNumberCol INT AUTO_INCREMENT, Name VARCHAR(10) )

In this case sys.sequences table will still show that maxvalue is upper limit for BIGINT, but the real limit will be for INTEGER data type. If we try to enter a value for AutoNumberCol by ourselves, that activity will fail if the number is bigger than 2.7 billion which is upper limit for INTEGER data type.

Maxvalue is always set to BIGINT upper limit because internally MonetDB is storing serial numbers as BIGINT. This doesn't mean that you can not use HUGEINT as AUTO_INCREMENT, you can.

We can generate a new value for our sequence with this statement. "seq_7697" is the name of our sequence. This will increment our sequence by one, so this statement should be used to get next number and then to use it for something. This is usually useful when we want to create new value, and then to use it both in primary key column and foreign key column.

SELECT NEXT VALUE FOR seq_7697

When we are using SERIAL or BIGSERIAL data type, that column will get PRIMARY KEY and NOT NULL constraints.

Always and By Default

Whenever we use SERIAL, BIGSERIAL, we still have ability to enter sequence values by ourselves. Sequence will not be aware of the number we have entered (1) by hand. If sequence try to use the same number (2) that we already entered by hand, it will raise primary key exception.

This is not true if we use AUTO_INCREMENT syntax. In that case we don't have primary key constraint, so it will not be a problem if we enter two same values in our AUTO_INCREMENT column.

If we want to disable ability to enter our own value in sequence column, then we could use this generic syntax.
THIS SYNTAX WILL NOT WORK IN MONETDB. It will create a new sequence, but it will not prevent us from entering our own values.

CREATE TABLE AutoGeneratedTable ( AutoGeneratedCol INT GENERATED ALWAYS AS IDENTITY, Name VARCHAR(10) )

This web page explains that this should not be allowed, but in MonetDB it doesn't work as it should.

[Err] ERROR:  cannot insert into column "rank_id"
DETAIL:  Column "rank_id" is an identity column defined as GENERATED ALWAYS.
https://www.sqltutorial.org/sql-identity/ 

Sequence parameters

Although previous syntax is not prohibiting from entering our own values, that syntax will allows us to use sequence parameters.

CREATE TABLE AutoGeneratedTable ( AutoGeneratedCol INT GENERATED ALWAYS AS IDENTITY ( INCREMENT BY 2 MINVALUE 7 MAXVALUE 10 CACHE 8 CYCLE ), Name VARCHAR(10) )

The parameters (1), allow us to control what will be the content of sys.sequences table (2). As we can see, now the first sequence number will be 7 and next sequence number will be 9 (3).

We can use these parameters:

• START WITH number– This is the starting number in sequence. It could be negative or positive. If this parameter is not declared, then the default value is MINVALUE for ascending sequences and MAXVALUE for descending sequences. START WITH parameter must be bigger than MINVALUE parameter.
• INCREMENT BY number – This way we define difference between two consecutive numbers in a sequence. Default value is 1. If "number" is positive then we have ascending sequence. For negative "number", sequence will be descending.
• NO MINVALUE – NO MIN VALUE means that we are using default value. Default value for ascending sequences is either 1 or START WITH parameter. Default value for descending sequences is minimum value for data type that is associated with the sequence.
• MINVALUE number – Can be negative or positive. Must be smaller than MAXVALUE.
• NO MAXVALUE – This means that we are using the default value. For ascending sequences that value will be the maximum value of the data type that is associated with the sequence. For descending sequences, it is either -1 or START WITH number.
• MAXVALUE number – Can be positive or negative number. Must be greater than minimum value.
• NO CACHE – System will cache next number to be used. This could create problem because in the case of the system failure, that number will be lost. We can prevent such scenario with NO CACHE.
• CACHE number –In order to increase performance, we can cache several next values for our sequence. Number of cached values will be smaller or equal to the CACHE number. In the case of the system failure, all these values will be lost.
• CYCLE – After ascending sequence reach the end of a range, it will start again from the minimal value for associated data type or from the MINVALUE parameter. After descending sequence reach the start of a range, it will start again from the maximal value for associated data type or from the MAXVALUE parameter.
• NO CYCLE – This will not allow looping. If we reach the value outside of the limits of our range, an error will be raised.

Sequence as an Object

We can create sequence as an object and then we can use that object when we create a new table. We create a new sequence like this. We can define all of the parameters in this statement.

CREATE SEQUENCE "seq_NewSequence" AS SMALLINT START WITH 10 INCREMENT BY 2 MINVALUE 7 MAXVALUE 20 CACHE 8 CYCLE

Now that we have our sequence, we can use it for our new table. We will bind sequence object to our column as a default value.

CREATE TABLE SequenceObject ( SequenceCol SMALLINT DEFAULT NEXT VALUE FOR "seq_NewSequence", name VARCHAR(10) );

If we want to alter our sequence object, then we use this statement. Again, we can use all of the parameters. The only difference is that here instead of "START WITH" we use "RESTART WITH".

ALTER SEQUENCE "seq_NewSequence" RESTART WITH 21 INCREMENT BY 3 MINVALUE 15 MAXVALUE 100 CACHE 10 NO CYCLE

Sequence object can be deleted with this statement. It is only possible to delete sequence object if references to that object are deleted. That means that we have to change DEFAULT value of a columns that are using that sequence object.

DROP SEQUENCE "seq_NewSequence";

Info functions

Two statements below are the same:

SELECT NEXT VALUE FOR seq_NewSequence
SELECT NEXT_VALUE_FOR( 'schema', 'seq_NewSequence' )

Both of them will return number 21, but the next time we call them they will return number 22. They shift the sequence.

If we just want to check what is the next number in a sequence then we can use this function. As we can see, it will always return the same number, sequence will not be shifted after each usage.

SELECT GET_VALUE_FOR('schema', 'seq_NewSequence')

Intervals

INTERVAL data types allow us to conduct arithmetic calculations with temporal data types. Keyword DATE is casting operator. This operator is used so that Monetdb transform string to date data type.

SELECT DATE '2021-12-31' + INTERVAL '10' DAY;

It is possible to subtract two dates or times. Result will be interval in seconds.

SELECT date '2020-09-28'      - date '2020-09-27' AS "DifferenceInSeconds";	SELECT time '14:35:45'      - time '14:35:40' AS "DifferenceInSeconds";

Current Date and Time

With these variables we can get information about current date, time and time zone (TZ).

SELECT CURRENT_DATE as "Date"
     , CURRENT_TIME as "Time(6)+TZ"
     , CURRENT_TIMESTAMP as "Timestamp(6)+TZ"
     , NOW as "Timestamp(6)+TZ"
     , LOCALTIME as "Time(6)"
     , LOCALTIMESTAMP as "Timestamp(6)"
     , CURRENT_TIMEZONE as "Interval second";

Transform Strings to Temporal Data Types

There are several ways how we can create temporal data types from the strings. If we have dates, times, or timestamps in ISO format then we can use casting operator, casting function or convert function.

SELECT DATE '1987-09-23' AS "Date"      , TIME '11:40' AS "Time"      , TIMESTAMP '1987-09-23 11:40' AS "Timestamp"; —————————————————– You can also use TIMETZ, TIMESTAMPTZ.	SELECT CAST('1987-09-23' as date) AS "Date"      , CAST('11:40' as time) AS "Time"      , CAST('1987-09-23 11:40' as timestamp) AS "Timestamp";
SELECT CONVERT('1987-09-23', DATE) AS "Date"      , CONVERT('11:40', TIME) AS "Time"      , CONVERT('1987-09-23 11:40', TIMESTAMP) AS "Timestamp";

If string is not in temporal ISO format, then we have  to use functions str_to_date, str_to_time or str_to_timestamp. These functions have second argument where we describe how to interpret string.

SELECT STR_TO_DATE('23-09-1987', '%d-%m-%Y') AS "Date"
     , STR_TO_TIME('11:40', '%H:%M') AS "Time"
     , STR_TO_TIMESTAMP('23-09-1987 11:40', '%d-%m-%Y %H:%M') AS "Timestamp";

All the specifiers used to describe dates and times can be found on the bottom of this page:
https://www.monetdb.org/documentation-Dec2023/user-guide/sql-functions/date-time-functions/

Here are just the major specifiers:

Do it Reverse, Transform Dates and Times to Strings

Counterpart to functions above, are functions that transform temporal types to strings. Here we have functions DATE_TO_STR, TIME_TO_STR and TIMESTAMP_TO_STR. Again, we are using specifiers to describe how we want our string to look like.

SELECT DATE_TO_STR( DATE '1987-09-23', '%Y-%m-%d') AS "Date string"
     , TIME_TO_STR( TIME '11:40', '%H:%M') AS "Time string"
     , TIMESTAMP_TO_STR( TIMESTAMP '1987-09-23 11:40', '%Y-%m-%d %H:%M') AS "Timestamp string";

Extracting Parts of Times and Dates

It is possible to extract parts of temporal data types by using function EXTRACT.

EXTRACT(YEAR FROM CURRENT_DATE)

We can extract these parts:

It is also possible to use specialized functions to extract temporal parts. Some of these functions have double quotes around them.

This two functions can help to extract days or seconds from seconds interval.

Max and Min Date or Time

This is how we can find latest or earliest date or time.

Difference Between Two Timestamps – timestampdiff functions

When subtracting two timestamps we can decide in which measure units to get the result.

Truncating Timestamp

This is great function that allows to round our Timestamp to 'millennium', 'century', 'decade', 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second', 'milliseconds' or 'microseconds'. In example bellow we rounded our timestamp to months. That is why all the values to the right side of the month are getting their minimal values.

Unix Time

If we want to transform temporal data in and from Unix time, then we can use these two functions. One will transform number of seconds (counted from 01/01/1970, which is beginning of time) to regular timestamp. Second one will do the reverse. It will transform regular timestamp to seconds from the beginning of Unix time.