
Redshift data types are not a whole lot different from the standard relational database types.
Relational Databases store data in tables, which are made up of rows and columns. A column is the smallest granularity of logical data storage. Each of these columns have attributes associated with it. A Redshift Data Type, in this context defines the attributes of a column.
There are 4 categories of built-in Redshift data types: Character, Numeric, Datetime and Boolean. Knowing these data types and their attributes is key to writing quality DDL statements in Redshift.
The tables below lists the types within each of these categories.
Related: Understand what Amazon Redshift is !
char(10) or character(10) or nchar(10)
Let us look at an example of creating a table in Redshift with the char data type. Since a char datatype uses up the entire allocated space, use char types for small fields. For larger character fields, use VARCHAR.
CREATE TABLE employees
(
marital_status char(1) default 'U'
);
varchar(20)or nvarchar(10) or text(10) or character varying (10)
Below is an example of a redshift create table statement with two VARCHAR fields, first name and last name.
CREATE TABLE employees
(
first_name varchar(30),
last_name varchar(30)
);
An incorrectly defined Redshift numeric datatype can wreak havoc on performance and throw off your calculations. So lets focus on the simple basics !
A Redshift numeric data type is used to store numbers, we all know that. But, what kind of numbers ?
Rule of thumb, if the number of digits to the right of the decimal is constant, use a decimal type. If they vary based on computation, then use a floating-point. Read the above definitions a couple of times, and let it sink in.
smallint or int2
integer or int or int4
bigint or int8
decimal(precision, scale)
Now let’s look at an example on defining a Redshift decimal column.
Example1: Define a Redshift decimal column based on the number 3219.22
Precision: Number of digits to the left of the decimal + number of digits to the right of the decimal = 6
Scale: Number of digits to the right of the decimal = 2
Column definition = decimal (6,2)
real or float4
float or float8 or double precision
date
Related post: Frequently used Redshift date functions by developers.
timestamp
time
timetz
timestamptz
boolean
This is the latest version of Amazon Redshift Documentation
Another great blog post by Nick Corbett, AWS Professional Services on Agile Analytics with Amazon Redshift
Cookie | Duration | Description |
---|---|---|
cookielawinfo-checkbox-analytics | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics". |
cookielawinfo-checkbox-functional | 11 months | The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional". |
cookielawinfo-checkbox-necessary | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary". |
cookielawinfo-checkbox-others | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other. |
cookielawinfo-checkbox-performance | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance". |
viewed_cookie_policy | 11 months | The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data. |