了解 Redshift 的数据类型与特性

Redshift 入门

Jason Myers

Principal Architect

基础数据类型

数值类型

SMALLINT, INTEGER, BIGINT
DECIMAL / NUMERIC
DOUBLE PRECISION
REAL

日期时间类型

DATE
TIME, TIMETZ
TIMESTAMP, TIMESTAMPTZ

字符类型

CHAR
VARCHAR

布尔类型

BOOLEAN

特殊数据类型

SUPER 类型

半结构化数据
- 数组
- 元组
- 嵌套结构（如 JSON）
使用 PartiQL 处理
最大 16MB

VARBYTE 类型

二进制数据（BLOB）
图像与视频

不受支持的 PostgreSQL 类型：

PostgreSQL 类型	Redshift 类型
DATETIME	TIMESTAMP, TIMESTAMPTZ
SERIAL	INTEGER, BIGINT
UUID	VARCHAR
JSON	SUPER, VARCHAR
ARRAY	SUPER, VARCHAR
BIT	BOOLEAN, SMALLINT, VARCHAR

查看列及其数据类型

-- View the column details
SELECT column_name, 
       data_type, 
       character_maximum_length AS character_max_len,
       numeric_precision, 
       numeric_scale
  -- Using a view with both internal and external schema's columns
  FROM SVV_ALL_COLUMNS 
 -- Only in the spectrumdb schema
 WHERE schema_name = 'spectrumdb'
   -- For the ecommerce_sales table
   AND table_name = 'ecommerce_sales';

查看列及其数据类型（续）

column_name                  | data_type         | character_max_len | numeric_precision | numeric_scale
=============================|===================|===================|===================|==============
year_qtr                     | character varying | 100               | null              | null
total_sales                  | integer           | null              | 32                | 0
ecom_sales                   | integer           | null              | 32                | 0
percent_ecom                 | real              | null              | null              | null
percent_change_quarter_total | real              | null              | null              | null
percent_change_quarter_ecom  | real              | null              | null              | null
percent_change_year_total    | real              | null              | null              | null
percent_change_year_ecom     | real              | null              | null              | null

数据类型"兼容性"

隐式转换：赋值、比较
务必复核结果！

源类型	目标类型
CHAR	VARCHAR
DATE	CHAR, VARCHAR, TIMESTAMP, TIMESTAMPTZ
TIMESTAMP	CHAR, DATE, VARCHAR, TIMESTAMPTZ
BIGINT	BOOLEAN, CHAR, DECIMAL, DOUBLE PRECISION, INTEGER, REAL, SMALLINT, VARCHAR
DECIMAL	BIGINT, CHAR, DOUBLE PRECISION, INTEGER, REAL, SMALLINT, VARCHAR

¹ https://docs.aws.amazon.com/redshift/latest/dg/c_Supported_data_types.html#r_Type_conversion

显式转换数据类型

CAST

-- Casting a decimal to a integer and aliasing it
SELECT CAST(2.00 AS INTEGER) AS our_int;

our_int
========
2
(1 row)

TO 函数

TO_CHAR, TO_DATE, TO_NUMBER

-- Convert the string to a date
SELECT CAST('14-01-2024 02:36:48' AS DATE) AS out_date;

date/time field value out of range: "14-01-2024 02:36:48"
HINT:  Perhaps you need a different "datestyle" setting.

-- Parse the string to a date
SELECT TO_DATE('14-01-2024 02:36:48', 'DD-MM-YYYY') AS our_date;

our_date
========
2024-01-14
(1 row)

TO 函数（续）

-- Get the name of the month in a date
SELECT TO_CHAR(date '2024-01-14', 'MONTH') AS month_name;

month_name
========
JANUARY
(1 row)

日期时间与数值的格式字符串

¹ https://docs.aws.amazon.com/redshift/latest/dg/r_FORMAT_strings.html

Passons à la pratique !

Redshift 入门