PostgreSQL 에서는 merge 구문을 아직 지원하지 않습니다.
하지만, function 을 작성하거나 INSERT 와 UPDATE 구문으로의 SQL 분리없이
아래와 같이 처리할수 있습니다.
예제를 살펴보시죠.
SQL> => create table t (n int primary key, c varchar(10));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index “t_pkey” for table “t”
insert into t values (1, ‘hello’);
CREATE TABLE
SQL> insert into t values (1, ‘hello’);
INSERT 0 1
SQL> select * from t;
n | c
—+——-
1 | hello
(1 row)
SQL> with
SQL> upsert
SQL> as
SQL> (select 2 as n, ‘world’::varchar as c),
SQL> update_option
SQL> as
SQL> (update t set c = upsert.c from upsert where t.n = upsert.n)
SQL> insert into t
SQL> select upsert.n, upsert.c from upsert where not exists(select 1 from t where t.n = upsert.n);
INSERT 0 1
SQL> select * from t order by n;
n | c
—+——-
1 | hello
2 | world
(2 rows)
SQL> with
SQL> upsert
SQL> as
SQL> (select 1 as n, ‘goodbye’::varchar as c),
SQL> update_option
SQL> as
SQL> (update t set c = upsert.c from upsert where t.n = upsert.n)
SQL> insert into t
SQL> select upsert.n, upsert.c from upsert where not exists(select 1 from t where t.n = upsert.n);
INSERT 0 0
SQL> select * from t order by n;
n | c
—+———
1 | goodbye
2 | world
(2 rows)
위 예제는
http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/
에서 발췌했습니다.
