Stack Overflow
Stack Overflow

Questions tagged [upsert]

UPSERT is a combination of UPDATE and INSERT, typically used in relational databases.

UPSERT refers to types of statements (usually database statements) that INSERT an item (record) to a resource (table in a database) where, if the item already exists, it will update the existing item with the fields provided. The term UPSERT is an amalgamation of UPDATE and INSERT and is common slang among database developers.

1227 questions
17
votes
1 answer

Is MERGE an atomic statement in SQL2008?

I am using a MERGE statement as an UPSERT to either add a new record or update the current one. I have multiple threads driving the database through multiple connections and multiple statements (one connection and statement per thread). I am…
OldCurmudgeon
  • 64,482
  • 16
  • 119
  • 213
17
votes
6 answers

Add or replace entity in Azure Table Storage

I'm working with Windows Azure Table Storage and have a simple requirement: add a new row, overwriting any existing row with that PartitionKey/RowKey. However, saving the changes always throws an exception, even if I pass in the ReplaceOnUpdate…
EMP
  • 59,148
  • 53
  • 164
  • 220
17
votes
1 answer

PostgreSQL multiple on conflicts in one upsert statement

I have two unique constraints on the same table, and I want to do an upsert statement on that table. Is it possible to specify the two conflicts in the upsert? I saw this: How to upsert in Postgres on conflict on one of 2 columns? but my issue is…
chris
  • 1,869
  • 4
  • 29
  • 52
17
votes
6 answers

Postgres UPSERT (INSERT or UPDATE) only if value is different

I'm updating a Postgres 8.4 database (from C# code) and the basic task is simple enough: either UPDATE an existing row or INSERT a new one if one doesn't exist yet. Normally I would do this: UPDATE my_table SET value1 = :newvalue1, ..., updated_time…
EMP
  • 59,148
  • 53
  • 164
  • 220
16
votes
7 answers

How to update and upsert multiple documents in MongoDB using C# Drivers

I am using MongoDB 2, and I want to update multiple documents and upsert a value like processed:true into the collection. But MongoDB c# api only allows us to either Update Multiple Records or Upsert a single record. How to solve this problem using…
Nitin Agarwal
  • 563
  • 1
  • 6
  • 19
16
votes
1 answer

mongodb - create doc if not exist, else push to array

I have a document in the following form: { "_id" : ObjectId("4d2d8deff4e6c1d71fc29a07"), "user_id" : "714638ba-2e08-2168-2b99-00002f3d43c0", "events" : [ { "profile" : 10, "data" : "....." } { …
LiorH
  • 18,524
  • 17
  • 70
  • 98
16
votes
1 answer

How to include excluded rows in RETURNING from INSERT ... ON CONFLICT

I've got this table (generated by Django): CREATE TABLE feeds_person ( id serial PRIMARY KEY, created timestamp with time zone NOT NULL, modified timestamp with time zone NOT NULL, name character varying(4000) NOT NULL, url character…
Dustin Wyatt
  • 4,046
  • 5
  • 31
  • 60
16
votes
2 answers

Bulk Partial Upsert in Elasticseach with python

I want to send n upsert partial requests to ES, is such a thing possible? So if the document doesn't exist, insert my partial doc. If it already exists, update it with the partial doc. Using the bulk helpers, I've tried a ton of variations, but they…
L-R
  • 1,214
  • 1
  • 19
  • 40
15
votes
5 answers

How to upsert pandas DataFrame to PostgreSQL table?

I've scraped some data from web sources and stored it all in a pandas DataFrame. Now, in order harness the powerful db tools afforded by SQLAlchemy, I want to convert said DataFrame into a Table() object and eventually upsert all data into a…
nate
  • 440
  • 2
  • 8
  • 18
15
votes
1 answer

PostgreSQL ON CONFLICT with a WHERE clause

Postgres documentation makes it seem like a WHERE clause is possible as an ON CONFLICT condition: https://www.postgresql.org/docs/9.5/static/sql-insert.html I have not been able to get this working (if it's possible). Here is one of the many…
smeckydev
  • 433
  • 2
  • 4
  • 13
15
votes
1 answer

Bulk upsert with SQLAlchemy

I am working on bulk upserting lots of data into PostgreSQL with SQLAlchemy 1.1.0b, and I'm running into duplicate key errors. from sqlalchemy import * from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.automap import automap_base import…
Sienna
  • 1,570
  • 3
  • 24
  • 48
15
votes
1 answer

Can INSERT [...] ON CONFLICT be used for foreign key violations?

Given => select * from referenced; referenced_id | name ---------------+------- 1 | one 2 | two 3 | three and => select * from entries; entry_id | referenced_id | name …
peterwimsey
  • 532
  • 2
  • 6
  • 17
15
votes
2 answers

Mongodb upsert only update selected fields, but insert all

I am trying to use upsert in MongoDB to update a single field in a document if found OR insert a whole new document with lots of fields. The problem is that it appears to me that MongoDB either replaces every field or inserts a subset of fields in…
agnsaft
  • 1,791
  • 7
  • 30
  • 49
14
votes
4 answers

How to update or insert on Sequel dataset?

I just started using Sequel in a really small Sinatra app. Since I've got only one DB table, I don't need to use models. I want to update a record if it exists or insert a new record if it does not. I came up with the following solution: rec =…
nooga
  • 550
  • 1
  • 6
  • 19
14
votes
2 answers

MongoDb upsert exception invalid BSON field

This exception: Exception in thread "Thread-1" java.lang.IllegalArgumentException: Invalid BSON field name id at org.bson.AbstractBsonWriter.writeName(AbstractBsonWriter.java:516) at…
Aubin
  • 14,617
  • 9
  • 61
  • 84
1 2 3
…
81 82