Prisma•2mo ago

create many skip existing

I want to createmany rows in a table, but if that row already exists (excluing the ID row), then skip that row. afaik, the skipDuplicates key will skip duplicates within the set of new rows you are adding, but it does not check the existing rows eg:

| id | col1 | col2 |
| 0  | aaa  | bbb  |
| 1  | ccc  | ddd  |
| 2  | eee  | fff  |

| id | col1 | col2 |
| 0  | aaa  | bbb  |
| 1  | ccc  | ddd  |
| 2  | eee  | fff  |

if I do createMany with skipDuplicates and add rows:

| col1 | col2 |
| aaa  | bbb  |
| ccc  | ddd  |
| eee  | fff  |

| col1 | col2 |
| aaa  | bbb  |
| ccc  | ddd  |
| eee  | fff  |

this will result in duplicate rows but with different ID keys.
how can I avoid this issue? I want to be able to automatically skip any rows that already exist. currently the ID key is set to autoincrement(). What should I do?

7 Replies

Bean•2mo ago

@🔨 Moderator I apologize for pinging. but I need help and I do not know how to solve this issue

Harry•2mo ago

@Bean we aren’t here for help sorry, please wait for someone from the prisma team or community. Please only ping the mods if the is an issue like spam, etc

Nitin•2mo ago

@Bean have you tried asking the question in the #ask-ai channel?

moosthuizen•2mo ago

@Bean On PostgreSQL at least, skipDuplicates: true causes ON CONFLICT DO NOTHING to be included in the query (https://www.postgresql.org/docs/9.5/sql-insert.html#SQL-ON-CONFLICT). You can cause a conflict by defining a unique constraint on col1 and col2. This will cause new "duplicate" data to violate that constraint, which will then be skipped because of the DO NOTHING. Hopefully this gives you the behaviour that you are looking for?

PostgreSQL Documentation

INSERT

Bean•2mo ago

not quite. the columns themselves dont have any unique data. but its the combination of the columns that is unique currently im thinking to use a hash id and hash the contents of the columns together and use that as the unique constraint, but I want to avoid using a hash since I dont want to the database to get big. I want to use numbers so it stores less data and is faster but idk how I could even do this in the first place in prisma

moosthuizen•2mo ago

You can do a unique constraint with multiple columns, which will make sure that the combination of data across all those columns specified in the contraint is unique, not the columns taken individually. @@unique([col1, col2]) You are literally describing a multi-colum unique contraint 🙂

Bean•2mo ago

Ooo I didn't know that was an option Thanks for letting me know

Gaming

Programming

create many skip existing