1

I am learning using rust to write a web service, now facing a problem that when I am insert data into PostgreSQL 13 using diesel:

diesel::insert_into(songs::table)
        .values(&new_song)
        .on_conflict_do_nothing()
        .execute(&connection)
        .unwrap();

this way will wast some increment id when insert data conflict. For example the current row id is 3, if with some conflict insert, the next id will become 8. is it possible to insert without id wasted? select conflict data in each insert command? This is the id define of PostgreSQL 13:

ALTER TABLE public.songs 
    ALTER id ADD GENERATED ALWAYS AS IDENTITY;
kmdreko
  • 42,554
  • 6
  • 57
  • 106
Dolphin
  • 29,069
  • 61
  • 260
  • 539
  • I am facing the same problem.@liangqicai – Dolphin Sep 03 '21 at 05:35
  • 2
    This is nothing to do with diesel, it is a feature of Postgresql - see notes under https://www.postgresql.org/docs/13/sql-createsequence.html. Is it a problem? Most applications just ignore gaps in id numbers. – harmic Sep 03 '21 at 05:42
  • I know this would be happen and always not an error, but I still want to known a better way to handle this. @harmic – Dolphin Sep 03 '21 at 06:18
  • 1
    Hello, you can try to use a transaction. If no error you commit at the end. If not, you rollback (https://docs.diesel.rs/diesel/connection/trait.TransactionManager.html) – Zeppi Sep 03 '21 at 06:24
  • I read the docs, but It is sad that I did not know how to implement the solution. I am a newbie to rust. I will tried to using your suggestion in the furture. @Zeppi – Dolphin Sep 03 '21 at 08:36

0 Answers0