In that case all the scripts must be modified. It would be nice if there was a more generic way to do this OlivierPons yes, it's possible. I have found this to be unreliable. It appears Postgres sometimes executes the insert before it has executed the select and I end up with a duplicate key violation even though the record hasn't been inserted yet.
Show 7 more comments. Do main table insert. Mike T Kuberchaun Kuberchaun This is the fastest way I have found to do mass inserts when I do not know if the row already exists. Lookup up correlated subquery. SQL requires there to be something and 'X' is a common thing to use.
It's small and it makes it obvious a correlated subquery is being used and meets the requirements of of what SQL requires. You mentioned "insert all your data into assuming temp table and do a select distinct from that".
Would this solution be reliable for concurrent write operations? Quassnoi Quassnoi k 88 88 gold badges silver badges bronze badges. There is one record. Feel free to edit the answer. This is exactly the problem I face and my version is 9. Thanks for reading. This answer is the same as John Doe, where the caveat of concurrent write operations has been pointed out. But you can use both way to find the better answer for you. I think this is a nice quick way to solve the issue, but only when you're sure the source table will never grow huge.
I've got a table which will never have more than rows, so I can use this solution. WOW, this is exactly what I needed. I was worried I'd need to create a function or a temp table, but this precludes all that--thank you! HenleyChiu has quite a good point. Since those selects are agains existing table maybe we can add a where clause to each ensuring we only select 'Bob''s row?
If anyone else was wondering too, from the docs : "Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row i.
If a nonnull value is returned then the operation proceeds with that row value. Exactly this answer I was looking for. I love this answer, use function and trigger. Now I find another way to break the deadlock using functions and triggers Zoey Hewll 3, 2 2 gold badges 17 17 silver badges 30 30 bronze badges. Ritesh Jha Ritesh Jha 3 3 silver badges 6 6 bronze badges. This is essentially the same as the answer here stackoverflow. Brian Burns Stack Overflow for Teams — Collaborate and share knowledge with a private group.
Create a free Team What is Teams? Learn more. Asked 4 years, 7 months ago. Active 4 years, 7 months ago. Viewed 21k times. Improve this question. Jack Douglas Add a comment. One approach would be to create a non-constrained no unique indexes table to insert all your data into and do a select distinct from that to do your insert into your hundred table.
So high level would be. Create temporary table. See docs here. If you just want to insert or not insert and not update otherwise , you can do it like this using the invoice example :. I know this question is from a while ago, but thought this might help someone. I think the easiest way to do this is via a trigger.
Execute this code from a psql prompt or however you like to execute queries directly on the database. Then you can insert as normal from Python. The approach with the most upvotes from John Doe does somehow work for me but in my case from expected rows i get only
0コメント