Amazon Redshift data types cheatsheet

In this post, we are going to get started with all the supported data types that you can use in Amazon Resdhift database, including 4 major group: Numeric Types, Character Types, Datetime Types, Boolean Types.

1. Numeric Types

NUMERIC type can be referred to DECIMAL type to store values with a user-defined precision. But it is used generically to refer the bigger group of data types containing integer, decimal, and floating-point data types.

Integer Types

Use the SMALLINT, INTEGER, and BIGINT data types to store whole numbers of various ranges.

Name	Alias	Storage	Range
SMALLINT	INT2	2 bytes	-32768 to +32767
INTEGER	INT or INT4	4 bytes	-2147483648 to +2147483647
BIGINT	INT8	8 bytes	-9223372036854775808 to 9223372036854775807

DECIMAL or NUMERIC Type

Use the DECIMAL or NUMERIC data type to store values with a user-defined precision.

Name	Alias	Storage	Range
DECIMAL	NUMERIC	Variable, up to 128 bits for uncompressed DECIMAL types.	128-bit signed integers with up to 38 digits of precision.

Floating-Point Types

Use the REAL and DOUBLE PRECISION data types to store numeric values with variable precision.

Name	Alias	Storage	Range
REAL	FLOAT4	4 bytes	6 significant digits of precision.
DOUBLE PRECISION	FLOAT8 or FLOAT	8 bytes	15 significant digits of precision.

2. Character Types

Name	Alias	Storage	Range
CHAR	CHARACTER or NCHAR	Length of string, including trailing blanks (if any)	4096 bytes
VARCHAR	CHARACTER VARYING or NVARCHAR	4 bytes + total bytes for characters, where each character can be 1 to 4 bytes.	65535 bytes (64K -1)
BPCHAR	—	Converted to fixed-length CHAR(256).	256 bytes
TEXT		Converted to VARCHAR(256).	260 bytes

3. Datetime Types

Name	Alias	Storage	Range	Resolution
DATE	4 bytes	4713 BC to 294276 AD	1 day
TIMESTAMP	TIMESTAMP WITHOUT TIME ZONE	8 bytes	4713 BC to 294276 AD	1 microsecond
TIMESTAMPTZ	TIMESTAMP WITH TIME ZONE	8 bytes	4713 BC to 294276 AD	1 microsecond

4. Boolean Type

Use the BOOLEAN data type to store true and false values in a single-byte column.

State	Valid Literal Values	Storage
True	TRUE ‘t’ ‘true’ ‘y’ ‘yes’ ‘1’	1 byte
False	FALSE ‘f’ ‘false’ ‘n’ ‘no’ ‘0’	1 byte
Unknown	NULL	1 byte

Looking for a good GUI tool to work with Redshift on AWS? Try TablePlus.

TablePlus is a modern, native client with intuitive GUI tools to create, access, query & edit multiple relational databases: MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Amazon Redshift, MariaDB, CockroachDB, Vertica, Cassandra, Oracle, and Redis.