How to execute SQL with array parameter with null values?

[igkg]

I use this approach to update multiple records at once:

query := UPDATE table target
		SET 
			field_int = nullif(src.field_int, -1),
			field_str = nullif(src.field_str,''),
			field_timestamp = nullif(src.field_timestamp ,'0001-01-01T00:00:00Z')
		FROM ( SELECT * FROM UNNEST($1::my_type[]) AS src) AS src
		WHERE target.id = src.id
	if _, err := dbConnection.Exec(context.Background(), query, pq.Array(there.myRecords)); err != nil {

and I have serializer for the type

func (v myRecordStruct) Value() (driver.Value, error) {
	s := fmt.Sprintf((%d,%d,%q,%q),
		v.id,
                v.field_int,
                v.field_str,
		v.field_timestamp.Format(time.RFC3339)

I have 2 questions:

1. Can I avoid creating the type (my_type) on the postgresql side?
2. Can I somehow pass null value from golang side to avoid nullif approach?

[jackc]

I would use a Batch containing one UPDATE per row. The performance should be comparable since all the queries would still be sent at once. But your queries would be much simpler and you would get a result per row so you would know if the UPDATE didn't actually affect a row. Also, your nil/NULL handling would be simplified.

However, that would require using the direct pgx interface rather than through database/sql.

[igkg]

@jackc If I want to update 100k records Batch will copy my update 100k times, am I right?
I want to send query once with 100k rows of parameters but in one db roundtrip - is it possible?

[jackc]

A Batch of 100k updates should execute in a single round trip (plus a prepare/describe, if the update query has not been cached, same as a normal query).

I recommend this approach if you can use the native interface. It should be similar performance to the single query.

However, there is a way to use your unnest strategy without defining a type. You can pass multiple arrays to unnest instead of a single array of records.