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 per day and read load would be 600 - 800 TPS Since we have opted for an embedded data model, we are using upsert queries in most places. We have unique indexes built on the right fields to make the search faster. Out of the 30 million TPS day, we will be performing at least 10 million upsert per day I would like to know: 1. Is it wise to keep the documents embedded and perform upserts every time (considering the load) rather than keeping the documents in 2 separate collections and perform joins to report the data to client applications? 2. Considering the load, I suspect that too much of upsert would created high IO and crash the system.

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 assumed I could just use ON CONFLICT DO NOTHING in combination with RETURNING "id": INSERT INTO "tag" ("name") VALUES( 'foo' ) ON CONFLICT DO NOTHING RETURNING "id"; But this returns an empty result set, if the tag with the name "foo" already exists. I then changed the query to use a noop DO UPDATE clause: INSERT INTO "tag" ("name") VALUES( 'foo' ) ON CONFLICT ("name") DO UPDATE SET "name" = 'foo' RETURNING "id"; This works as intended, but it is somewhat confusing, because I'm just setting the name to the already existing value. Is this the way to go about this problem or is there a simpler approach I'm missing?

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 real select. There are other partial indexes over that columns, but they don't include this new INSERT. Talk is cheap, here is the code.

-- CURRENT STATE

CREATE TABLE test (
	id bigserial NOT NULL, -- PK
	client_id varchar NOT NULL,
	order_id varchar NOT NULL,
	typ varchar(255) NOT NULL,
	value integer
	CONSTRAINT test_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX idx1 ON test (client_id, order_id, typ) WHERE (typ in ['foo', 'bar']);

-- what's even more interesting, this non-unique index is actually unique
-- or at least behaves like unique
CREATE INDEX idx2 ON test (client_id, order_id);

-- WHAT I WANT

INSERT INTO test
(client_id, order_id, typ, value) 
VALUES
($1, $2, 'baz', $3) --  this typ is not present in the table!
ON CONFLICT (client_id, typ)
DO UPDATE SET
	value = value + EXCLUDED.value

-- how I am going to achieve this
CREATE UNIQUE INDEX idx3 ON test (client_id, typ) WHERE (typ = 'baz');

As far as I know, during index creation postgres will scan the full table, looking for a single baz, which is guaranteed not to be there. **The questions are:** 1) Is there a way to tell postgres to skip that index validation, because "I swear to god, it's unique" 2) (x-y problem) maybe I miss something and there is a simplier way to achieve this without index creation.

[This classic concurrency safety article](https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/) is clearly designed for only upserting one row at a time. In my situation, I have a table-valued input and I want to upsert each row in a concurrency safe way. I know that this isn't always possible, but I want to get as close as possible. MERGE seems like a natural solution to it, but I distrust it and truly am in a situation where it is [bug prone](https://michaeljswart.com/2021/08/what-to-avoid-if-you-want-to-use-merge/) . The remaining two approaches in Michael J. Swart's article are: 1) _Inside a Transaction With Lock Hints (Update More Common)_

CREATE PROCEDURE s_AccountDetails_Upsert ( @Email nvarchar(4000), @Etc nvarchar(max) )
AS 
SET XACT_ABORT ON;
BEGIN TRAN
 
  UPDATE TOP (1) dbo.AccountDetails WITH (UPDLOCK, SERIALIZABLE)
     SET Etc = @Etc
   WHERE Email = @Email;
 
  IF (@@ROWCOUNT = 0)
  BEGIN      
      INSERT dbo.AccountDetails ( Email, Etc )
      VALUES ( @Email, @Etc );
  END 
COMMIT

2) _Inside a Transaction With Lock Hints (Insert More Common)_

CREATE PROCEDURE s_AccountDetails_Upsert ( @Email nvarchar(4000), @Etc nvarchar(max) )
AS 
SET XACT_ABORT ON;
BEGIN TRAN
 
  INSERT dbo.AccountDetails ( Email, Etc )
  SELECT @Email, @Etc
  WHERE NOT EXISTS (
      SELECT *
      FROM dbo.AccountDetails WITH (UPDLOCK, SERIALIZABLE)
      WHERE Email = @Email
  )
 
  IF (@@ROWCOUNT = 0)
  BEGIN      
      UPDATE TOP (1) dbo.AccountDetails
      SET Etc = @Etc
      WHERE Email = @Email;
  END 
COMMIT

I could adapt either of these to use table variables (e.g. I suspect that IF (@@ROWCOUNT = 0) needs totally removing), but **does the usage of a table-valued input make it obvious that we should prefer either the first or second solution?** If not, then on what basis should the decision be made?

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, or on failure, INSERT. This is similar to UPDATE, then for unmatched rows, INSERT. Whether concurrent access allows modifications which could cause row loss is implementation independent. Further PostgreSQL's [INSERT ... ON CONFLICT DO NOTHING/UPDATE](https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5#INSERT_..._ON_CONFLICT_DO_NOTHING.2FUPDATE_.28.22UPSERT.22.29) is marketed as *UPSERT* and was added in 9.5 What then is MERGE? And how does it fit into the mix?

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 from an OpenVMS database) that contain similarly-formatted content, however they are from two different companies: AB_CustomerData.txt and CD_CustomerData.txt. Customer Format files RecordType: CU01 ---------------- RecordType 2 characters Company 2 characters CustomerNumber 7 characters CustomerName 50 characters RecordType: CU02 ---------------- RecordType 2 characters Company 2 characters CustomerNumber 7 characters City 9 characters State 8 characters RecordType: CU03 ---------------- RecordType 2 characters Company 2 characters CustomerNumber 7 characters Phone 10 characters AB_CustomerData.txt ------------------- CU01AB0001234ABC Company CU02AB0001234SmalltownAnywhere CU03AB00012342135551212 CU01AB0002345Unbrella Corp CU02AB0002345SmalltownAnywhere CU03AB00023452135551213 CU01AB0003456MegaCorp CU02AB0003456SmalltownAnywhere CU03AB00034562135551214 CD_CustomerData.txt ------------------- CU01CD0001234Jake's Widgets CU02CD0001234SmalltownAnywhere CU03CD00012342134441313 CU01CD0005678Jane's Doohickies CU02CD0005678SmalltownAnywhere CU03CD00056782135551314 CU01CD0006789Frank's Thingamabobs CU02CD0006789SmalltownAnywhere CU03CD00067892135551315 My end result is to have this in my production table: | Company | CustomerNumber | CustomerName | City | State | Phone| |-|-|-|-|-|-| | AB | 0001234 | ABC Company | Smalltown | Anywhere | 2135551212| | AB | 0002345 | Umbrella Corp | Smalltown | Anywhere | 2135551213| | AB | 0003456 | MegaCorp | Smalltown | Anywhere | 2135551214| | CD | 0001234 | Jake's Widgets | Smalltown | Anywhere | 2135551313| | CD | 0005678 | Jane's Doohickies | Smalltown | Anywhere | 2135551314| | CD | 0006789 | Frank's Thingamabobs | Smalltown | Anywhere | 2135551315| I have a ForEach container to loop through these files in my directory, and do the following: - load the file into a pre-staging table - process the customer record type (CU01, CU02, CU03 for each customer) into record-type specific staging tables (ie: record-type CU01 goes to a CU01 staging table, etc) - merge the record types into one larger staging table, containing all records - merge join the staging table and the production table, to prepare for upserting - upsert the production table My conditional splits are defined as follows:

INSERT: (ISNULL(Production_CustomerNumber) && 
!ISNULL(Staging_CustomerNumber)) && (ISNULL(Production_Company) && 
!ISNULL(Staging_Company))

UPDATE: (!ISNULL(Production_CustomerNumber) && 
!ISNULL(Staging_CustomerNumber)) && (!ISNULL(Production_Company) && 
!ISNULL(Staging_Company))

DELETE: (!ISNULL(Production_CustomerNumber) && 
ISNULL(Staging_CustomerNumber)) && (!ISNULL(Production_Company) && 
ISNULL(Staging_Company))

On the first pass of the ForEach container, the data from the first company file loads correctly all the way through to production. However, on the second pass of the ForEach container, any data pre-existing in the production table gets deleted. I am almost positive it is because of my conditional split definitions, but I can't seem to figure out where.

There is a [similar question](https://stackoverflow.com/questions/52283031/running-a-mysql-insert-when-update-fails) , 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,
  revoked bigint NOT NULL DEFAULT '0',
  created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id,serial,revoked)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci

I want to insert into the table with a pair of (id, serial), if **neither** of id or serial appears in the table. So, I think I might do this:

UPDATE device SET updated='' WHERE id='' OR 
serial='' ON UPDATE FAIL INSERT (id,serial,...) VALUES (...)

The **ON UPDATE FAIL...** part is hypothetical. I know that MySQL does not have this syntax. So I want a **transactional** way to accomplish this. Note that the PRIMARY KEY cannot be modified, although adding other indexes is allowed, without change business logic, of course.

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 for rowid
  , slug text
  , text text
  
  , CONSTRAINT "itId" UNIQUE("itId")
  , CONSTRAINT "slug" UNIQUE("slug")
);

-- fill the table:
insert into it
    (itId, slug, text)
values
    (null, 'a', 'letter a')
  , (null, 'b', 'letter b')
  , (null, 'c', 'letter c')
;

| itId | slug | text | | ---- | ---- | -------- | | 1 | a | letter a | | 2 | b | letter b | | 3 | c | letter c | Then, here's an attempt of batch upsert of rows using different keys:

insert into it
    (itId, slug, text)
values
    (null, 'a', 'latin letter a') -- update text by slug
  , (   2, 'β', 'greek letter beta') -- update slug and text by primary key
  , (   9, 'c', 'latin letter c') -- update primary key and text by slug
on conflict (itId) do update set
    text = coalesce(excluded.text, text)
  , itId = itId -- save current value for autoincremented column
  , slug = coalesce(excluded.slug, slug)
on conflict (slug) do update set
    text = coalesce(excluded.text, text)
  , itId = coalesce(excluded.itId, itId) -- here's a trouble!

(I'm using [SQLite 3.35](https://www.sqlite.org/releaselog/3_35_4.html) , which allows multiple on conflict clauses) | itId | slug | text | | ---- | ---- | ----------------- | | 2 | β | greek letter beta | | **4**| a | latin letter a | | 9 | c | latin letter c | As you can see, b changed to β -- that's okay; c has changed its _itId_ according to upserted value 3 -> 9. But there's a trouble with a (was matched by slug): _excluded.itId_ resolves with a value 4 instead of null within second on conflict clause. It seems that the value of _itId_ for row a is replaced with the next available autoincrement value for column _itId_ (an alias of _rowid_). Expectation: itId = coalesce(excluded.itId, itId) => coalesce(null, 1) => 1: | itId | slug | text | | ---- | ---- | ----------------- | | **1**| a | latin letter a | | 2 | β | greek letter beta | | 9 | c | latin letter c | Is there a way to achieve such a result using insert ... on conflict do update for all of these cases? [Online playground](https://sqlime.org/#deta:rwfs8hlesabw)

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 query that pulls data from other tables in the db, it throws a Sequelize error. SequelizeDatabaseError: relation "roleAllocations" does not exist I've tried to find any spelling errors, no luck. From what I've found online it's said to be a trigger in the database that might be changing the name of the table. There seems to be no triggers in the database. PostgreSQL@9.5.13

I need to upsert data to a PostgreSQL database from a high traffic application which needs to be optimized for write performance. The different rows to upsert in a batch will have values for different columns. They are not full updates of all columns. And these would be upserts so the rows would need to be inserted or updated. My idea is to do an INSERT ON CONFLICT UPDATE where in the update part I'd use CASE WHEN conditions to write into the update part the data to update to for each row, based on the id's. I cannot simply set the columns to EXCLUDED.column_name as not all incoming rows will have all rows set. Some will have NULLs. I would like to know: 1. Is this an ok way of going about this (see example below)? 2. Are there performance issues in doing this the way it is shown below? Is the size of the query going to affect performance? Or using those CASE conditions in the update part? Other performance issues? 3. Is there a way to pass the parameters to the database just once instead of repeating them for the insert and update parts? Would using named parameters work for this or are they also passed twice? 4. If this is not the best way to do this, how would you go about it? Is there a standard way of doing a batch upsert with this kind of data with different columns provided for different rows to insert? **Example of what my idea for doing this is:** **Schema (PostgreSQL v16)**

CREATE TABLE employees (emp_id INTEGER, name TEXT, department TEXT,
PRIMARY KEY (emp_id));

--- **Query #1**

INSERT INTO employees VALUES (1, 'john', 'sales');

--- **Query #2**

INSERT INTO employees (emp_id, name, department) VALUES (1, DEFAULT, 'it'), 
(2, 'jack', 'sales') 
ON CONFLICT (emp_id) DO UPDATE SET name = CASE 
WHEN employees.emp_id=1 THEN employees.name 
WHEN employees.emp_id=2 THEN 'jack' END, 
department = CASE WHEN employees.emp_id=1 THEN 'it' 
WHEN employees.emp_id=2 THEN 'sales' END
WHERE employees.emp_id IN (1, 2);

**Expectation is that Query #2 inserted new employee jack and updated employee john's department to 'it'** **Query #3** SELECT * FROM employees; | emp_id | name | department | | ------ | ---- | ---------- | | 1 | john | it | | 2 | jack | sales | ---

CREATE TABLE SourceProducts(
    ProductID		INT,
    ProductName		VARCHAR(50),
    Price			DECIMAL(9,2)
)
GO
    
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(1,'Table',100)
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(2,'Desk',80)
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(3,'Chair',50)
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(4,'Computer',300)
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(7,'Monitor',400)
GO
    
CREATE TABLE TargetProducts(
    ProductID		INT,
    ProductName		VARCHAR(50),
    Price			DECIMAL(9,2)
)
GO
    
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(1,'Table',100)
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(2,'Desk',180)
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(5,'Bed',50)
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(6,'Cupboard',300)
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(7,'Monitor',900)
GO

Currently I am using the above data as a sample to understand how merge functionality operates. I am using the below code to upsert , but I have a condition that it should update only monitor not desk (I may replace this with other condition like todays date)

MERGE TargetProducts AS Target
    USING SourceProducts	AS Source
    ON Source.ProductID = Target.ProductID

	-- For Updates
    WHEN MATCHED 
	THEN UPDATE SET
        Target.ProductName	= Source.ProductName,
        Target.Price		= Source.Price
    
    -- For Inserts
    WHEN NOT MATCHED BY Target THEN
        INSERT (ProductID,ProductName, Price) 
        VALUES (Source.ProductID,Source.ProductName, Source.Price);
    
		MERGE INTO TargetProducts AS target
		USING SourceProducts AS source
		ON target.ProductID = source.ProductID
			WHEN MATCHED 
			AND source.ProductName = 'Monitor' 
			THEN
				-- Update existing rows
				UPDATE SET 
				Target.ProductName	= Source.ProductName,
				Target.Price		= Source.Price
			WHEN NOT MATCHED 			
			THEN
				-- Insert new rows
				INSERT (ProductID,ProductName, Price) 
				VALUES (Source.ProductID,Source.ProductName, Source.Price);

However , the condition isn't making any difference in this case. Where I am going wrong.

Suppose we have the following table: CREATE TABLE names( id SERIAL NOT NULL, CONSTRAINT names__pk PRIMARY KEY(id), name TEXT NOT NULL, CONSTRAINT names__name__unq UNIQUE(name) ); INSERT INTO names(name) VALUES('apple') ON CONFLICT(name) DO NOTHING RETURNING id; When a row does not exist, RETURNING clause works, but if there already is a row, the query returns nothing. Is there a way to modify the query so that it always returns the field id?

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,
  price_cents int unsigned NOT NULL,
  quantity smallint NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY idx_products_unique (user_id,article_id,price_cents),
  KEY fk_products_article (article_id),
  CONSTRAINT fk_products_article FOREIGN KEY (article_id) REFERENCES articles (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_products_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

Now I can do INSERT with this query:

INSERT INTO products (user_id,article_id,price_cents,quantity) 
VALUES (1,1,200,1) 
ON DUPLICATE KEY UPDATE price_cents=VALUES(price_cents),quantity=quantity+VALUES(quantity)

So now I have 1 product (ID 1) with quantity 1 and price 200. Now I insert 2 more products with:

INSERT INTO products (user_id,article_id,price_cents,quantity) 
VALUES (1,1,200,1),(1,1,199,1) 
ON DUPLICATE KEY UPDATE price_cents=VALUES(price_cents),quantity=quantity+VALUES(quantity)

Now I have 2 products, one (ID 1) with quantity 2 and price 200 and the other (ID 2) with quantity 1 and price 199. Good. The problem comes now: I want to update the product with price 199 and set a new price to 200. What I do is:

INSERT INTO products (id,user_id,article_id,price_cents,quantity) 
VALUES (2,1,1,200) 
ON DUPLICATE KEY UPDATE price_cents=VALUES(price_cents),quantity=quantity+VALUES(quantity)

and what I would like is a single product with id 1, price 200, and quantity 3, but I get Number:0x426, Message: "Duplicate entry '1-1-200' for key 'products.idx_products_unique' because MySQL does not delete the product with ID 2. Is there a way to achieve this in MySQL (keep in mind that I want to perform these operations in bulk)?

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.g. I have in db: 1 a_value b_value c_value d_value and I'm upserting INSERT INTO MYTABLE(a, b, c, d) values ('a_value', 'b_value', 'c_value', 'd_value__new') on conflict (a, b, c, d) DO UPDATE SET d= excluded.d; But this creates new entry when I expected that it will update d_value to d_value_new on existing entry. What am I doing wrong? See example here: https://dbfiddle.uk/nL7fRpt_

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 a unique key index Insert Into newtable (Code) Values ('001') On Conflict(Code) Do Nothing --> Seq value is 1 Insert Into newtable (Code) Values ('001') On Conflict(Code) Do Nothing Insert Into newtable (Code) Values ('001') On Conflict(Code) Do Nothing Insert Into newtable (Code) Values ('002') On Conflict(Code) Do Nothing --> Seq value is 4 Why is Seq 4? Is there any way to increase value on successful inserts only?

This is a very common problem, faced by virtually every DBA who has to responsibilities to both application and BI teams. Consider the following: 1. You have two T-SQL **servers**, Production and Reporting. 2. Every day, at 6am, you want to copy new data from Production to Reporting. There may be filters or some other ETL done in this process. 3. You don't want to copy any more data than you need to, so ideally you want a MERGE of some sort. **What are the idiomatic solutions to this problem?** In my own research, I have found: - Linked Servers - They are very slow when doing upserts and are prone to sending your whole table over the network when it only needed a few rows. - SSIS - Painfully unportable, very old, and forces you to jump through extra hoops like putting staging tables on both servers. I hope that there is something better.

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 values])..., will the trigger run sequentially on the table_a rows in the order they were stated? In other words, will the upsert into table_b be done for row 1, then for row 2, in the order they were given in VALUES? (I ask because, if so, reordering the VALUES may help me solve a deadlock on the upsert.)

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
  score = NEW.score,
  updated_at = NEW.inserted_at
WHERE NEW.inserted_at > current_scores.updated_at;

It has been suggested to me that duplicating the WHERE from the ON CONFLICT as an index predicate, like this:

INSERT INTO 
  current_scores (user_id, score, updated_at)
VALUES 
  (NEW.user_id, NEW.score, NEW.inserted_at)
ON CONFLICT (user_id)

-- added index predicate
WHERE NEW.inserted_at > current_scores.updated_at

DO UPDATE SET
  score = NEW.score,
  updated_at = NEW.inserted_at
WHERE NEW.inserted_at > current_scores.updated_at;

...could improve performance by reducing the frequency that ON CONFLICT needs to run. But I don't understand why this might be the case. If ON CONFLICT is expensive, this seems like something the database could figure out on its own. Can an index predicate be used to reduce conflict resolution and improve performance?

The [docs on INSERT](https://www.postgresql.org/docs/16/sql-insert.html) 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 without a predicate) will be inferred (and thus used by ON CONFLICT) if such an index satisfying every other criteria is available. I'm not sure I understand this. I **think** it's saying that if I have a non-partial index like this:

CREATE UNIQUE INDEX ON managers (department_id);

and I write an insert with ON CONFLICT (department_id), it will infer that it should use the index above. But if I have a partial index like this:

CREATE UNIQUE INDEX ON managers (department_id) WHERE active = true;

then I need to use the index_predicate to direct it to use the partial index, like ON CONFLICT (department_id) WHERE active = true. Is that correct?

I'm trying to use SQLite's upsert syntax as described [in their documentation](https://www.sqlite.org/lang_UPSERT.html) However I get the following error, in both WebSql (SQLite v3.41.2) and SQLite on Android (SQLite v3.40.0)

could not prepare statement (1 near \"DO\": syntax error)

The error suggests this syntax isn't supported but UPSERT support was added in 3.24.0. I also tested the exact query I'm using on [dbFiddle](https://dbfiddle.uk/LC7RWg2g) and it runs fine, I'm lost! /edit The query was in the dbFiddle, but for clarity, I'm using [Ionic](https://ionicframework.com/) with the [SQLite plugin](https://ionicframework.com/docs/v5/native/sqlite) . Given the following table CREATE TABLE Drawings( id INTEGER NOT NULL UNIQUE, title TEXT NOT NULL, fileId TEXT NOT NULL, documentUrl TEXT, PRIMARY KEY(id)) Running the following query INSERT INTO Drawings(id, title, fileId, documentURL) VALUES(1,'asd','asd123123asd','qwefdfg') ON CONFLICT (id) DO UPDATE SET title = excluded.title, fileId = excluded.fileId, documentURL = excluded.documentUrl Yields the error

could not prepare statement (1 near \"DO\": syntax error)

Basically it seems that the ON CONFLICT DO UPDATE isn't supported but that was added in SQLite 3.24.0 and I'm testing in 3.41.2 (WebSQL) and 3.40.0 (Android API 34)