By default LibreOffice Base uses the HSQLDB database management system. Like any database system, HSQLDB has a rich set of data types to choose from when building your database. An understanding of the types and when you might, if ever, use them will help you as you create your tables for your database projects. We will look at five categories of data types in LibreOffice base: integers, floating point, text, time, and other.
An integer is a whole number. There are four integer types, and their differences are mostly related to size.
- TINYINT – the smallest of the integer types. Its size is 28. That is 1 to 256 if there are no negatives, and -128 to 127 if there are negatives. You shouldn’t use this type unless you can guarantee the numbers will always fall into this range.
- SMALLINT – with a size of 216, this integer type gives you a little more breathing room. If you don’t need negatives, you get 65,536, but if you need negatives, the range is -32,768 to 32,767. Like the TINYINT, make sure your numbers will fall into this range.
- INTEGER or INT – the most commonly used integer type, with a size of 232. That’s 4,294,967,296 without negatives and -2,147,483,648 to 2,147,483,647 with negatives. This is the type usually used for record identities. You are pretty safe using this type for most of your integer needs. The one exception is when you need extremely large integer numbers.
- BIGINT – rarely used. Size is 264 = big, whopping whole numbers (i.e. 1.84467440737 X 1019). Unless you using some extremely big numbers (calculating the distance to the next parallel world) or extremely small numbers (the size of a pit on an electron), you will not ever need to use this type.
Floating-point numbers are numbers with decimals, or real numbers. They are made of a whole part and a partial part separated by a decimal point. There are four floating-point types. The basic difference between them is the level of accuracy.
- DECIMAL and NUMERIC – These types have an unlimited range. When you define them, you specify the total numbers of digits (or places) allowed and the number of those digits which will fall after the decimal point, i.e. “number” DECIMAL(10, 2) would mean a field named number that has 10 places with two after the decimal point so that the largest number for this definition is 99,999,999.99. The accuracy for DECIMAL and NUMERIC is nearly perfect.
- DOUBLE or REAL – These have a more limited range. A max of 15 decimal places. Accuracy is not so good. If you just need an approximate number, these are okay. For more precise calculations and better rounding, use DECIMAL or NUMERIC.
Text is a string of characters, not meant to represent a number or other type, enclosed in quotes. There are four different text data types, and they differ mostly in how they consume space.
- VARCHAR – or variable characters. With VARCHAR, you define the max number of characters for the field, i.e. “name” VARCHAR(30) for a variable named name that can have up to 30 characters. If you do not use all 30 characters, the field will only use the needed space in the database.
- VARCHAR_IGNORECASE – a case insensitive version of VARCHAR.
- CHAR or CHARACTER – a fixed size text field. The size is set at definition much like the VARCHAR. The big difference is a CHAR and CHARACTER field is always the defined size. If the text given does not fill all the space, the text is padded with spaces. This type is best used when you expect a set number of characters in most cases, like a credit card number.
- LONGVARCHAR – is designed for large blocks of text. A good example is a database of articles and the article text would go in a LONGVARCHAR.
These are types for defining fields that track the date and time. There are three types, and they differ in the content they contain.
- DATE – contains a date. The format for date entry is YYYY-MM-DD, i.e. 2013-10-29.
- TIME – contains a clock time. The format for time uses a 24 hour clock, as HH:MM:SS, ie 14:25:36 for 2:25:36 PM.
- TIMESTAMP or DATETIME – a combination of both the date and the time. Once again the time is in 24 hour clock format, as YYYY-MM-DD HH:MM:SS, i.e. 2013-10-29 14:25:36.
The types in this category are not really related. They just don’t fit in any of the above categories.
- BOOLEAN or BIT – Think of a BOOLEAN as a three-state switch. You can turn it on, TRUE, you can turn it off, FALSE, or you can put it in neutral, NULL. Though you will find few needs for the NULL state, TRUE and FALSE states are handy for saying an option is available or not, turned on or off.
- BINARY, VARBINARY, LONGVARBINARY – These type are for storing binary data like images. We won’t go into them too much today, but will revisit them at some point in the future.
- OTHER or OBJECT – You may not find much use for this type when working in LibreOffice, but for completeness, OTHER and OBJECT are used to store serialized Java objects.
There you have it, the data types of the HSQLDB database engine used by LibreOffice by default. In later articles, we will use some of these data types to create tables for database collections.