Error inserting data with array values: "wrong element type"

[ksten-ab]

When using:

sqlx = { version = "0.3.5", features = ["chrono", "postgres", "tls"] }
chrono = "0.4.11"

This code:

let insert_result = sqlx::query(
    r#"
        INSERT INTO timeseries ("time", "value", "series_id")
        SELECT * FROM UNNEST($1::timestamptz[], $2::float8[], $3::int4[])
    "#
)
.bind(&times)      // Vec<DateTime<Utc>>
.bind(&values)     // Vec<f64>
.bind(&series_ids) // Vec<i32>
.execute(&self.db) // PgPool
.await;

Produces an error with this message:

wrong element type

Which occurs when Postgres is decoding an array in array_recv (at line 1317):

     element_type = pq_getmsgint(buf, sizeof(Oid));
     if (element_type != spec_element_type)
     {
         /* XXX Can we allow taking the input element type in any cases? */
         ereport(ERROR,
                 (errcode(ERRCODE_DATATYPE_MISMATCH),
                  errmsg("wrong element type")));
     }

[abonander]

@ksten-ab can you please confirm whether this is still an issue with 0.4.0-beta.1 and try to narrow it down to one of the types since the Postgres error isn't helpful?

[Pajn]

I have a related issue, that may be the same which is why I answer here, but I can create a new one if you prefer.

It seems like timestamptz[] isn't supported with the "chrono" feature, only with "time"

With the code:

  sqlx::query!(
    "
    INSERT INTO records (time, message, data) 
    VALUES (
      UNNEST($1::timestamp with time zone[]),
      UNNEST($2::text[]),
      UNNEST($3::jsonb[])
    )
    ",
    &records.iter().collect::<Vec<_>>(),
    &records.iter().map(|r| r.message).collect::<Vec<String>>(),
    &records
      .iter()
      .map(|r| r.data)
      .collect::<Vec<serde_json::Value>>(),
  )
  .execute(pool)
  .await?;

I get the error optional feature `time` required for type TIMESTAMPTZ[] of param #1 which I think I can narrow down to this code https://github.com/launchbadge/sqlx/blob/5f3245d7f4535a5afaec8de369a931c8293fdb55/sqlx-core/src/postgres/type_info.rs#L168-L180

With the feature time added as well, that error goes away however I haven't tried converting to using time instead so I don't know if it would work all the way.