1. Learning about Redshift data types and features
Let's dive into the world of Redshift data types and functions.
2. Basic data types
Let's start by exploring the basic data types in Amazon Redshift. We have access to the most common types we are used to seeing in typical databases, such as numeric types like SMALLINT, INTEGER, and BIGINT, which are 2, 4,and 8 byte numbers respectively.
Next we have DECIMAL or NUMERIC for handling currency and other values where a specific precision and scale are useful. Finally, we have DOUBLE PRECISION and REAL which are variable precision float values. When thinking about dates and times, we have DATE, TIME, and TIMETZ which represent date, time and time with a timezone. Redshift uses TIMESTAMP and TIMESTAMPTZ for handling combined date and time values. Then we have character types including CHAR and VARCHAR. The final type we have is the BOOLEAN type for true/false values.
3. Special data types
In addition to the typical data types we just looked at, Redshift has a few mighty data types. The SUPER type is versatile, handling semistructured data like JSON. It leverages PartiQL for handling these data types and has a maximum size limit of 16MB. The VARBYTE type is designed for binary data, making it suitable for holding BLOBs, images, and videos.
4. Unsupported PostgreSQL types:
Redshift does not directly support all PostgreSQL types. As we can see in the table below, DATETIME, SERIAL, UUID, and JSON are some of the unsupported types; however, Redshift does provide other types that can hold the same data. Some of those Redshift types might have different performance characteristics.
5. Viewing columns and their data types
To view columns and their data types, Redshift provides views similar to the ones we used for schemas and tables named SVV-REDSHIFT-COLUMNS AND SVV-ALL-COLUMNS. In the example query, we select the column name, data type, character maximum length, numeric precision, and numeric scale from the desired table and schema.
6. Viewing columns and their data types (cont)
The output provides a clear view of the table's columns, associated data types, and other qualifiers on the data type, such as length for varchars and precisions for decimals.
7. Data type "compatibility"
Redshift has some data type compatibility, which means it can implicitly convert the date type to another when used in an assignment or a comparison. However, it's crucial to double-check the outcomes. This table illustrates a few data types that Redshift can implicitly convert. The complete list is available in the AWS documentation.
8. Explicitly casting data types
There will still be times when we need to perform explicit type conversions, and we can use the CAST function to do that. In this example, we cast a decimal value to an integer and alias it as 'our-int', resulting in the expected output.
9. TO functions
Redshift also supplies TO functions, such as TO CHAR, TO DATE, and TO NUMBER, to simplify specific type conversions. For example, if we attempt to cast a string to a date, it expects it to be in the proper date format. If not, we'll encounter an error due to date formatting, as shown here. Using TO DATE with the correct format resolves the issue and provides the desired output.
10. TO functions (cont)
The TO functions are versatile, allowing us to manipulate and format data as needed. In this example, we use TO CHAR to extract the month name from a date field, resulting in 'JANUARY.' TO functions use various datetime and numeric format strings; a complete list is available in the AWS documentation.
11. Let's practice!
It's time to put your knowledge into practice! Use this opportunity to work on exercises and enhance your understanding of Redshift's data types and their capabilities.