CODEWITHSUNDEEP
sql-serverpostgresqlupsert
Mohit Singla
Mohit Singla

Reputation: 15

Alternative of REPLACE (which is provided in Microsoft SQL Server) in Postgres

I need something like

REPLACE into table (id, name, age) values(1, "A", 19)

in Postgres. The above query only works in Microsoft SQL Server. What I need is to get a batch insert query in PostgreSQL, and if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new batch rows are inserted.

i need to make something like following work- CREATE TABLE conversation (cid int primary key, conversationcreateat int,ClosedDate varchar);

with abc as( INSERT INTO conversation(cid, ClosedDate,conversationcreateat) values(1, 'a', 19),(1, 'b', 20)

ON CONFLICT (cid) DO UPDATE SET ClosedDate = currentRow.ClosedDate, conversationcreateat = currentRow.conversationcreateat ) select 1;

this is bulk upsert

Upvotes: 0

Views: 305

Answers (1)

mwalter
mwalter

Reputation: 132

Look if it works for you:

    INSERT INTO table1 (id , description , createdat, lastupdate ) 
    VALUES (1 , 'test' , now() , now() )
    ON CONFLICT(id) DO UPDATE SET descricao =EXCLUDED.description, createdat=EXCLUDED.createdat , lastupdate= EXCLUDED.lastupdate 
--returning id , createdat;

this will garantee your insert gets done , the EXCLUDED. reuses the original insert values can be other value, the conflict columns tells to treat only the specified columns errors and can even return a value as it is commented.

Upvotes: 0

Related Questions