回答編集履歴
1
追記
test
CHANGED
|
@@ -9,3 +9,39 @@
|
|
|
9
9
|
Type定義を行いその構造体にCASTして比較を行うと、記述は簡潔になります。
|
|
10
10
|
|
|
11
11
|
[CREATE TYPE](https://www.postgresql.jp/document/11/html/sql-createtype.html)
|
|
12
|
+
|
|
13
|
+
|
|
14
|
+
|
|
15
|
+
追記
|
|
16
|
+
|
|
17
|
+
--
|
|
18
|
+
|
|
19
|
+
full outer join (全外部結合）を使用して比較します。
|
|
20
|
+
|
|
21
|
+
```SQL
|
|
22
|
+
|
|
23
|
+
with list as (
|
|
24
|
+
|
|
25
|
+
select * from (values
|
|
26
|
+
|
|
27
|
+
(1, 'ピアノ', '2019/01/01 01:01:01'::timestamp)
|
|
28
|
+
|
|
29
|
+
,(2, 'カバン', '2019/01/31 10:01:01'::timestamp)
|
|
30
|
+
|
|
31
|
+
,(3, '筆箱', '2019/02/01 10:01:01'::timestamp)
|
|
32
|
+
|
|
33
|
+
) as w(id, name, last_update_time)
|
|
34
|
+
|
|
35
|
+
)
|
|
36
|
+
|
|
37
|
+
select coalesce(tbl.id, list.id) as id
|
|
38
|
+
|
|
39
|
+
from tbl full outer join list
|
|
40
|
+
|
|
41
|
+
on tbl.id=list.id
|
|
42
|
+
|
|
43
|
+
where tbl.id is null
|
|
44
|
+
|
|
45
|
+
or tbl.last_update_time!=list.last_update_time
|
|
46
|
+
|
|
47
|
+
```
|