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.
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 | Values allowed | Main Operationsallowed | Example column in apple table |
---|---|---|---|
INTEGER | Any integer number | Any arithmetic operation valid on integer numbers | apple.year |
CHAR or VARCHAR | Character strings of any length | Concatenation | apple.variety |
DATE | Any valid date | Difference between two dates and a few operations between date an interval | apple.harvest_day |
NUMERIC | Numbers accepting decimal values | any arithmetic operation valid on real numbers | 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 | Values allowed | Main Operations allowed |
---|---|---|
BOOLEAN |
Values allowed are TRUE and FALSE | AND, OR and NOT logical operators |
TIMESTAMP |
Represents a point in time, with arbitrary precision. Example: 2020-03-01 08:34:12 | date arithmetic operations |
INTERVAL |
Represents a period of time with arbitrary precision. Example: 1 month 3 days | date arithmetic operations |
BIGINT |
Integers in a range bigger than INTEGER data type |
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!