Which database data type is used for fields that contain whole numbers?

There are four simple data types that represent how information is internally stored in the 1010data Insights Platform.

The simple data types in the platform are:

• integer
• big integer
• decimal
• text

The integer data type is often used to hold information like transaction IDs and store numbers. Integers are also used to represent date or time values, using the date, month, quarter, and time forms. For instance, the date 12/10/2013 is stored as the integer 20131210. The time 10:25:30 is stored as the integer 102530. For more information, see Integer.

The big integer data type supports whole number values that exceed the range that is supported by the integer data type. Most commonly, big integer columns are used in links, selections, and tabulations, since data such as keys and IDs are often stored in columns of this type. For more information, see Big integer.

The decimal data type may contain values such as sales prices, unemployment rates, interest rates, and floating-point values. In the 1010data Insights Platform, a decimal data type can also hold a value that contains a combination of date and time information. For more information, see Decimal.

The text data type is used for alphanumeric values that may include symbols, and spaces, such as customer IDs, product names, sizes, ZIP codes, or geographic locations. For more information, see Text.

In the Insights Platform, the data types are represented in various ways.

The data type of a column is referred to by its type name or its sigil in different contexts. Different interfaces may tend to use one or the other or both.

Data types are different from display formats, which control how values are displayed within columns.

• Examples of data types
  • A note about IDs
• Metadata
  • Semantic types

Data types tell your database what kind of data it should expect in each column. Think of data types as a field’s classification—each field can only have one data type, and that data type might be a kind of number, text, boolean, or timestamp.

Different databases support different sets of data types — this guide covers some of the most common.

Examples of data types

• String Types (TEXT, CHAR, VCHAR, etc.) - In the world of technology, snippets of text are referred to as “strings.” (You’ve probably heard of a “string of text” before.) Strings can contain numbers and special characters in addition to letters. These fields store things like names, addresses, or anything else that is text.

• Numerical Types (Integer, Float, DoubleFloat, Decimal, etc.) - These fields store numbers. Integers are whole numbers; floats and decimals are ways to store numbers with decimals in them. Numerical types store things like ages, bank account balances, costs, latitudes, and longitudes.

• Temporal Types (Timestamp, Date, Time etc.) - These fields are a special format used to store dates and times (or both), called “timestamps.” Sometimes timestamps are stored in an integer, called an Epoch UNIX Timestamp.

• Boolean Types - A value in these fields can be one of two options, usually TRUE or FALSE. Not all databases support boolean types.

A field may return null if it lacks a value entirely. Null doesn’t mean that a value is zero, rather that it is unknown and not listed.

In Metabase, you can view the data type of a field by navigating to the Data Browser, selecting the gray book icon next to a table to access the Data Reference page, and clicking on Fields in this table in the left sidebar. Data types for each field are listed in the third column.

A note about IDs

Your database most likely has one or more ID fields that act as primary or foreign keys linking tables to each other. While these fields are important, “ID” itself is not a data type.

For example, your PRODUCT_ID field may be an integer or a string, as it could be made up of numbers or a combination of numbers and letters.

As the name suggests, metadata is data that describes other data. In other words, it’s information that tells you about the data found in your database. For example, we could label a column that looks like just a bunch of numbers with the label “latitude,” which would give that column additional meaning and context.

In Metabase, administrators can edit field display names, descriptions, and semantic types (also known as field types) to give their users additional context about the purpose of each field and indicate to Metabase how different fields should be intepreted.

Semantic types

While data types tell your database what kind of values to expect in a field, semantic types indicate the meaning of a field. You may have several fields in your database with the data type type/text, but not all text fields have the same meaning or purpose. Semantic types are essential to building relationships between tables.

In Metabase, semantic types are known as field types, and play an important role in telling Metabase how to interpret each column. Correctly categorizing your field types makes it possible for Metabase to determine what chart type to show you, create maps based on location information, or display URLs as links.

Thanks for your feedback!

Get articles like this one in your inbox every month