Skip to main content

Questions tagged [upsert]

UPSERT: SQL-UPDATE a row, or if not found INSERT a new row.

Filter by
Sorted by
Tagged with
0 votes
1 answer
59 views

How to upsert in Sql using Merge statement with where clause

CREATE TABLE SourceProducts( ProductID INT, ProductName VARCHAR(50), Price DECIMAL(9,2) ) GO INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(1,'...
amit agarwal's user avatar
0 votes
1 answer
283 views

How to have Postgres return id when ON CONFLICT DO NOTHING

Suppose we have the following table: CREATE TABLE names( id SERIAL NOT NULL, CONSTRAINT names__pk PRIMARY KEY(id), name TEXT NOT NULL, CONSTRAINT names__name__unq UNIQUE(name) ); INSERT INTO names(...
user14381362's user avatar
1 vote
1 answer
351 views

postrges on conflict update with all columns as unique constraint

I have a table with columns 'id', 'a', 'b, 'c', 'd'. Also, I have a unique constraint on ('a', 'b', 'c', 'd') so no entries can be added where all those fields are the same. I want to upsert entries e....
kichma's user avatar
  • 13
1 vote
1 answer
123 views

What are the idiomatic approaches to cross-server upserts?

This is a very common problem, faced by virtually every DBA who has to responsibilities to both application and BI teams. Consider the following: You have two T-SQL servers, Production and Reporting. ...
J. Mini's user avatar
  • 537
0 votes
1 answer
106 views

In PostgreSQL, can an index predicate be used to improve performance of `ON CONFLICT`?

Consider the following upsert, run in a trigger: INSERT INTO current_scores (user_id, score, updated_at) VALUES (NEW.user_id, NEW.score, NEW.inserted_at) ON CONFLICT (user_id) DO UPDATE SET ...
Nathan Long's user avatar
0 votes
1 answer
307 views

PostgreSQL: does the order of rows in a bulk insert control the order of `FOR EACH ROW` triggers?

I have a trigger like BEFORE INSERT ON table_a FOR EACH ROW EXECUTE FUNCTION upsert_into_table_b(). If I execute a bulk insert like INSERT INTO table_a ([columns]) VALUES ([row 1 values]), ([row 2 ...
Nathan Long's user avatar
0 votes
1 answer
198 views

In PostgreSQL, what is the `index_predicate` for in `INSERT`?

The docs on INSERT say: If an index_predicate is specified, it must, as a further requirement for inference, satisfy arbiter indexes. Note that this means a non-partial unique index (a unique index ...
Nathan Long's user avatar
0 votes
0 answers
125 views

SQLite Upsert - syntax error

I'm trying to use SQLite's upsert syntax as described in their documentation However I get the following error, in both WebSql (SQLite v3.41.2) and SQLite on Android (SQLite v3.40.0) could not prepare ...
Eriedor's user avatar
  • 51
0 votes
0 answers
391 views

duplicate key value INSERT ON CONFLICT UPDATE

With this table definition: create table t1 ( id uuid not null primary key, "u1" integer not null references u, "p1" integer not null references p, "c1" ...
Slim's user avatar
  • 259
-1 votes
1 answer
824 views

Including deleted_at in unique index for upsert

I have a table with a deleted_at column, see comment. The deleted_at column behaves like a one-way boolean event flag, i.e. once a row is deleted it can't be restored. There is also an asynchronous ...
mozey's user avatar
  • 99
0 votes
1 answer
58 views

Is UPSERT feature complete?

Here, a lot of energy seems spent on Coalesce - I think it is to avoid the case where a null value in the update record could overwrite a non-null value. Say we have a table of businesses and some ...
Ken Lyle's user avatar
1 vote
2 answers
1k views

Insert values and add values to foreign key

I have two tables: CREATE TABLE est ( est_id serial PRIMARY KEY , est_nom varchar(70) ); CREATE TABLE bm ( id serial PRIMARY KEY , est_nom int , nom varchar(70) , CONSTRAINT FK_bm_est FOREIGN KEY(...
David Palacios's user avatar
2 votes
1 answer
513 views

UPSERT fails for INSERT expression with SELECT

I ran into a problem with the UPSERT statement in SQLite. I have a master and a slave database, the updates to the slave database should be merged into the master database. I would like to use the ...
flacer's user avatar
  • 23
2 votes
1 answer
2k views

How to put a condition in the conflict_action of an Upsert?

I have a table like this: CREATE TABLE mytable ( pk1 varchar(4), pk2 varchar(4), col1 varchar(7), col2 varchar(6), ...
user2740's user avatar
  • 147
2 votes
1 answer
205 views

Skip (or speedup) postgres unique index creation if I know beforehand that it's unique

I have a large Postgres table (dbeaver says it has about 3e9 rows and almost 4Tb of disk space) I need an UPSERT, but ON CONFLICT section fields don't contain PK and I can't know it without performing ...
Alexander Dmitriev's user avatar
4 votes
1 answer
698 views

UPSERT based on unique combination of (INTEGER, NULL), (NULL, INTEGER)

My table, for simplicity, looks like this: id | foreign_key_1 | foreign_key_2 | value As clear from the names, the fields foreign_key_1 and foreign_key_2 refer to PKs in two other tables. The tricky ...
Don Draper's user avatar
0 votes
2 answers
753 views

Upsert always updates postgreSQL

I have sql query: INSERT INTO books VALUES (12, 0, CURRENT_TIMESTAMP) ON CONFLICT (id) WHERE updated IS NULL OR updated + INTERVAL '2min' < CURRENT_TIMESTAMP DO UPDATE SET version = books.version +...
user122222's user avatar
0 votes
1 answer
413 views

MySQL: how to insert only if update fails

There is a similar question, but my requirement is different. The table schema is: CREATE TABLE `device` ( `id` int NOT NULL, `device_type` varchar(64) NOT NULL, `serial` varchar(128) NOT NULL, ...
xrfang's user avatar
  • 143
1 vote
0 answers
396 views

postgres upsert returning even if not inserting

I've read the insert doc. I'm not sure I follow every line, but I've read it. I have a table that's a mapping from query strings to uuids: create table foo.query_uuids ( query_string text not ...
user3742898's user avatar
0 votes
1 answer
135 views

Upserts while enforcing uniqueness over a table column, plus a set of joined records

I'm on Postgres 12.8. I have a table of data points and tags attached to them: CREATE TABLE data_points ( id INT GENERATED BY DEFAULT AS IDENTITY, date DATE NOT NULL, value INT NOT NULL, ...
x1a4's user avatar
  • 111
5 votes
1 answer
289 views

What's the difference between these three versions of TSQL snippet?

Version 1 DECLARE @key INTEGER = 33, @val INTEGER = 44; BEGIN TRANSACTION; INSERT dbo.t([key], val) SELECT @key, @val WHERE NOT EXISTS ( SELECT 1 FROM dbo.t WITH (UPDLOCK, SERIALIZABLE) ...
Fajela Tajkiya's user avatar
1 vote
1 answer
102 views

How could this UPSERT query be made shorter?

I have a table storing the result of some simulations. Besides the few columns that identity the row, every single column in the row is getting replaced at each update. The query feels quite long and ...
Thomas's user avatar
  • 303
3 votes
1 answer
3k views

DELETE plus INSERT, or UPSERT row?

I'm trying to figure out the fastest way to: INSERT a row if it doesn't exist REPLACE (all columns at once) the row if it exists I thought about DELETE + INSERT, but that looks like 2 scans and if I ...
Thomas's user avatar
  • 303
1 vote
1 answer
601 views

Looking for an alternative to a CTE that will work as a subquery in IF EXISTS

I have an IF EXISTS 'upsert' running fine by itself in it’s own stored proc. But when I try and use the same statement referencing a CTE, it doesn't recognize the CTE. I see in related post that I'm ...
hap76's user avatar
  • 15
0 votes
1 answer
413 views

SQLite: excluded int primary key null value replaced with next auto value

I'm trying to create generic upsert query builder with specific behavior. Table has two unique constraints: primary key itId and column slug: create table it ( itId integer primary key -- an alias ...
Denis Borzenko's user avatar
4 votes
1 answer
6k views

UPDATE statement on UPSERT not incrementing value

I have the following QUERY: INSERT INTO ReservationCounter (student_id, reservation_count) VALUES (1, 1) ON CONFLICT (student_id) DO UPDATE SET reservation_count=excluded.reservation_count+1; When ...
medicengonzo's user avatar
1 vote
1 answer
1k views

UPSERT with UPDATE on variable column gives error "command cannot affect row a second time"

Hey I'm trying to insert or update(if the constraints are duplicated) based on a query result, these are the create table statement: CREATE TABLE IF NOT EXISTS public.inventory ( inventory_id serial ...
IBot's user avatar
  • 149
3 votes
0 answers
8k views

UPSERT multiple rows, do ON CONFLICT DO UPDATE SET conditional check/update row-wise

I'm trying to UPSERT multiple rows into a Postgres table (ie. INSERT ON CONFLICT DO UPDATE SET multiple rows). But I want the ON CONFLICT DO UPDATE SET conditional check/update do be done row-wise (ie....
étale-cohomology's user avatar
1 vote
1 answer
4k views

Sqlite upsert from select with conflict does not always update row

Assuming a database built with the following: PRAGMA foreign_keys = ON; CREATE TABLE foo ( id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, UNIQUE (name) ); INSERT INTO foo (name) VALUES ...
user avatar
1 vote
1 answer
2k views

unable to update a single column in upsert: postgres

I came across a scenario where I would be trying to update a single column using UPSERT statement. But I get the error: ERROR: source for a multiple-column UPDATE item must be a sub-SELECT or ROW() ...
Sajith P Shetty's user avatar
1 vote
0 answers
1k views

Postgres: with identity column as unique key, need help in upsert

Follow the below script to create two tables source_t and dest_t. drop table if exists source_t; create table source_t as (select 1 id, 'dummy1' varcol union select 3,'dummy3'); drop table if exists ...
Sajith P Shetty's user avatar
0 votes
1 answer
993 views

How to update a column with multiple row values for each group?

In my Postgres table, there are about 2 million rows in total and for each ticker, there are 752 rows. I have an algorithm that shows different prediction price values for each date and ticker which ...
Kaushik Ganesan's user avatar
2 votes
1 answer
198 views

Selecting gives one row, updating affects 2

When I select using WHERE clause that uses the same columns as a UNIQUE constraint "mytable_uniq_detail_date" (detail_id and date), I get a single row, but when I upsert a row data that (as ...
Roman Reimche's user avatar
3 votes
2 answers
2k views

Why does upsert result in a deadlock?

I'm doing the below upsert statement, which I need to have run multiple times in a short timeframe. I wrote this statement based on examples from Aaron Bertrand's Please stop using this UPSERT anti-...
Leon Cullens's user avatar
0 votes
1 answer
878 views

Postgresql: 'upserting' into two tables (vertical partitioning) using the same id with a unique constraint

I have two tables, one containing all the hot columns and one the static ones. The static table has an unique constraint. When the conflict on the unique constraint triggers only the hot columns in ...
BarNiy's user avatar
  • 3
2 votes
1 answer
2k views

PostgreSQL relative update breaks unique constraint

I have unique index on column for UPSERT. When I am trying to update column using v = v + 1 expression my unique index breaks. SQL CREATE TABLE test(v bigint, data jsonb DEFAULT '{}'::jsonb); INSERT ...
Olleggerr's user avatar
0 votes
2 answers
992 views

UPSERT unless the date is the same

There's a table CREATE TABLE IF NOT EXISTS table00 ( userid int8 PRIMARY KEY, save date NOT NULL, value int4 ); where I want to upsert values IFF the date is not the same ...
étale-cohomology's user avatar
1 vote
1 answer
97 views

Mysql8 fail to upsert generated uuid

I have a mysql8 table with 2 columns: ID(Int) AND UUID(Varchar36): CREATE TABLE `upsert_test` ( `ID` int unsigned DEFAULT NULL COMMENT 'id;', `UUID` varchar(36) DEFAULT NULL ) ENGINE=InnoDB; I ...
Woods Chen's user avatar
-1 votes
1 answer
709 views

Locking row for INSERT, still getting duplicate key exception

I am trying to run the following query: WITH upd AS ( UPDATE fila_all SET id = id WHERE id = (select id from fila_all fa where fa.id not in (select msg_id from fila_consumers where group_name = '...
simao's user avatar
  • 107
9 votes
2 answers
8k views

Why is the serial primary key incrementing despite a using "On Conflict Do Nothing?

I have the below table in PostgreSQL 13: table name: newtable field type ----- ---- Seq bigserial code varchar Seq is the primary key (auto-increment) Code is ...
Don2's user avatar
  • 469
4 votes
2 answers
2k views

How to "succinctly" detect changed values in the PostgreSQL's upsert (on conflict) where clause while supporting null changes?

We maintain a large data warehouse implemented in PostgreSQL and python. One very common pattern we do is to make upserts, and log when things were updated. We have some unique key my_key and values, ...
André Christoffer Andersen's user avatar
1 vote
1 answer
3k views

PostgreSQL UPSERT issue with NOT NULL columns

I want o know if it's possible to do an upsert when one of the columns (column1 or column2) is null, I just need a partial update, and of course, I'm trying to avoid a SELECT to verify if the record ...
Carlos Marmolejo's user avatar
0 votes
2 answers
714 views

MySQL update a record on duplicate key update (merge)

I have a table products with the follow schema: CREATE TABLE `products` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint unsigned NOT NULL, `article_id` bigint unsigned NOT NULL, ...
Pioz's user avatar
  • 101
3 votes
1 answer
326 views

High amount of disk writes during upsert with no changes on table

We are running some nightly data sync jobs which move data from an external database into postgres using an upsert statement. The data is updated in batches and using parallel workers (10 currently). ...
Birne94's user avatar
  • 371
10 votes
1 answer
5k views

Syntax shorthand for updating only changed rows in UPSERT

I have a query which upserts into Postgres when a key conflicts only if there are changes to one of the other columns (to avoid unnecessary inserts and returned rows where nothing was actually changed)...
Caustic's user avatar
  • 203
0 votes
1 answer
459 views

Insert results from query, update old existing row if query doesn't still return same value

I am trying to refactor a table which contains historical data and therefore isn't ever deleted, but has an 'active' field. Each day we run a query (a few joins, but quite slow) to get some data to ...
Mitch Kent's user avatar
5 votes
1 answer
922 views

Cannot use "ON CONFLICT" with postgres updatable view and partial index

I have an updatable view pointing to an underlying table that has a partial index. It looks something like this CREATE TABLE if not exists foo ( a INT, b INT, x INT, y INT, z ...
Kyle's user avatar
  • 101
1 vote
1 answer
625 views

Upserting 50K records into big table (app. 350 columns) in postgresql

Using C# I'm trying to upsert from csv file (~ 55000 records) to a postgresql table with 350 columns. I'm just building the upsert statements from header and record which are comma separated. Like ...
alaa_sayegh's user avatar
3 votes
1 answer
3k views

SQL UPSERT in Postgres

I have the following SQL: UPDATE table_name as t set value = c.value from (values(10, 'key_1', 60),(11, 'key_2', 10)) as c(id, key, value) where t.id = c.id and t.key = c.key This works fine ...
cycero's user avatar
  • 135
1 vote
1 answer
1k views

Upsert Row If Columns Don't Match

I have a table with unique identifier sid, somecolumn and boolean changed that is needed by other program to detect change in row, I'm using insert into ... on conflict (sid) DO UPDATE ... to insert ...
Phalelashvili's user avatar