How to bulk insert a struct with an Option<T> using UNNEST?

[cole-k]

Context

I am using Postgres 15 and sqxl 0.6.2 with features ["runtime-tokio-native-tls", "postgres", "uuid", "time"] although the latter two seem irrelevant to this issue.

Question

I am having difficulty bulk inserting a struct which has an Option<String> into my database using UNNEST. I followed the example in the FAQ to do bulk inserts. My code works when I don't insert the Option<String>.

I can get the query to go through when using query_unchecked!() but I'd prefer to not have to resort to that.

Error message

Look at the Repro section below for context. I changed the names to match my minimum repro.

error[E0308]: mismatched types
   --> src/app_user.rs:42:5
    |
42  |     &images[..]
    |     ^
    |     |
    |     expected struct `std::string::String`, found enum `std::option::Option`
    |     expected due to the type of this binding
    |
    = note: expected reference `&[std::string::String]`
               found reference `&[std::option::Option<std::string::String>]`

What I tried

Using query_unchecked! works.

I found the PgHasArrayType while poking around in the docs and thought maybe I needed to tell sqlx that you can have arrays of Option<String>, but there seems to be one for Option<T> already.

I've tried queries without the Option<String> and they work. I've also observed that single insertions without the UNNEST work, so it seems like I'm doing something wrong with the array. I've also tried to force postgres to infer/accept the type with some hacks I found, but that didn't work either.

Repro

This is my best attempt at a minimum repro but I didn't want to init an entire project to test it. It's very close to my code so it should be right. I can make an actual repo with the repro if needed.

./migrations/0_app_user_table.sql

create table app_user
(
    app_user_id    uuid primary key    default gen_random_uuid(),

    name           text                not null,

    image          text
);

./src/app_user.rs

use sqlx::{PgPool, Error};

pub struct AppUser {
  // app_user_id omitted for simplicity
  pub name: String,
  pub image: Option<String>,
}

pub async fn add_app_users(
  db: &PgPool,
  userNames: &Vec<String>,
  userImages: &Vec<Option<String>>
) -> Result<(), sqlx::Error> {
  sqlx::query!(
    r#"
      INSERT INTO app_user(name, image)
      SELECT * FROM UNNEST($1::text[], $2::text[]) AS t(name, image)
    "#,
    &names[..],
    &images[..] // <- error here
  ).execute(db)
  .await?;

  Ok(())
}

[gyzerok]

Looking for an answer to the same problem. Have you had any success?

[fistons]

Looks like there is work around here: #571 (comment)