PostgreSQL code to implement the replace into function

Time:2022-5-8

PostgreSQL 9.5-

Implement with function or with


create table test(id int primary key, info text, crt_time timestamp);
with upsert as (update test set info='test',crt_time=now() where id=1 returning *) insert into test select 1,'test',now() where not exists (select 1 from upsert where id=1); 

PostgreSQL 9.5+

PostgreSQL 9.5 introduces a new function, update (insert on conflict do), which returns directly when a constraint error is encountered during insertion, or executes update instead.

INSERT INTO table_ Name values () on conflict (unique index field) do
UPDATE ...

Supplement: summary of select into usage in PostgreSQL

In normal SQL, PostgreSQL supports seek into……

However, select is not supported during dynamic call into……

For example:


create or replace FUNCTION test () RETURNS void AS
$body$
DECLARE
toalnum int;
BEGIN
execute 'select sum(colname) into totalnum';
return;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

The above situation will report an error…..

As this is amended as follows


create or replace FUNCTION test () RETURNS void AS
$body$
DECLARE
toalnum int;
BEGIN
execute 'select sum(colname)' into totalnum;
return;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

The above is my personal experience. I hope I can give you a reference, and I hope you can support developpaer. If you have any mistakes or don’t consider completely, please don’t hesitate to comment.

Recommended Today

Hadoop study notes day1

First acquaintance with Hadoop Hadoop core components Hadoop HDFS (distributed file storage system): solve massive data storage Hadoop YARN (cluster resource management and task scheduling framework): Solve resource task scheduling Hadoop MapReduce (distributed computing framework): solve massive data computing Overall overview of Hadoop cluster Hadoop cluster includes two clusters: HDFS cluster, YARN cluster The two […]