Questions tagged [upsert]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
17 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
50 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 = '...
0
votes
1answer
82 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
38 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
82 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
29 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
56 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
480 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
54 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
51 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
25 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
228 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
78 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
88 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 ...
1
vote
1answer
104 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
85 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
3k 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
5k 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
269 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
326 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
129 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
1k 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
27 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
61 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
6k 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
861 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
843 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
2k 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 ...
14
votes
3answers
10k 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
0answers
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
285 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 ...
0
votes
2answers
4k 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
219 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 ...
15
votes
1answer
12k 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
669 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 ... (...
2
votes
1answer
8k 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
118 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 = ...
9
votes
1answer
9k 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
929 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
18k 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
671 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 ...
1
vote
0answers
374 views

Postgres upsert perform multiple actions _OR_ versioning of events in an “append only” store

Background I am running on postgres 9.5 I have a table with events (in theory, it is, currently, an append only store). Certain event types can appear only once on a given date. (So currently, we ...
10
votes
2answers
5k views

PostgreSQL Upsert not working on Partitioned Table

Have a table like this: CREATE TABLE aggregated_master ( "user" BIGINT, type TEXT, date TIMESTAMP, operations BIGINT, amount NUMERIC, PRIMARY KEY ( "user", ...
6
votes
1answer
14k views

PostgreSQL/Psycopg2 upsert syntax to update columns

I want to have Psycopg2 update col1, col2 and col3 when there is a conflict of id. In my Python code I have the insert SQL currently as: insert_sql = '''INSERT INTO {t} (id,col1,col2,col3) ...
4
votes
1answer
2k views

How do I do an UPSERT in SQL Server, returning the pre-updated values?

All of the syntax is valid PostgreSQL as an example of an UPSERT that returns the old and new values for the field. Let's say I have a table foo with (1,A)...(5,E). CREATE TEMP TABLE foo AS SELECT ...
6
votes
1answer
6k views

How to pass value to ON CONFLICT clause that's not a column?

Basically, I'm trying to do multiple inserts in a single command in postgresql, where my ON CONFLICT has a per-insert parameter. Here is the query (formatted for psycopg2): INSERT INTO web_pages ...
3
votes
1answer
347 views

Lock a row if it exists, create new record otherwise

I have a table that holds metadata for "documents", let's call it DOCS. Other tables point to the metadata table's ID, to build the parts of the document. I want to make sure that, in my application,...
20
votes
2answers
22k views

'Column reference is ambiguous' when upserting element into table

I am using PostgreSQL as my database. And I need to create an entry in the database, and if it's already exists, just update its fields, but one of the fields should be updated only if it's not set. ...