0520 CHECK, RETURNING and Other in MonetDB

Sample Table

CREATE TABLE tabProducts (     Color VARCHAR(10),     Size  VARCHAR(5),     Qty   INT );

INSERT INTO tabProducts (Color, Size, Qty) VALUES    ('Red',  'M',  10),    ('Red',  'XXL', 10),    ('Blue', 'XL', 30);

RETURNING Clause

Only SELECT statement returns some values. INSERT, UPDATE, DELETE just silently do their job, without any feedback.

If we execute statement: UPDATE t1 SET Col1 = 'zzz' WHERE Id = 99;

We can afterward check rows that are updated: SELECT * FROM t1 WHERE Id = 99;

If we could do both things with one statement that would simplify things and reduce the strain on a database.

INSERT INTO tabProducts (Color, Size, Qty) VALUES ( 'Blue', 'S', 40 ),        ( 'Blue', 'S', 50 ) RETURNING Color, Size, Qty;

This INSERT statement will return values.  RETURNING clause will show us all the values that we have entered.

We can do the same thing with DELETE and UPDATE. Delete will return deleted rows, and UPDATE will return updated rows.

DELETE FROM tabProducts WHERE Qty = 50 RETURNING Size, Qty;

UPDATE tabProducts SET Size = 'M' WHERE Qty = 40 RETURNING Size, Color, Qty;

We can use expressions in the RETURNING clause.

UPDATE tabProducts SET Qty = 60 WHERE Qty = 40 RETURNING COUNT( * ), SUM( Qty );

DELETE FROM tabProducts WHERE Qty = 60 RETURNING Qty + 15;

INSERT INTO tabProducts VALUES( 'Blue', 'M', 100 ) RETURNING Color, SUM( Qty ) GROUP BY Color;

This will not work. We can not use GROUP BY in the RETURNING clause. RETURNING clause must remain simple.

Referencing Columns by Their Position

SELECT Color, Size, qty FROM tabProducts ORDER BY 1, 2; --BY Color, qty

SELECT Color, SUM( qty ) FROM tabProducts GROUP BY 1;  —BY Color

In MonetDB it is possible to reference columns by their position in the select clause.

Referencing Set of Columns with the Keyword ALL

SELECT Color, qty, COUNT( Size ) FROM tabProducts GROUP BY ALL;    –BY Color, qty

When using GROUP BY, we can only group by columns which are not aggregated. If we want to group by all of such columns, we can use "GROUP BY ALL".

SELECT Color, Size, qty FROM tabProducts ORDER BY ALL;  –BY Color, Size, qty

We can use ALL in the ORDER BY clause. This is the same as "ORDER BY Color, Size, qty".

MonetDB will accept this syntax, but there is a bug and this syntax does nothing. There will be no sorting.

Instead of the keyword ALL, we can use the star "*" sign.

SELECT Color, qty, COUNT( Size ) FROM tabProducts GROUP BY *;    –BY Color, qty

SELECT Color, Size, qty FROM tabProducts ORDER BY *;  –BY Color, Size, qty

IS [NOT] DISTINCT FROM

Anything compared with NULL will return NULL.

SELECT 'null' = null; SELECT null = null;

IS [NOT] DISTINCT FROM is a null-safe comparison operator. This operator will always return TRUE or FALSE, even if one of operands is NULL.

SELECT NULL IS DISTINCT FROM NULL;		SELECT 'A' IS DISTINCT FROM NULL;
SELECT NULL IS NOT DISTINCT FROM NULL;		SELECT 'A' IS NOT DISTINCT FROM NULL;

CHECK Constraint

A check constraint is a type of rule which specifies a condition ( boolean expression )  that must be met by each row in a database table. This rule limits acceptable values for data.

In our table I will add check constraint that quantity column must have values less then 1. This will fail because the existing data is already bigger than 1. ALTER TABLE tabProducts ADD CONSTRAINT "QtyLess1" CHECK (qty < 1);

If we change our condition, so that qty must be less than 100, then it will succeed. ALTER TABLE tabProducts ADD CONSTRAINT "QtyLess100" CHECK (qty < 100); After that, we will try to insert number 111 into qty column =>

INSERT INTO tabProducts( Color, Size, Qty ) VALUES ( 'Blue', 'XL', 111 );

This will fail because of the constraint (111>100).

UPDATE tabProducts SET qty = 111 WHERE qty = 30;

This also mean that we can not update the value in the qty column to a value that is bigger than 100.

We can only insert values that are smaller then 100. UPDATE tabProducts SET qty = 99 WHERE qty = 30;

SELECT * FROM tabProducts;

How to Add Check Constraint?

We can add several constraints on the same column. We now have two constraints, that "qty > 0" and "qty < 100". ALTER TABLE tabProducts ADD CONSTRAINT "QtyGrt0" CHECK (qty > 0);

That is not efficient. It is much better to add both constraints with one statement. We can connect conditions with AND, OR. ALTER TABLE tabProducts ADD CONSTRAINT QtyConstraints CHECK (qty > 0 AND qty < 100);

Constraints can combine several columns in the requirement expression: ALTER TABLE tabProducts ADD CONSTRAINT CheckColorSize CHECK ( Color = 'Red' OR Size = 'XL' );

We can add constraint during the creation of a table. CREATE TABLE tabOrders ( Price INT, Qty INT, CONSTRAINT ValidOrders CHECK ( Price > 10 AND Qty < 10 )  );

During table creation, we can add a constraint that is at the single column level. The server will provide a default name for such a constraint.

CREATE TABLE tabOneColumn ( Col1 CHAR, Col2 INT CHECK ( Col2 < 999 )  ); Default name consists of the table name, the column name, and the keyword "check". In this example, that would be "tabonecolumn_col2_check".

Instead of using the default name for a single-column CHECK constraint, it is much better to give the constraint a specific name.

CREATE TABLE tabOneColumnNamed ( Col1 INT CONSTRAINT Col1Constraint CHECK ( Col1 < 999 )  );

Limitations on CHECK Constraints

CHECK constraint can only refer to one row of a table. We can not use aggregation functions, because that would break such limitation. ALTER TABLE tabProducts ADD CONSTRAINT OverFlow CHECK ( SUM( Qty ) < 1000 ); This also means that a CHECK constraint cannot refer to tables other than the table on which it is defined.

A simple check on a single column has minimal impact on performance. We should avoid complex check conditions.

CHECK Constraints and Nulls

INSERT INTO tabOneColumn ( Col1, Col2 ) VALUES ( null, null );

This INSERT statement will always succeed. CHECK constraints can not check null values. SELECT * FROM tabOneColumn;

This CHECK constraint will always return null, because nothing can be compared with a null. ALTER TABLE tabOneColumn ADD CONSTRAINT ZZZ CHECK ( Col1 <> null ); CHECK will prevent entry of a row, only if condition is returning FALSE, so this constraint is useless. INSERT INTO tabOneColumn ( Col1, Col2 ) VALUES ( null, 33 );

SELECT * FROM tabOneColumn;

How to Delete CHECK Constraints?

We can find our CHECK constraint in the view "Information_schema.check_constraint". SELECT * FROM information_schema.check_constraints WHERE table_name = 'tabonecolumn';

We can delete constraints like this: – We can delete just the CHECK constraint. – We can delete the whole table.

ALTER TABLE tabonecolumn DROP CONSTRAINT zzz; DROP TABLE tabOneColumnNamed;

Now, our constraints are gone.

SELECT * FROM information_schema.check_constraints WHERE table_name = 'tabOneColumnNamed' or constraint_name = 'zzz';

LIKE and ILIKE Operators

LIKE is a pattern matching operator. It can help us to find patterns in a text. LIKE is based on two wildcard characters. Percent sign "%" will replace any set of consecutive characters. Underscore "_" will replace exactly one character. Here are some examples:

SELECT 'zzz' LIKE '%';          –true SELECT 'zzz' LIKE '___';   –true

SELECT 'Azz' LIKE 'A%';    –true SELECT 'AzzA' LIKE '%zz%';    –true

SELECT 'AzzA' LIKE 'A__A';   –true SELECT 'Azz' LIKE 'Azz_';   –false

ILIKE is case insensitive version.

SELECT 'fff' LIKE 'F_F';   –false

SELECT 'fff' ILIKE 'F_F';   –true

We can negate LIKE with NOT.

SELECT 'M' NOT LIKE 'M';   –false

SELECT 'M' LIKE 'M';   –true

Default escape character is backslash "\".	SELECT '%_' LIKE '\%\_';  –true
We can declare any other character to be ESCAPE sign.	SELECT '%_' LIKE '#%#_' ESCAPE '#';  –-true

Function regexp_replace

For more complex patterns, we can use the regexp_replace function. This function accepts the original string, a search pattern, and a string that will replace the found pattern.

We will replace "m", followed by two letters, with the "z". SELECT regexp_replace( 'maaam', 'm.{2}', 'z' );

This function is case sensitive, except if we use the forth, optional argument.

SELECT regexp_replace( 'maaam', 'M.{2}', 'z' ); — false

SELECT regexp_replace( 'maaam', 'M.{2}', 'z', 'i' ); –true

This function also accepts other modifiers (flags). I tested that it will accept "m,i,s,x,xx". Flag "xx" is the same as "x".

It is interesting that it will not accept flag "g". It seems that this modifier is constanly turned on.

SELECT regexp_replace( 'SSS', 'S', 'P' );

SELECT regexp_replace('first\nfirst', '^first', '*', 'm') AS result; SELECT regexp_replace('a\nb', 'a.b', 'X', 's') AS result; SELECT regexp_replace('abc123', ' 1 2 3 ', '', 'x') AS result;

Here are examples that you can try with and without modifier (flag).

SELECT regexp_replace('Prisca Gbaguidi', '\\w+\\s\\w+', 'Mireille Gbaguidi'); –Mireille Gbaguidi	Backslashes have to be escaped.
SELECT regexp_replace('Prisca Gbaguidi', '(\\w+)(\\s)(\\w+)', '\\3 \\1'); —Gbaguidi Prisca	Regex with numbered capturing groups.

TRUNCATE and SERIAL Data Type

We will examine how TRUNCATE can affect the SERIAL data type. CREATE TABLE Students ( ID SERIAL, Name VARCHAR(10) ); INSERT INTO students(name) VALUES ('Wiremu'), ('Hinewai');

SELECT * FROM Students;

We will truncate this table, but we will continue sequence. TRUNCATE students CONTINUE IDENTITY; INSERT INTO students(name) VALUES ('Wiremu'), ('Hinewai');

SELECT * FROM Students;

Default behavior is to restart sequence. TRUNCATE students RESTART IDENTITY; INSERT INTO students(name) VALUES ('Wiremu'), ('Hinewai');

SELECT * FROM Students;