Questions tagged [upsert]
UPSERT: SQL-UPDATE a row, or if not found INSERT a new row.
109
questions
0
votes
0
answers
32
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" ...
- 203
-1
votes
1
answer
49
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 ...
- 99
0
votes
1
answer
38
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 ...
- 33
1
vote
2
answers
124
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(...
2
votes
1
answer
125
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 ...
- 23
0
votes
1
answer
313
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),
...
- 127
2
votes
1
answer
74
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 ...
4
votes
1
answer
284
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 ...
- 197
0
votes
2
answers
121
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 +...
- 105
0
votes
0
answers
33
views
Optimize big batched upserts into azure managed PostgreSQL 11
I have a 3,600,000,000 record table (growing) that I daily need to perform ~50,000,000 upserts into, ~30,000,000 are updates and 20,000,000 new records each day.
I would like to keep a year of data ...
- 101
1
vote
1
answer
158
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,
...
- 145
1
vote
0
answers
183
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 ...
- 111
0
votes
1
answer
80
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,
...
- 111
5
votes
1
answer
277
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)
...
- 945
1
vote
1
answer
58
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 ...
- 261
1
vote
1
answer
1k
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 ...
- 261
1
vote
1
answer
266
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 ...
- 15
0
votes
1
answer
244
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 ...
4
votes
1
answer
2k
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 ...
- 143
1
vote
1
answer
864
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 ...
- 149
2
votes
0
answers
5k
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....
- 123
1
vote
1
answer
2k
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 ...
- 111
1
vote
1
answer
933
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() ...
- 134
1
vote
0
answers
855
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 ...
- 134
0
votes
1
answer
724
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
1
answer
66
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 ...
3
votes
2
answers
1k
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-...
- 133
0
votes
1
answer
513
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 ...
- 3
2
votes
1
answer
1k
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 ...
- 23
0
votes
2
answers
685
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 ...
- 123
1
vote
1
answer
77
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 ...
- 111
-1
votes
1
answer
445
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 = '...
- 99
4
votes
2
answers
5k
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-...
- 269
3
votes
2
answers
1k
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, ...
1
vote
1
answer
2k
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
2
answers
420
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,
...
- 101
3
votes
1
answer
234
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).
...
- 331
10
votes
1
answer
3k
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)...
- 203
0
votes
1
answer
367
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 ...
- 205
4
votes
1
answer
675
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 ...
- 91
1
vote
1
answer
442
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 ...
- 133
3
votes
1
answer
2k
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 ...
- 135
0
votes
1
answer
644
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 ...
3
votes
2
answers
8k
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 ...
- 131
0
votes
0
answers
38
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 ...
- 1
8
votes
1
answer
14k
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 ...
- 327
5
votes
1
answer
643
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 ...
- 2,278
5
votes
1
answer
792
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 ...
- 53
1
vote
1
answer
349
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 ...
- 53
1
vote
1
answer
3k
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 ...
- 27