How can I set a field to NULL using the Redshift Data API?

[JoseAngel1196]

According to the official doc, if you're using sql.Parameter:

• You can't set a value to NULL. The Data API interprets it as the literal string NULL. The following example replaces id with the literal string null. Not the SQL NULL value. (link)
• You can't set a zero length value. The Data API SQL statement fails. The following example trys to set id with a zero length value and results in a failure of the SQL statement. (link)

It looks like we can't use either an empty string or a nil value, so what can I do besides using a default value or omitting the field if it is empty? In Go, I saw that we have the this field interface FieldMemberIsNull, but I'm not sure how to use it. Have anyone done this before?

[JoseAngel1196]

Hey @lucix-aws , thoughts?

[lucix-aws]

Is that 2nd (link) supposed to be a code snippet? Both of those links go to redshift documentation.

[JoseAngel1196]

I'm trying to figure out is how to send a field NULL using the Redshift Data API which looks like we don't have a way to do it but wanted to check in with the team first

[JoseAngel1196]

Here's the code:

if value == "" {
				paramValue = "null" // Can't use "" or nil

			} else {
				paramValue = fmt.Sprintf("%v", value)
			}
			parameters = append(parameters, types.SqlParameter{
				Name:  aws.String(paramName),
				Value: aws.String(paramValue),
			})

[JoseAngel1196]

When I send a empty string, the API returns with a ValidationException

[lucix-aws]

I haven't used redshift, but based on their documentation there it does seem to be flatly impossible to parameterize a NULL as you've surmised. I think you'd have to put it into the ExecuteStatementInput.Sql value directly.

Note that Field and its variant FieldMemberIsNull is an unrelated union that only appears in operation responses, specifically only in GetStatementResultOutput.Records. I assume that's how they model actual null values in records etc.

[JoseAngel1196]

Yeah, that’s a total bummer. It’s so weird that they’re not using the FieldMember type in the SqlParameter.