Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/).
# Data types
**Topics**
+ [
## Multibyte characters
](#c_Supported_data_types-multi-byte-characters)
+ [
# Numeric types
](r_Numeric_types201.md)
+ [
# Character types
](r_Character_types.md)
+ [
# Datetime types
](r_Datetime_types.md)
+ [
# Boolean type
](r_Boolean_type.md)
+ [
# HLLSKETCH type
](r_HLLSKTECH_type.md)
+ [
# SUPER type
](r_SUPER_type.md)
+ [
# VARBYTE type
](r_VARBYTE_type.md)
+ [
## Type compatibility and conversion
](#r_Type_conversion)
Each value that Amazon Redshift stores or retrieves has a data type with a fixed set of associated properties. Data types are declared when tables are created. A data type constrains the set of values that a column or argument can contain.
The following table lists the data types that you can use in Amazon Redshift tables.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/c_Supported_data_types.html)
**Note**
For information about unsupported data types, such as "char" (notice that char is enclosed in quotation marks), see [Unsupported PostgreSQL data types](c_unsupported-postgresql-datatypes.md).
## Multibyte characters
The VARCHAR data type supports UTF-8 multibyte characters up to a maximum of four bytes. Five-byte or longer characters are not supported. To calculate the size of a VARCHAR column that contains multibyte characters, multiply the number of characters by the number of bytes per character. For example, if a string has four Chinese characters, and each character is three bytes long, then you will need a VARCHAR(12) column to store the string.
The VARCHAR data type doesn't support the following invalid UTF-8 codepoints:
`0xD800 – 0xDFFF` (Byte sequences: `ED A0 80` – `ED BF BF`)
The CHAR data type doesn't support multibyte characters.
# Numeric types
**Topics**
+ [
## Integer types
](#r_Numeric_types201-integer-types)
+ [
## DECIMAL or NUMERIC type
](#r_Numeric_types201-decimal-or-numeric-type)
+ [
## Notes about using 128-bit DECIMAL or NUMERIC columns
](#r_Numeric_types201-notes-about-using-128-bit-decimal-or-numeric-columns)
+ [
## Floating-Point types
](#r_Numeric_types201-floating-point-types)
+ [
# Computations with numeric values
](r_numeric_computations201.md)
+ [
# Integer and floating-point literals
](r_numeric_literals201.md)
+ [
# Examples with numeric types
](r_Examples_with_numeric_types201.md)
Numeric data types include integers, decimals, and floating-point numbers.
## Integer types
Use the SMALLINT, INTEGER, and BIGINT data types to store whole numbers of various ranges. You cannot store values outside of the allowed range for each type.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_Numeric_types201.html)
## DECIMAL or NUMERIC type
Use the DECIMAL or NUMERIC data type to store values with a *user-defined precision*. The DECIMAL and NUMERIC keywords are interchangeable. In this document, *decimal* is the preferred term for this data type. The term *numeric* is used generically to refer to integer, decimal, and floating-point data types.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_Numeric_types201.html)
Define a DECIMAL column in a table by specifying a *precision* and *scale*:
```
decimal(precision, scale)
```
*precision*
The total number of significant digits in the whole value: the number of digits on both sides of the decimal point. For example, the number `48.2891` has a precision of 6 and a scale of 4. The default precision, if not specified, is 18. The maximum precision is 38.
If the number of digits to the left of the decimal point in an input value exceeds the precision of the column minus its scale, the value cannot be copied into the column (or inserted or updated). This rule applies to any value that falls outside the range of the column definition. For example, the allowed range of values for a `numeric(5,2)` column is `-999.99` to `999.99`.
*scale*
The number of decimal digits in the fractional part of the value, to the right of the decimal point. Integers have a scale of zero. In a column specification, the scale value must be less than or equal to the precision value. The default scale, if not specified, is 0. The maximum scale is 37.
If the scale of an input value that is loaded into a table is greater than the scale of the column, the value is rounded to the specified scale. For example, the PRICEPAID column in the SALES table is a DECIMAL(8,2) column. If a DECIMAL(8,4) value is inserted into the PRICEPAID column, the value is rounded to a scale of 2.
```
insert into sales
values (0, 8, 1, 1, 2000, 14, 5, 4323.8951, 11.00, null);
select pricepaid, salesid from sales where salesid=0;
pricepaid | salesid
-----------+---------
4323.90 | 0
(1 row)
```
However, results of explicit casts of values selected from tables are not rounded.
**Note**
The maximum positive value that you can insert into a DECIMAL(19,0) column is `9223372036854775807` (263 -1). The maximum negative value is `-9223372036854775808`. For example, an attempt to insert the value `9999999999999999999` (19 nines) will cause an overflow error. Regardless of the placement of the decimal point, the largest string that Amazon Redshift can represent as a DECIMAL number is `9223372036854775807`. For example, the largest value that you can load into a DECIMAL(19,18) column is `9.223372036854775807`.
These rules are because DECIMAL values with 19 or fewer significant digits of precision are stored internally as 8-byte integers, while DECIMAL values with 20 to 38 significant digits of precision are stored as 16-byte integers.
## Notes about using 128-bit DECIMAL or NUMERIC columns
Do not arbitrarily assign maximum precision to DECIMAL columns unless you are certain that your application requires that precision. 128-bit values use twice as much disk space as 64-bit values and can slow down query execution time.
## Floating-Point types
Use the REAL and DOUBLE PRECISION data types to store numeric values with *variable precision*. These types are *inexact* types, meaning that some values are stored as approximations, such that storing and returning a specific value may result in slight discrepancies. If you require exact storage and calculations (such as for monetary amounts), use the DECIMAL data type.
REAL represents the single-precision floating point format, according to the IEEE Standard 754 for Binary Floating-Point Arithmetic. It has a precision of about 6 digits, and a range of around 1E-37 to 1E\$137. You can also specify this data type as FLOAT4.
DOUBLE PRECISION represents the double-precision floating point format, according to the IEEE Standard 754 for Binary Floating-Point Arithmetic. It has a precision of about 15 digits, and a range of around 1E-307 to 1E\$1308. You can also specify this data type as FLOAT or FLOAT8.
In addition to ordinary numeric values, the floating-point types have several special values. Use single quotation marks around these values when using them in SQL:
+ `NaN` – not-a-number
+ `Infinity` – infinity
+ `-Infinity` – negative infinity
For example, to insert not-a-number in column `day_charge` of table `customer_activity` run the following SQL:
```
insert into customer_activity(day_charge) values('NaN');
```
# Computations with numeric values
In this context, *computation* refers to binary mathematical operations: addition, subtraction, multiplication, and division. This section describes the expected return types for these operations, as well as the specific formula that is applied to determine precision and scale when DECIMAL data types are involved.
When numeric values are computed during query processing, you might encounter cases where the computation is impossible and the query returns a numeric overflow error. You might also encounter cases where the scale of computed values varies or is unexpected. For some operations, you can use explicit casting (type promotion) or Amazon Redshift configuration parameters to work around these problems.
For information about the results of similar computations with SQL functions, see [Aggregate functions](c_Aggregate_Functions.md).
## Return types for computations
Given the set of numeric data types supported in Amazon Redshift, the following table shows the expected return types for addition, subtraction, multiplication, and division operations. The first column on the left side of the table represents the first operand in the calculation, and the top row represents the second operand.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_numeric_computations201.html)
## Precision and scale of computed DECIMAL results
The following table summarizes the rules for computing resulting precision and scale when mathematical operations return DECIMAL results. In this table, `p1` and `s1` represent the precision and scale of the first operand in a calculation and `p2` and `s2` represent the precision and scale of the second operand. (Regardless of these calculations, the maximum result precision is 38, and the maximum result scale is 38.)
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_numeric_computations201.html)
For example, the PRICEPAID and COMMISSION columns in the SALES table are both DECIMAL(8,2) columns. If you divide PRICEPAID by COMMISSION (or vice versa), the formula is applied as follows:
```
Precision = 8-2 + 2 + max(4,2+8-2+1)
= 6 + 2 + 9 = 17
Scale = max(4,2+8-2+1) = 9
Result = DECIMAL(17,9)
```
The following calculation is the general rule for computing the resulting precision and scale for operations performed on DECIMAL values with set operators such as UNION, INTERSECT, and EXCEPT or functions such as COALESCE and DECODE:
```
Scale = max(s1,s2)
Precision = min(max(p1-s1,p2-s2)+scale,19)
```
For example, a DEC1 table with one DECIMAL(7,2) column is joined with a DEC2 table with one DECIMAL(15,3) column to create a DEC3 table. The schema of DEC3 shows that the column becomes a NUMERIC(15,3) column.
```
create table dec3 as select * from dec1 union select * from dec2;
```
Result
```
select "column", type, encoding, distkey, sortkey
from pg_table_def where tablename = 'dec3';
column | type | encoding | distkey | sortkey
-------+---------------+----------+---------+---------
c1 | numeric(15,3) | none | f | 0
```
In the above example, the formula is applied as follows:
```
Precision = min(max(7-2,15-3) + max(2,3), 19)
= 12 + 3 = 15
Scale = max(2,3) = 3
Result = DECIMAL(15,3)
```
## Notes on division operations
For division operations, divide-by-zero conditions return errors.
The scale limit of 100 is applied after the precision and scale are calculated. If the calculated result scale is greater than 100, division results are scaled as follows:
+ Precision = ` precision - (scale - max_scale)`
+ Scale = ` max_scale `
If the calculated precision is greater than the maximum precision (38), the precision is reduced to 38, and the scale becomes the result of: `max((38 + scale - precision), min(4, 100))`
## Overflow conditions
Overflow is checked for all numeric computations. DECIMAL data with a precision of 19 or less is stored as 64-bit integers. DECIMAL data with a precision that is greater than 19 is stored as 128-bit integers. The maximum precision for all DECIMAL values is 38, and the maximum scale is 37. Overflow errors occur when a value exceeds these limits, which apply to both intermediate and final result sets:
+ Explicit casting results in runtime overflow errors when specific data values do not fit the requested precision or scale specified by the cast function. For example, you cannot cast all values from the PRICEPAID column in the SALES table (a DECIMAL(8,2) column) and return a DECIMAL(7,3) result:
```
select pricepaid::decimal(7,3) from sales;
ERROR: Numeric data overflow (result precision)
```
This error occurs because *some* of the larger values in the PRICEPAID column cannot be cast.
+ Multiplication operations produce results in which the result scale is the sum of the scale of each operand. If both operands have a scale of 4, for example, the result scale is 8, leaving only 10 digits for the left side of the decimal point. Therefore, it is relatively easy to run into overflow conditions when multiplying two large numbers that both have significant scale.
The following example results in an overflow error.
```
SELECT CAST(1 AS DECIMAL(38, 20)) * CAST(10 AS DECIMAL(38, 20));
ERROR: 128 bit numeric data overflow (multiplication)
```
You can work around the overflow error by using division instead of multiplication. Use the following example to divide by 1 divided by the original divisor.
```
SELECT CAST(1 AS DECIMAL(38, 20)) / (1 / CAST(10 AS DECIMAL(38, 20)));
+----------+
| ?column? |
+----------+
| 10 |
+----------+
```
## Numeric calculations with INTEGER and DECIMAL types
When one of the operands in a calculation has an INTEGER data type and the other operand is DECIMAL, the INTEGER operand is implicitly cast as a DECIMAL:
+ INT2 (SMALLINT) is cast as DECIMAL(5,0)
+ INT4 (INTEGER) is cast as DECIMAL(10,0)
+ INT8 (BIGINT) is cast as DECIMAL(19,0)
For example, if you multiply SALES.COMMISSION, a DECIMAL(8,2) column, and SALES.QTYSOLD, a SMALLINT column, this calculation is cast as:
```
DECIMAL(8,2) * DECIMAL(5,0)
```
# Integer and floating-point literals
Literals or constants that represent numbers can be integer or floating-point.
## Integer literals
An integer constant is a sequence of the digits 0-9, with an optional positive (\$1) or negative (-) sign preceding the digits.
## Syntax
```
[ + | - ] digit ...
```
## Examples
Valid integers include the following:
```
23
-555
+17
```
## Floating-point literals
Floating-point literals (also referred to as decimal, numeric, or fractional literals) are sequences of digits that can include a decimal point, and optionally the exponent marker (e).
## Syntax
```
[ + | - ] digit ... [ . ] [ digit ...]
[ e | E [ + | - ] digit ... ]
```
## Arguments
e \$1 E
e or E indicates that the number is specified in scientific notation.
## Examples
Valid floating-point literals include the following:
```
3.14159
-37.
2.0e19
-2E-19
```
# Examples with numeric types
## CREATE TABLE statement
The following CREATE TABLE statement demonstrates the declaration of different numeric data types:
```
create table film (
film_id integer,
language_id smallint,
original_language_id smallint,
rental_duration smallint default 3,
rental_rate numeric(4,2) default 4.99,
length smallint,
replacement_cost real default 25.00);
```
## Attempt to insert an integer that is out of range
The following example attempts to insert the value 33000 into a SMALLINT column.
```
insert into film(language_id) values(33000);
```
The range for SMALLINT is -32768 to \$132767, so Amazon Redshift returns an error.
```
An error occurred when executing the SQL command:
insert into film(language_id) values(33000)
ERROR: smallint out of range [SQL State=22003]
```
## Insert a decimal value into an integer column
The following example inserts the a decimal value into an INT column.
```
insert into film(language_id) values(1.5);
```
This value is inserted but rounded up to the integer value 2.
## Insert a decimal that succeeds because its scale is rounded
The following example inserts a decimal value that has higher precision that the column.
```
insert into film(rental_rate) values(35.512);
```
In this case, the value `35.51` is inserted into the column.
## Attempt to insert a decimal value that is out of range
In this case, the value `350.10` is out of range. The number of digits for values in DECIMAL columns is equal to the column's precision minus its scale (4 minus 2 for the RENTAL\$1RATE column). In other words, the allowed range for a `DECIMAL(4,2)` column is `-99.99` through `99.99`.
```
insert into film(rental_rate) values (350.10);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^2 for field with precision 4, scale 2.
```
## Insert variable-precision values into a REAL column
The following example inserts variable-precision values into a REAL column.
```
insert into film(replacement_cost) values(1999999.99);
insert into film(replacement_cost) values(1999.99);
select replacement_cost from film;
+------------------+
| replacement_cost |
+------------------+
| 2000000 |
| 1999.99 |
+------------------+
```
The value `1999999.99` is converted to `2000000` to meet the precision requirement for the `REAL` column. The value `1999.99` is loaded as is.
# Character types
**Topics**
+ [
## Storage and ranges
](#r_Character_types-storage-and-ranges)
+ [
## CHAR or CHARACTER
](#r_Character_types-char-or-character)
+ [
## VARCHAR or CHARACTER VARYING
](#r_Character_types-varchar-or-character-varying)
+ [
## NCHAR and NVARCHAR types
](#r_Character_types-nchar-and-nvarchar-types)
+ [
## TEXT and BPCHAR types
](#r_Character_types-text-and-bpchar-types)
+ [
## Significance of trailing blanks
](#r_Character_types-significance-of-trailing-blanks)
+ [
# Examples with character types
](r_Examples_with_character_types.md)
Character data types include CHAR (character) and VARCHAR (character varying).
## Storage and ranges
CHAR and VARCHAR data types are defined in terms of bytes, not characters. A CHAR column can only contain single-byte characters, so a CHAR(10) column can contain a string with a maximum length of 10 bytes. A VARCHAR can contain multibyte characters, up to a maximum of four bytes per character. For example, a VARCHAR(12) column can contain 12 single-byte characters, 6 two-byte characters, 4 three-byte characters, or 3 four-byte characters.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_Character_types.html)
**Note**
The CREATE TABLE syntax supports the MAX keyword for character data types. For example:
```
create table test(col1 varchar(max));
```
For CHAR, MAX defines the column width as 4096 bytes.
For VARCHAR, MAX defines the column width as 65,535 bytes in CREATE TABLE statements. For in-memory operations, VARCHAR(MAX) supports up to 16,000,000 bytes.
## CHAR or CHARACTER
Use a CHAR or CHARACTER column to store fixed-length strings. These strings are padded with blanks, so a CHAR(10) column always occupies 10 bytes of storage.
```
char(10)
```
A CHAR column without a length specification results in a CHAR(1) column.
## VARCHAR or CHARACTER VARYING
Use a VARCHAR or CHARACTER VARYING column to store variable-length strings with a fixed limit. These strings are not padded with blanks, so a VARCHAR(120) column consists of a maximum of 120 single-byte characters, 60 two-byte characters, 40 three-byte characters, or 30 four-byte characters.
```
varchar(120)
```
If you use the VARCHAR data type without a length specifier in a CREATE TABLE statement, the default length is 256.
[String functions](String_functions_header.md) now support up to 16,000,000 bytes. For example, CONCAT function output was previously limited to 65535 bytes, but now supports up to 16,000,000 bytes.
```
SELECT LEN(CONCAT(REPEAT('A', 5000000), REPEAT('B', 5000000))) AS total_length;
total_length
--------------
10000000
```
## NCHAR and NVARCHAR types
You can create columns with the NCHAR and NVARCHAR types (also known as NATIONAL CHARACTER and NATIONAL CHARACTER VARYING types). These types are converted to CHAR and VARCHAR types, respectively, and are stored in the specified number of bytes.
An NCHAR column without a length specification is converted to a CHAR(1) column.
An NVARCHAR column without a length specification is converted to a VARCHAR(256) column.
## TEXT and BPCHAR types
You can create an Amazon Redshift table with a TEXT column, but it is converted to a VARCHAR(256) column that accepts variable-length values with a maximum of 256 characters.
You can create an Amazon Redshift column with a BPCHAR (blank-padded character) type, which Amazon Redshift converts to a fixed-length CHAR(256) column.
## Significance of trailing blanks
Both CHAR and VARCHAR data types store strings up to *n* bytes in length. An attempt to store a longer string into a column of these types results in an error, unless the extra characters are all spaces (blanks), in which case the string is truncated to the maximum length. If the string is shorter than the maximum length, CHAR values are padded with blanks, but VARCHAR values store the string without blanks.
Trailing blanks in CHAR values are always semantically insignificant. They are disregarded when you compare two CHAR values, not included in LENGTH calculations, and removed when you convert a CHAR value to another string type.
Trailing spaces in VARCHAR and CHAR values are treated as semantically insignificant when values are compared.
Length calculations return the length of VARCHAR character strings with trailing spaces included in the length. Trailing blanks are not counted in the length for fixed-length character strings.
# Examples with character types
## CREATE TABLE statement
The following CREATE TABLE statement demonstrates the use of VARCHAR and CHAR data types:
```
create table address(
address_id integer,
address1 varchar(100),
address2 varchar(50),
district varchar(20),
city_name char(20),
state char(2),
postal_code char(5)
);
```
The following examples use this table.
## Trailing blanks in variable-length character strings
Because ADDRESS1 is a VARCHAR column, the trailing blanks in the second inserted address are semantically insignificant. In other words, these two inserted addresses *match*.
```
insert into address(address1) values('9516 Magnolia Boulevard');
insert into address(address1) values('9516 Magnolia Boulevard ');
```
```
select count(*) from address
where address1='9516 Magnolia Boulevard';
count
-------
2
(1 row)
```
If the ADDRESS1 column were a CHAR column and the same values were inserted, the COUNT(\$1) query would recognize the character strings as the same and return `2`.
## Results of the LENGTH function
The LENGTH function recognizes trailing blanks in VARCHAR columns:
```
select length(address1) from address;
length
--------
23
25
(2 rows)
```
A value of `Augusta` in the CITY\$1NAME column, which is a CHAR column, would always return a length of 7 characters, regardless of any trailing blanks in the input string.
## Values that exceed the length of the column
Character strings are not truncated to fit the declared width of the column:
```
insert into address(city_name) values('City of South San Francisco');
ERROR: value too long for type character(20)
```
A workaround for this problem is to cast the value to the size of the column:
```
insert into address(city_name)
values('City of South San Francisco'::char(20));
```
In this case, the first 20 characters of the string (`City of South San Fr`) would be loaded into the column.
# Datetime types
**Topics**
+ [
## Storage and ranges
](#r_Datetime_types-storage-and-ranges)
+ [
## DATE
](#r_Datetime_types-date)
+ [
## TIME
](#r_Datetime_types-time)
+ [
## TIMETZ
](#r_Datetime_types-timetz)
+ [
## TIMESTAMP
](#r_Datetime_types-timestamp)
+ [
## TIMESTAMPTZ
](#r_Datetime_types-timestamptz)
+ [
# Examples with datetime types
](r_Examples_with_datetime_types.md)
+ [
# Date, time, and timestamp literals
](r_Date_and_time_literals.md)
+ [
# Interval data types and literals
](r_interval_data_types.md)
Datetime data types include DATE, TIME, TIMETZ, TIMESTAMP, and TIMESTAMPTZ.
## Storage and ranges
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_Datetime_types.html)
## DATE
Use the DATE data type to store simple calendar dates without timestamps.
## TIME
TIME is an alias of TIME WITHOUT TIME ZONE.
Use the TIME data type to store the time of day.
TIME columns store values with up to a maximum of six digits of precision for fractional seconds.
By default, TIME values are Coordinated Universal Time (UTC) in both user tables and Amazon Redshift system tables.
## TIMETZ
TIMETZ is an alias of TIME WITH TIME ZONE.
Use the TIMETZ data type to store the time of day with a time zone.
TIMETZ columns store values with up to a maximum of six digits of precision for fractional seconds.
By default, TIMETZ values are UTC in both user tables and Amazon Redshift system tables.
## TIMESTAMP
TIMESTAMP is an alias of TIMESTAMP WITHOUT TIME ZONE.
Use the TIMESTAMP data type to store complete timestamp values that include the date and the time of day.
TIMESTAMP columns store values with up to a maximum of six digits of precision for fractional seconds.
If you insert a date into a TIMESTAMP column, or a date with a partial timestamp value, the value is implicitly converted into a full timestamp value. This full timestamp value has default values (00) for missing hours, minutes, and seconds. Time zone values in input strings are ignored.
By default, TIMESTAMP values are UTC in both user tables and Amazon Redshift system tables.
## TIMESTAMPTZ
TIMESTAMPTZ is an alias of TIMESTAMP WITH TIME ZONE.
Use the TIMESTAMPTZ data type to input complete timestamp values that include the date, the time of day, and a time zone. When an input value includes a time zone, Amazon Redshift uses the time zone to convert the value to UTC and stores the UTC value.
To view a list of supported time zone names, run the following command.
```
select pg_timezone_names();
```
To view a list of supported time zone abbreviations, run the following command.
```
select pg_timezone_abbrevs();
```
You can also find current information about time zones in the [IANA Time Zone Database](https://www.iana.org/time-zones).
The following table has examples of time zone formats.
| Format | Example |
| --- | --- |
| dd mon hh:mi:ss yyyy tz | 17 Dec 07:37:16 1997 PST |
| mm/dd/yyyy hh:mi:ss.ss tz | 12/17/1997 07:37:16.00 PST |
| mm/dd/yyyy hh:mi:ss.ss tz | 12/17/1997 07:37:16.00 US/Pacific |
| yyyy-mm-dd hh:mi:ss\$1/-tz | 1997-12-17 07:37:16-08 |
| dd.mm.yyyy hh:mi:ss tz | 17.12.1997 07:37:16.00 PST |
TIMESTAMPTZ columns store values with up to a maximum of six digits of precision for fractional seconds.
If you insert a date into a TIMESTAMPTZ column, or a date with a partial timestamp, the value is implicitly converted into a full timestamp value. This full timestamp value has default values (00) for missing hours, minutes, and seconds.
TIMESTAMPTZ values are UTC in user tables.
# Examples with datetime types
Following, you can find examples for working with datetime types supported by Amazon Redshift.
## Date examples
The following examples insert dates that have different formats and display the output.
```
create table datetable (start_date date, end_date date);
```
```
insert into datetable values ('2008-06-01','2008-12-31');
insert into datetable values ('Jun 1,2008','20081231');
```
```
select * from datetable order by 1;
start_date | end_date
-----------------------
2008-06-01 | 2008-12-31
2008-06-01 | 2008-12-31
```
If you insert a timestamp value into a DATE column, the time portion is ignored and only the date is loaded.
## Time examples
The following examples insert TIME and TIMETZ values that have different formats and display the output.
```
create table timetable (start_time time, end_time timetz);
```
```
insert into timetable values ('19:11:19','20:41:19 UTC');
insert into timetable values ('191119', '204119 UTC');
```
```
select * from timetable order by 1;
start_time | end_time
------------------------
19:11:19 | 20:41:19+00
19:11:19 | 20:41:19+00
```
## Time stamp examples
If you insert a date into a TIMESTAMP or TIMESTAMPTZ column, the time defaults to midnight. For example, if you insert the literal `20081231`, the stored value is `2008-12-31 00:00:00`.
To change the time zone for the current session, use the [SET](r_SET.md) command to set the [timezone](r_timezone_config.md) configuration parameter.
The following example inserts timestamps that have different formats and display the resulting table.
```
create table tstamp(timeofday timestamp, timeofdaytz timestamptz);
insert into tstamp values('Jun 1,2008 09:59:59', 'Jun 1,2008 09:59:59 EST' );
insert into tstamp values('Dec 31,2008 18:20','Dec 31,2008 18:20');
insert into tstamp values('Jun 1,2008 09:59:59 EST', 'Jun 1,2008 09:59:59');
SELECT * FROM tstamp;
+---------------------+------------------------+
| timeofday | timeofdaytz |
+---------------------+------------------------+
| 2008-06-01 09:59:59 | 2008-06-01 14:59:59+00 |
| 2008-12-31 18:20:00 | 2008-12-31 18:20:00+00 |
| 2008-06-01 09:59:59 | 2008-06-01 09:59:59+00 |
+---------------------+------------------------+
```
# Date, time, and timestamp literals
Following are rules for working with date, time, and timestamp literals supported by Amazon Redshift.
## Dates
The following input dates are all valid examples of literal date values for the DATE data type that you can load into Amazon Redshift tables. The default `MDY DateStyle` mode is assumed to be in effect. This mode means that the month value precedes the day value in strings such as `1999-01-08` and `01/02/00`.
**Note**
A date or timestamp literal must be enclosed in quotation marks when you load it into a table.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_Date_and_time_literals.html)
## Times
The following input times are all valid examples of literal time values for the TIME and TIMETZ data types that you can load into Amazon Redshift tables.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_Date_and_time_literals.html)
## Timestamps
The following input timestamps are all valid examples of literal time values for the TIMESTAMP and TIMESTAMPTZ data types that you can load into Amazon Redshift tables. All of the valid date literals can be combined with the following time literals.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_Date_and_time_literals.html)
## Special datetime values
The following special values can be used as datetime literals and as arguments to date functions. They require single quotation marks and are converted to regular timestamp values during query processing.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_Date_and_time_literals.html)
The following examples show how `now` and `today` work with the DATEADD function.
```
select dateadd(day,1,'today');
date_add
---------------------
2009-11-17 00:00:00
(1 row)
select dateadd(day,1,'now');
date_add
----------------------------
2009-11-17 10:45:32.021394
(1 row)
```
# Interval data types and literals
You can use an interval data type to store durations of time in units such as, `seconds`, `minutes`, `hours`, `days`, `months`, and `years`. Interval data types and literals can be used in datetime calculations, such as, adding intervals to dates and timestamps, summing intervals, and subtracting an interval from a date or timestamp. Interval literals can be used as input values to interval data type columns in a table.
## Syntax of interval data type
To specify an interval data type to store a duration of time in years and months:
```
INTERVAL year_to_month_qualifier
```
To specify an interval data type to store a duration in days, hours, minutes, and seconds:
```
INTERVAL day_to_second_qualifier [ (fractional_precision) ]
```
## Syntax of interval literal
To specify an interval literal to define a duration of time in years and months:
```
INTERVAL quoted-string year_to_month_qualifier
```
To specify an interval literal to define a duration in days, hours, minutes, and seconds:
```
INTERVAL quoted-string day_to_second_qualifier [ (fractional_precision) ]
```
## Arguments
*quoted-string*
Specifies a positive or negative numeric value specifying a quantity and the datetime unit as an input string. If the *quoted-string* contains only a numeric, then Amazon Redshift determines the units from the *year\$1to\$1month\$1qualifier* or *day\$1to\$1second\$1qualifier*. For example, `'23' MONTH` represents `1 year 11 months`, `'-2' DAY` represents `-2 days 0 hours 0 minutes 0.0 seconds`, `'1-2' MONTH` represents `1 year 2 months`, and `'13 day 1 hour 1 minute 1.123 seconds' SECOND` represents `13 days 1 hour 1 minute 1.123 seconds`. For more information about output formats of an interval, see [Interval styles](#r_interval_data_types-interval-styles).
*year\$1to\$1month\$1qualifier*
Specifies the range of the interval. If you use a qualifier and create an interval with time units smaller than the qualifier, Amazon Redshift truncates and discards the smaller parts of the interval. Valid values for *year\$1to\$1month\$1qualifier* are:
+ `YEAR`
+ `MONTH`
+ `YEAR TO MONTH`
*day\$1to\$1second\$1qualifier*
Specifies the range of the interval. If you use a qualifier and create an interval with time units smaller than the qualifier, Amazon Redshift truncates and discards the smaller parts of the interval. Valid values for *day\$1to\$1second\$1qualifier* are:
+ `DAY`
+ `HOUR`
+ `MINUTE`
+ `SECOND`
+ `DAY TO HOUR`
+ `DAY TO MINUTE`
+ `DAY TO SECOND`
+ `HOUR TO MINUTE`
+ `HOUR TO SECOND`
+ `MINUTE TO SECOND`
The output of the INTERVAL literal is truncated to the smallest INTERVAL component specified. For example, when using a MINUTE qualifier, Amazon Redshift discards the time units smaller than MINUTE.
```
select INTERVAL '1 day 1 hour 1 minute 1.123 seconds' MINUTE
```
The resulting value is truncated to `'1 day 01:01:00'`.
*fractional\$1precision*
Optional parameter that specifies the number of fractional digits allowed in the interval. The *fractional\$1precision* argument should only be specified if your interval contains SECOND. For example, `SECOND(3)` creates an interval that allows only three fractional digits, such as 1.234 seconds. The maximum number of fractional digits is six.
The session configuration `interval_forbid_composite_literals` determines whether an error is returned when an interval is specified with both YEAR TO MONTH and DAY TO SECOND parts. For more information, see [interval\$1forbid\$1composite\$1literals](r_interval_forbid_composite_literals.md).
## Interval arithmetic
You can use interval values with other datetime values to perform arithmetic operations. The following tables describe the available operations and what data type results from each operation.
**Note**
Operations that can produce both `date` and `timestamp` results do so based on the smallest unit of time involved in the equation. For example, when you add an `interval` to a `date` the result is a `date` if it is a YEAR TO MONTH interval, and a timestamp if it is a DAY TO SECOND interval.
Operations where the first operand is an `interval` produce the following results for the given second operand:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_interval_data_types.html)
Operations where the first operand is a `date` produce the following results for the given second operand:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_interval_data_types.html)
Operations where the first operand is a `timestamp` produce the following results for the given second operand:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_interval_data_types.html)
## Interval styles
You can use the SQL [SET](r_SET.md) command to change the output display format of your interval values. When you use the interval data type in SQL, cast it to text to see the expected interval style, for example, `YEAR TO MONTH::text`. Available values to SET the `IntervalStyle` value are:
+ `postgres` – follows PostgreSQL style. This is the default.
+ `postgres_verbose` – follows PostgreSQL verbose style.
+ `sql_standard` – follows the SQL standard interval literals style.
The following command sets the interval style to `sql_standard`.
```
SET IntervalStyle to 'sql_standard';
```
**postgres output format**
The following is the output format for `postgres` interval style. Each numeric value can be negative.
```
' [, ...]'
```
```
select INTERVAL '1-2' YEAR TO MONTH::text
varchar
---------------
1 year 2 mons
```
```
select INTERVAL '1 2:3:4.5678' DAY TO SECOND::text
varchar
------------------
1 day 02:03:04.5678
```
**postgres\$1verbose output format**
postgres\$1verbose syntax is similar to postgres, but postgres\$1verbose outputs also contain the unit of time.
```
'[@] [, ...] [direction]'
```
```
select INTERVAL '1-2' YEAR TO MONTH::text
varchar
-----------------
@ 1 year 2 mons
```
```
select INTERVAL '1 2:3:4.5678' DAY TO SECOND::text
varchar
---------------------------
@ 1 day 2 hours 3 mins 4.56 secs
```
**sql\$1standard output format**
Interval year to month values are formatted as the following. Specifying a negative sign before the interval indicates the interval is a negative value and applies to the entire interval.
```
'[-]yy-mm'
```
Interval day to second values are formatted as the following.
```
'[-]dd hh:mm:ss.ffffff'
```
```
SELECT INTERVAL '1-2' YEAR TO MONTH::text
varchar
-------
1-2
```
```
select INTERVAL '1 2:3:4.5678' DAY TO SECOND::text
varchar
---------------
1 2:03:04.5678
```
## Examples of interval data type
The following examples demonstrate how to use INTERVAL data types with tables.
```
create table sample_intervals (y2m interval month, h2m interval hour to minute);
insert into sample_intervals values (interval '20' month, interval '2 days 1:1:1.123456' day to second);
select y2m::text, h2m::text from sample_intervals;
y2m | h2m
---------------+-----------------
1 year 8 mons | 2 days 01:01:00
```
```
update sample_intervals set y2m = interval '2' year where y2m = interval '1-8' year to month;
select * from sample_intervals;
y2m | h2m
---------+-----------------
2 years | 2 days 01:01:00
```
```
delete from sample_intervals where h2m = interval '2 1:1:0' day to second;
select * from sample_intervals;
y2m | h2m
-----+-----
```
## Examples of interval literals
The following examples are run with interval style set to `postgres`.
The following example demonstrates how to create an INTERVAL literal of 1 year.
```
select INTERVAL '1' YEAR
intervaly2m
---------------
1 years 0 mons
```
If you specify a *quoted-string* that exceeds the qualifier, the remaining units of time are truncated from the interval. In the following example, an interval of 13 months becomes 1 year and 1 month, but the remaining 1 month is left out because of the YEAR qualifier.
```
select INTERVAL '13 months' YEAR
intervaly2m
---------------
1 years 0 mons
```
If you use a qualifier lower than your interval string, leftover units are included.
```
select INTERVAL '13 months' MONTH
intervaly2m
---------------
1 years 1 mons
```
Specifying a precision in your interval truncates the number of fractional digits to the specified precision.
```
select INTERVAL '1.234567' SECOND (3)
intervald2s
--------------------------------
0 days 0 hours 0 mins 1.235 secs
```
If you don't specify a precision, Amazon Redshift uses the maximum precision of 6.
```
select INTERVAL '1.23456789' SECOND
intervald2s
-----------------------------------
0 days 0 hours 0 mins 1.234567 secs
```
The following example demonstrates how to create a ranged interval.
```
select INTERVAL '2:2' MINUTE TO SECOND
intervald2s
------------------------------
0 days 0 hours 2 mins 2.0 secs
```
Qualifiers dictate the units that you're specifying. For example, even though the following example uses the same *quoted-string* of '2:2' as the previous example, Amazon Redshift recognizes that it uses different units of time because of the qualifier.
```
select INTERVAL '2:2' HOUR TO MINUTE
intervald2s
------------------------------
0 days 2 hours 2 mins 0.0 secs
```
Abbreviations and plurals of each unit are also supported. For example, `5s`, `5 second`, and `5 seconds` are equivalent intervals. Supported units are years, months, hours, minutes, and seconds.
```
select INTERVAL '5s' SECOND
intervald2s
------------------------------
0 days 0 hours 0 mins 5.0 secs
```
```
select INTERVAL '5 HOURS' HOUR
intervald2s
------------------------------
0 days 5 hours 0 mins 0.0 secs
```
```
select INTERVAL '5 h' HOUR
intervald2s
------------------------------
0 days 5 hours 0 mins 0.0 secs
```
# Examples of interval literals without qualifier syntax
**Note**
The following examples demonstrate using an interval literal without a `YEAR TO MONTH` or `DAY TO SECOND` qualifier. For information about using the recommended interval literal with a qualifier, see [Interval data types and literals](r_interval_data_types.md).
Use an interval literal to identify specific periods of time, such as `12 hours` or `6 months`. You can use these interval literals in conditions and calculations that involve datetime expressions.
An interval literal is expressed as a combination of the INTERVAL keyword with a numeric quantity and a supported date part, for example `INTERVAL '7 days'` or `INTERVAL '59 minutes'`. You can connect several quantities and units to form a more precise interval, for example: `INTERVAL '7 days, 3 hours, 59 minutes'`. Abbreviations and plurals of each unit are also supported; for example: `5 s`, `5 second`, and `5 seconds` are equivalent intervals.
If you don't specify a date part, the interval value represents seconds. You can specify the quantity value as a fraction (for example: `0.5 days`).
The following examples show a series of calculations with different interval values.
The following adds 1 second to the specified date.
```
select caldate + interval '1 second' as dateplus from date
where caldate='12-31-2008';
dateplus
---------------------
2008-12-31 00:00:01
(1 row)
```
The following adds 1 minute to the specified date.
```
select caldate + interval '1 minute' as dateplus from date
where caldate='12-31-2008';
dateplus
---------------------
2008-12-31 00:01:00
(1 row)
```
The following adds 3 hours and 35 minutes to the specified date.
```
select caldate + interval '3 hours, 35 minutes' as dateplus from date
where caldate='12-31-2008';
dateplus
---------------------
2008-12-31 03:35:00
(1 row)
```
The following adds 52 weeks to the specified date.
```
select caldate + interval '52 weeks' as dateplus from date
where caldate='12-31-2008';
dateplus
---------------------
2009-12-30 00:00:00
(1 row)
```
The following adds 1 week, 1 hour, 1 minute, and 1 second to the specified date.
```
select caldate + interval '1w, 1h, 1m, 1s' as dateplus from date
where caldate='12-31-2008';
dateplus
---------------------
2009-01-07 01:01:01
(1 row)
```
The following adds 12 hours (half a day) to the specified date.
```
select caldate + interval '0.5 days' as dateplus from date
where caldate='12-31-2008';
dateplus
---------------------
2008-12-31 12:00:00
(1 row)
```
The following subtracts 4 months from February 15, 2023 and the result is October 15, 2022.
```
select date '2023-02-15' - interval '4 months';
?column?
---------------------
2022-10-15 00:00:00
```
The following subtracts 4 months from March 31, 2023 and the result is November 30, 2022. The calculation considers the number of days in a month.
```
select date '2023-03-31' - interval '4 months';
?column?
---------------------
2022-11-30 00:00:00
```
# Boolean type
Use the BOOLEAN data type to store true and false values in a single-byte column. The following table describes the three possible states for a Boolean value and the literal values that result in that state. Regardless of the input string, a Boolean column stores and outputs "t" for true and "f" for false.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_Boolean_type.html)
You can use an IS comparison to check a Boolean value only as a predicate in the WHERE clause. You can't use the IS comparison with a Boolean value in the SELECT list.
## Examples
You could use a BOOLEAN column to store an "Active/Inactive" state for each customer in a CUSTOMER table.
```
create table customer(
custid int,
active_flag boolean default true);
```
```
insert into customer values(100, default);
```
```
select * from customer;
custid | active_flag
-------+--------------
100 | t
```
If no default value (`true` or `false`) is specified in the CREATE TABLE statement, inserting a default value means inserting a null.
In this example, the query selects users from the USERS table who like sports but do not like theatre:
```
select firstname, lastname, likesports, liketheatre
from users
where likesports is true and liketheatre is false
order by userid limit 10;
firstname | lastname | likesports | liketheatre
----------+------------+------------+-------------
Lars | Ratliff | t | f
Mufutau | Watkins | t | f
Scarlett | Mayer | t | f
Shafira | Glenn | t | f
Winifred | Cherry | t | f
Chase | Lamb | t | f
Liberty | Ellison | t | f
Aladdin | Haney | t | f
Tashya | Michael | t | f
Lucian | Montgomery | t | f
(10 rows)
```
The following example selects users from the USERS table for whom is it unknown whether they like rock music.
```
select firstname, lastname, likerock
from users
where likerock is unknown
order by userid limit 10;
firstname | lastname | likerock
----------+----------+----------
Rafael | Taylor |
Vladimir | Humphrey |
Barry | Roy |
Tamekah | Juarez |
Mufutau | Watkins |
Naida | Calderon |
Anika | Huff |
Bruce | Beck |
Mallory | Farrell |
Scarlett | Mayer |
(10 rows)
```
The following example returns an error because it uses an IS comparison in the SELECT list.
```
select firstname, lastname, likerock is true as "check"
from users
order by userid limit 10;
[Amazon](500310) Invalid operation: Not implemented
```
The following example succeeds because it uses an equal comparison ( = ) in the SELECT list instead of the IS comparison.
```
select firstname, lastname, likerock = true as "check"
from users
order by userid limit 10;
firstname | lastname | check
----------+-----------+------
Rafael | Taylor |
Vladimir | Humphrey |
Lars | Ratliff | true
Barry | Roy |
Reagan | Hodge | true
Victor | Hernandez | true
Tamekah | Juarez |
Colton | Roy | false
Mufutau | Watkins |
Naida | Calderon |
```
# HLLSKETCH type
Use the HLLSKETCH data type for HyperLogLog sketches. Amazon Redshift supports HyperLogLog sketch representations that are either sparse or dense. Sketches begin as sparse and switch to dense when the dense format is more efficient to minimize the memory footprint that is used.
Amazon Redshift automatically transitions a sparse HyperLogLog sketch when importing, exporting, or printing sketches in the following JSON format.
```
{"logm":15,"sparse":{"indices":[4878,9559,14523],"values":[1,2,1]}}
```
Amazon Redshift uses a string representation in a Base64 format to represent a dense HyperLogLog sketch.
Amazon Redshift uses the following string representation in a Base64 format to represent a dense HyperLogLog sketch.
```
"ABAABA..."
```
The maximum size of a HLLSKETCH object is 24,580 bytes when used in raw compression.
# SUPER type
Use the SUPER data type to store semi-structured data or documents as values. Although Amazon Redshift is capable of storing such values using VARCHAR, we recommend that you use the SUPER data type instead.
Semi-structured data doesn't conform to the rigid and tabular structure of the relational data model used in SQL databases. It contains tags that reference distinct entities within the data. They can contain complex values such as arrays, nested structures, and other complex structures that are associated with serialization formats, such as JSON. The SUPER data type is a set of schemaless array and structure values that encompass all other scalar types of Amazon Redshift.
The SUPER data type supports up to 16 MB of data for an individual SUPER object. For more information on the SUPER data type, including examples of implementing it in a table, see [Semi-structured data in Amazon Redshift](super-overview.md).
Amazon Redshift provides built-in support to ingest the following semi-structured data formats using the COPY command:
+ JSON
+ ARRAY
+ TEXT
+ CSV
SUPER objects larger than 1MB can only be ingested from the following file formats:
+ Parquet
+ JSON
+ TEXT
+ CSV
The SUPER data type has the following properties:
+ An Amazon Redshift scalar value:
+ A null
+ A boolean
+ A number, such as smallint, integer, bigint, decimal, or floating point (such as float4 or float8)
+ A string value, such as varchar or char
+ A complex value:
+ An array of values, including scalar or complex
+ A structure, also known as tuple or object, that is a map of attribute names and values (scalar or complex)
Any of the two types of complex values contain their own scalars or complex values without having any restrictions for regularity.
The default compression encoding for the SUPER data type is ZSTD. For more information on compression encoding, see [Compression encodings](c_Compression_encodings.md).
The SUPER data type supports the persistence of semi-structured data in a schemaless form. Although hierarchical data model can change, the old versions of data can coexist in the same SUPER column.
Amazon Redshift uses PartiQL to enable navigation into arrays and structures. Amazon Redshift also uses the PartiQL syntax to iterate over SUPER arrays. For more information, see [PartiQL – an SQL-compatible query language for Amazon Redshift](super-partiql.md).
Amazon Redshift uses dynamic typing to process schemaless SUPER data without needing to declare the data types before you use them in your query. For more information, see [Dynamic typing](query-super.md#dynamic-typing-lax-processing).
You can apply dynamic data masking policies to scalar values on the paths of SUPER type columns. For more information about dynamic data masking, see [Dynamic data masking](t_ddm.md). For information about using dynamic data masking with the SUPER data type, see [Using dynamic data masking with SUPER data type paths](t_ddm-super.md).
We recommend that you set the `r_enable_case_sensitive_super_attribute` configuration option to true when working with SUPER data. For more information, see [enable\$1case\$1sensitive\$1super\$1attribute](r_enable_case_sensitive_super_attribute.md).
# VARBYTE type
Use a VARBYTE, VARBINARY, or BINARY VARYING column to store variable-length binary value with a fixed limit.
```
varbyte [ (n) ]
```
The maximum number of bytes (*n*) can range from 1 – 16,777,216. The default is 64,000.
Some examples where you might want to use a VARBYTE data type are as follows:
+ Joining tables on VARBYTE columns.
+ Creating materialized views that contain VARBYTE columns. Incremental refresh of materialized views that contain VARBYTE columns is supported. However, aggregate functions other than COUNT, MIN, and MAX and GROUP BY on VARBYTE columns don't support incremental refresh.
To ensure that all bytes are printable characters, Amazon Redshift uses the hex format to print VARBYTE values. For example, the following SQL converts the hexadecimal string `6162` into a binary value. Even though the returned value is a binary value, the results are printed as hexadecimal `6162`.
```
select from_hex('6162');
from_hex
----------
6162
```
Amazon Redshift supports casting between VARBYTE and the following data types:
+ CHAR
+ VARCHAR
+ SMALLINT
+ INTEGER
+ BIGINT
When casting with CHAR and VARCHAR the UTF-8 format is used. For more information about the UTF-8 format, see [TO\$1VARBYTE](r_TO_VARBYTE.md). When casting from SMALLINT, INTEGER, and BIGINT the number of bytes of the original data type is maintained. That is two bytes for SMALLINT, four bytes for INTEGER, and eight bytes for BIGINT.
The following SQL statement casts a VARCHAR string to a VARBYTE. Even though the returned value is a binary value, the results are printed as hexadecimal `616263`.
```
select 'abc'::varbyte;
varbyte
---------
616263
```
The following SQL statement casts a CHAR value in a column to a VARBYTE. This example creates a table with a CHAR(10) column (c), inserts character values that are shorter than the length of 10. The resulting cast pads the result with a space characters (hex'20') to the defined column size. Even though the returned value is a binary value, the results are printed as hexadecimal.
```
create table t (c char(10));
insert into t values ('aa'), ('abc');
select c::varbyte from t;
c
----------------------
61612020202020202020
61626320202020202020
```
The following SQL statement casts a SMALLINT string to a VARBYTE. Even though the returned value is a binary value, the results are printed as hexadecimal `0005`, which is two bytes or four hexadecimal characters.
```
select 5::smallint::varbyte;
varbyte
---------
0005
```
The following SQL statement casts an INTEGER to a VARBYTE. Even though the returned value is a binary value, the results are printed as hexadecimal `00000005`, which is four bytes or eight hexadecimal characters.
```
select 5::int::varbyte;
varbyte
----------
00000005
```
The following SQL statement casts a BIGINT to a VARBYTE. Even though the returned value is a binary value, the results are printed as hexadecimal `0000000000000005`, which is eight bytes or 16 hexadecimal characters.
```
select 5::bigint::varbyte;
varbyte
------------------
0000000000000005
```
Amazon Redshift features that support the VARBYTE data type include:
+ [VARBYTE operators](r_VARBYTE_OPERATORS.md)
+ [CONCAT](r_CONCAT.md)
+ [LEN](r_LEN.md)
+ [LENGTH function](r_LENGTH.md)
+ [OCTET\$1LENGTH](r_OCTET_LENGTH.md)
+ [SUBSTRING function](r_SUBSTRING.md)
+ [FROM\$1HEX](r_FROM_HEX.md)
+ [TO\$1HEX](r_TO_HEX.md)
+ [FROM\$1VARBYTE](r_FROM_VARBYTE.md)
+ [TO\$1VARBYTE](r_TO_VARBYTE.md)
+ [GETBIT](r_GETBIT.md)
+ [Loading a column of the VARBYTE data type](copy-usage-varbyte.md)
+ [Unloading a column of the VARBYTE data type](r_UNLOAD.md#unload-usage-notes)
## Limitations when using the VARBYTE data type with Amazon Redshift
The following are limitations when using the VARBYTE data type with Amazon Redshift:
+ Amazon Redshift Spectrum supports the VARBYTE data type only for Parquet and ORC files.
+ Amazon Redshift query editor and Amazon Redshift query editor v2 don't yet fully support VARBYTE data type. Therefore, use a different SQL client when working with VARBYTE expressions.
As a workaround to use the query editor, if the length of your data is at or below 16,000,000 bytes and the content is valid UTF-8, you can cast the VARBYTE values to VARCHAR, for example:
```
select to_varbyte('6162', 'hex')::varchar;
```
+ You can't use VARBYTE data types with Python or Lambda user-defined functions (UDFs).
+ You can't create a HLLSKETCH column from a VARBYTE column or use APPROXIMATE COUNT DISTINCT on a VARBYTE column.
+ VARBYTE values larger than 1 MB can only be ingested from the following file formats:
+ Parquet
+ Text
+ Comma‐separated values (CSV)
## Type compatibility and conversion
Following, you can find a discussion about how type conversion rules and data type compatibility work in Amazon Redshift.
### Compatibility
Data type matching and matching of literal values and constants to data types occurs during various database operations, including the following:
+ Data manipulation language (DML) operations on tables
+ UNION, INTERSECT, and EXCEPT queries
+ CASE expressions
+ Evaluation of predicates, such as LIKE and IN
+ Evaluation of SQL functions that do comparisons or extractions of data
+ Comparisons with mathematical operators
The results of these operations depend on type conversion rules and data type compatibility. *Compatibility* implies that a one-to-one matching of a certain value and a certain data type is not always required. Because some data types are *compatible*, an implicit conversion, or *coercion*, is possible (for more information, see [Implicit conversion types](#implicit-conversion-types)). When data types are incompatible, you can sometimes convert a value from one data type to another by using an explicit conversion function.
### General compatibility and conversion rules
Note the following compatibility and conversion rules:
+ In general, data types that fall into the same type category (such as different numeric data types) are compatible and can be implicitly converted.
For example, with implicit conversion you can insert a decimal value into an integer column. The decimal is rounded to produce a whole number. Or you can extract a numeric value, such as `2008`, from a date and insert that value into an integer column.
+ Numeric data types enforce overflow conditions that occur when you attempt to insert out-of-range values. For example, a decimal value with a precision of 5 does not fit into a decimal column that was defined with a precision of 4. An integer or the whole part of a decimal is never truncated; however, the fractional part of a decimal can be rounded up or down, as appropriate. However, results of explicit casts of values selected from tables are not rounded.
+ Different types of character strings are compatible; VARCHAR column strings containing single-byte data and CHAR column strings are comparable and implicitly convertible. VARCHAR strings that contain multibyte data are not comparable. Also, you can convert a character string to a date, time, timestamp, or numeric value if the string is an appropriate literal value; any leading or trailing spaces are ignored. Conversely, you can convert a date, time, timestamp, or numeric value to a fixed-length or variable-length character string.
**Note**
A character string that you want to cast to a numeric type must contain a character representation of a number. For example, you can cast the strings `'1.0'` or `'5.9'` to decimal values, but you cannot cast the string `'ABC'` to any numeric type.
+ If you compare DECIMAL values with character strings, Amazon Redshift attempts to convert the character string to a DECIMAL value. When comparing all other numeric values with character strings, the numeric values are converted to character strings. To enforce the opposite conversion (for example, converting character strings to integers, or converting DECIMAL values to character strings), use an explicit function, such as [CAST](r_CAST_function.md).
+ To convert 64-bit DECIMAL or NUMERIC values to a higher precision, you must use an explicit conversion function such as the CAST or CONVERT functions.
+ When converting DATE or TIMESTAMP to TIMESTAMPTZ, or converting TIME to TIMETZ, the time zone is set to the current session time zone. The session time zone is UTC by default. For more information about setting the session time zone, see [timezone](r_timezone_config.md).
+ Similarly, TIMESTAMPTZ is converted to DATE, TIME, or TIMESTAMP based on the current session time zone. The session time zone is UTC by default. After the conversion, time zone information is dropped.
+ Character strings that represent a timestamp with time zone specified are converted to TIMESTAMPTZ using the current session time zone, which is UTC by default. Likewise, character strings that represent a time with time zone specified are converted to TIMETZ using the current session time zone, which is UTC by default.
### Implicit conversion types
There are two types of implicit conversions:
+ Implicit conversions in assignments, such as setting values in INSERT or UPDATE commands.
+ Implicit conversions in expressions, such as performing comparisons in the WHERE clause.
The table following lists the data types that can be converted implicitly in assignments or expressions. You can also use an explicit conversion function to perform these conversions.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/c_Supported_data_types.html)
**Note**
Implicit conversions between TIMESTAMPTZ, TIMESTAMP, DATE, TIME, TIMETZ, or character strings use the current session time zone. For information about setting the current time zone, see [timezone](r_timezone_config.md).
The GEOMETRY and GEOGRAPHY data types can't be implicitly converted to any other data type, except each other. For more information, see [CAST function](r_CAST_function.md).
The VARBYTE data type can't be implicitly converted to any other data type. For more information, see [CAST function](r_CAST_function.md).
### Using dynamic typing for the SUPER data type
Amazon Redshift uses dynamic typing to process schemaless SUPER data without the need to declare the data types before you use them in your query. Dynamic typing uses the results of navigating into SUPER data columns without having to explicitly cast them into Amazon Redshift types. For more information about using dynamic typing for SUPER data type, see [Dynamic typing](query-super.md#dynamic-typing-lax-processing).
You can cast SUPER values to and from other data types with some exceptions. For more information, see [Limitations](limitations-super.md).