認識 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

一起來練習吧！

Redshift 入門