Open sahilsalim99 opened 1 week ago
hi there - as an alternative, would it be possible to try something like this https://github.com/snowflakedb/gosnowflake/issues/831#issuecomment-1621083222 ?
For our use case we do not have info on the data type of the columns we're trying to insert into. Hence it would be great if passing nil to a variant column would work as it does for other data types.
tried one of the solutions for myself, which i suggested above
create table test_db.public.go1170 (c1 variant);
...
insertQuery := "INSERT INTO test_db.public.go1170 (c1) SELECT (?)"
fmt.Printf("Inserting VARIANT into table: %v\n", insertQuery)
_, err = conn.ExecContext(ctx, insertQuery,
// nil, --> this is indeed mapped to TEXT
sql.NullInt64{}, // --> this is not
)
if err != nil {
log.Fatalf("failed to run the query. %v, err: %v", insertQuery, err)
}
TEXT
(=VARCHAR
) datatype anymore
INFO[0000]connection.go:301 gosnowflake.(*snowflakeConn).ExecContext Exec: "INSERT INTO test_db.public.go1170 (c1) SELECT (?)", [{ 1 {0 false}}]
DEBU[0000]log.go:176 gosnowflake.(*defaultLogger).Debugf empty qcc
INFO[0000]connection.go:118 gosnowflake.(*snowflakeConn).exec parameters: map[]
DEBU[0000]log.go:176 gosnowflake.(*defaultLogger).Debugf TYPE: sql.NullInt64, {0 false}
INFO[0000]connection.go:127 gosnowflake.(*snowflakeConn).exec bindings: map[1:{FIXED <nil> <nil>}]
select * from test_db.public.go1170 ;
I was able to insert null value into
VARIANT
column without needing to pass it toPARSE_JSON()
first. Would this help you ?
Currently we are inserting null in all other data types using nil.. would it be possible to replace nil with sql.NullInt64{} without running into any issues for all the other data types?
Also, are there plans to add support for nil binding for variant columns in the future?
Hi @sahilsalim99 ! Variants are quite specific, because they can contain anything. It can be a string, an int or an object. If you just want to insert nil
, we don't know what type is required.
We are planning to add support to binding nil variants, but this is still going to be something like:
sql.Exec("INSERT INTO test_variant VALUES (?)", DataTypeNilVariant, reflect.TypeOf(1))
sql.Exec("INSERT INTO test_variant VALUES (?)", DataTypeNilVariant, reflect.TypeOf(""))
What is the current behavior?
My code
_, err = db.ExecContext(ctx, "INSERT INTO test_variant SELECT ?", nil)
The above results in
What is the desired behavior?
Being able to pass nil to variant columns while binding
How would this improve
gosnowflake
?It would make it easier to work with variant columns.
The current workaround we're making use of involves wrapping it with a TRY_PARSE_JSON/PARSEJSON : `, err = db.ExecContext(ctx, "INSERT INTO test_variant SELECT TRY_PARSE_JSON(?)", nil)`