github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/insert (about) 1 exec-ddl 2 CREATE TABLE abcde ( 3 a INT NOT NULL, 4 b INT, 5 c INT DEFAULT (10), 6 d INT AS (b + c + 1) STORED, 7 e INT AS (a) STORED 8 ) 9 ---- 10 11 exec-ddl 12 CREATE TABLE xyz ( 13 x TEXT PRIMARY KEY, 14 y INT8, 15 z FLOAT8 16 ) 17 ---- 18 19 exec-ddl 20 CREATE TABLE uv ( 21 u DECIMAL, 22 v BYTES 23 ) 24 ---- 25 26 exec-ddl 27 CREATE TABLE mutation ( 28 m INT PRIMARY KEY, 29 n INT, 30 "o:write-only" INT DEFAULT(10), 31 "p:write-only" INT AS (o + n) STORED, 32 "q:delete-only" INT AS (m * p) STORED, 33 CHECK (m > 0) 34 ) 35 ---- 36 37 exec-ddl 38 CREATE TABLE checks ( 39 a INT PRIMARY KEY CHECK (a > 0), 40 b INT, 41 c INT, 42 d INT AS (c + 1) STORED, 43 CHECK (b < d) 44 ) 45 ---- 46 47 exec-ddl 48 CREATE TABLE decimals ( 49 a DECIMAL(10,0) PRIMARY KEY CHECK (round(a) = a), 50 b DECIMAL(5,1)[] CHECK (b[0] > 1), 51 c DECIMAL(10,1) DEFAULT (1.23), 52 d DECIMAL(10,1) AS (a+c) STORED 53 ) 54 ---- 55 56 # Unknown target table. 57 build 58 INSERT INTO unknown VALUES (1, 2, 3) 59 ---- 60 error (42P01): no data source matches prefix: "unknown" 61 62 # ------------------------------------------------------------------------------ 63 # Tests without target column names. 64 # ------------------------------------------------------------------------------ 65 66 # Specify values for all non-hidden columns. 67 build 68 INSERT INTO abcde VALUES (1, 2, 3) 69 ---- 70 insert abcde 71 ├── columns: <none> 72 ├── insert-mapping: 73 │ ├── column1:7 => a:1 74 │ ├── column2:8 => b:2 75 │ ├── column3:9 => c:3 76 │ ├── column11:11 => d:4 77 │ ├── column1:7 => e:5 78 │ └── column10:10 => rowid:6 79 └── project 80 ├── columns: column11:11!null column1:7!null column2:8!null column3:9!null column10:10 81 ├── project 82 │ ├── columns: column10:10 column1:7!null column2:8!null column3:9!null 83 │ ├── values 84 │ │ ├── columns: column1:7!null column2:8!null column3:9!null 85 │ │ └── (1, 2, 3) 86 │ └── projections 87 │ └── unique_rowid() [as=column10:10] 88 └── projections 89 └── (column2:8 + column3:9) + 1 [as=column11:11] 90 91 # Don't specify values for null or default columns. 92 build 93 INSERT INTO abcde VALUES (1) 94 ---- 95 insert abcde 96 ├── columns: <none> 97 ├── insert-mapping: 98 │ ├── column1:7 => a:1 99 │ ├── column8:8 => b:2 100 │ ├── column9:9 => c:3 101 │ ├── column11:11 => d:4 102 │ ├── column1:7 => e:5 103 │ └── column10:10 => rowid:6 104 └── project 105 ├── columns: column11:11 column1:7!null column8:8 column9:9!null column10:10 106 ├── project 107 │ ├── columns: column8:8 column9:9!null column10:10 column1:7!null 108 │ ├── values 109 │ │ ├── columns: column1:7!null 110 │ │ └── (1,) 111 │ └── projections 112 │ ├── NULL::INT8 [as=column8:8] 113 │ ├── 10 [as=column9:9] 114 │ └── unique_rowid() [as=column10:10] 115 └── projections 116 └── (column8:8 + column9:9) + 1 [as=column11:11] 117 118 # Ordered input. 119 build 120 INSERT INTO abcde SELECT y FROM xyz ORDER BY y, z LIMIT 10 121 ---- 122 insert abcde 123 ├── columns: <none> 124 ├── insert-mapping: 125 │ ├── y:8 => a:1 126 │ ├── column10:10 => b:2 127 │ ├── column11:11 => c:3 128 │ ├── column13:13 => d:4 129 │ ├── y:8 => e:5 130 │ └── column12:12 => rowid:6 131 └── project 132 ├── columns: column13:13 y:8 column10:10 column11:11!null column12:12 133 ├── project 134 │ ├── columns: column10:10 column11:11!null column12:12 y:8 135 │ ├── limit 136 │ │ ├── columns: y:8 z:9 137 │ │ ├── internal-ordering: +8,+9 138 │ │ ├── sort 139 │ │ │ ├── columns: y:8 z:9 140 │ │ │ ├── ordering: +8,+9 141 │ │ │ ├── limit hint: 10.00 142 │ │ │ └── project 143 │ │ │ ├── columns: y:8 z:9 144 │ │ │ └── scan xyz 145 │ │ │ └── columns: x:7!null y:8 z:9 146 │ │ └── 10 147 │ └── projections 148 │ ├── NULL::INT8 [as=column10:10] 149 │ ├── 10 [as=column11:11] 150 │ └── unique_rowid() [as=column12:12] 151 └── projections 152 └── (column10:10 + column11:11) + 1 [as=column13:13] 153 154 # Ignore ORDER BY without LIMIT. 155 build 156 INSERT INTO abcde SELECT y FROM xyz ORDER BY y, z 157 ---- 158 insert abcde 159 ├── columns: <none> 160 ├── insert-mapping: 161 │ ├── y:8 => a:1 162 │ ├── column10:10 => b:2 163 │ ├── column11:11 => c:3 164 │ ├── column13:13 => d:4 165 │ ├── y:8 => e:5 166 │ └── column12:12 => rowid:6 167 └── project 168 ├── columns: column13:13 y:8 column10:10 column11:11!null column12:12 169 ├── project 170 │ ├── columns: column10:10 column11:11!null column12:12 y:8 171 │ ├── project 172 │ │ ├── columns: y:8 z:9 173 │ │ └── scan xyz 174 │ │ └── columns: x:7!null y:8 z:9 175 │ └── projections 176 │ ├── NULL::INT8 [as=column10:10] 177 │ ├── 10 [as=column11:11] 178 │ └── unique_rowid() [as=column12:12] 179 └── projections 180 └── (column10:10 + column11:11) + 1 [as=column13:13] 181 182 # Use placeholders. 183 build 184 INSERT INTO xyz VALUES ($1, $2, $3) 185 ---- 186 insert xyz 187 ├── columns: <none> 188 ├── insert-mapping: 189 │ ├── column1:4 => x:1 190 │ ├── column2:5 => y:2 191 │ └── column3:6 => z:3 192 └── values 193 ├── columns: column1:4 column2:5 column3:6 194 └── ($1, $2, $3) 195 196 # Null expressions. 197 build 198 INSERT INTO abcde VALUES (2, null, null) 199 ---- 200 insert abcde 201 ├── columns: <none> 202 ├── insert-mapping: 203 │ ├── column1:7 => a:1 204 │ ├── column2:8 => b:2 205 │ ├── column3:9 => c:3 206 │ ├── column11:11 => d:4 207 │ ├── column1:7 => e:5 208 │ └── column10:10 => rowid:6 209 └── project 210 ├── columns: column11:11 column1:7!null column2:8 column3:9 column10:10 211 ├── project 212 │ ├── columns: column10:10 column1:7!null column2:8 column3:9 213 │ ├── values 214 │ │ ├── columns: column1:7!null column2:8 column3:9 215 │ │ └── (2, NULL::INT8, NULL::INT8) 216 │ └── projections 217 │ └── unique_rowid() [as=column10:10] 218 └── projections 219 └── (column2:8 + column3:9) + 1 [as=column11:11] 220 221 # Duplicate expressions. 222 build 223 INSERT INTO abcde SELECT 2, $1 + 1, $1 + 1 224 ---- 225 insert abcde 226 ├── columns: <none> 227 ├── insert-mapping: 228 │ ├── "?column?":7 => a:1 229 │ ├── "?column?":8 => b:2 230 │ ├── "?column?":8 => c:3 231 │ ├── column10:10 => d:4 232 │ ├── "?column?":7 => e:5 233 │ └── column9:9 => rowid:6 234 └── project 235 ├── columns: column10:10 "?column?":7!null "?column?":8 column9:9 236 ├── project 237 │ ├── columns: column9:9 "?column?":7!null "?column?":8 238 │ ├── project 239 │ │ ├── columns: "?column?":7!null "?column?":8 240 │ │ ├── values 241 │ │ │ └── () 242 │ │ └── projections 243 │ │ ├── 2 [as="?column?":7] 244 │ │ └── $1 + 1 [as="?column?":8] 245 │ └── projections 246 │ └── unique_rowid() [as=column9:9] 247 └── projections 248 └── ("?column?":8 + "?column?":8) + 1 [as=column10:10] 249 250 # Use DEFAULT VALUES. 251 build 252 INSERT INTO uv DEFAULT VALUES 253 ---- 254 insert uv 255 ├── columns: <none> 256 ├── insert-mapping: 257 │ ├── column4:4 => u:1 258 │ ├── column5:5 => v:2 259 │ └── column6:6 => rowid:3 260 └── project 261 ├── columns: column4:4 column5:5 column6:6 262 ├── values 263 │ └── () 264 └── projections 265 ├── NULL::DECIMAL [as=column4:4] 266 ├── NULL::BYTES [as=column5:5] 267 └── unique_rowid() [as=column6:6] 268 269 # Use DEFAULT expressions in VALUES expression. 270 build 271 INSERT INTO abcde ((VALUES (1, DEFAULT, 2), (2, 3, 4), (3, 2, DEFAULT), (4, DEFAULT, DEFAULT))) 272 ---- 273 insert abcde 274 ├── columns: <none> 275 ├── insert-mapping: 276 │ ├── column1:7 => a:1 277 │ ├── column2:8 => b:2 278 │ ├── column3:9 => c:3 279 │ ├── column11:11 => d:4 280 │ ├── column1:7 => e:5 281 │ └── column10:10 => rowid:6 282 └── project 283 ├── columns: column11:11 column1:7!null column2:8 column3:9!null column10:10 284 ├── project 285 │ ├── columns: column10:10 column1:7!null column2:8 column3:9!null 286 │ ├── values 287 │ │ ├── columns: column1:7!null column2:8 column3:9!null 288 │ │ ├── (1, NULL::INT8, 2) 289 │ │ ├── (2, 3, 4) 290 │ │ ├── (3, 2, 10) 291 │ │ └── (4, NULL::INT8, 10) 292 │ └── projections 293 │ └── unique_rowid() [as=column10:10] 294 └── projections 295 └── (column2:8 + column3:9) + 1 [as=column11:11] 296 297 # Use DEFAULT expressions in VALUES expression wrapped by WITH clause (error). 298 build 299 INSERT INTO abcde WITH a AS (SELECT 1) VALUES (1, DEFAULT, 2) 300 ---- 301 error (42601): DEFAULT can only appear in a VALUES list within INSERT or on the right side of a SET 302 303 # Too many values. 304 build 305 INSERT INTO xyz VALUES ('foo', 2, 3, 4) 306 ---- 307 error (42601): INSERT has more expressions than target columns, 4 expressions for 3 targets 308 309 # Return values from insert. 310 build 311 INSERT INTO abcde SELECT 1 RETURNING * 312 ---- 313 project 314 ├── columns: a:1!null b:2 c:3!null d:4 e:5!null 315 └── insert abcde 316 ├── columns: a:1!null b:2 c:3!null d:4 e:5!null rowid:6!null 317 ├── insert-mapping: 318 │ ├── "?column?":7 => a:1 319 │ ├── column8:8 => b:2 320 │ ├── column9:9 => c:3 321 │ ├── column11:11 => d:4 322 │ ├── "?column?":7 => e:5 323 │ └── column10:10 => rowid:6 324 └── project 325 ├── columns: column11:11 "?column?":7!null column8:8 column9:9!null column10:10 326 ├── project 327 │ ├── columns: column8:8 column9:9!null column10:10 "?column?":7!null 328 │ ├── project 329 │ │ ├── columns: "?column?":7!null 330 │ │ ├── values 331 │ │ │ └── () 332 │ │ └── projections 333 │ │ └── 1 [as="?column?":7] 334 │ └── projections 335 │ ├── NULL::INT8 [as=column8:8] 336 │ ├── 10 [as=column9:9] 337 │ └── unique_rowid() [as=column10:10] 338 └── projections 339 └── (column8:8 + column9:9) + 1 [as=column11:11] 340 341 # Return values from aliased table. 342 build 343 INSERT INTO abcde AS foo SELECT 1 RETURNING foo.a + 1, foo.b * foo.c 344 ---- 345 project 346 ├── columns: "?column?":12!null "?column?":13 347 ├── insert foo 348 │ ├── columns: a:1!null b:2 c:3!null d:4 e:5!null rowid:6!null 349 │ ├── insert-mapping: 350 │ │ ├── "?column?":7 => a:1 351 │ │ ├── column8:8 => b:2 352 │ │ ├── column9:9 => c:3 353 │ │ ├── column11:11 => d:4 354 │ │ ├── "?column?":7 => e:5 355 │ │ └── column10:10 => rowid:6 356 │ └── project 357 │ ├── columns: column11:11 "?column?":7!null column8:8 column9:9!null column10:10 358 │ ├── project 359 │ │ ├── columns: column8:8 column9:9!null column10:10 "?column?":7!null 360 │ │ ├── project 361 │ │ │ ├── columns: "?column?":7!null 362 │ │ │ ├── values 363 │ │ │ │ └── () 364 │ │ │ └── projections 365 │ │ │ └── 1 [as="?column?":7] 366 │ │ └── projections 367 │ │ ├── NULL::INT8 [as=column8:8] 368 │ │ ├── 10 [as=column9:9] 369 │ │ └── unique_rowid() [as=column10:10] 370 │ └── projections 371 │ └── (column8:8 + column9:9) + 1 [as=column11:11] 372 └── projections 373 ├── a:1 + 1 [as="?column?":12] 374 └── b:2 * c:3 [as="?column?":13] 375 376 # Use returning INSERT as a FROM expression. 377 build 378 SELECT * FROM [INSERT INTO abcde VALUES (1) RETURNING *] 379 ---- 380 with &1 381 ├── columns: a:12!null b:13 c:14!null d:15 e:16!null 382 ├── project 383 │ ├── columns: abcde.a:1!null abcde.b:2 abcde.c:3!null abcde.d:4 abcde.e:5!null 384 │ └── insert abcde 385 │ ├── columns: abcde.a:1!null abcde.b:2 abcde.c:3!null abcde.d:4 abcde.e:5!null rowid:6!null 386 │ ├── insert-mapping: 387 │ │ ├── column1:7 => abcde.a:1 388 │ │ ├── column8:8 => abcde.b:2 389 │ │ ├── column9:9 => abcde.c:3 390 │ │ ├── column11:11 => abcde.d:4 391 │ │ ├── column1:7 => abcde.e:5 392 │ │ └── column10:10 => rowid:6 393 │ └── project 394 │ ├── columns: column11:11 column1:7!null column8:8 column9:9!null column10:10 395 │ ├── project 396 │ │ ├── columns: column8:8 column9:9!null column10:10 column1:7!null 397 │ │ ├── values 398 │ │ │ ├── columns: column1:7!null 399 │ │ │ └── (1,) 400 │ │ └── projections 401 │ │ ├── NULL::INT8 [as=column8:8] 402 │ │ ├── 10 [as=column9:9] 403 │ │ └── unique_rowid() [as=column10:10] 404 │ └── projections 405 │ └── (column8:8 + column9:9) + 1 [as=column11:11] 406 └── with-scan &1 407 ├── columns: a:12!null b:13 c:14!null d:15 e:16!null 408 └── mapping: 409 ├── abcde.a:1 => a:12 410 ├── abcde.b:2 => b:13 411 ├── abcde.c:3 => c:14 412 ├── abcde.d:4 => d:15 413 └── abcde.e:5 => e:16 414 415 # Try to use aggregate function in RETURNING clause. 416 build 417 INSERT INTO abcde VALUES (1) RETURNING sum(a) 418 ---- 419 error (42803): sum(): aggregate functions are not allowed in RETURNING 420 421 # Try to use SRF in RETURNING clause. 422 build 423 INSERT INTO abcde VALUES (1) RETURNING generate_series(1, 10) 424 ---- 425 error (0A000): generate_series(): generator functions are not allowed in RETURNING 426 427 # Try to use non-returning INSERT as expression. 428 build 429 SELECT * FROM [INSERT INTO abcde VALUES (1)] 430 ---- 431 error (42703): statement source "INSERT INTO abcde VALUES (1)" does not return any columns 432 433 # Use CTE with multiple variables. 434 build 435 WITH a AS (SELECT y, y+1 FROM xyz) INSERT INTO abcde SELECT * FROM a 436 ---- 437 with &1 (a) 438 ├── project 439 │ ├── columns: "?column?":4 xyz.y:2 440 │ ├── scan xyz 441 │ │ └── columns: x:1!null xyz.y:2 z:3 442 │ └── projections 443 │ └── xyz.y:2 + 1 [as="?column?":4] 444 └── insert abcde 445 ├── columns: <none> 446 ├── insert-mapping: 447 │ ├── y:11 => a:5 448 │ ├── "?column?":12 => b:6 449 │ ├── column13:13 => c:7 450 │ ├── column15:15 => d:8 451 │ ├── y:11 => e:9 452 │ └── column14:14 => rowid:10 453 └── project 454 ├── columns: column15:15 y:11 "?column?":12 column13:13!null column14:14 455 ├── project 456 │ ├── columns: column13:13!null column14:14 y:11 "?column?":12 457 │ ├── with-scan &1 (a) 458 │ │ ├── columns: y:11 "?column?":12 459 │ │ └── mapping: 460 │ │ ├── xyz.y:2 => y:11 461 │ │ └── "?column?":4 => "?column?":12 462 │ └── projections 463 │ ├── 10 [as=column13:13] 464 │ └── unique_rowid() [as=column14:14] 465 └── projections 466 └── ("?column?":12 + column13:13) + 1 [as=column15:15] 467 468 # Use CTE with multiple variables. 469 build 470 WITH a AS (SELECT y, y+1 FROM xyz), b AS (SELECT y+1, y FROM xyz) 471 INSERT INTO abcde TABLE a UNION TABLE b 472 ---- 473 with &1 (a) 474 ├── project 475 │ ├── columns: "?column?":4 xyz.y:2 476 │ ├── scan xyz 477 │ │ └── columns: x:1!null xyz.y:2 z:3 478 │ └── projections 479 │ └── xyz.y:2 + 1 [as="?column?":4] 480 └── with &2 (b) 481 ├── project 482 │ ├── columns: "?column?":8 xyz.y:6 483 │ ├── scan xyz 484 │ │ └── columns: x:5!null xyz.y:6 z:7 485 │ └── projections 486 │ └── xyz.y:6 + 1 [as="?column?":8] 487 └── insert abcde 488 ├── columns: <none> 489 ├── insert-mapping: 490 │ ├── y:19 => a:9 491 │ ├── "?column?":20 => b:10 492 │ ├── column21:21 => c:11 493 │ ├── column23:23 => d:12 494 │ ├── y:19 => e:13 495 │ └── column22:22 => rowid:14 496 └── project 497 ├── columns: column23:23 y:19 "?column?":20 column21:21!null column22:22 498 ├── project 499 │ ├── columns: column21:21!null column22:22 y:19 "?column?":20 500 │ ├── union 501 │ │ ├── columns: y:19 "?column?":20 502 │ │ ├── left columns: y:15 "?column?":16 503 │ │ ├── right columns: "?column?":17 y:18 504 │ │ ├── with-scan &1 (a) 505 │ │ │ ├── columns: y:15 "?column?":16 506 │ │ │ └── mapping: 507 │ │ │ ├── xyz.y:2 => y:15 508 │ │ │ └── "?column?":4 => "?column?":16 509 │ │ └── with-scan &2 (b) 510 │ │ ├── columns: "?column?":17 y:18 511 │ │ └── mapping: 512 │ │ ├── "?column?":8 => "?column?":17 513 │ │ └── xyz.y:6 => y:18 514 │ └── projections 515 │ ├── 10 [as=column21:21] 516 │ └── unique_rowid() [as=column22:22] 517 └── projections 518 └── ("?column?":20 + column21:21) + 1 [as=column23:23] 519 520 # Non-referenced CTE with mutation. 521 build 522 WITH cte AS (SELECT b FROM [INSERT INTO abcde VALUES (1) RETURNING *]) INSERT INTO abcde VALUES (1) 523 ---- 524 with &1 525 ├── project 526 │ ├── columns: abcde.a:1!null abcde.b:2 abcde.c:3!null abcde.d:4 abcde.e:5!null 527 │ └── insert abcde 528 │ ├── columns: abcde.a:1!null abcde.b:2 abcde.c:3!null abcde.d:4 abcde.e:5!null rowid:6!null 529 │ ├── insert-mapping: 530 │ │ ├── column1:7 => abcde.a:1 531 │ │ ├── column8:8 => abcde.b:2 532 │ │ ├── column9:9 => abcde.c:3 533 │ │ ├── column11:11 => abcde.d:4 534 │ │ ├── column1:7 => abcde.e:5 535 │ │ └── column10:10 => rowid:6 536 │ └── project 537 │ ├── columns: column11:11 column1:7!null column8:8 column9:9!null column10:10 538 │ ├── project 539 │ │ ├── columns: column8:8 column9:9!null column10:10 column1:7!null 540 │ │ ├── values 541 │ │ │ ├── columns: column1:7!null 542 │ │ │ └── (1,) 543 │ │ └── projections 544 │ │ ├── NULL::INT8 [as=column8:8] 545 │ │ ├── 10 [as=column9:9] 546 │ │ └── unique_rowid() [as=column10:10] 547 │ └── projections 548 │ └── (column8:8 + column9:9) + 1 [as=column11:11] 549 └── with &2 (cte) 550 ├── project 551 │ ├── columns: b:13 552 │ └── with-scan &1 553 │ ├── columns: a:12!null b:13 c:14!null d:15 e:16!null 554 │ └── mapping: 555 │ ├── abcde.a:1 => a:12 556 │ ├── abcde.b:2 => b:13 557 │ ├── abcde.c:3 => c:14 558 │ ├── abcde.d:4 => d:15 559 │ └── abcde.e:5 => e:16 560 └── insert abcde 561 ├── columns: <none> 562 ├── insert-mapping: 563 │ ├── column1:23 => abcde.a:17 564 │ ├── column24:24 => abcde.b:18 565 │ ├── column25:25 => abcde.c:19 566 │ ├── column27:27 => abcde.d:20 567 │ ├── column1:23 => abcde.e:21 568 │ └── column26:26 => rowid:22 569 └── project 570 ├── columns: column27:27 column1:23!null column24:24 column25:25!null column26:26 571 ├── project 572 │ ├── columns: column24:24 column25:25!null column26:26 column1:23!null 573 │ ├── values 574 │ │ ├── columns: column1:23!null 575 │ │ └── (1,) 576 │ └── projections 577 │ ├── NULL::INT8 [as=column24:24] 578 │ ├── 10 [as=column25:25] 579 │ └── unique_rowid() [as=column26:26] 580 └── projections 581 └── (column24:24 + column25:25) + 1 [as=column27:27] 582 583 # Insert CTE that returns no columns. 584 build 585 WITH cte AS (INSERT INTO abcde VALUES (1)) SELECT * FROM cte 586 ---- 587 error (0A000): WITH clause "cte" does not return any columns 588 589 # Use SRF in RETURNING clause. 590 build 591 INSERT INTO abcde VALUES (1) RETURNING generate_series(1, 100) 592 ---- 593 error (0A000): generate_series(): generator functions are not allowed in RETURNING 594 595 # ------------------------------------------------------------------------------ 596 # Tests with target column names. 597 # ------------------------------------------------------------------------------ 598 599 # Specify values for all non-computed columns. 600 build 601 INSERT INTO abcde (c, b, a) VALUES (1, 2, 3) 602 ---- 603 insert abcde 604 ├── columns: <none> 605 ├── insert-mapping: 606 │ ├── column3:9 => a:1 607 │ ├── column2:8 => b:2 608 │ ├── column1:7 => c:3 609 │ ├── column11:11 => d:4 610 │ ├── column3:9 => e:5 611 │ └── column10:10 => rowid:6 612 └── project 613 ├── columns: column11:11!null column1:7!null column2:8!null column3:9!null column10:10 614 ├── project 615 │ ├── columns: column10:10 column1:7!null column2:8!null column3:9!null 616 │ ├── values 617 │ │ ├── columns: column1:7!null column2:8!null column3:9!null 618 │ │ └── (1, 2, 3) 619 │ └── projections 620 │ └── unique_rowid() [as=column10:10] 621 └── projections 622 └── (column2:8 + column1:7) + 1 [as=column11:11] 623 624 # Don't specify values for null or default columns. 625 build 626 INSERT INTO abcde (a) VALUES (1) 627 ---- 628 insert abcde 629 ├── columns: <none> 630 ├── insert-mapping: 631 │ ├── column1:7 => a:1 632 │ ├── column8:8 => b:2 633 │ ├── column9:9 => c:3 634 │ ├── column11:11 => d:4 635 │ ├── column1:7 => e:5 636 │ └── column10:10 => rowid:6 637 └── project 638 ├── columns: column11:11 column1:7!null column8:8 column9:9!null column10:10 639 ├── project 640 │ ├── columns: column8:8 column9:9!null column10:10 column1:7!null 641 │ ├── values 642 │ │ ├── columns: column1:7!null 643 │ │ └── (1,) 644 │ └── projections 645 │ ├── NULL::INT8 [as=column8:8] 646 │ ├── 10 [as=column9:9] 647 │ └── unique_rowid() [as=column10:10] 648 └── projections 649 └── (column8:8 + column9:9) + 1 [as=column11:11] 650 651 # Insert value into hidden rowid column. 652 build 653 INSERT INTO abcde (a, rowid) VALUES (1, 2) RETURNING * 654 ---- 655 project 656 ├── columns: a:1!null b:2 c:3!null d:4 e:5!null 657 └── insert abcde 658 ├── columns: a:1!null b:2 c:3!null d:4 e:5!null rowid:6!null 659 ├── insert-mapping: 660 │ ├── column1:7 => a:1 661 │ ├── column9:9 => b:2 662 │ ├── column10:10 => c:3 663 │ ├── column11:11 => d:4 664 │ ├── column1:7 => e:5 665 │ └── column2:8 => rowid:6 666 └── project 667 ├── columns: column11:11 column1:7!null column2:8!null column9:9 column10:10!null 668 ├── project 669 │ ├── columns: column9:9 column10:10!null column1:7!null column2:8!null 670 │ ├── values 671 │ │ ├── columns: column1:7!null column2:8!null 672 │ │ └── (1, 2) 673 │ └── projections 674 │ ├── NULL::INT8 [as=column9:9] 675 │ └── 10 [as=column10:10] 676 └── projections 677 └── (column9:9 + column10:10) + 1 [as=column11:11] 678 679 # Use DEFAULT expressions in VALUES expression. 680 build 681 INSERT INTO abcde (c, b, a, rowid) 682 VALUES (DEFAULT, DEFAULT, 1, DEFAULT), (3, 2, 1, DEFAULT), (DEFAULT, DEFAULT, 2, 100) 683 RETURNING *, rowid 684 ---- 685 insert abcde 686 ├── columns: a:1!null b:2 c:3!null d:4 e:5!null rowid:6!null 687 ├── insert-mapping: 688 │ ├── column3:9 => a:1 689 │ ├── column2:8 => b:2 690 │ ├── column1:7 => c:3 691 │ ├── column11:11 => d:4 692 │ ├── column3:9 => e:5 693 │ └── column4:10 => rowid:6 694 └── project 695 ├── columns: column11:11 column1:7!null column2:8 column3:9!null column4:10 696 ├── values 697 │ ├── columns: column1:7!null column2:8 column3:9!null column4:10 698 │ ├── (10, NULL::INT8, 1, unique_rowid()) 699 │ ├── (3, 2, 1, unique_rowid()) 700 │ └── (10, NULL::INT8, 2, 100) 701 └── projections 702 └── (column2:8 + column1:7) + 1 [as=column11:11] 703 704 # Verify that there is no compile-time error when trying to insert a NULL 705 # DEFAULT value into a not-null column (it will fail at runtime). 706 build 707 INSERT INTO abcde (a) VALUES (DEFAULT) 708 ---- 709 insert abcde 710 ├── columns: <none> 711 ├── insert-mapping: 712 │ ├── column1:7 => a:1 713 │ ├── column8:8 => b:2 714 │ ├── column9:9 => c:3 715 │ ├── column11:11 => d:4 716 │ ├── column1:7 => e:5 717 │ └── column10:10 => rowid:6 718 └── project 719 ├── columns: column11:11 column1:7 column8:8 column9:9!null column10:10 720 ├── project 721 │ ├── columns: column8:8 column9:9!null column10:10 column1:7 722 │ ├── values 723 │ │ ├── columns: column1:7 724 │ │ └── (NULL::INT8,) 725 │ └── projections 726 │ ├── NULL::INT8 [as=column8:8] 727 │ ├── 10 [as=column9:9] 728 │ └── unique_rowid() [as=column10:10] 729 └── projections 730 └── (column8:8 + column9:9) + 1 [as=column11:11] 731 732 # Mismatched type. 733 build 734 INSERT INTO xyz (x) VALUES (10) 735 ---- 736 error (42804): value type int doesn't match type string of column "x" 737 738 # Try to insert into computed column. 739 build 740 INSERT INTO abcde (a, b, c, d) VALUES (1, 2, 3, 4) 741 ---- 742 error (55000): cannot write directly to computed column "d" 743 744 # Try to insert DEFAULT expression into computed column. 745 build 746 INSERT INTO abcde (a, d) VALUES (1, DEFAULT) 747 ---- 748 error (55000): cannot write directly to computed column "d" 749 750 # Too many values. 751 build 752 INSERT INTO abcde (a, b) VALUES (1, 2, 3) 753 ---- 754 error (42601): INSERT has more expressions than target columns, 3 expressions for 2 targets 755 756 # Too few values. 757 build 758 INSERT INTO abcde (a, b) VALUES (1) 759 ---- 760 error (42601): INSERT has more target columns than expressions, 1 expressions for 2 targets 761 762 # Duplicate column name. 763 build 764 INSERT INTO abcde (a, b, a) VALUES (1, 2, 3) 765 ---- 766 error (42601): multiple assignments to the same column "a" 767 768 # Undefined column name. 769 build 770 INSERT INTO abcde (a, unk) VALUES (1, 2) 771 ---- 772 error (42703): column "unk" does not exist 773 774 # Return values from insert. 775 build 776 INSERT INTO abcde (b, a) SELECT x::int, y FROM xyz RETURNING * 777 ---- 778 project 779 ├── columns: a:1!null b:2!null c:3!null d:4!null e:5 780 └── insert abcde 781 ├── columns: a:1!null b:2!null c:3!null d:4!null e:5 rowid:6!null 782 ├── insert-mapping: 783 │ ├── y:8 => a:1 784 │ ├── x:10 => b:2 785 │ ├── column11:11 => c:3 786 │ ├── column13:13 => d:4 787 │ ├── y:8 => e:5 788 │ └── column12:12 => rowid:6 789 └── project 790 ├── columns: column13:13!null y:8 x:10!null column11:11!null column12:12 791 ├── project 792 │ ├── columns: column11:11!null column12:12 y:8 x:10!null 793 │ ├── project 794 │ │ ├── columns: x:10!null y:8 795 │ │ ├── scan xyz 796 │ │ │ └── columns: xyz.x:7!null y:8 z:9 797 │ │ └── projections 798 │ │ └── xyz.x:7::INT8 [as=x:10] 799 │ └── projections 800 │ ├── 10 [as=column11:11] 801 │ └── unique_rowid() [as=column12:12] 802 └── projections 803 └── (x:10 + column11:11) + 1 [as=column13:13] 804 805 # Return hidden column. 806 build 807 INSERT INTO abcde (rowid, a) VALUES (1, 2) RETURNING *, rowid 808 ---- 809 insert abcde 810 ├── columns: a:1!null b:2 c:3!null d:4 e:5!null rowid:6!null 811 ├── insert-mapping: 812 │ ├── column2:8 => a:1 813 │ ├── column9:9 => b:2 814 │ ├── column10:10 => c:3 815 │ ├── column11:11 => d:4 816 │ ├── column2:8 => e:5 817 │ └── column1:7 => rowid:6 818 └── project 819 ├── columns: column11:11 column1:7!null column2:8!null column9:9 column10:10!null 820 ├── project 821 │ ├── columns: column9:9 column10:10!null column1:7!null column2:8!null 822 │ ├── values 823 │ │ ├── columns: column1:7!null column2:8!null 824 │ │ └── (1, 2) 825 │ └── projections 826 │ ├── NULL::INT8 [as=column9:9] 827 │ └── 10 [as=column10:10] 828 └── projections 829 └── (column9:9 + column10:10) + 1 [as=column11:11] 830 831 # Use returning INSERT as a FROM expression. 832 build 833 SELECT * FROM [INSERT INTO abcde (a, b) SELECT y+1, y FROM xyz RETURNING *] 834 ---- 835 with &1 836 ├── columns: a:14!null b:15 c:16!null d:17 e:18 837 ├── project 838 │ ├── columns: abcde.a:1!null abcde.b:2 abcde.c:3!null abcde.d:4 abcde.e:5 839 │ └── insert abcde 840 │ ├── columns: abcde.a:1!null abcde.b:2 abcde.c:3!null abcde.d:4 abcde.e:5 rowid:6!null 841 │ ├── insert-mapping: 842 │ │ ├── "?column?":10 => abcde.a:1 843 │ │ ├── y:8 => abcde.b:2 844 │ │ ├── column11:11 => abcde.c:3 845 │ │ ├── column13:13 => abcde.d:4 846 │ │ ├── "?column?":10 => abcde.e:5 847 │ │ └── column12:12 => rowid:6 848 │ └── project 849 │ ├── columns: column13:13 y:8 "?column?":10 column11:11!null column12:12 850 │ ├── project 851 │ │ ├── columns: column11:11!null column12:12 y:8 "?column?":10 852 │ │ ├── project 853 │ │ │ ├── columns: "?column?":10 y:8 854 │ │ │ ├── scan xyz 855 │ │ │ │ └── columns: x:7!null y:8 z:9 856 │ │ │ └── projections 857 │ │ │ └── y:8 + 1 [as="?column?":10] 858 │ │ └── projections 859 │ │ ├── 10 [as=column11:11] 860 │ │ └── unique_rowid() [as=column12:12] 861 │ └── projections 862 │ └── (y:8 + column11:11) + 1 [as=column13:13] 863 └── with-scan &1 864 ├── columns: a:14!null b:15 c:16!null d:17 e:18 865 └── mapping: 866 ├── abcde.a:1 => a:14 867 ├── abcde.b:2 => b:15 868 ├── abcde.c:3 => c:16 869 ├── abcde.d:4 => d:17 870 └── abcde.e:5 => e:18 871 872 # ------------------------------------------------------------------------------ 873 # Propagate desired INSERT types. 874 # ------------------------------------------------------------------------------ 875 876 # Propagate types to VALUES. 877 build 878 INSERT INTO xyz VALUES ($1, $2 + 1, $3 + 1) 879 ---- 880 insert xyz 881 ├── columns: <none> 882 ├── insert-mapping: 883 │ ├── column1:4 => x:1 884 │ ├── column2:5 => y:2 885 │ └── column3:6 => z:3 886 └── values 887 ├── columns: column1:4 column2:5 column3:6 888 └── ($1, $2 + 1, $3 + 1.0) 889 890 # Propagate types to VALUES (named columns). 891 build 892 INSERT INTO xyz (z, y, x) VALUES ($1 + 1, $2 + 1, $3) 893 ---- 894 insert xyz 895 ├── columns: <none> 896 ├── insert-mapping: 897 │ ├── column3:6 => x:1 898 │ ├── column2:5 => y:2 899 │ └── column1:4 => z:3 900 └── values 901 ├── columns: column1:4 column2:5 column3:6 902 └── ($1 + 1.0, $2 + 1, $3) 903 904 # Propagate types to projection list. 905 build 906 INSERT INTO xyz ((SELECT $1, $2 + 1, $3 + 1)) 907 ---- 908 insert xyz 909 ├── columns: <none> 910 ├── insert-mapping: 911 │ ├── "?column?":4 => x:1 912 │ ├── "?column?":5 => y:2 913 │ └── "?column?":6 => z:3 914 └── project 915 ├── columns: "?column?":4 "?column?":5 "?column?":6 916 ├── values 917 │ └── () 918 └── projections 919 ├── $1 [as="?column?":4] 920 ├── $2 + 1 [as="?column?":5] 921 └── $3 + 1.0 [as="?column?":6] 922 923 # Propagate types to projection list (named columns). 924 build 925 INSERT INTO xyz (x, y, z) SELECT $1, $2 + 1, $3 + 1 926 ---- 927 insert xyz 928 ├── columns: <none> 929 ├── insert-mapping: 930 │ ├── "?column?":4 => x:1 931 │ ├── "?column?":5 => y:2 932 │ └── "?column?":6 => z:3 933 └── project 934 ├── columns: "?column?":4 "?column?":5 "?column?":6 935 ├── values 936 │ └── () 937 └── projections 938 ├── $1 [as="?column?":4] 939 ├── $2 + 1 [as="?column?":5] 940 └── $3 + 1.0 [as="?column?":6] 941 942 # Propagate types to UNION. 943 build 944 INSERT INTO xyz (SELECT $1, $2 + 1, $3 + 1) UNION ALL (SELECT $1, $2 + 1, $3 + 1) 945 ---- 946 insert xyz 947 ├── columns: <none> 948 ├── insert-mapping: 949 │ ├── "?column?":10 => x:1 950 │ ├── "?column?":11 => y:2 951 │ └── "?column?":12 => z:3 952 └── union-all 953 ├── columns: "?column?":10 "?column?":11 "?column?":12 954 ├── left columns: "?column?":4 "?column?":5 "?column?":6 955 ├── right columns: "?column?":7 "?column?":8 "?column?":9 956 ├── project 957 │ ├── columns: "?column?":4 "?column?":5 "?column?":6 958 │ ├── values 959 │ │ └── () 960 │ └── projections 961 │ ├── $1 [as="?column?":4] 962 │ ├── $2 + 1 [as="?column?":5] 963 │ └── $3 + 1.0 [as="?column?":6] 964 └── project 965 ├── columns: "?column?":7 "?column?":8 "?column?":9 966 ├── values 967 │ └── () 968 └── projections 969 ├── $1 [as="?column?":7] 970 ├── $2 + 1 [as="?column?":8] 971 └── $3 + 1.0 [as="?column?":9] 972 973 # Propagate types to UNION (named columns). 974 build 975 INSERT INTO xyz (x, z, y) SELECT $1, $2 + 1, $3 + 1 UNION ALL SELECT $1, $2 + 1, $3 + 1 976 ---- 977 insert xyz 978 ├── columns: <none> 979 ├── insert-mapping: 980 │ ├── "?column?":10 => x:1 981 │ ├── "?column?":12 => y:2 982 │ └── "?column?":11 => z:3 983 └── union-all 984 ├── columns: "?column?":10 "?column?":11 "?column?":12 985 ├── left columns: "?column?":4 "?column?":5 "?column?":6 986 ├── right columns: "?column?":7 "?column?":8 "?column?":9 987 ├── project 988 │ ├── columns: "?column?":4 "?column?":5 "?column?":6 989 │ ├── values 990 │ │ └── () 991 │ └── projections 992 │ ├── $1 [as="?column?":4] 993 │ ├── $2 + 1.0 [as="?column?":5] 994 │ └── $3 + 1 [as="?column?":6] 995 └── project 996 ├── columns: "?column?":7 "?column?":8 "?column?":9 997 ├── values 998 │ └── () 999 └── projections 1000 ├── $1 [as="?column?":7] 1001 ├── $2 + 1.0 [as="?column?":8] 1002 └── $3 + 1 [as="?column?":9] 1003 1004 # ------------------------------------------------------------------------------ 1005 # Tests with mutation columns. 1006 # ------------------------------------------------------------------------------ 1007 1008 # Test mutation columns with default and computed values. 1009 build 1010 INSERT INTO mutation (m, n) VALUES (1, 2) 1011 ---- 1012 insert mutation 1013 ├── columns: <none> 1014 ├── insert-mapping: 1015 │ ├── column1:6 => m:1 1016 │ ├── column2:7 => n:2 1017 │ ├── column8:8 => o:3 1018 │ └── column9:9 => p:4 1019 ├── check columns: check1:10 1020 └── project 1021 ├── columns: check1:10!null column1:6!null column2:7!null column8:8!null column9:9!null 1022 ├── project 1023 │ ├── columns: column9:9!null column1:6!null column2:7!null column8:8!null 1024 │ ├── project 1025 │ │ ├── columns: column8:8!null column1:6!null column2:7!null 1026 │ │ ├── values 1027 │ │ │ ├── columns: column1:6!null column2:7!null 1028 │ │ │ └── (1, 2) 1029 │ │ └── projections 1030 │ │ └── 10 [as=column8:8] 1031 │ └── projections 1032 │ └── column8:8 + column2:7 [as=column9:9] 1033 └── projections 1034 └── column1:6 > 0 [as=check1:10] 1035 1036 # Use RETURNING clause and ensure that mutation columns aren't projected. 1037 build 1038 INSERT INTO mutation (m, n) VALUES (1, 2) RETURNING * 1039 ---- 1040 insert mutation 1041 ├── columns: m:1!null n:2!null 1042 ├── insert-mapping: 1043 │ ├── column1:6 => m:1 1044 │ ├── column2:7 => n:2 1045 │ ├── column8:8 => o:3 1046 │ └── column9:9 => p:4 1047 ├── check columns: check1:10 1048 └── project 1049 ├── columns: check1:10!null column1:6!null column2:7!null column8:8!null column9:9!null 1050 ├── project 1051 │ ├── columns: column9:9!null column1:6!null column2:7!null column8:8!null 1052 │ ├── project 1053 │ │ ├── columns: column8:8!null column1:6!null column2:7!null 1054 │ │ ├── values 1055 │ │ │ ├── columns: column1:6!null column2:7!null 1056 │ │ │ └── (1, 2) 1057 │ │ └── projections 1058 │ │ └── 10 [as=column8:8] 1059 │ └── projections 1060 │ └── column8:8 + column2:7 [as=column9:9] 1061 └── projections 1062 └── column1:6 > 0 [as=check1:10] 1063 1064 # Try to reference write-only mutation column in RETURNING clause. 1065 build 1066 INSERT INTO mutation (m, n) VALUES (1, 2) RETURNING o 1067 ---- 1068 error (42703): column "o" does not exist 1069 1070 # Try to reference delete-only mutation column in RETURNING clause. 1071 build 1072 INSERT INTO mutation (m, n) VALUES (1, 2) RETURNING p 1073 ---- 1074 error (42703): column "p" does not exist 1075 1076 # Try to insert into mutation column. 1077 build 1078 INSERT INTO mutation (m, n, p) VALUES (1, 2, 3) 1079 ---- 1080 error (42703): column "p" does not exist 1081 1082 # ------------------------------------------------------------------------------ 1083 # Test check constraints. 1084 # ------------------------------------------------------------------------------ 1085 1086 # Insert constants. 1087 build 1088 INSERT INTO checks (a, b, c) VALUES (1, 2, 3) 1089 ---- 1090 insert checks 1091 ├── columns: <none> 1092 ├── insert-mapping: 1093 │ ├── column1:5 => a:1 1094 │ ├── column2:6 => b:2 1095 │ ├── column3:7 => c:3 1096 │ └── column8:8 => d:4 1097 ├── check columns: check1:9 check2:10 1098 └── project 1099 ├── columns: check1:9!null check2:10!null column1:5!null column2:6!null column3:7!null column8:8!null 1100 ├── project 1101 │ ├── columns: column8:8!null column1:5!null column2:6!null column3:7!null 1102 │ ├── values 1103 │ │ ├── columns: column1:5!null column2:6!null column3:7!null 1104 │ │ └── (1, 2, 3) 1105 │ └── projections 1106 │ └── column3:7 + 1 [as=column8:8] 1107 └── projections 1108 ├── column2:6 < column8:8 [as=check1:9] 1109 └── column1:5 > 0 [as=check2:10] 1110 1111 # Insert results of SELECT. 1112 build 1113 INSERT INTO checks SELECT a, b, c FROM abcde 1114 ---- 1115 insert checks 1116 ├── columns: <none> 1117 ├── insert-mapping: 1118 │ ├── abcde.a:5 => checks.a:1 1119 │ ├── abcde.b:6 => checks.b:2 1120 │ ├── abcde.c:7 => checks.c:3 1121 │ └── column11:11 => checks.d:4 1122 ├── check columns: check1:12 check2:13 1123 └── project 1124 ├── columns: check1:12 check2:13!null abcde.a:5!null abcde.b:6 abcde.c:7 column11:11 1125 ├── project 1126 │ ├── columns: column11:11 abcde.a:5!null abcde.b:6 abcde.c:7 1127 │ ├── project 1128 │ │ ├── columns: abcde.a:5!null abcde.b:6 abcde.c:7 1129 │ │ └── scan abcde 1130 │ │ ├── columns: abcde.a:5!null abcde.b:6 abcde.c:7 abcde.d:8 e:9 rowid:10!null 1131 │ │ └── computed column expressions 1132 │ │ ├── abcde.d:8 1133 │ │ │ └── (abcde.b:6 + abcde.c:7) + 1 1134 │ │ └── e:9 1135 │ │ └── abcde.a:5 1136 │ └── projections 1137 │ └── abcde.c:7 + 1 [as=column11:11] 1138 └── projections 1139 ├── abcde.b:6 < column11:11 [as=check1:12] 1140 └── abcde.a:5 > 0 [as=check2:13] 1141 1142 # ------------------------------------------------------------------------------ 1143 # Test decimal column rounding. 1144 # ------------------------------------------------------------------------------ 1145 1146 opt 1147 INSERT INTO decimals (a, b) VALUES (1.1, ARRAY[0.95, NULL, 15]) 1148 ---- 1149 insert decimals 1150 ├── columns: <none> 1151 ├── insert-mapping: 1152 │ ├── a:8 => decimals.a:1 1153 │ ├── b:9 => decimals.b:2 1154 │ ├── c:10 => decimals.c:3 1155 │ └── d:12 => decimals.d:4 1156 ├── check columns: check1:13 check2:14 1157 └── project 1158 ├── columns: check1:13!null check2:14 a:8!null b:9 c:10!null d:12!null 1159 ├── values 1160 │ ├── columns: a:8!null b:9 c:10!null d:12!null 1161 │ └── (1, crdb_internal.round_decimal_values(ARRAY[0.95,NULL,15], 1), 1.2, 2.2) 1162 └── projections 1163 ├── true [as=check1:13] 1164 └── b:9[0] > 1 [as=check2:14] 1165 1166 # Regression test for #38293; the default values should be separate projections. 1167 exec-ddl 1168 CREATE TABLE defvals ( 1169 id SERIAL NOT NULL PRIMARY KEY, 1170 arr1 STRING(100) ARRAY NOT NULL DEFAULT ARRAY[], 1171 arr2 INT ARRAY NOT NULL DEFAULT ARRAY[] 1172 ) 1173 ---- 1174 1175 build 1176 INSERT INTO defvals(id) VALUES (1) 1177 ---- 1178 insert defvals 1179 ├── columns: <none> 1180 ├── insert-mapping: 1181 │ ├── column1:4 => id:1 1182 │ ├── column5:5 => arr1:2 1183 │ └── column6:6 => arr2:3 1184 └── project 1185 ├── columns: column5:5!null column6:6!null column1:4!null 1186 ├── values 1187 │ ├── columns: column1:4!null 1188 │ └── (1,) 1189 └── projections 1190 ├── ARRAY[] [as=column5:5] 1191 └── ARRAY[] [as=column6:6] 1192 1193 exec-ddl 1194 CREATE TABLE defvals2 ( 1195 id SERIAL NOT NULL PRIMARY KEY, 1196 arr1 STRING(100) ARRAY NOT NULL DEFAULT ARRAY[NULL], 1197 arr2 INT ARRAY NOT NULL DEFAULT ARRAY[NULL] 1198 ) 1199 ---- 1200 1201 build 1202 INSERT INTO defvals2(id) VALUES (1) 1203 ---- 1204 insert defvals2 1205 ├── columns: <none> 1206 ├── insert-mapping: 1207 │ ├── column1:4 => id:1 1208 │ ├── column5:5 => arr1:2 1209 │ └── column6:6 => arr2:3 1210 └── project 1211 ├── columns: column5:5 column6:6 column1:4!null 1212 ├── values 1213 │ ├── columns: column1:4!null 1214 │ └── (1,) 1215 └── projections 1216 ├── ARRAY[NULL] [as=column5:5] 1217 └── ARRAY[NULL] [as=column6:6] 1218 1219 # ------------------------------------------------------------------------------ 1220 # Test partial index column values. 1221 # ------------------------------------------------------------------------------ 1222 1223 exec-ddl 1224 CREATE TABLE partial_indexes ( 1225 a INT PRIMARY KEY, 1226 b INT, 1227 c STRING, 1228 INDEX (b), 1229 INDEX (b) WHERE c = 'foo', 1230 INDEX (c) WHERE a > b AND c = 'bar' 1231 ) 1232 ---- 1233 1234 build 1235 INSERT INTO partial_indexes VALUES (2, 1, 'bar') 1236 ---- 1237 insert partial_indexes 1238 ├── columns: <none> 1239 ├── insert-mapping: 1240 │ ├── column1:4 => a:1 1241 │ ├── column2:5 => b:2 1242 │ └── column3:6 => c:3 1243 ├── partial index pred columns: indexpred1:7 indexpred2:8 1244 └── project 1245 ├── columns: indexpred1:7!null indexpred2:8!null column1:4!null column2:5!null column3:6!null 1246 ├── values 1247 │ ├── columns: column1:4!null column2:5!null column3:6!null 1248 │ └── (2, 1, 'bar') 1249 └── projections 1250 ├── column3:6 = 'foo' [as=indexpred1:7] 1251 └── (column1:4 > column2:5) AND (column3:6 = 'bar') [as=indexpred2:8]