Upsert on PostgreSQL

gistfile1.sql

/* ref: http://lets.postgresql.jp/documents/technical/9.1/1 */

create table members (
    id      serial        primary key
  , name    varchar(255)  not null unique
  , gender  char(1)       not null    -- 'F': female, 'M': male
  , role    varchar(255)
);

-- Run 'update' statement, or run 'insert' statement when failed.
-- 先に update 文を実行し、それが失敗したら insert 文を実行する
with _updated as (
         update members
         set gender = 'F'
           , role   = 'Leader'
         where members.name = 'Haruhi'
         returning true as result   -- Return true when updated successfully, or null when failed.
                                    -- 該当行が見つかって update が成功したら true を返し、そうでなければ null を返す
     )
insert into members (name, gender, role)
select 'Haruhi', 'F', 'Leader'
where ( select result from _updated ) is null  -- Run 'insert' statement only when 'update' statment is failed.
                                               -- update 文が成功しなかったときだけ insert 文を実行
;