Questions tagged [upsert]

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

Filter by
Sorted by
Tagged with
1 vote
1 answer
70 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
113 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
  • 93
0 votes
1 answer
48 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
123 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
97 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
114 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
301 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
  • 237
-1 votes
1 answer
605 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
56 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
696 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
392 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
0 votes
1 answer
1k 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
  • 127
2 votes
1 answer
181 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
592 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
622 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
340 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
358 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
109 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
288 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
91 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
  • 293
3 votes
1 answer
2k 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
  • 293
1 vote
1 answer
507 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
387 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
3 votes
1 answer
5k 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
2 votes
0 answers
7k 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
910 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
137 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
773 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
922 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
94 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
643 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
8 votes
2 answers
7k 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
  • 429
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
658 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
307 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
4k 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
433 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
875 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
570 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
973 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
4 votes
2 answers
10k views

Efficient key value store in Postgres

I want to use Postgres(11 in CloudSQL) as an efficient key value store. I have about 200GB of dictionaries (average size is 10kB, the structure can be different and nested). I'm thinking about ...
Lukasz Madon's user avatar
0 votes
0 answers
44 views

How to UPSERT tree in PostgreSQL?

There is a tree-structures in two-tables with ParentId field. How to make UPSERT (INSERT ... ON CONFLICT (...) DO UPDATE ...) between them? The problem is that parent record for the current record in ...
osya's user avatar
  • 1