Either for the existing row or for the newly created row from the insert clause. If col2 is duplicated, the second clause's conflict will hit and simply store the final value of :col2.Otherwise a new row is inserted with col1 set to its final value and col2 set to a temporary and unique value. If col1 is duplicated, the first clause's conflict will hit and not insert a new row.And only then it updates col2 to its final value. One solution to this is a CTE which firstly only inserts a new row with col1 set and assigns a random value for col2 (avoiding a constraint violation on col2). So that a combined unique constrained will NOT solve the insert/on conflict problem, as suggested in other answers. It seems to be the case that for the OP col1 and col2 are independently unique. columns ), because I know what I want to do, and then I know which single one of the many unique constraints, is the one that can get violated. So I can, and fairly likely you too, in your case?, generate the correct on conflict (. And if I'm configuring notification prefs, for a category - then instead I know that the constraint that can get violated, is site_id, people_id, category_id. If I'm inserting a notification preference, for a page - then there can be a unique conflict, on the site_id, people_id, page_id constraint. The on conflict clause is dynamically generated, depending on what I'm trying to do. ![]() RunUpdateSingleRow(insertStatement, values)Īnd: private def thingColumnName(notfPref: PageNotfPref): String =Įlse if () If (notfPref.wholeSite) true.asAnyRef else NullBoolean, ![]() On conflict (site_id, people_id, $thingColumnName) <- look There can be only one on-conflict clause. Val thingColumnName = thingColumnName(notfificationPreference) So only one constraint is "active", at a time. and only *one* of page-id, category-id, whole-site-true/false ![]() unique (site_id, people_id, pages_in_category_id) unique (site_id, people_id, pages_in_whole_site) (License: Not CC0, only CC-By) // there're these unique constraints: (If many, then I'm wondering if something is weird / oddly-designed, hmm.) You can typically (I would think) generate a statement with only one on conflict that specifies the one and only constraint that is of relevance, for the thing you are inserting.īecause typically, only one constraint is the "relevant" one, at a time. You would need to modify the logic of this stored function so that it updates the columns exactly the way you want it to. Do nothing, and loop to try the UPDATE again. INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col2) DO UPDATE SET col3 = data INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col1) DO UPDATE SET col3 = data if someone else inserts the same key concurrently, or key2 UPDATE dupes SET col3 = data WHERE col1 = key1 and col2 = key2 CREATE OR REPLACE FUNCTION merge_db(key1 INT, key2 INT, data TEXT) RETURNS VOID AS For example in the above Q1 query, should postgresql update col1 when there is a conflict on col2? But what if that leads to another conflict on col1? how is postgresql expected to handle that? A solutionĪ solution is to combine ON CONFLICT with old fashioned UPSERT. Postgresql behaves this way is because what should happen when a conflict occurs on the second column is not well defined. ![]() Let's call this query Q2 (this fails with a syntax error) Why? ON CONFLICT (col1,col2) DO UPDATE SET col3 = 'c', col2 = 2 However such an index would not guarantee that col1 and col2 would be unique individually which is one of the OP's requirements. This gives the impression that the following query should work, but it does not because it would actually require a together unique index on col1 and col2. Must, as a further requirement for inference, satisfy arbiter indexes. Table_name unique indexes that, without regard to order, containĮxactly the conflict_target-specified columns/expressions are inferred Index_expression expressions, and an optional index_predicate. Inference, it consists of one or more index_column_name columns and/or The result is ERROR: duplicate key value violates unique constraint "col2_unique"Ĭonflict_target can perform unique index inference. ON CONFLICT (col1) DO UPDATE SET col3 = 'c', col2 = 2 Reproducing the problem INSERT INTO dupes values(3,2,'c') A sample table and data CREATE TABLE dupes(col1 int primary key, col2 int, col3 text,
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |