1

I am having difficultly finding documentation on how to insert data into an ARRAY column type using SQL on a Snowflake table.

Snowflake Documentation: https://docs.snowflake.net/manuals/sql-reference/data-types-semistructured.html#array

// example table
CREATE OR REPLACE TABLE array_test_table (
  id number,
  ids array
);

// This does not work
INSERT INTO array_test_table (id, ids) 
VALUES (1, '[1,2,3]' );

I have tried using using postgres's syntax like ones described here: https://stackoverflow.com/questions/33335338/inserting-array-values

I originally asked this question here: Snowflake Community

1

It looks like you need to apply a function to parse the array. As of 9/13/2018 functions are not allowed in value lists see Is there a literal syntax for arrays

One way around this is to combine INSERT INTO and SELECT statement. Example:

CREATE OR REPLACE TABLE array_test_table (
  id number,
  ids array
);

INSERT INTO array_test_table (id, ids) 
SELECT $1, PARSE_JSON($2)
  FROM VALUES (1, '[1,2,3]' );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.