Hot answers tagged

34

You can try this IF EXISTS(select * from test where id=30122) update test set name='john' where id=3012 ELSE insert into test(name) values('john'); Other approach for better performance is update test set name='john' where id=3012 IF @@ROWCOUNT=0 insert into test(name) values('john'); and also read this bad habits to kick on schema prefix


27

UPDATE (2015-08-20): There is now an official implementation for handling upserts through the use of ON CONFLICT DO UPDATE (official documentation). At the time of this writing, this feature currently resides in PostgreSQL 9.5 Alpha 2, which is available for download here: Postgres source directories. Here is an example, assuming item_id is your Primary ...


22

PostgreSQL now has UPSERT. The preferred method according a similar StackOverflow question is currently the following: CREATE TABLE db (a INT PRIMARY KEY, b TEXT); CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ BEGIN LOOP -- first try to update the key UPDATE db SET b = data WHERE a = key; IF found THEN ...


19

Multiple problems. Your setup, extended: CREATE TABLE a ( pk_a int PRIMARY KEY , a int , comment text -- added column to make effect clear ); CREATE TABLE b ( pk_b int PRIMARY KEY , b int , comment text ); INSERT INTO a VALUES (1, 11, 'comment from a') , (2, 22, 'comment from a'); INSERT INTO b VALUES (1, 77, 'comment from b'); ...


14

I think I probably meant to add that comment on the prior answer, about two separate statements. It was over a year ago, so I'm not totally sure anymore. The wCTE based query doesn't really solve the problem it's supposed to, but upon reviewing it again over a year later I don't see the possibility of lost updates in the wCTE version. (Note that all of ...


14

Assuming SQL Server 2008 or later, you could use MERGE: Table CREATE TABLE dbo.Test ( id integer NOT NULL, name varchar(30) NULL, CONSTRAINT PK_dbo_Test__id PRIMARY KEY CLUSTERED (id) ); Query MERGE dbo.Test WITH (SERIALIZABLE) AS T USING (VALUES (3012, 'john')) AS U (id, name) ON U.id = T.id WHEN MATCHED THEN UPDATE SET T....


13

Clarify ON CONFLICT DO UPDATE behavior Consider the manual here: For each individual row proposed for insertion, either the insertion proceeds, or, if an arbiter constraint or index specified by conflict_target is violated, the alternative conflict_action is taken. Bold emphasis mine. So you do not have to repeat predicates for columns included in ...


12

I think the simple answer to this is no. MERGE was Microsoft's answer to the more convoluted UPSERT logic. And you didn't even list the worst approach: IF (SELECT COUNT ... ) > 0 UPDATE ELSE INSERT I just threw up in my mouth a little typing that, but it's actually the one I see most often. In any case, if MERGE is not flexible or powerful ...


11

Whether the source is XML or a TVP does not make a huge difference. The overall operation is essentially: UPDATE existing rows INSERT missing rows You do it in that order because if you INSERT first, then all rows exist to get the UPDATE and you will do repeated work for any rows that were just inserted. Beyond that there are different ways to accomplish ...


11

From the docs, conflict_action specifies an alternative ON CONFLICT action. It can be either DO NOTHING, or a DO UPDATE clause specifying the exact details of the UPDATE action to be performed in case of a conflict. The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed ...


10

Upsert on partitioned tables is not implemented in versions earlier than Postgres 11. In Postgres 9.6: INSERT statements with ON CONFLICT clauses are unlikely to work as expected, as the ON CONFLICT action is only taken in case of unique violations on the specified target relation, not its child relations. Declarative partitioning does not resolve the ...


9

As far as I understand it you are not actually talking about an UPSERT here just combining two different CRUD operations in one stored procedure. CREATE PROC InsertOrUpdateYourTable @Id int = NULL OUTPUT, @Foo INT, @Bar VARCHAR(10) AS IF @Id IS NULL BEGIN INSERT INTO ...


8

This will work (as far as I tested) in all 3 cases, if the to-be-inserted values are all new or all already in the table or a mix: WITH val (name) AS ( VALUES -- rows to be inserted ('foo'), ('bar'), ('zzz') ), ins AS ( INSERT INTO tag (name) SELECT name FROM val ON ...


8

You can use the EXCLUDED keyword to access the values passed to INSERT. No need to pass them twice: insert_sql = ''' INSERT INTO {t} (id,col1, col2, col3) VALUES (%s, %s, NULLIF(%s, 'nan'), NULLIF(%s, 'nan')) ON CONFLICT (id) DO UPDATE SET (col1, col2, col3) = (EXCLUDED.col1, EXCLUDED.col2, EXCLUDED.col3) ; ...


7

Three things leap out: Your DELETE is on the 2nd column (RespondentID) of the current PK which means a scan, not a seek. Pointless ROWLOCK hint Your "UPSERT" pattern is not concurrency safe. The test for existence may pass for 2 overlapping (in time) concurrent threads giving an error. To fix Reverse your PK order in DEXTable to (RespondentID, ExportID). ...


7

This will insert new Name fields that were not previously in the Matrix table. INSERT IGNORE INTO AdminAccounts (Name) SELECT Name FROM Matrix; If you do not trust INSERT IGNORE, there is an alternative where you can manifest the new Name values before inserting them: CREATE TABLE NewName SELECT Name FROM Matrix WHERE 1=2; INSERT INTO NewName SELECT ...


7

Answer The error occurs here: CASE tmp_code WHEN COALESCE(tmp_code,0)=0 THEN Would have to be CASE WHEN COALESCE(tmp_code,0)=0 THEN You are mixing the two different syntax variants of PL/pgSQL CASE ("simple case" vs. "searched case") in an incompatible way. There is another error: update netcen.test set test=myobj.test, testname=myobj.testname ...


7

I think the problem is that you don't have a partial index and the ON CONFLICT syntax doesn't match the test_upsert_upsert_id_idx index but the other unique constraint. If you define the index as partial (with WHERE test_field IS NULL): CREATE UNIQUE INDEX test_upsert_upsert_id_idx ON public.test_upsert USING btree (name COLLATE pg_catalog."default", ...


6

While klin is technically right in his answer about how to fix your current function, let me question your whole approach. What you try to achieve is called 'UPSERT' and with PostgreSQL 9.1 (which offers writable CTEs) you have a very simple way to achieve this. I omitted the function definition for the sake of clarity, but you can easily wrap it in a ...


6

When doing upserts in PostgreSQL 9.5+ you must refer to the excluded data (that which failed to insert) by the alias excluded. Also, the on conflict option must refer to the key: (pk_b) rather than (b). Eg. insert into table_b (pk_b, b) select pk_a,a from table_a on conflict (pk_b) do update set b=excluded.b; For more information refer to the official ...


6

You can use merge with the output clause. Temp table: create table #foo ( id int unique, chr char(1) ); insert into #foo(id, chr) values (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E'); Merge: with t1 as ( select T.id, T.chr from (values (3, 'M'), (4, 'N'), (5, 'O'), (6, 'P'), ...


5

UPSERT 1 row at a time with function A "table-function" is a function returning a set of rows (acting like a table when called with SELECT * FROM myfunc()). What you have is not a table-function. Since nothing is returned you can use a simple call: SELECT merge_vehicles(vid, cid, vname, reg_no, name, name_1st) FROM ( VALUES (2335, 55, '246BDH', '...


5

You need to use a second CTE for the INSERT: WITH upsert AS ( UPDATE tbl SET a = 2 WHERE a = 1 RETURNING tbl.* ), inserted AS ( INSERT INTO tbl (a) SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert ) RETURNING * ) select * from upsert union all select * from inserted


5

I think yes. Haven't tried with actual INSERTs or UPDATEs yet, but this function works: CREATE TABLE a (id integer); CREATE OR REPLACE FUNCTION arraytest2(rec a[]) RETURNS SETOF integer AS $body$ SELECT b.* FROM unnest($1) b; $body$ LANGUAGE sql; This way you can write your INSERT statement as INSERT INTO tbl_weightment SELECT (r).* FROM unnest(rec) r; ...


5

+1 for @gbn's explanation of the likely cause but I'm not a fan of the suggested INSERT/UPDATE pattern, unless an update is truly the exceptional case (i.e. 99.99% of the time an insert occurs). My preferred approach has always been: BEGIN TRANSACTION UPDATE DEXTable WITH (UPDLOCK, HOLDLOCK) SET Exported = getdate() WHERE ExportID = @ExportID AND ...


5

INSERT INTO AdminAccounts (Name) SELECT t1.name FROM Matrix t1 WHERE NOT EXISTS(SELECT id FROM AdminAccounts t2 WHERE t2.Name = t1.Name)


5

Not possible, because There is currently no direct way in Postgres 9.6, because: In the UPDATE, only the special EXCLUDED row is visible (in addition to the updated row). There is no FROM clause allowed to join in additional tables. The EXCLUDED row is exactly the state of the would-be inserted row that was rejected by conflict. Exactly the columns of the ...


5

PostgreSQL 11 supports INSERT INTO ... ON CONFLICT with partitioned tables: CREATE TABLE o(id INT PRIMARY KEY, i INT) PARTITION BY RANGE (id); CREATE TABLE o1 PARTITION OF o FOR VALUES FROM (1) TO (1000); CREATE TABLE o2 PARTITION OF o FOR VALUES FROM (1000) TO (2000); INSERT INTO o(id, i) VALUES (1,1),(2,2),(1500,1500); INSERT INTO o(id, i) VALUES (1500,...


5

The ON CONFLICT clause can prevent duplicate key errors. There can still be friction with concurrent transactions trying to enter the same keys or update the same rows. So it's no insurance against deadlocks. Most importantly, add a consistent order to input rows with ORDER BY. To make sure the order is enforced I use a CTE, which materializes the result. (...


5

Your example suggests duplicate rows in the VALUES clause itself - which would result in: ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time Can be solved by folding duplicates in the input. See: How to include excluded rows in RETURNING from INSERT … ON CONFLICT But a related problem affects your outlined query as a whole, and that'...


Only top voted, non community-wiki answers of a minimum length are eligible