granicus.if.org Git - postgresql/blob - src/test/regress/sql/create_table.sql

   1 --
   2 -- CREATE_TABLE
   3 --
   4
   5 --
   6 -- CLASS DEFINITIONS
   7 --
   8 CREATE TABLE hobbies_r (
   9         name            text,
  10         person          text
  11 );
  12
  13 CREATE TABLE equipment_r (
  14         name            text,
  15         hobby           text
  16 );
  17
  18 CREATE TABLE onek (
  19         unique1         int4,
  20         unique2         int4,
  21         two                     int4,
  22         four            int4,
  23         ten                     int4,
  24         twenty          int4,
  25         hundred         int4,
  26         thousand        int4,
  27         twothousand     int4,
  28         fivethous       int4,
  29         tenthous        int4,
  30         odd                     int4,
  31         even            int4,
  32         stringu1        name,
  33         stringu2        name,
  34         string4         name
  35 );
  36
  37 CREATE TABLE tenk1 (
  38         unique1         int4,
  39         unique2         int4,
  40         two                     int4,
  41         four            int4,
  42         ten                     int4,
  43         twenty          int4,
  44         hundred         int4,
  45         thousand        int4,
  46         twothousand     int4,
  47         fivethous       int4,
  48         tenthous        int4,
  49         odd                     int4,
  50         even            int4,
  51         stringu1        name,
  52         stringu2        name,
  53         string4         name
  54 ) WITH OIDS;
  55
  56 CREATE TABLE tenk2 (
  57         unique1         int4,
  58         unique2         int4,
  59         two             int4,
  60         four            int4,
  61         ten                     int4,
  62         twenty          int4,
  63         hundred         int4,
  64         thousand        int4,
  65         twothousand int4,
  66         fivethous       int4,
  67         tenthous        int4,
  68         odd                     int4,
  69         even            int4,
  70         stringu1        name,
  71         stringu2        name,
  72         string4         name
  73 );
  74
  75
  76 CREATE TABLE person (
  77         name            text,
  78         age                     int4,
  79         location        point
  80 );
  81
  82
  83 CREATE TABLE emp (
  84         salary          int4,
  85         manager         name
  86 ) INHERITS (person) WITH OIDS;
  87
  88
  89 CREATE TABLE student (
  90         gpa             float8
  91 ) INHERITS (person);
  92
  93
  94 CREATE TABLE stud_emp (
  95         percent         int4
  96 ) INHERITS (emp, student);
  97
  98
  99 CREATE TABLE city (
 100         name            name,
 101         location        box,
 102         budget          city_budget
 103 );
 104
 105 CREATE TABLE dept (
 106         dname           name,
 107         mgrname         text
 108 );
 109
 110 CREATE TABLE slow_emp4000 (
 111         home_base        box
 112 );
 113
 114 CREATE TABLE fast_emp4000 (
 115         home_base        box
 116 );
 117
 118 CREATE TABLE road (
 119         name            text,
 120         thepath         path
 121 );
 122
 123 CREATE TABLE ihighway () INHERITS (road);
 124
 125 CREATE TABLE shighway (
 126         surface         text
 127 ) INHERITS (road);
 128
 129 CREATE TABLE real_city (
 130         pop                     int4,
 131         cname           text,
 132         outline         path
 133 );
 134
 135 --
 136 -- test the "star" operators a bit more thoroughly -- this time,
 137 -- throw in lots of NULL fields...
 138 --
 139 -- a is the type root
 140 -- b and c inherit from a (one-level single inheritance)
 141 -- d inherits from b and c (two-level multiple inheritance)
 142 -- e inherits from c (two-level single inheritance)
 143 -- f inherits from e (three-level single inheritance)
 144 --
 145 CREATE TABLE a_star (
 146         class           char,
 147         a                       int4
 148 );
 149
 150 CREATE TABLE b_star (
 151         b                       text
 152 ) INHERITS (a_star);
 153
 154 CREATE TABLE c_star (
 155         c                       name
 156 ) INHERITS (a_star);
 157
 158 CREATE TABLE d_star (
 159         d                       float8
 160 ) INHERITS (b_star, c_star);
 161
 162 CREATE TABLE e_star (
 163         e                       int2
 164 ) INHERITS (c_star);
 165
 166 CREATE TABLE f_star (
 167         f                       polygon
 168 ) INHERITS (e_star);
 169
 170 CREATE TABLE aggtest (
 171         a                       int2,
 172         b                       float4
 173 );
 174
 175 CREATE TABLE hash_i4_heap (
 176         seqno           int4,
 177         random          int4
 178 );
 179
 180 CREATE TABLE hash_name_heap (
 181         seqno           int4,
 182         random          name
 183 );
 184
 185 CREATE TABLE hash_txt_heap (
 186         seqno           int4,
 187         random          text
 188 );
 189
 190 CREATE TABLE hash_f8_heap (
 191         seqno           int4,
 192         random          float8
 193 );
 194
 195 -- don't include the hash_ovfl_heap stuff in the distribution
 196 -- the data set is too large for what it's worth
 197 --
 198 -- CREATE TABLE hash_ovfl_heap (
 199 --      x                       int4,
 200 --      y                       int4
 201 -- );
 202
 203 CREATE TABLE bt_i4_heap (
 204         seqno           int4,
 205         random          int4
 206 );
 207
 208 CREATE TABLE bt_name_heap (
 209         seqno           name,
 210         random          int4
 211 );
 212
 213 CREATE TABLE bt_txt_heap (
 214         seqno           text,
 215         random          int4
 216 );
 217
 218 CREATE TABLE bt_f8_heap (
 219         seqno           float8,
 220         random          int4
 221 );
 222
 223 CREATE TABLE array_op_test (
 224         seqno           int4,
 225         i                       int4[],
 226         t                       text[]
 227 );
 228
 229 CREATE TABLE array_index_op_test (
 230         seqno           int4,
 231         i                       int4[],
 232         t                       text[]
 233 );
 234
 235 CREATE TABLE testjsonb (
 236        j jsonb
 237 );
 238
 239 CREATE TABLE unknowntab (
 240         u unknown    -- fail
 241 );
 242
 243 CREATE TYPE unknown_comptype AS (
 244         u unknown    -- fail
 245 );
 246
 247 CREATE TABLE IF NOT EXISTS test_tsvector(
 248         t text,
 249         a tsvector
 250 );
 251
 252 CREATE TABLE IF NOT EXISTS test_tsvector(
 253         t text
 254 );
 255
 256 CREATE UNLOGGED TABLE unlogged1 (a int primary key);                    -- OK
 257 CREATE TEMPORARY TABLE unlogged2 (a int primary key);                   -- OK
 258 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname;
 259 REINDEX INDEX unlogged1_pkey;
 260 REINDEX INDEX unlogged2_pkey;
 261 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname;
 262 DROP TABLE unlogged2;
 263 INSERT INTO unlogged1 VALUES (42);
 264 CREATE UNLOGGED TABLE public.unlogged2 (a int primary key);             -- also OK
 265 CREATE UNLOGGED TABLE pg_temp.unlogged3 (a int primary key);    -- not OK
 266 CREATE TABLE pg_temp.implicitly_temp (a int primary key);               -- OK
 267 CREATE TEMP TABLE explicitly_temp (a int primary key);                  -- also OK
 268 CREATE TEMP TABLE pg_temp.doubly_temp (a int primary key);              -- also OK
 269 CREATE TEMP TABLE public.temp_to_perm (a int primary key);              -- not OK
 270 DROP TABLE unlogged1, public.unlogged2;
 271
 272 CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
 273 CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
 274 CREATE TABLE IF NOT EXISTS as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
 275 DROP TABLE as_select1;
 276
 277 -- check that the oid column is added before the primary key is checked
 278 CREATE TABLE oid_pk (f1 INT, PRIMARY KEY(oid)) WITH OIDS;
 279 DROP TABLE oid_pk;
 280
 281 --
 282 -- Partitioned tables
 283 --
 284
 285 -- cannot combine INHERITS and PARTITION BY (although grammar allows)
 286 CREATE TABLE partitioned (
 287         a int
 288 ) INHERITS (some_table) PARTITION BY LIST (a);
 289
 290 -- cannot use more than 1 column as partition key for list partitioned table
 291 CREATE TABLE partitioned (
 292         a1 int,
 293         a2 int
 294 ) PARTITION BY LIST (a1, a2);   -- fail
 295
 296 -- unsupported constraint type for partitioned tables
 297 CREATE TABLE partitioned (
 298         a int PRIMARY KEY
 299 ) PARTITION BY RANGE (a);
 300
 301 CREATE TABLE pkrel (
 302         a int PRIMARY KEY
 303 );
 304 CREATE TABLE partitioned (
 305         a int REFERENCES pkrel(a)
 306 ) PARTITION BY RANGE (a);
 307 DROP TABLE pkrel;
 308
 309 CREATE TABLE partitioned (
 310         a int UNIQUE
 311 ) PARTITION BY RANGE (a);
 312
 313 CREATE TABLE partitioned (
 314         a int,
 315         EXCLUDE USING gist (a WITH &&)
 316 ) PARTITION BY RANGE (a);
 317
 318 -- prevent column from being used twice in the partition key
 319 CREATE TABLE partitioned (
 320         a int
 321 ) PARTITION BY RANGE (a, a);
 322
 323 -- prevent using prohibited expressions in the key
 324 CREATE FUNCTION retset (a int) RETURNS SETOF int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE;
 325 CREATE TABLE partitioned (
 326         a int
 327 ) PARTITION BY RANGE (retset(a));
 328 DROP FUNCTION retset(int);
 329
 330 CREATE TABLE partitioned (
 331         a int
 332 ) PARTITION BY RANGE ((avg(a)));
 333
 334 CREATE TABLE partitioned (
 335         a int,
 336         b int
 337 ) PARTITION BY RANGE ((avg(a) OVER (PARTITION BY b)));
 338
 339 CREATE TABLE partitioned (
 340         a int
 341 ) PARTITION BY LIST ((a LIKE (SELECT 1)));
 342
 343 CREATE TABLE partitioned (
 344         a int
 345 ) PARTITION BY RANGE (('a'));
 346
 347 CREATE FUNCTION const_func () RETURNS int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE;
 348 CREATE TABLE partitioned (
 349         a int
 350 ) PARTITION BY RANGE (const_func());
 351 DROP FUNCTION const_func();
 352
 353 -- only accept "list" and "range" as partitioning strategy
 354 CREATE TABLE partitioned (
 355         a int
 356 ) PARTITION BY HASH (a);
 357
 358 -- specified column must be present in the table
 359 CREATE TABLE partitioned (
 360         a int
 361 ) PARTITION BY RANGE (b);
 362
 363 -- cannot use system columns in partition key
 364 CREATE TABLE partitioned (
 365         a int
 366 ) PARTITION BY RANGE (xmin);
 367
 368 -- functions in key must be immutable
 369 CREATE FUNCTION immut_func (a int) RETURNS int AS $$ SELECT a + random()::int; $$ LANGUAGE SQL;
 370 CREATE TABLE partitioned (
 371         a int
 372 ) PARTITION BY RANGE (immut_func(a));
 373 DROP FUNCTION immut_func(int);
 374
 375 -- cannot contain whole-row references
 376 CREATE TABLE partitioned (
 377         a       int
 378 ) PARTITION BY RANGE ((partitioned));
 379
 380 -- prevent using columns of unsupported types in key (type must have a btree operator class)
 381 CREATE TABLE partitioned (
 382         a point
 383 ) PARTITION BY LIST (a);
 384 CREATE TABLE partitioned (
 385         a point
 386 ) PARTITION BY LIST (a point_ops);
 387 CREATE TABLE partitioned (
 388         a point
 389 ) PARTITION BY RANGE (a);
 390 CREATE TABLE partitioned (
 391         a point
 392 ) PARTITION BY RANGE (a point_ops);
 393
 394 -- cannot add NO INHERIT constraints to partitioned tables
 395 CREATE TABLE partitioned (
 396         a int,
 397         CONSTRAINT check_a CHECK (a > 0) NO INHERIT
 398 ) PARTITION BY RANGE (a);
 399
 400 -- some checks after successful creation of a partitioned table
 401 CREATE FUNCTION plusone(a int) RETURNS INT AS $$ SELECT a+1; $$ LANGUAGE SQL;
 402
 403 CREATE TABLE partitioned (
 404         a int,
 405         b int,
 406         c text,
 407         d text
 408 ) PARTITION BY RANGE (a oid_ops, plusone(b), c collate "default", d collate "C");
 409
 410 -- check relkind
 411 SELECT relkind FROM pg_class WHERE relname = 'partitioned';
 412
 413 -- check that range partition key columns are marked NOT NULL
 414 SELECT attname, attnotnull FROM pg_attribute
 415   WHERE attrelid = 'partitioned'::regclass AND attnum > 0
 416   ORDER BY attnum;
 417
 418 -- prevent a function referenced in partition key from being dropped
 419 DROP FUNCTION plusone(int);
 420
 421 -- partitioned table cannot participate in regular inheritance
 422 CREATE TABLE partitioned2 (
 423         a int
 424 ) PARTITION BY LIST ((a+1));
 425 CREATE TABLE fail () INHERITS (partitioned2);
 426
 427 -- Partition key in describe output
 428 \d partitioned
 429 \d partitioned2
 430
 431 DROP TABLE partitioned, partitioned2;
 432
 433 --
 434 -- Partitions
 435 --
 436
 437 -- check partition bound syntax
 438
 439 CREATE TABLE list_parted (
 440         a int
 441 ) PARTITION BY LIST (a);
 442 -- syntax allows only string literal, numeric literal and null to be
 443 -- specified for a partition bound value
 444 CREATE TABLE part_1 PARTITION OF list_parted FOR VALUES IN ('1');
 445 CREATE TABLE part_2 PARTITION OF list_parted FOR VALUES IN (2);
 446 CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null);
 447 CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN (int '1');
 448 CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ('1'::int);
 449
 450 -- syntax does not allow empty list of values for list partitions
 451 CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ();
 452 -- trying to specify range for list partitioned table
 453 CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) TO (2);
 454
 455 -- specified literal can't be cast to the partition column data type
 456 CREATE TABLE bools (
 457         a bool
 458 ) PARTITION BY LIST (a);
 459 CREATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1);
 460 DROP TABLE bools;
 461
 462 CREATE TABLE range_parted (
 463         a date
 464 ) PARTITION BY RANGE (a);
 465
 466 -- trying to specify list for range partitioned table
 467 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES IN ('a');
 468 -- each of start and end bounds must have same number of values as the
 469 -- length of the partition key
 470 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a', 1) TO ('z');
 471 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a') TO ('z', 1);
 472
 473 -- cannot specify null values in range bounds
 474 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (unbounded);
 475
 476 -- check if compatible with the specified parent
 477
 478 -- cannot create as partition of a non-partitioned table
 479 CREATE TABLE unparted (
 480         a int
 481 );
 482 CREATE TABLE fail_part PARTITION OF unparted FOR VALUES IN ('a');
 483 DROP TABLE unparted;
 484
 485 -- cannot create a permanent rel as partition of a temp rel
 486 CREATE TEMP TABLE temp_parted (
 487         a int
 488 ) PARTITION BY LIST (a);
 489 CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES IN ('a');
 490 DROP TABLE temp_parted;
 491
 492 -- cannot create a table with oids as partition of table without oids
 493 CREATE TABLE no_oids_parted (
 494         a int
 495 ) PARTITION BY RANGE (a) WITHOUT OIDS;
 496 CREATE TABLE fail_part PARTITION OF no_oids_parted FOR VALUES FROM (1) TO (10) WITH OIDS;
 497 DROP TABLE no_oids_parted;
 498
 499 -- If the partitioned table has oids, then the partition must have them.
 500 -- If the WITHOUT OIDS option is specified for partition, it is overridden.
 501 CREATE TABLE oids_parted (
 502         a int
 503 ) PARTITION BY RANGE (a) WITH OIDS;
 504 CREATE TABLE part_forced_oids PARTITION OF oids_parted FOR VALUES FROM (1) TO (10) WITHOUT OIDS;
 505 \d+ part_forced_oids
 506 DROP TABLE oids_parted, part_forced_oids;
 507
 508 -- check for partition bound overlap and other invalid specifications
 509
 510 CREATE TABLE list_parted2 (
 511         a varchar
 512 ) PARTITION BY LIST (a);
 513 CREATE TABLE part_null_z PARTITION OF list_parted2 FOR VALUES IN (null, 'z');
 514 CREATE TABLE part_ab PARTITION OF list_parted2 FOR VALUES IN ('a', 'b');
 515
 516 CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN (null);
 517 CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('b', 'c');
 518
 519 CREATE TABLE range_parted2 (
 520         a int
 521 ) PARTITION BY RANGE (a);
 522
 523 -- trying to create range partition with empty range
 524 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (0);
 525 -- note that the range '[1, 1)' has no elements
 526 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (1);
 527
 528 CREATE TABLE part0 PARTITION OF range_parted2 FOR VALUES FROM (unbounded) TO (1);
 529 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (unbounded) TO (2);
 530 CREATE TABLE part1 PARTITION OF range_parted2 FOR VALUES FROM (1) TO (10);
 531 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO (unbounded);
 532 CREATE TABLE part2 PARTITION OF range_parted2 FOR VALUES FROM (20) TO (30);
 533 CREATE TABLE part3 PARTITION OF range_parted2 FOR VALUES FROM (30) TO (40);
 534 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (30);
 535 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (50);
 536
 537 -- now check for multi-column range partition key
 538 CREATE TABLE range_parted3 (
 539         a int,
 540         b int
 541 ) PARTITION BY RANGE (a, (b+1));
 542
 543 CREATE TABLE part00 PARTITION OF range_parted3 FOR VALUES FROM (0, unbounded) TO (0, unbounded);
 544 CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0, unbounded) TO (0, 1);
 545
 546 CREATE TABLE part10 PARTITION OF range_parted3 FOR VALUES FROM (1, unbounded) TO (1, 1);
 547 CREATE TABLE part11 PARTITION OF range_parted3 FOR VALUES FROM (1, 1) TO (1, 10);
 548 CREATE TABLE part12 PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, unbounded);
 549 CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, 20);
 550
 551 -- cannot create a partition that says column b is allowed to range
 552 -- from -infinity to +infinity, while there exist partitions that have
 553 -- more specific ranges
 554 CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, unbounded) TO (1, unbounded);
 555
 556 -- check schema propagation from parent
 557
 558 CREATE TABLE parted (
 559         a text,
 560         b int NOT NULL DEFAULT 0,
 561         CONSTRAINT check_a CHECK (length(a) > 0)
 562 ) PARTITION BY LIST (a);
 563
 564 CREATE TABLE part_a PARTITION OF parted FOR VALUES IN ('a');
 565
 566 -- only inherited attributes (never local ones)
 567 SELECT attname, attislocal, attinhcount FROM pg_attribute
 568   WHERE attrelid = 'part_a'::regclass and attnum > 0
 569   ORDER BY attnum;
 570
 571 -- able to specify column default, column constraint, and table constraint
 572 CREATE TABLE part_b PARTITION OF parted (
 573         b NOT NULL DEFAULT 1 CHECK (b >= 0),
 574         CONSTRAINT check_a CHECK (length(a) > 0)
 575 ) FOR VALUES IN ('b');
 576 -- conislocal should be false for any merged constraints
 577 SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass AND conname = 'check_a';
 578
 579 -- specify PARTITION BY for a partition
 580 CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c);
 581 CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ((b));
 582
 583 -- create a level-2 partition
 584 CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
 585
 586 -- Partition bound in describe output
 587 \d part_b
 588
 589 -- Both partition bound and partition key in describe output
 590 \d part_c
 591
 592 -- Show partition count in the parent's describe output
 593 -- Tempted to include \d+ output listing partitions with bound info but
 594 -- output could vary depending on the order in which partition oids are
 595 -- returned.
 596 \d parted
 597
 598 -- cleanup
 599 DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;