Questions tagged [upsert]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
28 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 ...
2
votes
1answer
40 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 ...
2
votes
2answers
413 views

Why does upsert result in a deadlock?

I'm doing the following upsert, that I run multiple times in a short timeframe: BEGIN TRANSACTION; INSERT AggregationMeasurement (ValueFloat, Date, AggregationConfigurationId) SELECT @ValueFloat, @...
0
votes
1answer
43 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 ...
2
votes
1answer
227 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 ...
0
votes
2answers
46 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 ...
1
vote
1answer
43 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 ...
0
votes
0answers
25 views

Most efficient way to update data that is "99% the same, 1% inserts/ updates."

I have a data source that doesn't do incremental changes. I need to pull the self-same 100 million row record set each day, whereby maybe 1% of the data is new, and 0.5% is updated. There sadly is no ...
-1
votes
1answer
55 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 = '...
1
vote
1answer
755 views

INSERT ... ON CONFLICT DO NOTHING: why is the serial number incremented?

I have a simple table as below in PostgreSQL 13: table name: newtable1 field type ----- ---- Seq bigserial code varchar Seq is the primary key (auto-...
0
votes
2answers
114 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, ...
0
votes
1answer
372 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 ...
0
votes
2answers
87 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, ...
1
vote
1answer
71 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). ...
6
votes
1answer
1k 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)...
0
votes
0answers
78 views

Postgresql UPSERT contention on simple table

Postgresql 12.3: I am finding CPU spikes and wait times from UPSERT contention for what I would hope is a very simple table/use case. The table definition is like so: CREATE TABLE public....
0
votes
0answers
66 views

Use of tempdb for bulk upsert operation

We are using SSIS for ETL purpose. There is a shortcoming in SSIS as it doesn't provide any in built component which will bulk upsert the destination. The alternative to it to use Staging tables which ...
0
votes
1answer
28 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 ...
4
votes
1answer
425 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 ...
0
votes
0answers
95 views

Space concerns for unique constraint on row for time-series db (PostgreSQL / TimescaleDB)

I was wondering if there is a more space-efficient way of doing unique row constraints on time-series DBs. Adding a normal unique key for the entire row will result in an index the same size or ...
1
vote
1answer
156 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 ...
2
votes
1answer
236 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 ...
0
votes
1answer
122 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 ...
2
votes
2answers
4k 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 ...
0
votes
0answers
34 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 ...
4
votes
1answer
7k views

Postgres Upsert Performance Considerations (millions of rows/hour)

I have tried doing some research for quite a while on the performance implications of the built-in upsert (INSERT ... ON CONFLICT UPDATE...) when a lot of upserts are made. The scale is something like ...
5
votes
1answer
383 views

Is it possible to upsert into a self-referencing table with a single statement in PostgreSQL?

If I have a table A, like so: A { id SERIAL title TEXT ... parentId INT references A.id via a foreign key constraint } I am pulling data from a source table - A_SOURCE - where there isn't a ...
4
votes
1answer
418 views

PostgreSQL Serialisation failure on different ids

Serializable isolation mode can be used to avoid race conditions when upserting equal ids. So for create table u(uid int primary key, name text); if we run two similar transactions T1 and T2: begin ...
0
votes
1answer
151 views

SSIS 2016 conditional split with multiple columns in conditions

I am having an issue in executing a conditional split for upserting records into my production table, using SSIS 2016 and MSSQL 2016 (Standard Ed.) I am trying to load two separate files (produced ...
0
votes
1answer
2k views

Is mongodb upsert the right option for write intensive use case?

I'm designing a data model for a transactional use case which is write intensive. The write load would be 900 - 1,600 Transactions Per Second (TPS) which would account for 30 million Inserts/Updates ...
1
vote
1answer
29 views

Upserting data with existing many-to-many connections

This is a strategy question - but one I've been grappling with all week and could really use some wisdom-as-a-service. Context I am trying to piece together a clean, actionable PostgreSQL database ...
0
votes
4answers
62 views

Why does updating our database take so long?

I currently have a small app take in a CSV and call a procedure to update/insert records to keep our MySQL 5.6.39 database up to date with the database of an external web app we use (that we don't ...
4
votes
0answers
7k views

Postgres bulk upsert performance

I am doing bulk UPSERT on a table with 30 million rows. The table has just two columns (varchar as a primary key and integer). The input data is imported to the temporary table first, and then bulk ...
1
vote
1answer
1k views

Postgres 10.0 upsert syntax error not sure what to google for

Postgres 10.0 table: pcr=> \d tableFindOld Table "public.tablefindold" Column | Type | Collation | Nullable | Default ----------+--...
-1
votes
1answer
1k views

Receiving error about ambiguous field, but there is only 1 table being referenced

My understanding of ambiguous fields, or where I see the error elsewhere, is when I've defined a JOIN on multiple records and failed to specifically say which table has the field when the field names ...
4
votes
1answer
3k views

Efficiently bulk upsert unrelated rows

As part of a manual replication process between databases with different but related schemas, for each table, we identify new and modified records in the source database and feed them via a table ...
1
vote
1answer
55 views

Isolation level issue

I need some help in choosing the correct isolation level on my transactions. My situation is as follows. I have a table with a unique constraint over two columns. For the sake of the argument, lets ...
16
votes
3answers
12k views

Concurrent transactions result in race condition with unique constraint on insert

I have a web service (http api) which allows a user to restfully create a resource. After authentication and validation I pass off the data to a Postgres function and allow it to check authorisation ...
7
votes
1answer
3k views

Why is Insert-Ignore so expensive in MySQL?

TL;DR: Inserting a large number of rows with no collisions is MUCH faster than (not) re-inserting the same rows, BOTH using the INSERT IGNORE syntax. Why is this? I would assume that the index lookup ...
1
vote
1answer
297 views

SequelizeDatabaseError While Upserting, Wrong Table Being Referenced

I'm selecting from a table, and the query runs fine. It even shows up on the client side without a hitch. ` SELECT * FROM public."roleAllocation" ` However, when I run an upsert function from a ...
1
vote
2answers
5k views

Insert if names do not exist and return ids if exist [closed]

Suppose I have an array of string "a","b","c","d","e". CREATE TABLE foo ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name text UNIQUE ); INSERT INTO foo (name) VALUES ('a'), ('d'); ...
0
votes
1answer
229 views

No data inserted with this WHERE NOT EXISTS expression?

I want to move data from MySQL to PostgreSQL. I am using mysql.connector and psycopg2 for querying the two databases. When I run WHERE NOT EXISTS for my PostgreSQL DB, it's doesn't insert the data ...
18
votes
1answer
15k views

Multiple on conflict targets

I have two unique indexes on columns a and b. I need something like this: insert into my_table (a, b) values (1, 2), (1, 2) on conflict (a) do update set c = 'a_violation' on conflict (b) do update ...
-1
votes
1answer
794 views

UPSERT in postgres stored procedure

Postgres already has a way to do "upsert" but I don't like sending these long queries, I wonder if it could be implemented as a stored procedure which would accept json and perform INSERT INTO ... (...
3
votes
1answer
9k views

Difference between UPSERT and MERGE?

From the PostgreSQL wiki, MERGE is typically used to merge two tables, and was introduced in the 2003 SQL standard. The REPLACE statement (a MySQL extension) or UPSERT sequence attempts an UPDATE, ...
1
vote
1answer
135 views

a SELECT or set-returning version of MERGE/UPSERT?

I'm learning MERGE or UPSERT (UPDATE + INSERT), which according to wikipeda, has the form: MERGE INTO tablename USING table_reference ON (condition) WHEN MATCHED THEN UPDATE SET column1 = ...
11
votes
1answer
11k views

Deadlock with multi-row INSERTs despite ON CONFLICT DO NOTHING

Setup I have a bulk insert function set_interactions(arg_rows text) that looks like this: with inserts as ( insert into interaction ( thing_id, associate_id, created_time) ...
1
vote
0answers
1k views

UPSERT or INCREMENTAL update of table but with DELETE

I have a following problem. We have two systems, an OLTP system and our Data Warehouse, both on PostgreSQL. I need to sync one table from OLTP system with DWH, so they're identical. I am using ...
1
vote
1answer
19k views

SQL Server : Merge and update multiple rows across columns

I am trying to consolidate data in one table and merge it into another. The data in the source destination table is as follows : name |dob |city |occupation --...
1
vote
2answers
720 views

Cumulative daily totals over 30 day interval

I have the below query which is meant to calculate CSR call metrics and totals per day. The way I run it now for the daily report is to group by extension and place now() and curdate() values in the ...