SQL Data Types

SQL Tutorial Home

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.

Year Apple Variety Number of Trees in Production Tons Produced Harvest day Price per Ton First summer storm
2020 Red Delicious 2000 102 06/23/2020 54.50 07/03/2020
2020 Magic Green 700 33 06/12/2020 62.60 07/03/2020
2020 Red Globus 500 26 05/30/2020 71.50 07/03/2020
2019 Red Delicious 1800 87 07/15/2019 52.25 07/12/2019
2014 Red Delicious 1500 71 06/28/2014 NULL NULL

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

Data Type INTEGER
Values allowed Any integer number
Main Operationsallowed Any arithmetic operation valid on integer numbers
Example column in apple table apple.year
Data Type CHAR or VARCHAR
Values allowed Character strings of any length
Main Operationsallowed Concatenation
Example column in apple table apple.variety
Data Type DATE
Values allowed Any valid date
Main Operationsallowed Difference between two dates and a few operations between date an interval
Example column in apple table apple.harvest_day
Data Type NUMERIC
Values allowed Numbers accepting decimal values
Main Operationsallowed any arithmetic operation valid on real numbers
Example column in apple table apple.price_per_ton

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:
Data Type BOOLEAN
Values allowed Values allowed are TRUE and FALSE
Main Operations allowed AND, OR and NOT logical operators
Data Type TIMESTAMP
Values allowed Represents a point in time, with arbitrary precision. Example: 2020-03-01 08:34:12
Main Operations allowed date arithmetic operations
Data Type INTERVAL
Values allowed Represents a period of time with arbitrary precision. Example: 1 month 3 days
Main Operations allowed date arithmetic operations
Data Type BIGINT
Values allowed Integers in a range bigger than INTEGER data type
Main Operations allowed any arithmetic operation valid on integers

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:

Column of apple table Original Data Type Returned Data Type
year INTEGER CHAR(4)
price_per_ton NUMERIC INTEGER
first_summer_storm DATE TIMESTAMP

The result shows the converted values in the table:

year AS CHAR(4) price_per_ton AS INTEGER First_summer_storm AS timestamp
2020 54 2020-03-07 00:00:00
2020 63 2020/03/07 00:00:00
2020 71 2020/03/07 00:00:00
2019 52 2019/12/07 00:00:00
2014 NULL NULL

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!

IN THIS PAGE