postgres: \COPY meta command isn't supported?

[akiradeveloper]

When I issue a meta command \COPY from a client to a server it results in an error.

&q = "\\COPY xxxx from '/tmp/yyyy' with csv header"
thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: Database(PgDatabaseError { severity: Error, code: "42601", message: "syntax error at or near \"\\\"", detail: None, hint: None, position: Some(Original(1)), where: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("scan.l"), line: Some(1176), routine: Some("scanner_yyerror") })',

And it seems the server tried to execute meta command in the server.

pg10_1      | 2021-11-11 06:40:45.615 UTC [48] ERROR:  syntax error at or near "\" at character 1
pg10_1      | 2021-11-11 06:40:45.615 UTC [48] STATEMENT:  \copy xxxx from '/tmp/yyyy' with csv header

I tried to use \COPY command because it is considered to be the most efficient way to import db data. Isn't this possible with sqlx?

[abonander]

\COPY as a meta command is specifically a feature of the psql client.

Good news, though, this functionality is available. You can call the following method on PgPool: https://docs.rs/sqlx/0.5.9/sqlx/struct.Pool.html#method.copy_in_raw

By mistake, the PgCopyIn type isn't exported, but you can see the public methods on it in the source here: https://github.com/launchbadge/sqlx/blob/master/sqlx-core/src/postgres/copy.rs#L142

Usage would look something like this (with Tokio):

let file = tokio::fs::File::open("/tmp/yyyy").await?;

// pg_pool: PgPool
let mut copy_in = pg_pool.copy_in_raw("COPY xxxx FROM STDIN (FORMAT CSV)").await?;

copy_in.read_from(file).await?;

let rows_inserted = copy_in.finish().await?;

Documentation on the COPY command is here: https://www.postgresql.org/docs/current/sql-copy.html

[akiradeveloper]

It works! Thank you.

[ghost]

Hi,
I try to use the copy_in_raw but get method not found in Pool<Postgres> error.

I need to perform billion of insert into postgres, and would like to use copy from stdin with the Pool.

Is there another/recommended way to perform massive insert with sqlx?

An example would be great, i am completly new to rust ;)
Thanks

[abonander]

The method was removed from Pool due to coherence issues with the new crate architecture but you can still acquire a connection and then call the method on it directly: https://docs.rs/sqlx/latest/sqlx/struct.PgConnection.html#method.copy_in_raw

[ghost]

Thanks you very much for your support, i post my example if it can helps other.

But i still have a question, do I need to release back the connection to the pool?

async fn pg_copy_from_stdin(pool: &Pool<Postgres>, text :&String) -> Result<(), MyError> {

    let mut pg_connection = pool.acquire().await.unwrap();
    let mut pg_copy_in = pg_connection
        .copy_in_raw("copy public.test (a, b, c) from stdin (format text)").await.unwrap();

    //let file = tokio::fs::File::open("/tmp/yyyy").await?;

    // let lines = vec!["my first value".to_string(), "\n".to_string(), "my second value".to_string()];
    // let lines_byte:  Vec<u8> = lines.into_iter().flat_map(|s| s.into_bytes()).collect();
    // let mut buff = Cursor::new(lines_byte);
    //
    // pg_copy_in.read_from(buff).await.unwrap();
    let mut lines: Vec<String> = vec![];
    lines.push(["a", "b", 0.to_string().as_str()].join("\t"));

    lines.push(["a2", "a3", 2454.to_string().as_str()].join("\t"));

    let data_to_copy = lines.join("\n");
    let mut reader = Cursor::new(data_to_copy);
    pg_copy_in.read_from(&mut reader).await.unwrap();

    let rows_inserted = pg_copy_in.finish().await.unwrap();

    println!("Nb row inserted: {rows_inserted:}");

    Ok(())
}