將字元串值數組拆分為行數表參考錯誤

我正在嘗試創建一個查詢單個數據庫記錄的表，但將關聯數組中的值拆分為單個數據庫記錄的單個行。到目前為止，我已經創建了具有最多 255 個數字行的數字表，但是當我嘗試將該表連接到一個表時，在該表中我按數字表順序按字元串拆分數組，我收到一個錯誤function split_part(character varying, "unknown", double precision) does not exist。知道我的設置有什麼問題嗎？

注意：我使用的是 Amazon Redshift

數字表：

範例查詢結果

number
1
2
3
4
...
255

詢問

SELECT 
   p0.n 
   + p1.n*2 
   + p2.n * POWER(2,2) 
   + p3.n * POWER(2,3)
   + p4.n * POWER(2,4)
   + p5.n * POWER(2,5)
   + p6.n * POWER(2,6)
   + p7.n * POWER(2,7) 
   as number
 FROM 
   (SELECT 0 as n UNION SELECT 1) p0,
   (SELECT 0 as n UNION SELECT 1) p1,
   (SELECT 0 as n UNION SELECT 1) p2,
   (SELECT 0 as n UNION SELECT 1) p3,
   (SELECT 0 as n UNION SELECT 1) p4,
   (SELECT 0 as n UNION SELECT 1) p5,
   (SELECT 0 as n UNION SELECT 1) p6,
   (SELECT 0 as n UNION SELECT 1) p7

解析器表查詢：

理想的查詢結果

row_id, record_id, medium_name
1, 24, work
2, 24, test
3, 24, success

詢問

SELECT row_number() over (order by 1) as row_id
       , test_lead.id as lead_id
       , split_part(test_lead.test_mediums, ', ', numbers.number) as medium_name
     FROM ${lead.SQL_TABLE_NAME} test_lead
     JOIN ${marketing_model_numbers.SQL_TABLE_NAME} numbers
     ON numbers.number <= regexp_count(test_lead.test_mediums, ',\\s') + 1

數組值結構：

["work", "test", "success"]

你numbers.number的是一個double precision. 但是split_part()期望第三個參數是 aninteger並且值不是隱式轉換的。嘗試顯式轉換numbers.number.

...
, split_part(test_lead.test_mediums, ', ', numbers.number::integer) as medium_name
....

引用自：https://dba.stackexchange.com/questions/215428