SQL Data Types

A SQL data type defines a set of valid values for a given column. In a SQL database, every table column has a data type.

The INTEGER data type allows any integer into a column, whereas the DATE data type stores date values. SQL Data types both define the valid values for a column, and the set of valid operations associated with the data types. For example, you can add, substract, multiply and divide integers, but you cannot add two date values.

In this lesson, we detail the most important data types allowed in standard SQL.

Most Frequent SQL Data Types

Let’s start by reviewing the data types used in the example apple table.

The following image shows the data types used in the table apple with a description of the data type allowed values and the valid operations for these data types

When you create a table you specify the data type of each of the columns. Here is an example using the SQL CREATE TABLE statement:

CREATE TABLE apple (
     year                             INTEGER,
     apple_variety                    VARCHAR(30),
     number_of_trees_in_production    INTEGER,
     tons_produced                    NUMERIC,
     harvest_day                      DATE,
     price_per_ton                    NUMERIC,
     first_summer_storm               NUMERIC
);

The same data type can be identified with different reserved words due to SQL-dialects. For example while BIGQUERY uses STRING to define text string columns, MySQL uses VARCHAR(n) or TEXT(n), and MS-SQL uses VARCHAR(n) or TEXT, note in MS-SQL TEXT data type don’t need a maximum length as in MySQL. These kind of differences are very usual. However there are other differences related to how the text string is stored. CHAR data type reserves a fixed length space on the database storage area, while VARCHAR only adjusts the storage space used to the length of value being stored.

More And More SQL Data Types

There are other data types not used in the apple table. Here are the most important data types to know:

SQL Functions Oriented to Data Types

SQL provides a large set of functions to operate on table column data. Some of them are oriented to process a specific data type. For example, there are functions to work with text strings like LENGTH() and SUBSTRING(). Other sets of functions work with DATE or TIMESTAMP data types.

Some of these functions are part of standard SQL, while others are of dialect-specific SQL functions. In previous lessons you already used the SUBSTRING() and EXTRACT() functions.

In this example, you want to obtain those years when the harvest was in June and the first summer storm was in the first 15 days of July. You can use the SQL query EXTRACT date function (dialect-specific for PostgreSQL):

SELECT year, apple_variety, tons_produced
FROM   apples
WHERE  EXTRACT (MONTH FROM harvest_day) = 6
  AND  EXTRACT (MONTH FROM first_summer_storm ) =7
  AND  EXTRACT (DAY FROM first_summer_storm ) <= 15

SQL Data Type Conversion

In some cases, you need to convert from one data type to another. One example is if you need to compare an INTEGER value with a BIGINT value. You need to convert the values to the same data type before you can do the comparison. In some cases, the database detects this need and executes the conversion automatically. In most cases, the developer needs to do an explicit conversion.

The data type conversion is done with the SQL standard CAST function:

SELECT -- convert year from integer to text
       CAST(year AS CHAR(4) ) AS year,
       -- discard decimals values
       CAST(price_per_ton AS INTEGER) AS price_ton 
       -- extent date too timestamp by adding hour 00:00:00 
       CAST(first_summer_storm AS TIMESTAMP) AS first_storm
FROM   apples

The query returns the columns with changes in their data types:

The result shows the converted values in the table:

Closing Words

The data types are very important for databases. You use data types in many ways:

• Define the domain of values for a table column.
• Define the parameters for a database store procedure.
• Define the return data type for a database scalar function.
• Define a store procedure that returns data. There are many more new concepts to learn about SQL queries. Keep going, Learn SQL and increase your skills!