I have been using MonetDB for a month now and have to say, I really like it. MonetDB is a columnar data store and it’s freaking fast.

However, when I started using it, I wasn’t on very good terms with my database. Here are the three things you have to know when you use MonetDB:

• Strings are quoted with single quotes. NOT double quotes! For example:

  SELECT * FROM table where field like '%foo';

• Field names that are special terms need to be double quoted! There are many special terms, such as: “range”, “external”, “end”, … For example:

  SELECT "end", other FROM table;

• The WHERE clause in a GROUP by statement is called HAVING (that’s just basic SQL knowledge, but good to remember): For example:

  SELECT * FROM table GROUP BY field HAVING field>500;

• Querying an INET datatype needs to convert a STRING to an INET in the query as well:

  SELECT * FROM table WHERE ip=inet '10.0.0.2'; 
  or even cooler: 
  SELECT * FROM table WHERE ip<<inet '10.2.0.0/16';

• MonetDB has schemas. They are almost like different databases if you want. You can switch schema by using:

  set schema foo;

• Inserting millions of records is fastest with the COPY INTO command:

  cat file | mclient -d databse -s  "COPY INTO table FROM STDIN using delimiters '\t' " -

• And here is how you create your own database and user:

  sudo monetdb create _database_ -p _password_
  sudo monetdb release _database_
  mclient -u monetdb -d _database_
    alter user set password '_password_' using old password 'monetdb';
    create user "_username_" with password '_password_' name '_name_' schema "sys";
    create schema "_your_schema_" authorization "_username_";
    alter user _username_ set schema "_your_schema_";

I hope these things will help you deal with MonetDB a bit easier. The database is worth a try!!