How can you insert data into a ARRAY column on a Snowflake table using SQL?

Question

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

Arthur Putnam · Accepted Answer · 2018-09-13 15:19:06Z

4

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]' );

answered Sep 13, 2018 at 15:19

Arthur Putnam

5632 gold badges6 silver badges12 bronze badges

Add a comment |

mustaccio · Accepted Answer · 2023-11-30 22:38:51Z

2

Another option is to use ARRAY_CONSTRUCT(1,2,3) instead of parsing JSON.

INSERT INTO array_test_table (id, ids) 
SELECT 1, ARRAY_CONSTRUCT(1,2,3) ;

https://docs.snowflake.com/en/sql-reference/functions/array_construct.html

edited Nov 30, 2023 at 22:38

mustaccio

28.9k24 gold badges60 silver badges77 bronze badges

answered Jan 24, 2022 at 23:11

Dale C

1212 bronze badges

Add a comment |

Stack Exchange Network

How can you insert data into a ARRAY column on a Snowflake table using SQL?

2 Answers 2

Hot Network Questions

How can you insert data into a ARRAY column on a Snowflake table using SQL?

2 Answers 2

Related

Hot Network Questions