Updated May 15, 2023
Introduction to Redshift Data Types
Redshift data types are the type of value that you wish to store in the particular field of your database. While creating the structure or schema of your database, it is very important to specify properly the type of value and the format in which the value will be stored in a particular field. This can be done by specifying the appropriate data type to that field. One more important thing that comes into picture is specifying the length of that field which will help to allocate that much for that field and there will not be any type of wastage of memory resource. The length specification can be done along with data type specification.
In this article, we will study the data types that are available in Redshift and how they can be used for representing the format and type of value being stored in the field. We will also have a look at the aliases of the data type and have a detailed description of each of the data types.
Data types in Redshift
Data types are a great way to apply the constraints on columns and arguments which tell about all the properties associated with the field and the format in which the value can be stored. In Amazon Redshift, table columns can have one of the following mentioned data types –
| Data type in Redshift | Alias of data type | Details |
| HILLSKETCH | None | This data type is mostly used in sketches of hyper log. |
| GEOMETRY | None | This data type is used when you have to store the data in spatial distribution. |
| DATE | None | This data type represents the date of calendar which includes the day, month and year value in it. |
| TIMETZ | This is the time along with the specification of the time zone | This helps in storing the time of the particular day along with the details of the time zone where it is being used. |
| TIME | This is the time without specification of the time zone | This helps in storing the time of the particular day without the details of the time zone where it is being used. |
| TIMESTAMPZ | This is the alias of TIMESTAMP value WITHOUT specification of TIME ZONE details. | WE can store the values of date as well as time in the same field which is referred as timestamp that is point in the timeline. |
| TIMESTAMP | This is the alias of TIMESTAMP value WITH specification of TIME ZONE details. | WE can store the values of date as well as time in the same field which is referred as timestamp that is point in the timeline along with the details of the time zone where it is being currently recorded and used. |
| BIGINT | INT8 | This is the integer value stored in eight byte space of memory and can store signed values in it. |
| SMALLINT | INT2 | This is the integer value stored in two byte space of memory and can store signed values in it. |
| INTEGER | INT, INT4 | Most commonly used data type for storing numerical values. This is the integer value stored in four-byte space of memory and can store signed values in it. |
| BOOLEAN | BOOL | This data type is used for storing the Boolean values that are logical in nature and can have one of the value of TRUE or FALSE. |
| REAL | FLOAT4 | This data type is used for storing the values of floating-point numbers with single precision. |
| DECIMAL | NUMERIC | This data type is used for storing the numeric value of precision which is selected and represents the exact number. |
| DOUBLE PRECISION | FLOAT, FLOAT8 | This helps in specifying the numeric value of floating-point numbers with double precision. |
| VARCHAR | NVARCHAR, CHARACTER VARYING, and TEXT | This data type is used for storing the string value which can have variable length and the control of the limit of characters is as defined by user. |
| CHAR | NCHAR, CHARACTER, and BPCHAR | This data type is used for specifying that the column will contain the string made up of characters whose length will be fixed. |
Implicit conversion of datatypes
Conversion of the value into corresponding data types can be explicitly or implicitly when using Amazon Redshift. When the value specified for storing in column does not match with the actual defined datatype of the column, then it is internally converted into the expected datatype using implicit conversion if the two datatypes are compatible in nature. These two datatypes are the datatype of the value truing to store and the datatype of the column in which it is to be stored.
Implicit conversion can take place in two situations either when assignment is done for example while update and insert commands are used to set the values or in case of expressions such as comparison statements mentioned inside the WHERE clause. The below table illustrates the list of the compatible datatypes for which implicit conversion takes place.
| Source data type | Final Data type |
| CHAR | VARCHAR |
| DECIMAL | CHAR |
| DECIMAL | BIGINT |
| DECIMAL | INTEGER |
| DECIMAL | FLOAT |
| DECIMAL | DOUBLE PRECISION |
| DECIMAL | SMALLINT |
| DECIMAL | REAL |
| DECIMAL | VARCHAR |
| TIMESTAMPZ | DATE |
| TIMESTAMPZ | TIMEZ |
| TIMESTAMPZ | CHAR |
| TIMESTAMPZ | VARCHAR |
| TIMESTAMPZ | TIMESTAMP |
| TIME | TIMETZ |
| TIME | VARCHAR |
| TIMETZ | TIME |
| TIMETZ | VARCHAR |
| REAL | CHAR |
| REAL | BIGINT |
| REAL | DECIMAL |
| REAL | SMALLINT |
| REAL | VARCHAR |
| REAL | INTEGER |
| SMALLINT | BOOLEAN |
| SMALLINT | BIGINT |
| SMALLINT | DECIMAL |
| SMALLINT | DOUBLE PRECISION FLOAT |
| SMALLINT | REAL |
| SMALLINT | CHAR |
| SMALLINT | VARCHAR |
| INTEGER | BIGINT |
| INTEGER | DECIMAL |
| INTEGER | DOUBLE PRECISION FLOAT |
| INTEGER | REAL |
| INTEGER | CHAR |
| INTEGER | VARCHAR |
Other than the above specified compatible data types, there are also other data types that are converted implicitly. However, you should be careful and cautious while performing the operations on columns of table with a specific data type and convert the values explicitly.
Conclusion
The Redshift data types are the type and format in which the values will be specified and stored inside the columns of the table. The article lists the supported datatypes in redshift and also the compatible datatypes for which implicit conversion is automatically done internally by redshift. One should be careful while performing insert, update, or any kind of condition specification in redshift as datatypes have huge impact on performance.
Recommended Articles
This is a guide to Redshift Data Types. Here we discuss the Introduction, Data types in Redshift,Implicit conversion of datatypes respectively. You may also have a look at the following articles to learn more –
