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
14 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
20 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
23 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
49 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 ...
1
vote
0answers
625 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
46 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
173 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 ...
2
votes
1answer
696 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
53 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 ...
7
votes
3answers
2k 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 ...
3
votes
0answers
718 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
97 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
1k 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
120 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 ...
6
votes
1answer
2k 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
345 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 ... (...
1
vote
1answer
3k 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
45 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 = ...
4
votes
1answer
3k 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
423 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 ...
0
votes
1answer
9k 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 --...
0
votes
2answers
273 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
249 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 ...
8
votes
2answers
2k 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", ...
4
votes
1answer
6k 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
953 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 ...
5
votes
1answer
3k 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
159 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,...
9
votes
1answer
10k 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. ...
1
vote
1answer
700 views

Should I be wary of ON DUPLICATE KEY UPDATE?

I'm new to MySQL, but I have reasonable experience with SQL Server. I know in SQL Server I should be very cautious about using MERGE statements because they can lead to some problems. Are there any ...
14
votes
2answers
6k views

PostgreSQL UPSERT issue with NULL values

I'm having an issue with using the new UPSERT feature in Postgres 9.5 I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can ...
3
votes
1answer
4k views

ON CONFLICT ON CONSTRAINT fails saying constraint doesn't exist

I'm trying to use new Postgresql 9.5 upsert feature. But for some reason my query saying constraint doesn't exist (when it does). My query is this INSERT INTO journals (ext_ids, title) VALUES ('{"...
0
votes
1answer
1k views

Set values conditionally in Postgres UPSERT

We have an UPSERT feature achieved through on conflict do set. How can I set the values conditionally in the set statement, like if(excluded.col1 is null) col1=table.col1 else col1=excluded.col1 We ...
13
votes
2answers
35k views

UPSERT with ON CONFLICT using values from source table in the UPDATE part

Given: CREATE TABLE A ( PK_A INT8 NOT NULL, A INT8, PRIMARY KEY (PK_A) ); CREATE TABLE B ( PK_B INT8 NOT NULL, B INT8, PRIMARY KEY (PK_B) ); This query: insert into table_b (pk_b, b) select pk_a,...
2
votes
1answer
2k views

Update the value when the row already exists

I have table 'A' in schema 'USER'. There are three columns in this table. below are the names of the columns: id (primary key) name age I want to insert a row in this table, but if the row is ...
0
votes
1answer
937 views

TEMP table alternatives for pl/pgsql

I am trying to emulate MERGE behavior with pl/pgsql: -- Generate the data from funtion CREATE TEMP TABLE temp_x (id int, id2 int, data text, created_at timestamp, updated_at timestamp) ON COMMIT ...
2
votes
1answer
142 views

How do we implement SQL Server IF EXISTS (SELECT) in MySQL 5.6?

We would like to convert the following SQL Server statement: IF EXISTS(SELECT OptionValue FROM Options WHERE OptionCatagory = 'General' AND OptionName = '...
18
votes
2answers
6k views

How to get the ID of the conflicting row in upsert?

I have a table tag with 2 columns: id (uuid) and name (text). I now want to insert a new tag into the table, but if the tag already exists, I want to simply get the id of the existing record. I ...
3
votes
3answers
1k views

Update Table from SP in SSIS

I know there are similar questions to this one, but I could not find one that answered my question. Inside my current SSIS package... I have a dataflow task which... Takes output from a stored ...
1
vote
1answer
2k views

How to return values from “upsert” query in case when UPDATE has been performed?

WITH upsert AS (UPDATE tbl SET a = 2 WHERE a = 1 RETURNING tbl.*) INSERT INTO tbl (a) SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert ) RETURNING * This "upsert" statement works however I would like ...
1
vote
1answer
2k views

How do I update and insert specific set of rows to an existing table using Merge in SQL Server 2008 R2?

I have a table in the database that I need to update based on the records being passed to it. I am given a table-valued-parameter that has the same schema as the existing table (for all intensive ...
19
votes
2answers
141k views

How to insert or update using single query?

I have a table test having columns id which primary key and auto incremented and name. I want to insert a new record if annd only if there are no records.For example input is id=30122 and name =john ...
9
votes
1answer
9k views

How to avoid using Merge query when upserting multiple data using xml parameter?

I am trying to update a table with an array of values. Each item in the array contains information that matches a row in a table in the SQL Server database. If the row already exists in the table, we ...
1
vote
2answers
69 views

What is the correct query set up to access the columns in table being passed as parameter?

I am trying to use table-valued parameter to bulk upsert some data into my table. I have the following stored procedure. This setup seems to agree with the intellisense. I am not getting any errors ...
2
votes
2answers
1k views

Call UPSERT function multiple times to different inputs

I have a function: merge_vehicles(vid, cid, vname, reg_no, name, name_1st) Can i call it multiple times on inputs like (2335, 55, '246BDH', '246BDH', '811', 1), (2336, 55, '038THX', '038THX', '831',...
8
votes
1answer
4k views

Why is CTE open to lost updates?

I don't understand what Craig Ringer meant when he commented: This solution is subject to lost updates if the inserting transaction rolls back; there's no check to enforce that the UPDATE affected ...
1
vote
1answer
626 views

UPSERT statement never completes, infinite loop?

Based on some of the stackoverflow answers I wrote the following UPSERT statement, which I think must be stuck in an infinite loop. I added a statement in there to avoid an infinite loop (as it would ...
0
votes
1answer
442 views

Auto increment holes in primary key - any issues?

I have an application that is creating large holes in the auto incremented primary surrogate key to the table as it uses lots of INSERT ... ON DUPLICATE KEY UPDATE queries. I'm looking into changing ...
1
vote
1answer
222 views

Name for SELECT equivalent of UPSERT

UPSERT means INSERT and\or UPDATE, is there a standard name for a stored procedure that will INSERT if necessary and then SELECT? e.g. I want to get the IS of a row that matches columns and give me ...
2
votes
1answer
525 views

How to use proper hints for Upsert involving a top 1 subquery

For this specific case (tracking Load Balancer switches) we wish to optimize an Upsert so that it does not exhibit any race conditions, cause any PK violation, or acquire any over sized locks. I ...