How to insert VARIANT data into a Snowflake table?

Using this table:

CREATE TABLE t (i int PRIMARY KEY, j variant); 

I'm trying to insert some JSON object into the table, e.g.

INSERT INTO t (i, j) VALUES (1, object_construct('a', 1)); 

However, that fails with:

SQL Error [2014] [22000]: SQL compilation error:
Invalid expression [OBJECT_CONSTRUCT('a', 1)] in VALUES clause

I can work around the problem with this:

INSERT INTO t (i, j) VALUES (1, null); UPDATE t SET j = object_construct('a', 1) WHERE i = 1; 

Why can't I use OBJECT_CONSTRUCT in an INSERT statement?

2 Answers

INSERT INTO ... VALUES:

Some expressions cannot be specified in the VALUES clause. As an alternative, specify the expression in a query clause. For example, you can replace:

INSERT INTO table1 (ID, varchar1, variant1) VALUES (4, 'Fourier', PARSE_JSON('{ "key1": "value1", "key2": "value2" }')); 

with:

INSERT INTO table1 (ID, varchar1, variant1) SELECT 4, 'Fourier', PARSE_JSON('{ "key1": "value1", "key2": "value2" }'); 

VALUES:

<expr> 

Each expression must be a constant, or an expression that can be evaluated as a constant during compilation of the SQL statement.

Most simple arithmetic expressions and string functions can be evaluated at compile time, but most other expressions cannot.

Same applies to both OBJECT_CONSTRUCT and OBJECT curly braces :

INSERT INTO t (i, j) SELECT 1, {'a', 1}; 

The manual suggests using INSERT .. SELECT:

To insert VARIANT data directly, use IIS (INSERT INTO ... SELECT).

I.e. this works:

INSERT INTO t (i, j) SELECT 1, object_construct('a', 1); 

However, it does not offer any explanation with respect to the limitation, or why this particular workaround is needed.

2

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobm5tYGyFeoCOoaawZaSkeqq60p6prWWmlr%2Bqrc2tZJ2ZpJZ6qrrTqGSaZaOjvLiyy5qinmWklq%2BtsQ%3D%3D