Shulou(Shulou.com)06/01 Report--

There is a new business requirement that needs to be met when inserting data into postgresql: if the record exists in the database, modify it; if it does not exist, insert it.

Merge can be used to solve the problem in Oracle. No similar operation has been done in Pg before. Baidu looked it up and found that PostgreSQL 9.5 has brought a new feature: UPSERT. UPSERT is the abbreviation of INSERT, ON CONFLICT UPDATE, which simply means: insert data, write when normal, and update when primary key conflicts.

For example, take a look at:

A test table with the following structure:

Yqm=#\ d student

Table "public.student"

Column | Type | Modifiers

-+

Id | integer |

Name | character varying (20) |

Indexes:

"id_cons" UNIQUE CONSTRAINT, btree (id)

The current figures are as follows:

Yqm=# select * from student

Id | name

-- +-

| 1 | a |

2 | b

3 | c

(3 rows)

The table to be inserted must have a uniqueness constraint, otherwise the following error will be reported:

Yqm=# insert into student values (4) on conflict (id) do update set name='as'

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

If there is such an error, you need to create a uniqueness constraint

Yqm=# alter table student add constraint id_cons unique (id)

ALTER TABLE

Insert again

Yqm=# insert into student values (4) on conflict (id) do update set name='as'

INSERT 0 1

Check the results. It has been added.

Yqm=# select * from student

Id | name

-- +-

| 1 | a |

2 | b

3 | c

4 | d

(4 rows)

Execute it again, and a change operation will be made.

Yqm=# insert into student values (4) on conflict (id) do update set name='as'

INSERT 0 1

Yqm=# select * from student

Id | name

-- +-

| 1 | a |

2 | b

3 | c

4 | as

(4 rows)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.