github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/upsert (about) 1 exec-ddl 2 CREATE TABLE abc ( 3 a INT NOT NULL, 4 b INT DEFAULT (10), 5 c INT AS (b + 1) STORED, 6 UNIQUE(a), 7 UNIQUE(b, c) 8 ) 9 ---- 10 11 exec-ddl 12 CREATE TABLE xyz ( 13 x INT PRIMARY KEY, 14 y INT, 15 z INT, 16 UNIQUE (y, z), 17 UNIQUE (z, y), 18 INDEX (y DESC) 19 ) 20 ---- 21 22 exec-ddl 23 CREATE TABLE uv ( 24 u INT, 25 v INT, 26 PRIMARY KEY (u, v) 27 ) 28 ---- 29 30 exec-ddl 31 CREATE TABLE noindex ( 32 x INT PRIMARY KEY, 33 y INT, 34 z INT 35 ) 36 ---- 37 38 exec-ddl 39 CREATE TABLE mutation ( 40 m INT PRIMARY KEY, 41 n INT, 42 "o:write-only" INT DEFAULT(10), 43 "p:write-only" INT AS (o + n) STORED, 44 "q:delete-only" INT AS (m * p) STORED, 45 CHECK (m > 0) 46 ) 47 ---- 48 49 exec-ddl 50 CREATE TABLE checks ( 51 a INT PRIMARY KEY CHECK (a > 0), 52 b INT, 53 c INT, 54 d INT AS (c + 1) STORED, 55 CHECK (b < d) 56 ) 57 ---- 58 59 exec-ddl 60 CREATE TABLE decimals ( 61 a DECIMAL(10,0) PRIMARY KEY CHECK (round(a) = a), 62 b DECIMAL(5,1)[] CHECK (b[0] > 1), 63 c DECIMAL(10,1) DEFAULT (1.23), 64 d DECIMAL(10,1) AS (a+c) STORED 65 ) 66 ---- 67 68 # ------------------------------------------------------------------------------ 69 # Basic tests. 70 # ------------------------------------------------------------------------------ 71 72 # Set single column, single column conflict. 73 build 74 INSERT INTO abc (a, b) 75 SELECT x, y FROM xyz 76 ON CONFLICT (a) DO 77 UPDATE SET a=5 78 ---- 79 upsert abc 80 ├── columns: <none> 81 ├── canary column: 13 82 ├── fetch columns: a:10 b:11 c:12 rowid:13 83 ├── insert-mapping: 84 │ ├── x:5 => a:1 85 │ ├── y:6 => b:2 86 │ ├── column9:9 => c:3 87 │ └── column8:8 => rowid:4 88 ├── update-mapping: 89 │ ├── upsert_a:16 => a:1 90 │ └── upsert_c:18 => c:3 91 └── project 92 ├── columns: upsert_a:16!null upsert_b:17 upsert_c:18 upsert_rowid:19 x:5!null y:6 column8:8 column9:9 a:10 b:11 c:12 rowid:13 a_new:14!null column15:15 93 ├── project 94 │ ├── columns: column15:15 x:5!null y:6 column8:8 column9:9 a:10 b:11 c:12 rowid:13 a_new:14!null 95 │ ├── project 96 │ │ ├── columns: a_new:14!null x:5!null y:6 column8:8 column9:9 a:10 b:11 c:12 rowid:13 97 │ │ ├── left-join (hash) 98 │ │ │ ├── columns: x:5!null y:6 column8:8 column9:9 a:10 b:11 c:12 rowid:13 99 │ │ │ ├── ensure-upsert-distinct-on 100 │ │ │ │ ├── columns: x:5!null y:6 column8:8 column9:9 101 │ │ │ │ ├── grouping columns: x:5!null 102 │ │ │ │ ├── project 103 │ │ │ │ │ ├── columns: column9:9 x:5!null y:6 column8:8 104 │ │ │ │ │ ├── project 105 │ │ │ │ │ │ ├── columns: column8:8 x:5!null y:6 106 │ │ │ │ │ │ ├── project 107 │ │ │ │ │ │ │ ├── columns: x:5!null y:6 108 │ │ │ │ │ │ │ └── scan xyz 109 │ │ │ │ │ │ │ └── columns: x:5!null y:6 z:7 110 │ │ │ │ │ │ └── projections 111 │ │ │ │ │ │ └── unique_rowid() [as=column8:8] 112 │ │ │ │ │ └── projections 113 │ │ │ │ │ └── y:6 + 1 [as=column9:9] 114 │ │ │ │ └── aggregations 115 │ │ │ │ ├── first-agg [as=y:6] 116 │ │ │ │ │ └── y:6 117 │ │ │ │ ├── first-agg [as=column8:8] 118 │ │ │ │ │ └── column8:8 119 │ │ │ │ └── first-agg [as=column9:9] 120 │ │ │ │ └── column9:9 121 │ │ │ ├── scan abc 122 │ │ │ │ ├── columns: a:10!null b:11 c:12 rowid:13!null 123 │ │ │ │ └── computed column expressions 124 │ │ │ │ └── c:12 125 │ │ │ │ └── b:11 + 1 126 │ │ │ └── filters 127 │ │ │ └── x:5 = a:10 128 │ │ └── projections 129 │ │ └── 5 [as=a_new:14] 130 │ └── projections 131 │ └── b:11 + 1 [as=column15:15] 132 └── projections 133 ├── CASE WHEN rowid:13 IS NULL THEN x:5 ELSE a_new:14 END [as=upsert_a:16] 134 ├── CASE WHEN rowid:13 IS NULL THEN y:6 ELSE b:11 END [as=upsert_b:17] 135 ├── CASE WHEN rowid:13 IS NULL THEN column9:9 ELSE column15:15 END [as=upsert_c:18] 136 └── CASE WHEN rowid:13 IS NULL THEN column8:8 ELSE rowid:13 END [as=upsert_rowid:19] 137 138 # Set all columns, multi-column conflict. 139 build 140 INSERT INTO abc (a, b, rowid) 141 SELECT x, y, z FROM xyz 142 ON CONFLICT (b, c) DO 143 UPDATE SET a=1, b=2, rowid=3 144 RETURNING * 145 ---- 146 project 147 ├── columns: a:1!null b:2 c:3 148 └── upsert abc 149 ├── columns: a:1!null b:2 c:3 rowid:4!null 150 ├── canary column: 12 151 ├── fetch columns: a:9 b:10 c:11 rowid:12 152 ├── insert-mapping: 153 │ ├── x:5 => a:1 154 │ ├── y:6 => b:2 155 │ ├── column8:8 => c:3 156 │ └── z:7 => rowid:4 157 ├── update-mapping: 158 │ ├── upsert_a:17 => a:1 159 │ ├── upsert_b:18 => b:2 160 │ ├── upsert_c:19 => c:3 161 │ └── upsert_rowid:20 => rowid:4 162 ├── return-mapping: 163 │ ├── upsert_a:17 => a:1 164 │ ├── upsert_b:18 => b:2 165 │ ├── upsert_c:19 => c:3 166 │ └── upsert_rowid:20 => rowid:4 167 └── project 168 ├── columns: upsert_a:17!null upsert_b:18 upsert_c:19 upsert_rowid:20 x:5!null y:6 z:7 column8:8 a:9 b:10 c:11 rowid:12 a_new:13!null b_new:14!null rowid_new:15!null column16:16!null 169 ├── project 170 │ ├── columns: column16:16!null x:5!null y:6 z:7 column8:8 a:9 b:10 c:11 rowid:12 a_new:13!null b_new:14!null rowid_new:15!null 171 │ ├── project 172 │ │ ├── columns: a_new:13!null b_new:14!null rowid_new:15!null x:5!null y:6 z:7 column8:8 a:9 b:10 c:11 rowid:12 173 │ │ ├── left-join (hash) 174 │ │ │ ├── columns: x:5!null y:6 z:7 column8:8 a:9 b:10 c:11 rowid:12 175 │ │ │ ├── ensure-upsert-distinct-on 176 │ │ │ │ ├── columns: x:5!null y:6 z:7 column8:8 177 │ │ │ │ ├── grouping columns: y:6 column8:8 178 │ │ │ │ ├── project 179 │ │ │ │ │ ├── columns: column8:8 x:5!null y:6 z:7 180 │ │ │ │ │ ├── scan xyz 181 │ │ │ │ │ │ └── columns: x:5!null y:6 z:7 182 │ │ │ │ │ └── projections 183 │ │ │ │ │ └── y:6 + 1 [as=column8:8] 184 │ │ │ │ └── aggregations 185 │ │ │ │ ├── first-agg [as=x:5] 186 │ │ │ │ │ └── x:5 187 │ │ │ │ └── first-agg [as=z:7] 188 │ │ │ │ └── z:7 189 │ │ │ ├── scan abc 190 │ │ │ │ ├── columns: a:9!null b:10 c:11 rowid:12!null 191 │ │ │ │ └── computed column expressions 192 │ │ │ │ └── c:11 193 │ │ │ │ └── b:10 + 1 194 │ │ │ └── filters 195 │ │ │ ├── y:6 = b:10 196 │ │ │ └── column8:8 = c:11 197 │ │ └── projections 198 │ │ ├── 1 [as=a_new:13] 199 │ │ ├── 2 [as=b_new:14] 200 │ │ └── 3 [as=rowid_new:15] 201 │ └── projections 202 │ └── b_new:14 + 1 [as=column16:16] 203 └── projections 204 ├── CASE WHEN rowid:12 IS NULL THEN x:5 ELSE a_new:13 END [as=upsert_a:17] 205 ├── CASE WHEN rowid:12 IS NULL THEN y:6 ELSE b_new:14 END [as=upsert_b:18] 206 ├── CASE WHEN rowid:12 IS NULL THEN column8:8 ELSE column16:16 END [as=upsert_c:19] 207 └── CASE WHEN rowid:12 IS NULL THEN z:7 ELSE rowid_new:15 END [as=upsert_rowid:20] 208 209 # UPDATE + WHERE clause. 210 build 211 INSERT INTO abc 212 SELECT x, y FROM xyz 213 ON CONFLICT (a) DO 214 UPDATE SET b=10 215 WHERE abc.a>0 216 ---- 217 upsert abc 218 ├── columns: <none> 219 ├── canary column: 13 220 ├── fetch columns: a:10 b:11 c:12 rowid:13 221 ├── insert-mapping: 222 │ ├── x:5 => a:1 223 │ ├── y:6 => b:2 224 │ ├── column9:9 => c:3 225 │ └── column8:8 => rowid:4 226 ├── update-mapping: 227 │ ├── upsert_b:17 => b:2 228 │ └── upsert_c:18 => c:3 229 └── project 230 ├── columns: upsert_a:16 upsert_b:17 upsert_c:18 upsert_rowid:19 x:5!null y:6 column8:8 column9:9 a:10 b:11 c:12 rowid:13 b_new:14!null column15:15!null 231 ├── project 232 │ ├── columns: column15:15!null x:5!null y:6 column8:8 column9:9 a:10 b:11 c:12 rowid:13 b_new:14!null 233 │ ├── project 234 │ │ ├── columns: b_new:14!null x:5!null y:6 column8:8 column9:9 a:10 b:11 c:12 rowid:13 235 │ │ ├── select 236 │ │ │ ├── columns: x:5!null y:6 column8:8 column9:9 a:10 b:11 c:12 rowid:13 237 │ │ │ ├── left-join (hash) 238 │ │ │ │ ├── columns: x:5!null y:6 column8:8 column9:9 a:10 b:11 c:12 rowid:13 239 │ │ │ │ ├── ensure-upsert-distinct-on 240 │ │ │ │ │ ├── columns: x:5!null y:6 column8:8 column9:9 241 │ │ │ │ │ ├── grouping columns: x:5!null 242 │ │ │ │ │ ├── project 243 │ │ │ │ │ │ ├── columns: column9:9 x:5!null y:6 column8:8 244 │ │ │ │ │ │ ├── project 245 │ │ │ │ │ │ │ ├── columns: column8:8 x:5!null y:6 246 │ │ │ │ │ │ │ ├── project 247 │ │ │ │ │ │ │ │ ├── columns: x:5!null y:6 248 │ │ │ │ │ │ │ │ └── scan xyz 249 │ │ │ │ │ │ │ │ └── columns: x:5!null y:6 z:7 250 │ │ │ │ │ │ │ └── projections 251 │ │ │ │ │ │ │ └── unique_rowid() [as=column8:8] 252 │ │ │ │ │ │ └── projections 253 │ │ │ │ │ │ └── y:6 + 1 [as=column9:9] 254 │ │ │ │ │ └── aggregations 255 │ │ │ │ │ ├── first-agg [as=y:6] 256 │ │ │ │ │ │ └── y:6 257 │ │ │ │ │ ├── first-agg [as=column8:8] 258 │ │ │ │ │ │ └── column8:8 259 │ │ │ │ │ └── first-agg [as=column9:9] 260 │ │ │ │ │ └── column9:9 261 │ │ │ │ ├── scan abc 262 │ │ │ │ │ ├── columns: a:10!null b:11 c:12 rowid:13!null 263 │ │ │ │ │ └── computed column expressions 264 │ │ │ │ │ └── c:12 265 │ │ │ │ │ └── b:11 + 1 266 │ │ │ │ └── filters 267 │ │ │ │ └── x:5 = a:10 268 │ │ │ └── filters 269 │ │ │ └── (rowid:13 IS NULL) OR (a:10 > 0) 270 │ │ └── projections 271 │ │ └── 10 [as=b_new:14] 272 │ └── projections 273 │ └── b_new:14 + 1 [as=column15:15] 274 └── projections 275 ├── CASE WHEN rowid:13 IS NULL THEN x:5 ELSE a:10 END [as=upsert_a:16] 276 ├── CASE WHEN rowid:13 IS NULL THEN y:6 ELSE b_new:14 END [as=upsert_b:17] 277 ├── CASE WHEN rowid:13 IS NULL THEN column9:9 ELSE column15:15 END [as=upsert_c:18] 278 └── CASE WHEN rowid:13 IS NULL THEN column8:8 ELSE rowid:13 END [as=upsert_rowid:19] 279 280 # Use RETURNING INSERT..ON CONFLICT as a FROM clause. 281 build 282 SELECT * 283 FROM [INSERT INTO abc (a, b) VALUES (1,2), (3,4) ON CONFLICT (a) DO UPDATE SET b=1 RETURNING *] 284 ORDER BY a, b DESC 285 ---- 286 sort 287 ├── columns: a:19!null b:20!null c:21!null 288 ├── ordering: +19,-20 289 └── with &1 290 ├── columns: a:19!null b:20!null c:21!null 291 ├── project 292 │ ├── columns: abc.a:1!null abc.b:2!null abc.c:3!null 293 │ └── upsert abc 294 │ ├── columns: abc.a:1!null abc.b:2!null abc.c:3!null rowid:4!null 295 │ ├── canary column: 12 296 │ ├── fetch columns: abc.a:9 abc.b:10 abc.c:11 rowid:12 297 │ ├── insert-mapping: 298 │ │ ├── column1:5 => abc.a:1 299 │ │ ├── column2:6 => abc.b:2 300 │ │ ├── column8:8 => abc.c:3 301 │ │ └── column7:7 => rowid:4 302 │ ├── update-mapping: 303 │ │ ├── upsert_b:16 => abc.b:2 304 │ │ └── upsert_c:17 => abc.c:3 305 │ ├── return-mapping: 306 │ │ ├── upsert_a:15 => abc.a:1 307 │ │ ├── upsert_b:16 => abc.b:2 308 │ │ ├── upsert_c:17 => abc.c:3 309 │ │ └── upsert_rowid:18 => rowid:4 310 │ └── project 311 │ ├── columns: upsert_a:15 upsert_b:16!null upsert_c:17!null upsert_rowid:18 column1:5!null column2:6!null column7:7 column8:8!null abc.a:9 abc.b:10 abc.c:11 rowid:12 b_new:13!null column14:14!null 312 │ ├── project 313 │ │ ├── columns: column14:14!null column1:5!null column2:6!null column7:7 column8:8!null abc.a:9 abc.b:10 abc.c:11 rowid:12 b_new:13!null 314 │ │ ├── project 315 │ │ │ ├── columns: b_new:13!null column1:5!null column2:6!null column7:7 column8:8!null abc.a:9 abc.b:10 abc.c:11 rowid:12 316 │ │ │ ├── left-join (hash) 317 │ │ │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8!null abc.a:9 abc.b:10 abc.c:11 rowid:12 318 │ │ │ │ ├── ensure-upsert-distinct-on 319 │ │ │ │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8!null 320 │ │ │ │ │ ├── grouping columns: column1:5!null 321 │ │ │ │ │ ├── project 322 │ │ │ │ │ │ ├── columns: column8:8!null column1:5!null column2:6!null column7:7 323 │ │ │ │ │ │ ├── project 324 │ │ │ │ │ │ │ ├── columns: column7:7 column1:5!null column2:6!null 325 │ │ │ │ │ │ │ ├── values 326 │ │ │ │ │ │ │ │ ├── columns: column1:5!null column2:6!null 327 │ │ │ │ │ │ │ │ ├── (1, 2) 328 │ │ │ │ │ │ │ │ └── (3, 4) 329 │ │ │ │ │ │ │ └── projections 330 │ │ │ │ │ │ │ └── unique_rowid() [as=column7:7] 331 │ │ │ │ │ │ └── projections 332 │ │ │ │ │ │ └── column2:6 + 1 [as=column8:8] 333 │ │ │ │ │ └── aggregations 334 │ │ │ │ │ ├── first-agg [as=column2:6] 335 │ │ │ │ │ │ └── column2:6 336 │ │ │ │ │ ├── first-agg [as=column7:7] 337 │ │ │ │ │ │ └── column7:7 338 │ │ │ │ │ └── first-agg [as=column8:8] 339 │ │ │ │ │ └── column8:8 340 │ │ │ │ ├── scan abc 341 │ │ │ │ │ ├── columns: abc.a:9!null abc.b:10 abc.c:11 rowid:12!null 342 │ │ │ │ │ └── computed column expressions 343 │ │ │ │ │ └── abc.c:11 344 │ │ │ │ │ └── abc.b:10 + 1 345 │ │ │ │ └── filters 346 │ │ │ │ └── column1:5 = abc.a:9 347 │ │ │ └── projections 348 │ │ │ └── 1 [as=b_new:13] 349 │ │ └── projections 350 │ │ └── b_new:13 + 1 [as=column14:14] 351 │ └── projections 352 │ ├── CASE WHEN rowid:12 IS NULL THEN column1:5 ELSE abc.a:9 END [as=upsert_a:15] 353 │ ├── CASE WHEN rowid:12 IS NULL THEN column2:6 ELSE b_new:13 END [as=upsert_b:16] 354 │ ├── CASE WHEN rowid:12 IS NULL THEN column8:8 ELSE column14:14 END [as=upsert_c:17] 355 │ └── CASE WHEN rowid:12 IS NULL THEN column7:7 ELSE rowid:12 END [as=upsert_rowid:18] 356 └── with-scan &1 357 ├── columns: a:19!null b:20!null c:21!null 358 └── mapping: 359 ├── abc.a:1 => a:19 360 ├── abc.b:2 => b:20 361 └── abc.c:3 => c:21 362 363 # Use table alias. 364 build 365 INSERT INTO abc AS tab (a, b) 366 VALUES (1, 2) 367 ON CONFLICT (a) DO 368 UPDATE SET a=tab.a*excluded.a 369 ---- 370 upsert tab 371 ├── columns: <none> 372 ├── canary column: 12 373 ├── fetch columns: a:9 b:10 c:11 rowid:12 374 ├── insert-mapping: 375 │ ├── column1:5 => a:1 376 │ ├── column2:6 => b:2 377 │ ├── column8:8 => c:3 378 │ └── column7:7 => rowid:4 379 ├── update-mapping: 380 │ ├── upsert_a:15 => a:1 381 │ └── upsert_c:17 => c:3 382 └── project 383 ├── columns: upsert_a:15 upsert_b:16 upsert_c:17 upsert_rowid:18 column1:5!null column2:6!null column7:7 column8:8!null a:9 b:10 c:11 rowid:12 a_new:13 column14:14 384 ├── project 385 │ ├── columns: column14:14 column1:5!null column2:6!null column7:7 column8:8!null a:9 b:10 c:11 rowid:12 a_new:13 386 │ ├── project 387 │ │ ├── columns: a_new:13 column1:5!null column2:6!null column7:7 column8:8!null a:9 b:10 c:11 rowid:12 388 │ │ ├── left-join (hash) 389 │ │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8!null a:9 b:10 c:11 rowid:12 390 │ │ │ ├── ensure-upsert-distinct-on 391 │ │ │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8!null 392 │ │ │ │ ├── grouping columns: column1:5!null 393 │ │ │ │ ├── project 394 │ │ │ │ │ ├── columns: column8:8!null column1:5!null column2:6!null column7:7 395 │ │ │ │ │ ├── project 396 │ │ │ │ │ │ ├── columns: column7:7 column1:5!null column2:6!null 397 │ │ │ │ │ │ ├── values 398 │ │ │ │ │ │ │ ├── columns: column1:5!null column2:6!null 399 │ │ │ │ │ │ │ └── (1, 2) 400 │ │ │ │ │ │ └── projections 401 │ │ │ │ │ │ └── unique_rowid() [as=column7:7] 402 │ │ │ │ │ └── projections 403 │ │ │ │ │ └── column2:6 + 1 [as=column8:8] 404 │ │ │ │ └── aggregations 405 │ │ │ │ ├── first-agg [as=column2:6] 406 │ │ │ │ │ └── column2:6 407 │ │ │ │ ├── first-agg [as=column7:7] 408 │ │ │ │ │ └── column7:7 409 │ │ │ │ └── first-agg [as=column8:8] 410 │ │ │ │ └── column8:8 411 │ │ │ ├── scan tab 412 │ │ │ │ ├── columns: a:9!null b:10 c:11 rowid:12!null 413 │ │ │ │ └── computed column expressions 414 │ │ │ │ └── c:11 415 │ │ │ │ └── b:10 + 1 416 │ │ │ └── filters 417 │ │ │ └── column1:5 = a:9 418 │ │ └── projections 419 │ │ └── a:9 * column1:5 [as=a_new:13] 420 │ └── projections 421 │ └── b:10 + 1 [as=column14:14] 422 └── projections 423 ├── CASE WHEN rowid:12 IS NULL THEN column1:5 ELSE a_new:13 END [as=upsert_a:15] 424 ├── CASE WHEN rowid:12 IS NULL THEN column2:6 ELSE b:10 END [as=upsert_b:16] 425 ├── CASE WHEN rowid:12 IS NULL THEN column8:8 ELSE column14:14 END [as=upsert_c:17] 426 └── CASE WHEN rowid:12 IS NULL THEN column7:7 ELSE rowid:12 END [as=upsert_rowid:18] 427 428 # Conflict columns are in different order than index key columns. 429 build 430 INSERT INTO abc (a, b) 431 VALUES (1, 2) 432 ON CONFLICT (c, b) DO 433 UPDATE SET a=5 434 ---- 435 upsert abc 436 ├── columns: <none> 437 ├── canary column: 12 438 ├── fetch columns: a:9 b:10 c:11 rowid:12 439 ├── insert-mapping: 440 │ ├── column1:5 => a:1 441 │ ├── column2:6 => b:2 442 │ ├── column8:8 => c:3 443 │ └── column7:7 => rowid:4 444 ├── update-mapping: 445 │ ├── upsert_a:15 => a:1 446 │ └── upsert_c:17 => c:3 447 └── project 448 ├── columns: upsert_a:15!null upsert_b:16 upsert_c:17 upsert_rowid:18 column1:5!null column2:6!null column7:7 column8:8!null a:9 b:10 c:11 rowid:12 a_new:13!null column14:14 449 ├── project 450 │ ├── columns: column14:14 column1:5!null column2:6!null column7:7 column8:8!null a:9 b:10 c:11 rowid:12 a_new:13!null 451 │ ├── project 452 │ │ ├── columns: a_new:13!null column1:5!null column2:6!null column7:7 column8:8!null a:9 b:10 c:11 rowid:12 453 │ │ ├── left-join (hash) 454 │ │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8!null a:9 b:10 c:11 rowid:12 455 │ │ │ ├── ensure-upsert-distinct-on 456 │ │ │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8!null 457 │ │ │ │ ├── grouping columns: column2:6!null column8:8!null 458 │ │ │ │ ├── project 459 │ │ │ │ │ ├── columns: column8:8!null column1:5!null column2:6!null column7:7 460 │ │ │ │ │ ├── project 461 │ │ │ │ │ │ ├── columns: column7:7 column1:5!null column2:6!null 462 │ │ │ │ │ │ ├── values 463 │ │ │ │ │ │ │ ├── columns: column1:5!null column2:6!null 464 │ │ │ │ │ │ │ └── (1, 2) 465 │ │ │ │ │ │ └── projections 466 │ │ │ │ │ │ └── unique_rowid() [as=column7:7] 467 │ │ │ │ │ └── projections 468 │ │ │ │ │ └── column2:6 + 1 [as=column8:8] 469 │ │ │ │ └── aggregations 470 │ │ │ │ ├── first-agg [as=column1:5] 471 │ │ │ │ │ └── column1:5 472 │ │ │ │ └── first-agg [as=column7:7] 473 │ │ │ │ └── column7:7 474 │ │ │ ├── scan abc 475 │ │ │ │ ├── columns: a:9!null b:10 c:11 rowid:12!null 476 │ │ │ │ └── computed column expressions 477 │ │ │ │ └── c:11 478 │ │ │ │ └── b:10 + 1 479 │ │ │ └── filters 480 │ │ │ ├── column2:6 = b:10 481 │ │ │ └── column8:8 = c:11 482 │ │ └── projections 483 │ │ └── 5 [as=a_new:13] 484 │ └── projections 485 │ └── b:10 + 1 [as=column14:14] 486 └── projections 487 ├── CASE WHEN rowid:12 IS NULL THEN column1:5 ELSE a_new:13 END [as=upsert_a:15] 488 ├── CASE WHEN rowid:12 IS NULL THEN column2:6 ELSE b:10 END [as=upsert_b:16] 489 ├── CASE WHEN rowid:12 IS NULL THEN column8:8 ELSE column14:14 END [as=upsert_c:17] 490 └── CASE WHEN rowid:12 IS NULL THEN column7:7 ELSE rowid:12 END [as=upsert_rowid:18] 491 492 # Conflict columns don't match unique index (too few columns). 493 build 494 INSERT INTO abc (a, b) 495 VALUES (1, 2) 496 ON CONFLICT (b) DO 497 UPDATE SET a=5 498 ---- 499 error (42P10): there is no unique or exclusion constraint matching the ON CONFLICT specification 500 501 # Conflict columns don't match unique index (too many columns). 502 build 503 INSERT INTO abc (a, b) 504 VALUES (1, 2) 505 ON CONFLICT (a, b) DO 506 UPDATE SET a=5 507 ---- 508 error (42P10): there is no unique or exclusion constraint matching the ON CONFLICT specification 509 510 # Conflict column not found. 511 build 512 INSERT INTO abc (a, b) 513 VALUES (1, 2) 514 ON CONFLICT (a, unknown) DO 515 UPDATE SET a=5 516 ---- 517 error (42703): column "unknown" does not exist 518 519 # ------------------------------------------------------------------------------ 520 # Test DO NOTHING. 521 # ------------------------------------------------------------------------------ 522 523 # No conflict columns specified (all non-duplicate indexes must be checked). 524 build 525 INSERT INTO xyz 526 VALUES (1, 2, 3), (4, 5, 6) 527 ON CONFLICT DO NOTHING 528 ---- 529 insert xyz 530 ├── columns: <none> 531 ├── insert-mapping: 532 │ ├── column1:4 => x:1 533 │ ├── column2:5 => y:2 534 │ └── column3:6 => z:3 535 └── upsert-distinct-on 536 ├── columns: column1:4!null column2:5!null column3:6!null 537 ├── grouping columns: column2:5!null column3:6!null 538 ├── project 539 │ ├── columns: column1:4!null column2:5!null column3:6!null 540 │ └── select 541 │ ├── columns: column1:4!null column2:5!null column3:6!null x:13 y:14 z:15 542 │ ├── left-join (hash) 543 │ │ ├── columns: column1:4!null column2:5!null column3:6!null x:13 y:14 z:15 544 │ │ ├── upsert-distinct-on 545 │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null 546 │ │ │ ├── grouping columns: column2:5!null column3:6!null 547 │ │ │ ├── project 548 │ │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null 549 │ │ │ │ └── select 550 │ │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null x:10 y:11 z:12 551 │ │ │ │ ├── left-join (hash) 552 │ │ │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null x:10 y:11 z:12 553 │ │ │ │ │ ├── upsert-distinct-on 554 │ │ │ │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null 555 │ │ │ │ │ │ ├── grouping columns: column1:4!null 556 │ │ │ │ │ │ ├── project 557 │ │ │ │ │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null 558 │ │ │ │ │ │ │ └── select 559 │ │ │ │ │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null x:7 y:8 z:9 560 │ │ │ │ │ │ │ ├── left-join (hash) 561 │ │ │ │ │ │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null x:7 y:8 z:9 562 │ │ │ │ │ │ │ │ ├── values 563 │ │ │ │ │ │ │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null 564 │ │ │ │ │ │ │ │ │ ├── (1, 2, 3) 565 │ │ │ │ │ │ │ │ │ └── (4, 5, 6) 566 │ │ │ │ │ │ │ │ ├── scan xyz 567 │ │ │ │ │ │ │ │ │ └── columns: x:7!null y:8 z:9 568 │ │ │ │ │ │ │ │ └── filters 569 │ │ │ │ │ │ │ │ └── column1:4 = x:7 570 │ │ │ │ │ │ │ └── filters 571 │ │ │ │ │ │ │ └── x:7 IS NULL 572 │ │ │ │ │ │ └── aggregations 573 │ │ │ │ │ │ ├── first-agg [as=column2:5] 574 │ │ │ │ │ │ │ └── column2:5 575 │ │ │ │ │ │ └── first-agg [as=column3:6] 576 │ │ │ │ │ │ └── column3:6 577 │ │ │ │ │ ├── scan xyz 578 │ │ │ │ │ │ └── columns: x:10!null y:11 z:12 579 │ │ │ │ │ └── filters 580 │ │ │ │ │ ├── column2:5 = y:11 581 │ │ │ │ │ └── column3:6 = z:12 582 │ │ │ │ └── filters 583 │ │ │ │ └── x:10 IS NULL 584 │ │ │ └── aggregations 585 │ │ │ └── first-agg [as=column1:4] 586 │ │ │ └── column1:4 587 │ │ ├── scan xyz 588 │ │ │ └── columns: x:13!null y:14 z:15 589 │ │ └── filters 590 │ │ ├── column3:6 = z:15 591 │ │ └── column2:5 = y:14 592 │ └── filters 593 │ └── x:13 IS NULL 594 └── aggregations 595 └── first-agg [as=column1:4] 596 └── column1:4 597 598 # Conflict columns are explicitly specified. 599 build 600 INSERT INTO xyz 601 VALUES (1, 2, 3), (4, 5, 6) 602 ON CONFLICT (y, z) DO NOTHING 603 ---- 604 insert xyz 605 ├── columns: <none> 606 ├── insert-mapping: 607 │ ├── column1:4 => x:1 608 │ ├── column2:5 => y:2 609 │ └── column3:6 => z:3 610 └── upsert-distinct-on 611 ├── columns: column1:4!null column2:5!null column3:6!null 612 ├── grouping columns: column2:5!null column3:6!null 613 ├── project 614 │ ├── columns: column1:4!null column2:5!null column3:6!null 615 │ └── select 616 │ ├── columns: column1:4!null column2:5!null column3:6!null x:7 y:8 z:9 617 │ ├── left-join (hash) 618 │ │ ├── columns: column1:4!null column2:5!null column3:6!null x:7 y:8 z:9 619 │ │ ├── values 620 │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null 621 │ │ │ ├── (1, 2, 3) 622 │ │ │ └── (4, 5, 6) 623 │ │ ├── scan xyz 624 │ │ │ └── columns: x:7!null y:8 z:9 625 │ │ └── filters 626 │ │ ├── column2:5 = y:8 627 │ │ └── column3:6 = z:9 628 │ └── filters 629 │ └── x:7 IS NULL 630 └── aggregations 631 └── first-agg [as=column1:4] 632 └── column1:4 633 634 # ------------------------------------------------------------------------------ 635 # Test excluded columns. 636 # ------------------------------------------------------------------------------ 637 638 build 639 INSERT INTO xyz 640 VALUES (1, 2, 3), (-1, -1, -1) 641 ON CONFLICT (z, y) DO 642 UPDATE SET x=excluded.x+1, y=excluded.y*xyz.y, z=excluded.x-excluded.z 643 WHERE excluded.y>xyz.y 644 RETURNING xyz.x*2, y+z 645 ---- 646 project 647 ├── columns: "?column?":16!null "?column?":17 648 ├── upsert xyz 649 │ ├── columns: x:1!null y:2 z:3!null 650 │ ├── canary column: 7 651 │ ├── fetch columns: x:7 y:8 z:9 652 │ ├── insert-mapping: 653 │ │ ├── column1:4 => x:1 654 │ │ ├── column2:5 => y:2 655 │ │ └── column3:6 => z:3 656 │ ├── update-mapping: 657 │ │ ├── upsert_x:13 => x:1 658 │ │ ├── upsert_y:14 => y:2 659 │ │ └── upsert_z:15 => z:3 660 │ ├── return-mapping: 661 │ │ ├── upsert_x:13 => x:1 662 │ │ ├── upsert_y:14 => y:2 663 │ │ └── upsert_z:15 => z:3 664 │ └── project 665 │ ├── columns: upsert_x:13!null upsert_y:14 upsert_z:15!null column1:4!null column2:5!null column3:6!null x:7 y:8 z:9 x_new:10!null y_new:11 z_new:12!null 666 │ ├── project 667 │ │ ├── columns: x_new:10!null y_new:11 z_new:12!null column1:4!null column2:5!null column3:6!null x:7 y:8 z:9 668 │ │ ├── select 669 │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null x:7 y:8 z:9 670 │ │ │ ├── left-join (hash) 671 │ │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null x:7 y:8 z:9 672 │ │ │ │ ├── ensure-upsert-distinct-on 673 │ │ │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null 674 │ │ │ │ │ ├── grouping columns: column2:5!null column3:6!null 675 │ │ │ │ │ ├── values 676 │ │ │ │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null 677 │ │ │ │ │ │ ├── (1, 2, 3) 678 │ │ │ │ │ │ └── (-1, -1, -1) 679 │ │ │ │ │ └── aggregations 680 │ │ │ │ │ └── first-agg [as=column1:4] 681 │ │ │ │ │ └── column1:4 682 │ │ │ │ ├── scan xyz 683 │ │ │ │ │ └── columns: x:7!null y:8 z:9 684 │ │ │ │ └── filters 685 │ │ │ │ ├── column2:5 = y:8 686 │ │ │ │ └── column3:6 = z:9 687 │ │ │ └── filters 688 │ │ │ └── (x:7 IS NULL) OR (column2:5 > y:8) 689 │ │ └── projections 690 │ │ ├── column1:4 + 1 [as=x_new:10] 691 │ │ ├── column2:5 * y:8 [as=y_new:11] 692 │ │ └── column1:4 - column3:6 [as=z_new:12] 693 │ └── projections 694 │ ├── CASE WHEN x:7 IS NULL THEN column1:4 ELSE x_new:10 END [as=upsert_x:13] 695 │ ├── CASE WHEN x:7 IS NULL THEN column2:5 ELSE y_new:11 END [as=upsert_y:14] 696 │ └── CASE WHEN x:7 IS NULL THEN column3:6 ELSE z_new:12 END [as=upsert_z:15] 697 └── projections 698 ├── x:1 * 2 [as="?column?":16] 699 └── y:2 + z:3 [as="?column?":17] 700 701 # Try to use excluded in RETURNING. 702 build 703 INSERT INTO xyz 704 VALUES (1, 2, 3) 705 ON CONFLICT (x) DO 706 UPDATE SET x=1 707 RETURNING excluded.x 708 ---- 709 error (42P01): no data source matches prefix: excluded 710 711 # Referencing column without "excluded" or "xyz" prefix is not allowed. 712 build 713 INSERT INTO xyz 714 VALUES (1, 2, 3) 715 ON CONFLICT (x) DO 716 UPDATE SET x=x+1 717 ---- 718 error (42702): column reference "x" is ambiguous (candidates: excluded.x, xyz.x) 719 720 # ------------------------------------------------------------------------------ 721 # Test UPDATE SET expressions. 722 # ------------------------------------------------------------------------------ 723 724 # Subquery. 725 build 726 INSERT INTO abc 727 VALUES (1, 2) 728 ON CONFLICT (a) DO 729 UPDATE SET (b, a)=(SELECT x, y+excluded.b FROM xyz WHERE x=excluded.a) 730 ---- 731 upsert abc 732 ├── columns: <none> 733 ├── canary column: 12 734 ├── fetch columns: a:9 b:10 c:11 rowid:12 735 ├── insert-mapping: 736 │ ├── column1:5 => a:1 737 │ ├── column2:6 => b:2 738 │ ├── column8:8 => c:3 739 │ └── column7:7 => rowid:4 740 ├── update-mapping: 741 │ ├── upsert_a:18 => a:1 742 │ ├── upsert_b:19 => b:2 743 │ └── upsert_c:20 => c:3 744 └── project 745 ├── columns: upsert_a:18 upsert_b:19 upsert_c:20 upsert_rowid:21 column1:5!null column2:6!null column7:7 column8:8!null a:9 b:10 c:11 rowid:12 x:13 "?column?":16 column17:17 746 ├── project 747 │ ├── columns: column17:17 column1:5!null column2:6!null column7:7 column8:8!null a:9 b:10 c:11 rowid:12 x:13 "?column?":16 748 │ ├── left-join-apply 749 │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8!null a:9 b:10 c:11 rowid:12 x:13 "?column?":16 750 │ │ ├── left-join (hash) 751 │ │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8!null a:9 b:10 c:11 rowid:12 752 │ │ │ ├── ensure-upsert-distinct-on 753 │ │ │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8!null 754 │ │ │ │ ├── grouping columns: column1:5!null 755 │ │ │ │ ├── project 756 │ │ │ │ │ ├── columns: column8:8!null column1:5!null column2:6!null column7:7 757 │ │ │ │ │ ├── project 758 │ │ │ │ │ │ ├── columns: column7:7 column1:5!null column2:6!null 759 │ │ │ │ │ │ ├── values 760 │ │ │ │ │ │ │ ├── columns: column1:5!null column2:6!null 761 │ │ │ │ │ │ │ └── (1, 2) 762 │ │ │ │ │ │ └── projections 763 │ │ │ │ │ │ └── unique_rowid() [as=column7:7] 764 │ │ │ │ │ └── projections 765 │ │ │ │ │ └── column2:6 + 1 [as=column8:8] 766 │ │ │ │ └── aggregations 767 │ │ │ │ ├── first-agg [as=column2:6] 768 │ │ │ │ │ └── column2:6 769 │ │ │ │ ├── first-agg [as=column7:7] 770 │ │ │ │ │ └── column7:7 771 │ │ │ │ └── first-agg [as=column8:8] 772 │ │ │ │ └── column8:8 773 │ │ │ ├── scan abc 774 │ │ │ │ ├── columns: a:9!null b:10 c:11 rowid:12!null 775 │ │ │ │ └── computed column expressions 776 │ │ │ │ └── c:11 777 │ │ │ │ └── b:10 + 1 778 │ │ │ └── filters 779 │ │ │ └── column1:5 = a:9 780 │ │ ├── max1-row 781 │ │ │ ├── columns: x:13!null "?column?":16 782 │ │ │ └── project 783 │ │ │ ├── columns: "?column?":16 x:13!null 784 │ │ │ ├── select 785 │ │ │ │ ├── columns: x:13!null y:14 z:15 786 │ │ │ │ ├── scan xyz 787 │ │ │ │ │ └── columns: x:13!null y:14 z:15 788 │ │ │ │ └── filters 789 │ │ │ │ └── x:13 = column1:5 790 │ │ │ └── projections 791 │ │ │ └── y:14 + column2:6 [as="?column?":16] 792 │ │ └── filters (true) 793 │ └── projections 794 │ └── x:13 + 1 [as=column17:17] 795 └── projections 796 ├── CASE WHEN rowid:12 IS NULL THEN column1:5 ELSE "?column?":16 END [as=upsert_a:18] 797 ├── CASE WHEN rowid:12 IS NULL THEN column2:6 ELSE x:13 END [as=upsert_b:19] 798 ├── CASE WHEN rowid:12 IS NULL THEN column8:8 ELSE column17:17 END [as=upsert_c:20] 799 └── CASE WHEN rowid:12 IS NULL THEN column7:7 ELSE rowid:12 END [as=upsert_rowid:21] 800 801 # Default expressions. 802 build 803 INSERT INTO abc 804 VALUES (1, 2) 805 ON CONFLICT (a) DO 806 UPDATE SET a=DEFAULT, b=DEFAULT 807 ---- 808 upsert abc 809 ├── columns: <none> 810 ├── canary column: 12 811 ├── fetch columns: a:9 b:10 c:11 rowid:12 812 ├── insert-mapping: 813 │ ├── column1:5 => a:1 814 │ ├── column2:6 => b:2 815 │ ├── column8:8 => c:3 816 │ └── column7:7 => rowid:4 817 ├── update-mapping: 818 │ ├── upsert_a:16 => a:1 819 │ ├── upsert_b:17 => b:2 820 │ └── upsert_c:18 => c:3 821 └── project 822 ├── columns: upsert_a:16 upsert_b:17!null upsert_c:18!null upsert_rowid:19 column1:5!null column2:6!null column7:7 column8:8!null a:9 b:10 c:11 rowid:12 a_new:13 b_new:14!null column15:15!null 823 ├── project 824 │ ├── columns: column15:15!null column1:5!null column2:6!null column7:7 column8:8!null a:9 b:10 c:11 rowid:12 a_new:13 b_new:14!null 825 │ ├── project 826 │ │ ├── columns: a_new:13 b_new:14!null column1:5!null column2:6!null column7:7 column8:8!null a:9 b:10 c:11 rowid:12 827 │ │ ├── left-join (hash) 828 │ │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8!null a:9 b:10 c:11 rowid:12 829 │ │ │ ├── ensure-upsert-distinct-on 830 │ │ │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8!null 831 │ │ │ │ ├── grouping columns: column1:5!null 832 │ │ │ │ ├── project 833 │ │ │ │ │ ├── columns: column8:8!null column1:5!null column2:6!null column7:7 834 │ │ │ │ │ ├── project 835 │ │ │ │ │ │ ├── columns: column7:7 column1:5!null column2:6!null 836 │ │ │ │ │ │ ├── values 837 │ │ │ │ │ │ │ ├── columns: column1:5!null column2:6!null 838 │ │ │ │ │ │ │ └── (1, 2) 839 │ │ │ │ │ │ └── projections 840 │ │ │ │ │ │ └── unique_rowid() [as=column7:7] 841 │ │ │ │ │ └── projections 842 │ │ │ │ │ └── column2:6 + 1 [as=column8:8] 843 │ │ │ │ └── aggregations 844 │ │ │ │ ├── first-agg [as=column2:6] 845 │ │ │ │ │ └── column2:6 846 │ │ │ │ ├── first-agg [as=column7:7] 847 │ │ │ │ │ └── column7:7 848 │ │ │ │ └── first-agg [as=column8:8] 849 │ │ │ │ └── column8:8 850 │ │ │ ├── scan abc 851 │ │ │ │ ├── columns: a:9!null b:10 c:11 rowid:12!null 852 │ │ │ │ └── computed column expressions 853 │ │ │ │ └── c:11 854 │ │ │ │ └── b:10 + 1 855 │ │ │ └── filters 856 │ │ │ └── column1:5 = a:9 857 │ │ └── projections 858 │ │ ├── NULL::INT8 [as=a_new:13] 859 │ │ └── 10 [as=b_new:14] 860 │ └── projections 861 │ └── b_new:14 + 1 [as=column15:15] 862 └── projections 863 ├── CASE WHEN rowid:12 IS NULL THEN column1:5 ELSE a_new:13 END [as=upsert_a:16] 864 ├── CASE WHEN rowid:12 IS NULL THEN column2:6 ELSE b_new:14 END [as=upsert_b:17] 865 ├── CASE WHEN rowid:12 IS NULL THEN column8:8 ELSE column15:15 END [as=upsert_c:18] 866 └── CASE WHEN rowid:12 IS NULL THEN column7:7 ELSE rowid:12 END [as=upsert_rowid:19] 867 868 # ------------------------------------------------------------------------------ 869 # Test mutation columns. 870 # ------------------------------------------------------------------------------ 871 872 build 873 INSERT INTO mutation (m, n) 874 VALUES (1, 2) 875 ON CONFLICT (m) DO 876 UPDATE SET m=mutation.m+1 877 ---- 878 upsert mutation 879 ├── columns: <none> 880 ├── canary column: 10 881 ├── fetch columns: m:10 n:11 o:12 p:13 q:14 882 ├── insert-mapping: 883 │ ├── column1:6 => m:1 884 │ ├── column2:7 => n:2 885 │ ├── column8:8 => o:3 886 │ └── column9:9 => p:4 887 ├── update-mapping: 888 │ ├── upsert_m:17 => m:1 889 │ ├── column8:8 => o:3 890 │ └── upsert_p:19 => p:4 891 ├── check columns: check1:20 892 └── project 893 ├── columns: check1:20 column1:6!null column2:7!null column8:8!null column9:9!null m:10 n:11 o:12 p:13 q:14 m_new:15 column16:16 upsert_m:17 upsert_n:18 upsert_p:19 894 ├── project 895 │ ├── columns: upsert_m:17 upsert_n:18 upsert_p:19 column1:6!null column2:7!null column8:8!null column9:9!null m:10 n:11 o:12 p:13 q:14 m_new:15 column16:16 896 │ ├── project 897 │ │ ├── columns: column16:16 column1:6!null column2:7!null column8:8!null column9:9!null m:10 n:11 o:12 p:13 q:14 m_new:15 898 │ │ ├── project 899 │ │ │ ├── columns: m_new:15 column1:6!null column2:7!null column8:8!null column9:9!null m:10 n:11 o:12 p:13 q:14 900 │ │ │ ├── left-join (hash) 901 │ │ │ │ ├── columns: column1:6!null column2:7!null column8:8!null column9:9!null m:10 n:11 o:12 p:13 q:14 902 │ │ │ │ ├── ensure-upsert-distinct-on 903 │ │ │ │ │ ├── columns: column1:6!null column2:7!null column8:8!null column9:9!null 904 │ │ │ │ │ ├── grouping columns: column1:6!null 905 │ │ │ │ │ ├── project 906 │ │ │ │ │ │ ├── columns: column9:9!null column1:6!null column2:7!null column8:8!null 907 │ │ │ │ │ │ ├── project 908 │ │ │ │ │ │ │ ├── columns: column8:8!null column1:6!null column2:7!null 909 │ │ │ │ │ │ │ ├── values 910 │ │ │ │ │ │ │ │ ├── columns: column1:6!null column2:7!null 911 │ │ │ │ │ │ │ │ └── (1, 2) 912 │ │ │ │ │ │ │ └── projections 913 │ │ │ │ │ │ │ └── 10 [as=column8:8] 914 │ │ │ │ │ │ └── projections 915 │ │ │ │ │ │ └── column8:8 + column2:7 [as=column9:9] 916 │ │ │ │ │ └── aggregations 917 │ │ │ │ │ ├── first-agg [as=column2:7] 918 │ │ │ │ │ │ └── column2:7 919 │ │ │ │ │ ├── first-agg [as=column8:8] 920 │ │ │ │ │ │ └── column8:8 921 │ │ │ │ │ └── first-agg [as=column9:9] 922 │ │ │ │ │ └── column9:9 923 │ │ │ │ ├── scan mutation 924 │ │ │ │ │ ├── columns: m:10!null n:11 o:12 p:13 q:14 925 │ │ │ │ │ └── check constraint expressions 926 │ │ │ │ │ └── m:10 > 0 927 │ │ │ │ └── filters 928 │ │ │ │ └── column1:6 = m:10 929 │ │ │ └── projections 930 │ │ │ └── m:10 + 1 [as=m_new:15] 931 │ │ └── projections 932 │ │ └── column8:8 + n:11 [as=column16:16] 933 │ └── projections 934 │ ├── CASE WHEN m:10 IS NULL THEN column1:6 ELSE m_new:15 END [as=upsert_m:17] 935 │ ├── CASE WHEN m:10 IS NULL THEN column2:7 ELSE n:11 END [as=upsert_n:18] 936 │ └── CASE WHEN m:10 IS NULL THEN column9:9 ELSE column16:16 END [as=upsert_p:19] 937 └── projections 938 └── upsert_m:17 > 0 [as=check1:20] 939 940 # ------------------------------------------------------------------------------ 941 # Test UPSERT. 942 # ------------------------------------------------------------------------------ 943 944 # Single column primary key. 945 build 946 UPSERT INTO xyz VALUES (1) 947 ---- 948 upsert xyz 949 ├── columns: <none> 950 ├── canary column: 6 951 ├── fetch columns: x:6 y:7 z:8 952 ├── insert-mapping: 953 │ ├── column1:4 => x:1 954 │ ├── column5:5 => y:2 955 │ └── column5:5 => z:3 956 ├── update-mapping: 957 │ ├── column5:5 => y:2 958 │ └── column5:5 => z:3 959 └── project 960 ├── columns: upsert_x:9 column1:4!null column5:5 x:6 y:7 z:8 961 ├── left-join (hash) 962 │ ├── columns: column1:4!null column5:5 x:6 y:7 z:8 963 │ ├── ensure-upsert-distinct-on 964 │ │ ├── columns: column1:4!null column5:5 965 │ │ ├── grouping columns: column1:4!null 966 │ │ ├── project 967 │ │ │ ├── columns: column5:5 column1:4!null 968 │ │ │ ├── values 969 │ │ │ │ ├── columns: column1:4!null 970 │ │ │ │ └── (1,) 971 │ │ │ └── projections 972 │ │ │ └── NULL::INT8 [as=column5:5] 973 │ │ └── aggregations 974 │ │ └── first-agg [as=column5:5] 975 │ │ └── column5:5 976 │ ├── scan xyz 977 │ │ └── columns: x:6!null y:7 z:8 978 │ └── filters 979 │ └── column1:4 = x:6 980 └── projections 981 └── CASE WHEN x:6 IS NULL THEN column1:4 ELSE x:6 END [as=upsert_x:9] 982 983 # Test multi-column primary key that contains all columns in table. 984 build 985 UPSERT INTO uv VALUES (1, 2) RETURNING * 986 ---- 987 upsert uv 988 ├── columns: u:1!null v:2!null 989 ├── upsert-mapping: 990 │ ├── column1:3 => u:1 991 │ └── column2:4 => v:2 992 └── values 993 ├── columns: column1:3!null column2:4!null 994 └── (1, 2) 995 996 # Use returning UPSERT as a FROM expression. 997 build 998 SELECT * FROM [UPSERT INTO abc VALUES (1, 2) RETURNING *] 999 ---- 1000 with &1 1001 ├── columns: a:14!null b:15!null c:16!null 1002 ├── project 1003 │ ├── columns: abc.a:1!null abc.b:2!null abc.c:3!null 1004 │ └── upsert abc 1005 │ ├── columns: abc.a:1!null abc.b:2!null abc.c:3!null rowid:4!null 1006 │ ├── canary column: 12 1007 │ ├── fetch columns: abc.a:9 abc.b:10 abc.c:11 rowid:12 1008 │ ├── insert-mapping: 1009 │ │ ├── column1:5 => abc.a:1 1010 │ │ ├── column2:6 => abc.b:2 1011 │ │ ├── column8:8 => abc.c:3 1012 │ │ └── column7:7 => rowid:4 1013 │ ├── update-mapping: 1014 │ │ ├── column1:5 => abc.a:1 1015 │ │ ├── column2:6 => abc.b:2 1016 │ │ └── column8:8 => abc.c:3 1017 │ ├── return-mapping: 1018 │ │ ├── column1:5 => abc.a:1 1019 │ │ ├── column2:6 => abc.b:2 1020 │ │ ├── column8:8 => abc.c:3 1021 │ │ └── upsert_rowid:13 => rowid:4 1022 │ └── project 1023 │ ├── columns: upsert_rowid:13 column1:5!null column2:6!null column7:7 column8:8!null abc.a:9 abc.b:10 abc.c:11 rowid:12 1024 │ ├── left-join (hash) 1025 │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8!null abc.a:9 abc.b:10 abc.c:11 rowid:12 1026 │ │ ├── ensure-upsert-distinct-on 1027 │ │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8!null 1028 │ │ │ ├── grouping columns: column7:7 1029 │ │ │ ├── project 1030 │ │ │ │ ├── columns: column8:8!null column1:5!null column2:6!null column7:7 1031 │ │ │ │ ├── project 1032 │ │ │ │ │ ├── columns: column7:7 column1:5!null column2:6!null 1033 │ │ │ │ │ ├── values 1034 │ │ │ │ │ │ ├── columns: column1:5!null column2:6!null 1035 │ │ │ │ │ │ └── (1, 2) 1036 │ │ │ │ │ └── projections 1037 │ │ │ │ │ └── unique_rowid() [as=column7:7] 1038 │ │ │ │ └── projections 1039 │ │ │ │ └── column2:6 + 1 [as=column8:8] 1040 │ │ │ └── aggregations 1041 │ │ │ ├── first-agg [as=column1:5] 1042 │ │ │ │ └── column1:5 1043 │ │ │ ├── first-agg [as=column2:6] 1044 │ │ │ │ └── column2:6 1045 │ │ │ └── first-agg [as=column8:8] 1046 │ │ │ └── column8:8 1047 │ │ ├── scan abc 1048 │ │ │ ├── columns: abc.a:9!null abc.b:10 abc.c:11 rowid:12!null 1049 │ │ │ └── computed column expressions 1050 │ │ │ └── abc.c:11 1051 │ │ │ └── abc.b:10 + 1 1052 │ │ └── filters 1053 │ │ └── column7:7 = rowid:12 1054 │ └── projections 1055 │ └── CASE WHEN rowid:12 IS NULL THEN column7:7 ELSE rowid:12 END [as=upsert_rowid:13] 1056 └── with-scan &1 1057 ├── columns: a:14!null b:15!null c:16!null 1058 └── mapping: 1059 ├── abc.a:1 => a:14 1060 ├── abc.b:2 => b:15 1061 └── abc.c:3 => c:16 1062 1063 # Use explicitly specified column names with secondary indexes present. Existing 1064 # values of other columns need to be fetched to delete existing index rows. 1065 build 1066 UPSERT INTO xyz (z, x, y) VALUES (1, 2, 3) 1067 ---- 1068 upsert xyz 1069 ├── columns: <none> 1070 ├── canary column: 7 1071 ├── fetch columns: x:7 y:8 z:9 1072 ├── insert-mapping: 1073 │ ├── column2:5 => x:1 1074 │ ├── column3:6 => y:2 1075 │ └── column1:4 => z:3 1076 ├── update-mapping: 1077 │ ├── column3:6 => y:2 1078 │ └── column1:4 => z:3 1079 └── project 1080 ├── columns: upsert_x:10 column1:4!null column2:5!null column3:6!null x:7 y:8 z:9 1081 ├── left-join (hash) 1082 │ ├── columns: column1:4!null column2:5!null column3:6!null x:7 y:8 z:9 1083 │ ├── ensure-upsert-distinct-on 1084 │ │ ├── columns: column1:4!null column2:5!null column3:6!null 1085 │ │ ├── grouping columns: column2:5!null 1086 │ │ ├── values 1087 │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null 1088 │ │ │ └── (1, 2, 3) 1089 │ │ └── aggregations 1090 │ │ ├── first-agg [as=column1:4] 1091 │ │ │ └── column1:4 1092 │ │ └── first-agg [as=column3:6] 1093 │ │ └── column3:6 1094 │ ├── scan xyz 1095 │ │ └── columns: x:7!null y:8 z:9 1096 │ └── filters 1097 │ └── column2:5 = x:7 1098 └── projections 1099 └── CASE WHEN x:7 IS NULL THEN column2:5 ELSE x:7 END [as=upsert_x:10] 1100 1101 # Use explicitly specified column names with no secondary indexes present. 1102 # Upsert implemented with blind Puts is possible. 1103 build 1104 UPSERT INTO noindex (x, y, z) VALUES (1, 2, 3) 1105 ---- 1106 upsert noindex 1107 ├── columns: <none> 1108 ├── upsert-mapping: 1109 │ ├── column1:4 => x:1 1110 │ ├── column2:5 => y:2 1111 │ └── column3:6 => z:3 1112 └── values 1113 ├── columns: column1:4!null column2:5!null column3:6!null 1114 └── (1, 2, 3) 1115 1116 # Use subset of explicitly specified column names with no secondary indexes 1117 # present. Existing values of other columns need to be fetched to provide 1118 # update values for unspecified columns. 1119 build 1120 UPSERT INTO checks (a, b, c) VALUES (1, 2, 3) 1121 ---- 1122 upsert checks 1123 ├── columns: <none> 1124 ├── canary column: 9 1125 ├── fetch columns: a:9 b:10 c:11 d:12 1126 ├── insert-mapping: 1127 │ ├── column1:5 => a:1 1128 │ ├── column2:6 => b:2 1129 │ ├── column3:7 => c:3 1130 │ └── column8:8 => d:4 1131 ├── update-mapping: 1132 │ ├── column2:6 => b:2 1133 │ ├── column3:7 => c:3 1134 │ └── column8:8 => d:4 1135 ├── check columns: check1:14 check2:15 1136 └── project 1137 ├── columns: check1:14!null check2:15 column1:5!null column2:6!null column3:7!null column8:8!null a:9 b:10 c:11 d:12 upsert_a:13 1138 ├── project 1139 │ ├── columns: upsert_a:13 column1:5!null column2:6!null column3:7!null column8:8!null a:9 b:10 c:11 d:12 1140 │ ├── left-join (hash) 1141 │ │ ├── columns: column1:5!null column2:6!null column3:7!null column8:8!null a:9 b:10 c:11 d:12 1142 │ │ ├── ensure-upsert-distinct-on 1143 │ │ │ ├── columns: column1:5!null column2:6!null column3:7!null column8:8!null 1144 │ │ │ ├── grouping columns: column1:5!null 1145 │ │ │ ├── project 1146 │ │ │ │ ├── columns: column8:8!null column1:5!null column2:6!null column3:7!null 1147 │ │ │ │ ├── values 1148 │ │ │ │ │ ├── columns: column1:5!null column2:6!null column3:7!null 1149 │ │ │ │ │ └── (1, 2, 3) 1150 │ │ │ │ └── projections 1151 │ │ │ │ └── column3:7 + 1 [as=column8:8] 1152 │ │ │ └── aggregations 1153 │ │ │ ├── first-agg [as=column2:6] 1154 │ │ │ │ └── column2:6 1155 │ │ │ ├── first-agg [as=column3:7] 1156 │ │ │ │ └── column3:7 1157 │ │ │ └── first-agg [as=column8:8] 1158 │ │ │ └── column8:8 1159 │ │ ├── scan checks 1160 │ │ │ ├── columns: a:9!null b:10 c:11 d:12 1161 │ │ │ ├── check constraint expressions 1162 │ │ │ │ └── a:9 > 0 1163 │ │ │ └── computed column expressions 1164 │ │ │ └── d:12 1165 │ │ │ └── c:11 + 1 1166 │ │ └── filters 1167 │ │ └── column1:5 = a:9 1168 │ └── projections 1169 │ └── CASE WHEN a:9 IS NULL THEN column1:5 ELSE a:9 END [as=upsert_a:13] 1170 └── projections 1171 ├── column2:6 < column8:8 [as=check1:14] 1172 └── upsert_a:13 > 0 [as=check2:15] 1173 1174 # Ensure that mutation columns are set by the insert and update. Use explicit 1175 # target columns. 1176 build 1177 UPSERT INTO mutation (m, n) VALUES (1, 2) 1178 ---- 1179 upsert mutation 1180 ├── columns: <none> 1181 ├── canary column: 10 1182 ├── fetch columns: m:10 n:11 o:12 p:13 q:14 1183 ├── insert-mapping: 1184 │ ├── column1:6 => m:1 1185 │ ├── column2:7 => n:2 1186 │ ├── column8:8 => o:3 1187 │ └── column9:9 => p:4 1188 ├── update-mapping: 1189 │ ├── column2:7 => n:2 1190 │ ├── column8:8 => o:3 1191 │ └── column9:9 => p:4 1192 ├── check columns: check1:16 1193 └── project 1194 ├── columns: check1:16 column1:6!null column2:7!null column8:8!null column9:9!null m:10 n:11 o:12 p:13 q:14 upsert_m:15 1195 ├── project 1196 │ ├── columns: upsert_m:15 column1:6!null column2:7!null column8:8!null column9:9!null m:10 n:11 o:12 p:13 q:14 1197 │ ├── left-join (hash) 1198 │ │ ├── columns: column1:6!null column2:7!null column8:8!null column9:9!null m:10 n:11 o:12 p:13 q:14 1199 │ │ ├── ensure-upsert-distinct-on 1200 │ │ │ ├── columns: column1:6!null column2:7!null column8:8!null column9:9!null 1201 │ │ │ ├── grouping columns: column1:6!null 1202 │ │ │ ├── project 1203 │ │ │ │ ├── columns: column9:9!null column1:6!null column2:7!null column8:8!null 1204 │ │ │ │ ├── project 1205 │ │ │ │ │ ├── columns: column8:8!null column1:6!null column2:7!null 1206 │ │ │ │ │ ├── values 1207 │ │ │ │ │ │ ├── columns: column1:6!null column2:7!null 1208 │ │ │ │ │ │ └── (1, 2) 1209 │ │ │ │ │ └── projections 1210 │ │ │ │ │ └── 10 [as=column8:8] 1211 │ │ │ │ └── projections 1212 │ │ │ │ └── column8:8 + column2:7 [as=column9:9] 1213 │ │ │ └── aggregations 1214 │ │ │ ├── first-agg [as=column2:7] 1215 │ │ │ │ └── column2:7 1216 │ │ │ ├── first-agg [as=column8:8] 1217 │ │ │ │ └── column8:8 1218 │ │ │ └── first-agg [as=column9:9] 1219 │ │ │ └── column9:9 1220 │ │ ├── scan mutation 1221 │ │ │ ├── columns: m:10!null n:11 o:12 p:13 q:14 1222 │ │ │ └── check constraint expressions 1223 │ │ │ └── m:10 > 0 1224 │ │ └── filters 1225 │ │ └── column1:6 = m:10 1226 │ └── projections 1227 │ └── CASE WHEN m:10 IS NULL THEN column1:6 ELSE m:10 END [as=upsert_m:15] 1228 └── projections 1229 └── upsert_m:15 > 0 [as=check1:16] 1230 1231 # Don't directly update mutation columns. However, computed columns do need to 1232 # be updated. Use implicit target columns. 1233 build 1234 UPSERT INTO mutation VALUES (1, 2) 1235 ---- 1236 upsert mutation 1237 ├── columns: <none> 1238 ├── canary column: 10 1239 ├── fetch columns: m:10 n:11 o:12 p:13 q:14 1240 ├── insert-mapping: 1241 │ ├── column1:6 => m:1 1242 │ ├── column2:7 => n:2 1243 │ ├── column8:8 => o:3 1244 │ └── column9:9 => p:4 1245 ├── update-mapping: 1246 │ ├── column2:7 => n:2 1247 │ ├── column8:8 => o:3 1248 │ └── column9:9 => p:4 1249 ├── check columns: check1:16 1250 └── project 1251 ├── columns: check1:16 column1:6!null column2:7!null column8:8!null column9:9!null m:10 n:11 o:12 p:13 q:14 upsert_m:15 1252 ├── project 1253 │ ├── columns: upsert_m:15 column1:6!null column2:7!null column8:8!null column9:9!null m:10 n:11 o:12 p:13 q:14 1254 │ ├── left-join (hash) 1255 │ │ ├── columns: column1:6!null column2:7!null column8:8!null column9:9!null m:10 n:11 o:12 p:13 q:14 1256 │ │ ├── ensure-upsert-distinct-on 1257 │ │ │ ├── columns: column1:6!null column2:7!null column8:8!null column9:9!null 1258 │ │ │ ├── grouping columns: column1:6!null 1259 │ │ │ ├── project 1260 │ │ │ │ ├── columns: column9:9!null column1:6!null column2:7!null column8:8!null 1261 │ │ │ │ ├── project 1262 │ │ │ │ │ ├── columns: column8:8!null column1:6!null column2:7!null 1263 │ │ │ │ │ ├── values 1264 │ │ │ │ │ │ ├── columns: column1:6!null column2:7!null 1265 │ │ │ │ │ │ └── (1, 2) 1266 │ │ │ │ │ └── projections 1267 │ │ │ │ │ └── 10 [as=column8:8] 1268 │ │ │ │ └── projections 1269 │ │ │ │ └── column8:8 + column2:7 [as=column9:9] 1270 │ │ │ └── aggregations 1271 │ │ │ ├── first-agg [as=column2:7] 1272 │ │ │ │ └── column2:7 1273 │ │ │ ├── first-agg [as=column8:8] 1274 │ │ │ │ └── column8:8 1275 │ │ │ └── first-agg [as=column9:9] 1276 │ │ │ └── column9:9 1277 │ │ ├── scan mutation 1278 │ │ │ ├── columns: m:10!null n:11 o:12 p:13 q:14 1279 │ │ │ └── check constraint expressions 1280 │ │ │ └── m:10 > 0 1281 │ │ └── filters 1282 │ │ └── column1:6 = m:10 1283 │ └── projections 1284 │ └── CASE WHEN m:10 IS NULL THEN column1:6 ELSE m:10 END [as=upsert_m:15] 1285 └── projections 1286 └── upsert_m:15 > 0 [as=check1:16] 1287 1288 # Use unknown name in upsert column list. 1289 build 1290 UPSERT INTO xyz (x, unknown) VALUES (1) 1291 ---- 1292 error (42703): column "unknown" does not exist 1293 1294 # ------------------------------------------------------------------------------ 1295 # Test check constraints. 1296 # ------------------------------------------------------------------------------ 1297 1298 # INSERT..ON CONFLICT 1299 build 1300 INSERT INTO checks (a, b) VALUES (1, 2) ON CONFLICT (a) DO UPDATE SET b=3, c=4 1301 ---- 1302 upsert checks 1303 ├── columns: <none> 1304 ├── canary column: 9 1305 ├── fetch columns: a:9 b:10 c:11 d:12 1306 ├── insert-mapping: 1307 │ ├── column1:5 => a:1 1308 │ ├── column2:6 => b:2 1309 │ ├── column7:7 => c:3 1310 │ └── column8:8 => d:4 1311 ├── update-mapping: 1312 │ ├── upsert_b:17 => b:2 1313 │ ├── upsert_c:18 => c:3 1314 │ └── upsert_d:19 => d:4 1315 ├── check columns: check1:20 check2:21 1316 └── project 1317 ├── columns: check1:20 check2:21 column1:5!null column2:6!null column7:7 column8:8 a:9 b:10 c:11 d:12 b_new:13!null c_new:14!null column15:15!null upsert_a:16 upsert_b:17!null upsert_c:18 upsert_d:19 1318 ├── project 1319 │ ├── columns: upsert_a:16 upsert_b:17!null upsert_c:18 upsert_d:19 column1:5!null column2:6!null column7:7 column8:8 a:9 b:10 c:11 d:12 b_new:13!null c_new:14!null column15:15!null 1320 │ ├── project 1321 │ │ ├── columns: column15:15!null column1:5!null column2:6!null column7:7 column8:8 a:9 b:10 c:11 d:12 b_new:13!null c_new:14!null 1322 │ │ ├── project 1323 │ │ │ ├── columns: b_new:13!null c_new:14!null column1:5!null column2:6!null column7:7 column8:8 a:9 b:10 c:11 d:12 1324 │ │ │ ├── left-join (hash) 1325 │ │ │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8 a:9 b:10 c:11 d:12 1326 │ │ │ │ ├── ensure-upsert-distinct-on 1327 │ │ │ │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8 1328 │ │ │ │ │ ├── grouping columns: column1:5!null 1329 │ │ │ │ │ ├── project 1330 │ │ │ │ │ │ ├── columns: column8:8 column1:5!null column2:6!null column7:7 1331 │ │ │ │ │ │ ├── project 1332 │ │ │ │ │ │ │ ├── columns: column7:7 column1:5!null column2:6!null 1333 │ │ │ │ │ │ │ ├── values 1334 │ │ │ │ │ │ │ │ ├── columns: column1:5!null column2:6!null 1335 │ │ │ │ │ │ │ │ └── (1, 2) 1336 │ │ │ │ │ │ │ └── projections 1337 │ │ │ │ │ │ │ └── NULL::INT8 [as=column7:7] 1338 │ │ │ │ │ │ └── projections 1339 │ │ │ │ │ │ └── column7:7 + 1 [as=column8:8] 1340 │ │ │ │ │ └── aggregations 1341 │ │ │ │ │ ├── first-agg [as=column2:6] 1342 │ │ │ │ │ │ └── column2:6 1343 │ │ │ │ │ ├── first-agg [as=column7:7] 1344 │ │ │ │ │ │ └── column7:7 1345 │ │ │ │ │ └── first-agg [as=column8:8] 1346 │ │ │ │ │ └── column8:8 1347 │ │ │ │ ├── scan checks 1348 │ │ │ │ │ ├── columns: a:9!null b:10 c:11 d:12 1349 │ │ │ │ │ ├── check constraint expressions 1350 │ │ │ │ │ │ └── a:9 > 0 1351 │ │ │ │ │ └── computed column expressions 1352 │ │ │ │ │ └── d:12 1353 │ │ │ │ │ └── c:11 + 1 1354 │ │ │ │ └── filters 1355 │ │ │ │ └── column1:5 = a:9 1356 │ │ │ └── projections 1357 │ │ │ ├── 3 [as=b_new:13] 1358 │ │ │ └── 4 [as=c_new:14] 1359 │ │ └── projections 1360 │ │ └── c_new:14 + 1 [as=column15:15] 1361 │ └── projections 1362 │ ├── CASE WHEN a:9 IS NULL THEN column1:5 ELSE a:9 END [as=upsert_a:16] 1363 │ ├── CASE WHEN a:9 IS NULL THEN column2:6 ELSE b_new:13 END [as=upsert_b:17] 1364 │ ├── CASE WHEN a:9 IS NULL THEN column7:7 ELSE c_new:14 END [as=upsert_c:18] 1365 │ └── CASE WHEN a:9 IS NULL THEN column8:8 ELSE column15:15 END [as=upsert_d:19] 1366 └── projections 1367 ├── upsert_b:17 < upsert_d:19 [as=check1:20] 1368 └── upsert_a:16 > 0 [as=check2:21] 1369 1370 # INSERT..ON CONFLICT DO NOTHING 1371 build 1372 INSERT INTO checks (a, b) VALUES (1, 2) ON CONFLICT (a) DO NOTHING 1373 ---- 1374 insert checks 1375 ├── columns: <none> 1376 ├── insert-mapping: 1377 │ ├── column1:5 => a:1 1378 │ ├── column2:6 => b:2 1379 │ ├── column7:7 => c:3 1380 │ └── column8:8 => d:4 1381 ├── check columns: check1:13 check2:14 1382 └── project 1383 ├── columns: check1:13 check2:14!null column1:5!null column2:6!null column7:7 column8:8 1384 ├── upsert-distinct-on 1385 │ ├── columns: column1:5!null column2:6!null column7:7 column8:8 1386 │ ├── grouping columns: column1:5!null 1387 │ ├── project 1388 │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8 1389 │ │ └── select 1390 │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8 a:9 b:10 c:11 d:12 1391 │ │ ├── left-join (hash) 1392 │ │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8 a:9 b:10 c:11 d:12 1393 │ │ │ ├── project 1394 │ │ │ │ ├── columns: column8:8 column1:5!null column2:6!null column7:7 1395 │ │ │ │ ├── project 1396 │ │ │ │ │ ├── columns: column7:7 column1:5!null column2:6!null 1397 │ │ │ │ │ ├── values 1398 │ │ │ │ │ │ ├── columns: column1:5!null column2:6!null 1399 │ │ │ │ │ │ └── (1, 2) 1400 │ │ │ │ │ └── projections 1401 │ │ │ │ │ └── NULL::INT8 [as=column7:7] 1402 │ │ │ │ └── projections 1403 │ │ │ │ └── column7:7 + 1 [as=column8:8] 1404 │ │ │ ├── scan checks 1405 │ │ │ │ ├── columns: a:9!null b:10 c:11 d:12 1406 │ │ │ │ ├── check constraint expressions 1407 │ │ │ │ │ └── a:9 > 0 1408 │ │ │ │ └── computed column expressions 1409 │ │ │ │ └── d:12 1410 │ │ │ │ └── c:11 + 1 1411 │ │ │ └── filters 1412 │ │ │ └── column1:5 = a:9 1413 │ │ └── filters 1414 │ │ └── a:9 IS NULL 1415 │ └── aggregations 1416 │ ├── first-agg [as=column2:6] 1417 │ │ └── column2:6 1418 │ ├── first-agg [as=column7:7] 1419 │ │ └── column7:7 1420 │ └── first-agg [as=column8:8] 1421 │ └── column8:8 1422 └── projections 1423 ├── column2:6 < column8:8 [as=check1:13] 1424 └── column1:5 > 0 [as=check2:14] 1425 1426 # UPSERT 1427 build 1428 UPSERT INTO checks (a, b) VALUES (1, 2) 1429 ---- 1430 upsert checks 1431 ├── columns: <none> 1432 ├── canary column: 9 1433 ├── fetch columns: a:9 b:10 c:11 d:12 1434 ├── insert-mapping: 1435 │ ├── column1:5 => a:1 1436 │ ├── column2:6 => b:2 1437 │ ├── column7:7 => c:3 1438 │ └── column8:8 => d:4 1439 ├── update-mapping: 1440 │ ├── column2:6 => b:2 1441 │ └── upsert_d:16 => d:4 1442 ├── check columns: check1:17 check2:18 1443 └── project 1444 ├── columns: check1:17 check2:18 column1:5!null column2:6!null column7:7 column8:8 a:9 b:10 c:11 d:12 column13:13 upsert_a:14 upsert_c:15 upsert_d:16 1445 ├── project 1446 │ ├── columns: upsert_a:14 upsert_c:15 upsert_d:16 column1:5!null column2:6!null column7:7 column8:8 a:9 b:10 c:11 d:12 column13:13 1447 │ ├── project 1448 │ │ ├── columns: column13:13 column1:5!null column2:6!null column7:7 column8:8 a:9 b:10 c:11 d:12 1449 │ │ ├── left-join (hash) 1450 │ │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8 a:9 b:10 c:11 d:12 1451 │ │ │ ├── ensure-upsert-distinct-on 1452 │ │ │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8 1453 │ │ │ │ ├── grouping columns: column1:5!null 1454 │ │ │ │ ├── project 1455 │ │ │ │ │ ├── columns: column8:8 column1:5!null column2:6!null column7:7 1456 │ │ │ │ │ ├── project 1457 │ │ │ │ │ │ ├── columns: column7:7 column1:5!null column2:6!null 1458 │ │ │ │ │ │ ├── values 1459 │ │ │ │ │ │ │ ├── columns: column1:5!null column2:6!null 1460 │ │ │ │ │ │ │ └── (1, 2) 1461 │ │ │ │ │ │ └── projections 1462 │ │ │ │ │ │ └── NULL::INT8 [as=column7:7] 1463 │ │ │ │ │ └── projections 1464 │ │ │ │ │ └── column7:7 + 1 [as=column8:8] 1465 │ │ │ │ └── aggregations 1466 │ │ │ │ ├── first-agg [as=column2:6] 1467 │ │ │ │ │ └── column2:6 1468 │ │ │ │ ├── first-agg [as=column7:7] 1469 │ │ │ │ │ └── column7:7 1470 │ │ │ │ └── first-agg [as=column8:8] 1471 │ │ │ │ └── column8:8 1472 │ │ │ ├── scan checks 1473 │ │ │ │ ├── columns: a:9!null b:10 c:11 d:12 1474 │ │ │ │ ├── check constraint expressions 1475 │ │ │ │ │ └── a:9 > 0 1476 │ │ │ │ └── computed column expressions 1477 │ │ │ │ └── d:12 1478 │ │ │ │ └── c:11 + 1 1479 │ │ │ └── filters 1480 │ │ │ └── column1:5 = a:9 1481 │ │ └── projections 1482 │ │ └── c:11 + 1 [as=column13:13] 1483 │ └── projections 1484 │ ├── CASE WHEN a:9 IS NULL THEN column1:5 ELSE a:9 END [as=upsert_a:14] 1485 │ ├── CASE WHEN a:9 IS NULL THEN column7:7 ELSE c:11 END [as=upsert_c:15] 1486 │ └── CASE WHEN a:9 IS NULL THEN column8:8 ELSE column13:13 END [as=upsert_d:16] 1487 └── projections 1488 ├── column2:6 < upsert_d:16 [as=check1:17] 1489 └── upsert_a:14 > 0 [as=check2:18] 1490 1491 # Use subqueries and excluded. 1492 build 1493 INSERT INTO checks 1494 SELECT a, b FROM abc 1495 ON CONFLICT (a) DO UPDATE SET a=excluded.a, b=(SELECT x FROM xyz WHERE x=checks.a) 1496 ---- 1497 upsert checks 1498 ├── columns: <none> 1499 ├── canary column: 11 1500 ├── fetch columns: checks.a:11 checks.b:12 checks.c:13 d:14 1501 ├── insert-mapping: 1502 │ ├── abc.a:5 => checks.a:1 1503 │ ├── abc.b:6 => checks.b:2 1504 │ ├── column9:9 => checks.c:3 1505 │ └── column10:10 => d:4 1506 ├── update-mapping: 1507 │ ├── abc.a:5 => checks.a:1 1508 │ ├── upsert_b:20 => checks.b:2 1509 │ └── upsert_d:22 => d:4 1510 ├── check columns: check1:23 check2:24 1511 └── project 1512 ├── columns: check1:23 check2:24!null abc.a:5!null abc.b:6 column9:9 column10:10 checks.a:11 checks.b:12 checks.c:13 d:14 b_new:18 column19:19 upsert_b:20 upsert_c:21 upsert_d:22 1513 ├── project 1514 │ ├── columns: upsert_b:20 upsert_c:21 upsert_d:22 abc.a:5!null abc.b:6 column9:9 column10:10 checks.a:11 checks.b:12 checks.c:13 d:14 b_new:18 column19:19 1515 │ ├── project 1516 │ │ ├── columns: column19:19 abc.a:5!null abc.b:6 column9:9 column10:10 checks.a:11 checks.b:12 checks.c:13 d:14 b_new:18 1517 │ │ ├── project 1518 │ │ │ ├── columns: b_new:18 abc.a:5!null abc.b:6 column9:9 column10:10 checks.a:11 checks.b:12 checks.c:13 d:14 1519 │ │ │ ├── left-join (hash) 1520 │ │ │ │ ├── columns: abc.a:5!null abc.b:6 column9:9 column10:10 checks.a:11 checks.b:12 checks.c:13 d:14 1521 │ │ │ │ ├── ensure-upsert-distinct-on 1522 │ │ │ │ │ ├── columns: abc.a:5!null abc.b:6 column9:9 column10:10 1523 │ │ │ │ │ ├── grouping columns: abc.a:5!null 1524 │ │ │ │ │ ├── project 1525 │ │ │ │ │ │ ├── columns: column10:10 abc.a:5!null abc.b:6 column9:9 1526 │ │ │ │ │ │ ├── project 1527 │ │ │ │ │ │ │ ├── columns: column9:9 abc.a:5!null abc.b:6 1528 │ │ │ │ │ │ │ ├── project 1529 │ │ │ │ │ │ │ │ ├── columns: abc.a:5!null abc.b:6 1530 │ │ │ │ │ │ │ │ └── scan abc 1531 │ │ │ │ │ │ │ │ ├── columns: abc.a:5!null abc.b:6 abc.c:7 rowid:8!null 1532 │ │ │ │ │ │ │ │ └── computed column expressions 1533 │ │ │ │ │ │ │ │ └── abc.c:7 1534 │ │ │ │ │ │ │ │ └── abc.b:6 + 1 1535 │ │ │ │ │ │ │ └── projections 1536 │ │ │ │ │ │ │ └── NULL::INT8 [as=column9:9] 1537 │ │ │ │ │ │ └── projections 1538 │ │ │ │ │ │ └── column9:9 + 1 [as=column10:10] 1539 │ │ │ │ │ └── aggregations 1540 │ │ │ │ │ ├── first-agg [as=abc.b:6] 1541 │ │ │ │ │ │ └── abc.b:6 1542 │ │ │ │ │ ├── first-agg [as=column9:9] 1543 │ │ │ │ │ │ └── column9:9 1544 │ │ │ │ │ └── first-agg [as=column10:10] 1545 │ │ │ │ │ └── column10:10 1546 │ │ │ │ ├── scan checks 1547 │ │ │ │ │ ├── columns: checks.a:11!null checks.b:12 checks.c:13 d:14 1548 │ │ │ │ │ ├── check constraint expressions 1549 │ │ │ │ │ │ └── checks.a:11 > 0 1550 │ │ │ │ │ └── computed column expressions 1551 │ │ │ │ │ └── d:14 1552 │ │ │ │ │ └── checks.c:13 + 1 1553 │ │ │ │ └── filters 1554 │ │ │ │ └── abc.a:5 = checks.a:11 1555 │ │ │ └── projections 1556 │ │ │ └── subquery [as=b_new:18] 1557 │ │ │ └── max1-row 1558 │ │ │ ├── columns: x:15!null 1559 │ │ │ └── project 1560 │ │ │ ├── columns: x:15!null 1561 │ │ │ └── select 1562 │ │ │ ├── columns: x:15!null y:16 z:17 1563 │ │ │ ├── scan xyz 1564 │ │ │ │ └── columns: x:15!null y:16 z:17 1565 │ │ │ └── filters 1566 │ │ │ └── x:15 = checks.a:11 1567 │ │ └── projections 1568 │ │ └── checks.c:13 + 1 [as=column19:19] 1569 │ └── projections 1570 │ ├── CASE WHEN checks.a:11 IS NULL THEN abc.b:6 ELSE b_new:18 END [as=upsert_b:20] 1571 │ ├── CASE WHEN checks.a:11 IS NULL THEN column9:9 ELSE checks.c:13 END [as=upsert_c:21] 1572 │ └── CASE WHEN checks.a:11 IS NULL THEN column10:10 ELSE column19:19 END [as=upsert_d:22] 1573 └── projections 1574 ├── upsert_b:20 < upsert_d:22 [as=check1:23] 1575 └── abc.a:5 > 0 [as=check2:24] 1576 1577 # Use ORDER BY in upsert input (should be ignored and not cause error). 1578 build 1579 INSERT INTO xyz 1580 SELECT a, b, c FROM abc ORDER BY a 1581 ON CONFLICT (z, y) DO UPDATE SET y=5 1582 ---- 1583 upsert xyz 1584 ├── columns: <none> 1585 ├── canary column: 8 1586 ├── fetch columns: x:8 y:9 z:10 1587 ├── insert-mapping: 1588 │ ├── a:4 => x:1 1589 │ ├── b:5 => y:2 1590 │ └── c:6 => z:3 1591 ├── update-mapping: 1592 │ └── upsert_y:13 => y:2 1593 └── project 1594 ├── columns: upsert_x:12 upsert_y:13 upsert_z:14 a:4!null b:5 c:6 x:8 y:9 z:10 y_new:11!null 1595 ├── project 1596 │ ├── columns: y_new:11!null a:4!null b:5 c:6 x:8 y:9 z:10 1597 │ ├── left-join (hash) 1598 │ │ ├── columns: a:4!null b:5 c:6 x:8 y:9 z:10 1599 │ │ ├── ensure-upsert-distinct-on 1600 │ │ │ ├── columns: a:4!null b:5 c:6 1601 │ │ │ ├── grouping columns: b:5 c:6 1602 │ │ │ ├── project 1603 │ │ │ │ ├── columns: a:4!null b:5 c:6 1604 │ │ │ │ └── scan abc 1605 │ │ │ │ ├── columns: a:4!null b:5 c:6 rowid:7!null 1606 │ │ │ │ └── computed column expressions 1607 │ │ │ │ └── c:6 1608 │ │ │ │ └── b:5 + 1 1609 │ │ │ └── aggregations 1610 │ │ │ └── first-agg [as=a:4] 1611 │ │ │ └── a:4 1612 │ │ ├── scan xyz 1613 │ │ │ └── columns: x:8!null y:9 z:10 1614 │ │ └── filters 1615 │ │ ├── b:5 = y:9 1616 │ │ └── c:6 = z:10 1617 │ └── projections 1618 │ └── 5 [as=y_new:11] 1619 └── projections 1620 ├── CASE WHEN x:8 IS NULL THEN a:4 ELSE x:8 END [as=upsert_x:12] 1621 ├── CASE WHEN x:8 IS NULL THEN b:5 ELSE y_new:11 END [as=upsert_y:13] 1622 └── CASE WHEN x:8 IS NULL THEN c:6 ELSE z:10 END [as=upsert_z:14] 1623 1624 # ------------------------------------------------------------------------------ 1625 # Test decimal column truncation. 1626 # ------------------------------------------------------------------------------ 1627 1628 # Fast UPSERT case. 1629 opt 1630 UPSERT INTO decimals (a, b) VALUES (1.1, ARRAY[0.95]) 1631 ---- 1632 upsert decimals 1633 ├── columns: <none> 1634 ├── canary column: 13 1635 ├── fetch columns: decimals.a:13 decimals.b:14 decimals.c:15 decimals.d:16 1636 ├── insert-mapping: 1637 │ ├── a:8 => decimals.a:1 1638 │ ├── b:17 => decimals.b:2 1639 │ ├── c:10 => decimals.c:3 1640 │ └── d:12 => decimals.d:4 1641 ├── update-mapping: 1642 │ ├── b:17 => decimals.b:2 1643 │ └── upsert_d:22 => decimals.d:4 1644 ├── check columns: check1:23 check2:24 1645 └── project 1646 ├── columns: check1:23 check2:24 a:8!null c:10!null d:12!null decimals.a:13 decimals.b:14 decimals.c:15 decimals.d:16 b:17 upsert_d:22 1647 ├── project 1648 │ ├── columns: upsert_a:20 upsert_d:22 b:17 a:8!null c:10!null d:12!null decimals.a:13 decimals.b:14 decimals.c:15 decimals.d:16 1649 │ ├── left-join (cross) 1650 │ │ ├── columns: a:8!null b:9 c:10!null d:12!null decimals.a:13 decimals.b:14 decimals.c:15 decimals.d:16 1651 │ │ ├── values 1652 │ │ │ ├── columns: a:8!null b:9 c:10!null d:12!null 1653 │ │ │ └── (1, crdb_internal.round_decimal_values(ARRAY[0.95], 1), 1.2, 2.2) 1654 │ │ ├── scan decimals 1655 │ │ │ ├── columns: decimals.a:13!null decimals.b:14 decimals.c:15 decimals.d:16 1656 │ │ │ └── constraint: /13: [/1 - /1] 1657 │ │ └── filters (true) 1658 │ └── projections 1659 │ ├── CASE WHEN decimals.a:13 IS NULL THEN a:8 ELSE decimals.a:13 END [as=upsert_a:20] 1660 │ ├── CASE WHEN decimals.a:13 IS NULL THEN d:12 ELSE crdb_internal.round_decimal_values(decimals.a:13 + decimals.c:15, 1) END [as=upsert_d:22] 1661 │ └── crdb_internal.round_decimal_values(b:9, 1) [as=b:17] 1662 └── projections 1663 ├── upsert_a:20 = round(upsert_a:20) [as=check1:23] 1664 └── b:17[0] > 1 [as=check2:24] 1665 1666 # Regular UPSERT case. 1667 opt 1668 UPSERT INTO decimals (a) VALUES (1.1) 1669 ---- 1670 upsert decimals 1671 ├── columns: <none> 1672 ├── canary column: 13 1673 ├── fetch columns: decimals.a:13 decimals.b:14 decimals.c:15 decimals.d:16 1674 ├── insert-mapping: 1675 │ ├── a:8 => decimals.a:1 1676 │ ├── b:9 => decimals.b:2 1677 │ ├── c:10 => decimals.c:3 1678 │ └── d:12 => decimals.d:4 1679 ├── update-mapping: 1680 │ └── upsert_d:22 => decimals.d:4 1681 ├── check columns: check1:23 check2:24 1682 └── project 1683 ├── columns: check1:23 check2:24 a:8!null b:9 c:10!null d:12!null decimals.a:13 decimals.b:14 decimals.c:15 decimals.d:16 upsert_d:22 1684 ├── project 1685 │ ├── columns: upsert_a:19 upsert_b:20 upsert_d:22 a:8!null b:9 c:10!null d:12!null decimals.a:13 decimals.b:14 decimals.c:15 decimals.d:16 1686 │ ├── left-join (cross) 1687 │ │ ├── columns: a:8!null b:9 c:10!null d:12!null decimals.a:13 decimals.b:14 decimals.c:15 decimals.d:16 1688 │ │ ├── values 1689 │ │ │ ├── columns: a:8!null b:9 c:10!null d:12!null 1690 │ │ │ └── (1, crdb_internal.round_decimal_values(CAST(NULL AS DECIMAL(5,1)[]), 1), 1.2, 2.2) 1691 │ │ ├── scan decimals 1692 │ │ │ ├── columns: decimals.a:13!null decimals.b:14 decimals.c:15 decimals.d:16 1693 │ │ │ └── constraint: /13: [/1 - /1] 1694 │ │ └── filters (true) 1695 │ └── projections 1696 │ ├── CASE WHEN decimals.a:13 IS NULL THEN a:8 ELSE decimals.a:13 END [as=upsert_a:19] 1697 │ ├── CASE WHEN decimals.a:13 IS NULL THEN b:9 ELSE decimals.b:14 END [as=upsert_b:20] 1698 │ └── CASE WHEN decimals.a:13 IS NULL THEN d:12 ELSE crdb_internal.round_decimal_values(decimals.a:13 + decimals.c:15, 1) END [as=upsert_d:22] 1699 └── projections 1700 ├── upsert_a:19 = round(upsert_a:19) [as=check1:23] 1701 └── upsert_b:20[0] > 1 [as=check2:24] 1702 1703 # INSERT...ON CONFLICT case. 1704 opt 1705 INSERT INTO decimals (a, b) VALUES (1.1, ARRAY[0.95]) 1706 ON CONFLICT (a) 1707 DO UPDATE SET b=ARRAY[0.99] 1708 ---- 1709 upsert decimals 1710 ├── columns: <none> 1711 ├── canary column: 13 1712 ├── fetch columns: decimals.a:13 decimals.b:14 decimals.c:15 decimals.d:16 1713 ├── insert-mapping: 1714 │ ├── a:8 => decimals.a:1 1715 │ ├── b:9 => decimals.b:2 1716 │ ├── c:10 => decimals.c:3 1717 │ └── d:12 => decimals.d:4 1718 ├── update-mapping: 1719 │ ├── upsert_b:22 => decimals.b:2 1720 │ └── upsert_d:24 => decimals.d:4 1721 ├── check columns: check1:25 check2:26 1722 └── project 1723 ├── columns: check1:25 check2:26 a:8!null b:9 c:10!null d:12!null decimals.a:13 decimals.b:14 decimals.c:15 decimals.d:16 upsert_b:22 upsert_d:24 1724 ├── project 1725 │ ├── columns: upsert_a:21 upsert_b:22 upsert_d:24 a:8!null b:9 c:10!null d:12!null decimals.a:13 decimals.b:14 decimals.c:15 decimals.d:16 1726 │ ├── left-join (cross) 1727 │ │ ├── columns: a:8!null b:9 c:10!null d:12!null decimals.a:13 decimals.b:14 decimals.c:15 decimals.d:16 1728 │ │ ├── values 1729 │ │ │ ├── columns: a:8!null b:9 c:10!null d:12!null 1730 │ │ │ └── (1, crdb_internal.round_decimal_values(ARRAY[0.95], 1), 1.2, 2.2) 1731 │ │ ├── scan decimals 1732 │ │ │ ├── columns: decimals.a:13!null decimals.b:14 decimals.c:15 decimals.d:16 1733 │ │ │ └── constraint: /13: [/1 - /1] 1734 │ │ └── filters (true) 1735 │ └── projections 1736 │ ├── CASE WHEN decimals.a:13 IS NULL THEN a:8 ELSE decimals.a:13 END [as=upsert_a:21] 1737 │ ├── CASE WHEN decimals.a:13 IS NULL THEN b:9 ELSE crdb_internal.round_decimal_values(ARRAY[0.99], 1) END [as=upsert_b:22] 1738 │ └── CASE WHEN decimals.a:13 IS NULL THEN d:12 ELSE crdb_internal.round_decimal_values(decimals.a:13 + decimals.c:15, 1) END [as=upsert_d:24] 1739 └── projections 1740 ├── upsert_a:21 = round(upsert_a:21) [as=check1:25] 1741 └── upsert_b:22[0] > 1 [as=check2:26]