Error when retrieving value from INSERT...RETURNING query

[ohmree]

I'm using postgres 13.1, rust nightly and these relevant crates:

sqlx = { version = "0.4.1", features = ["postgres", "macros", "time", "runtime-async-std-rustls"] }
tide = "0.15.0"
tide-sqlx = { version = "0.3.1", features = ["postgres"] }

Here's the SQL that was used to create my table:

CREATE TABLE song_urls (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    song_url text UNIQUE CONSTRAINT song_url NOT NULL
);

And here's the erroneous code, along with the error from the logging middleware:

let song_serial = sqlx::query!(
    r#"
    INSERT INTO song_urls (song_url)
    VALUES ($1)
    ON CONFLICT (song_url) DO NOTHING
    RETURNING id
    "#,
    song_url
)
// mut connection: RwLockWriteGuard<'_, ConnectionWrapInner<Postgres>>
.fetch_one(connection.acquire().await?)
.await?
.id;
// ...and a log snippet:
// tide::log::middleware <-- Request received
//     method PUT
//     path /ohmree
// sqlx::query /* SQLx ping */; rows: 0, elapsed: 301.709µs
// sqlx::query BEGIN; rows: 0, elapsed: 107.171µs
// sqlx::query INSERT INTO song_urls (song_url) …; rows: 0, elapsed: 1.380ms

// INSERT INTO
//   song_urls (song_url)
// VALUES
//   ($1) ON CONFLICT (song_url) DO NOTHING RETURNING id

// tide::log::middleware Internal error --> Response sent
//     message no rows returned by a query that expected to return at least one row
//     error_type anyhow::Error
//     method PUT
//     path /ohmree
//     status 500 - Internal Server Error
//     duration 7.884996ms

The route in question responds to PUT methods, which from a (very) quick look at the tide-sqlx code I see isn't considered safe (and therefore uses a transaction, am I correct?).

I should note that before using this crate I used plain sqlx without transactions, just a regular PgPool.

Now I'm very much a noob when it comes to SQL (I didn't even know about transactions before coming across this project) but I think that this might be a syntax thing, maybe for for some reason values can't be returned like this from transactions (which explains why the code worked before when I used plain sqlx)?

I've also tried using fetch_optional, which as expected returns a future that resolves to Ok(None).

[Fishrock123]

I think that ON CONFLICT (song_url) DO NOTHING means the return type is nullable, meaning fetch_optional would be required.

I don't think that this is a tide-sqlx issue, perhaps try asking in the SQLx discord.

[ohmree]

You're right, my bad.

The correct way to do what I was trying to do is as follows:

INSERT INTO song_urls (song_url)
VALUES ($1)
ON CONFLICT (song_url) DO UPDATE
SET song_url=EXCLUDED.song_url
RETURNING id

And I must say I didn't suspect the issue was in the DO NOTHING clause, thanks for pointing me the correct way and saving me a whole bunch of time :)