Get or create does not compile

1

Hi everybody

I am currently processing a lot of files. So I want to store the progress in PostgreSQL.
It works so far except one thing:

fn get_file_or_create(connection: &PgConnection, path: &String) -> models::File {
    use schema::files::dsl::*;

    let candidates = files
        .filter(path.eq(path))
        .load::<models::File>(connection)
        .expect("unable to load files");
    for file in candidates {
        return file;
    }

    // create
    diesel::insert_into(files)
        .values((path.eq(path), status.eq(models::SOURCE_STATUS_PROCESSING)))
        .get_result(connection)
        .expect("unable to create new file")
}

This gives me this error:

error[E0271]: type mismatch resolving `<() as diesel::query_source::AppearsInFromClause<schema::files::table>>::Count == diesel::query_source::Once`
  --> src/main.rs:95:10
   |
95 |         .get_result(connection)
   |          ^^^^^^^^^^ expected struct `diesel::query_source::Never`, found struct `diesel::query_source::Once`
   |
   = note: expected type `diesel::query_source::Never`
              found type `diesel::query_source::Once`

Did I miss something? Or is there a function that already provides that?
I am using rust 1.33.0 with edition 2018 on Arch Linux.
In other parts of the code I can insert and select other data.

Some more relevant source code:

/// models.rs
#[derive(Queryable, Identifiable, PartialEq)]
pub struct File {
    pub id: i32,
    pub path: String,
    pub status: String,
}

pub const SOURCE_STATUS_PROCESSING: &'static str = "Processing";
pub const SOURCE_STATUS_Complete: &'static str = "Complete";

/// schema.rs
table! {
    files (id) {
        id -> Int4,
        path -> Text,
        status -> Text,
    }
}

Cheers,
Stefan

2

Nevermind, I found the error. It was overlaying symbols.
Here is the working code:

fn get_file_or_create(connection: &PgConnection, new_path: &String) -> models::File {
    use schema::files::dsl::*;

    let candidates = files
        .filter(path.eq(new_path))
        .load::<models::File>(connection)
        .expect("unable to load files");
    for file in candidates {
        return file;
    }
    // create
    /*let new_file = models::NewFile {
        path: new_path,
        status: models::SOURCE_STATUS_PROCESSING,
    };*/
    diesel::insert_into(files)
        //.values(&new_file)
        .values((path.eq(new_path), status.eq(models::SOURCE_STATUS_PROCESSING)))
        .get_result(connection)
        .expect("unable to create new file")
}

Nevertheless, would you consider adding a get_or_create function?

Cheers,
Stefan

3

See http://docs.diesel.rs/diesel/pg/upsert/index.html and https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

4

Hi sean

Thanks for the response. I tried to get it working with code and SQL, but I can only get a syntax or type error:

WITH ins AS (
    INSERT INTO files (path, status) VALUES ('abc', 'bbb') 
    ON CONFLICT (SELECT path FROM files WHERE path = 'abc')
    DO UPDATE SET path = NULL 
    WHERE FALSE RETURNING id
) SELECT id from ins UNION ALL SELECT id from files WHERE path = 'abc';

Did you mean something like that?

Best,
Stefan