1-- 2-- Test domains. 3-- 4-- Test Comment / Drop 5create domain domaindroptest int4; 6comment on domain domaindroptest is 'About to drop this..'; 7create domain dependenttypetest domaindroptest; 8-- fail because of dependent type 9drop domain domaindroptest; 10ERROR: cannot drop type domaindroptest because other objects depend on it 11DETAIL: type dependenttypetest depends on type domaindroptest 12HINT: Use DROP ... CASCADE to drop the dependent objects too. 13drop domain domaindroptest cascade; 14NOTICE: drop cascades to type dependenttypetest 15-- this should fail because already gone 16drop domain domaindroptest cascade; 17ERROR: type "domaindroptest" does not exist 18-- Test domain input. 19-- Note: the point of checking both INSERT and COPY FROM is that INSERT 20-- exercises CoerceToDomain while COPY exercises domain_in. 21create domain domainvarchar varchar(5); 22create domain domainnumeric numeric(8,2); 23create domain domainint4 int4; 24create domain domaintext text; 25-- Test explicit coercions --- these should succeed (and truncate) 26SELECT cast('123456' as domainvarchar); 27 domainvarchar 28--------------- 29 12345 30(1 row) 31 32SELECT cast('12345' as domainvarchar); 33 domainvarchar 34--------------- 35 12345 36(1 row) 37 38-- Test tables using domains 39create table basictest 40 ( testint4 domainint4 41 , testtext domaintext 42 , testvarchar domainvarchar 43 , testnumeric domainnumeric 44 ); 45INSERT INTO basictest values ('88', 'haha', 'short', '123.12'); -- Good 46INSERT INTO basictest values ('88', 'haha', 'short text', '123.12'); -- Bad varchar 47ERROR: value too long for type character varying(5) 48INSERT INTO basictest values ('88', 'haha', 'short', '123.1212'); -- Truncate numeric 49-- Test copy 50COPY basictest (testvarchar) FROM stdin; -- fail 51ERROR: value too long for type character varying(5) 52CONTEXT: COPY basictest, line 1, column testvarchar: "notsoshorttext" 53COPY basictest (testvarchar) FROM stdin; 54select * from basictest; 55 testint4 | testtext | testvarchar | testnumeric 56----------+----------+-------------+------------- 57 88 | haha | short | 123.12 58 88 | haha | short | 123.12 59 | | short | 60(3 rows) 61 62-- check that domains inherit operations from base types 63select testtext || testvarchar as concat, testnumeric + 42 as sum 64from basictest; 65 concat | sum 66-----------+-------- 67 hahashort | 165.12 68 hahashort | 165.12 69 | 70(3 rows) 71 72-- check that union/case/coalesce type resolution handles domains properly 73select coalesce(4::domainint4, 7) is of (int4) as t; 74 t 75--- 76 t 77(1 row) 78 79select coalesce(4::domainint4, 7) is of (domainint4) as f; 80 f 81--- 82 f 83(1 row) 84 85select coalesce(4::domainint4, 7::domainint4) is of (domainint4) as t; 86 t 87--- 88 t 89(1 row) 90 91drop table basictest; 92drop domain domainvarchar restrict; 93drop domain domainnumeric restrict; 94drop domain domainint4 restrict; 95drop domain domaintext; 96-- Test domains over array types 97create domain domainint4arr int4[1]; 98create domain domainchar4arr varchar(4)[2][3]; 99create table domarrtest 100 ( testint4arr domainint4arr 101 , testchar4arr domainchar4arr 102 ); 103INSERT INTO domarrtest values ('{2,2}', '{{"a","b"},{"c","d"}}'); 104INSERT INTO domarrtest values ('{{2,2},{2,2}}', '{{"a","b"}}'); 105INSERT INTO domarrtest values ('{2,2}', '{{"a","b"},{"c","d"},{"e","f"}}'); 106INSERT INTO domarrtest values ('{2,2}', '{{"a"},{"c"}}'); 107INSERT INTO domarrtest values (NULL, '{{"a","b","c"},{"d","e","f"}}'); 108INSERT INTO domarrtest values (NULL, '{{"toolong","b","c"},{"d","e","f"}}'); 109ERROR: value too long for type character varying(4) 110INSERT INTO domarrtest (testint4arr[1], testint4arr[3]) values (11,22); 111select * from domarrtest; 112 testint4arr | testchar4arr 113---------------+--------------------- 114 {2,2} | {{a,b},{c,d}} 115 {{2,2},{2,2}} | {{a,b}} 116 {2,2} | {{a,b},{c,d},{e,f}} 117 {2,2} | {{a},{c}} 118 | {{a,b,c},{d,e,f}} 119 {11,NULL,22} | 120(6 rows) 121 122select testint4arr[1], testchar4arr[2:2] from domarrtest; 123 testint4arr | testchar4arr 124-------------+-------------- 125 2 | {{c,d}} 126 | {} 127 2 | {{c,d}} 128 2 | {{c}} 129 | {{d,e,f}} 130 11 | 131(6 rows) 132 133select array_dims(testint4arr), array_dims(testchar4arr) from domarrtest; 134 array_dims | array_dims 135------------+------------ 136 [1:2] | [1:2][1:2] 137 [1:2][1:2] | [1:1][1:2] 138 [1:2] | [1:3][1:2] 139 [1:2] | [1:2][1:1] 140 | [1:2][1:3] 141 [1:3] | 142(6 rows) 143 144COPY domarrtest FROM stdin; 145COPY domarrtest FROM stdin; -- fail 146ERROR: value too long for type character varying(4) 147CONTEXT: COPY domarrtest, line 1, column testchar4arr: "{qwerty,w,e}" 148select * from domarrtest; 149 testint4arr | testchar4arr 150---------------+--------------------- 151 {2,2} | {{a,b},{c,d}} 152 {{2,2},{2,2}} | {{a,b}} 153 {2,2} | {{a,b},{c,d},{e,f}} 154 {2,2} | {{a},{c}} 155 | {{a,b,c},{d,e,f}} 156 {11,NULL,22} | 157 {3,4} | {q,w,e} 158 | 159(8 rows) 160 161update domarrtest set 162 testint4arr[1] = testint4arr[1] + 1, 163 testint4arr[3] = testint4arr[3] - 1 164where testchar4arr is null; 165select * from domarrtest where testchar4arr is null; 166 testint4arr | testchar4arr 167------------------+-------------- 168 {12,NULL,21} | 169 {NULL,NULL,NULL} | 170(2 rows) 171 172drop table domarrtest; 173drop domain domainint4arr restrict; 174drop domain domainchar4arr restrict; 175create domain dia as int[]; 176select '{1,2,3}'::dia; 177 dia 178--------- 179 {1,2,3} 180(1 row) 181 182select array_dims('{1,2,3}'::dia); 183 array_dims 184------------ 185 [1:3] 186(1 row) 187 188select pg_typeof('{1,2,3}'::dia); 189 pg_typeof 190----------- 191 dia 192(1 row) 193 194select pg_typeof('{1,2,3}'::dia || 42); -- should be int[] not dia 195 pg_typeof 196----------- 197 integer[] 198(1 row) 199 200drop domain dia; 201-- Test domains over composites 202create type comptype as (r float8, i float8); 203create domain dcomptype as comptype; 204create table dcomptable (d1 dcomptype unique); 205insert into dcomptable values (row(1,2)::dcomptype); 206insert into dcomptable values (row(3,4)::comptype); 207insert into dcomptable values (row(1,2)::dcomptype); -- fail on uniqueness 208ERROR: duplicate key value violates unique constraint "dcomptable_d1_key" 209DETAIL: Key (d1)=((1,2)) already exists. 210insert into dcomptable (d1.r) values(11); 211select * from dcomptable; 212 d1 213------- 214 (1,2) 215 (3,4) 216 (11,) 217(3 rows) 218 219select (d1).r, (d1).i, (d1).* from dcomptable; 220 r | i | r | i 221----+---+----+--- 222 1 | 2 | 1 | 2 223 3 | 4 | 3 | 4 224 11 | | 11 | 225(3 rows) 226 227update dcomptable set d1.r = (d1).r + 1 where (d1).i > 0; 228select * from dcomptable; 229 d1 230------- 231 (11,) 232 (2,2) 233 (4,4) 234(3 rows) 235 236alter domain dcomptype add constraint c1 check ((value).r <= (value).i); 237alter domain dcomptype add constraint c2 check ((value).r > (value).i); -- fail 238ERROR: column "d1" of table "dcomptable" contains values that violate the new constraint 239select row(2,1)::dcomptype; -- fail 240ERROR: value for domain dcomptype violates check constraint "c1" 241insert into dcomptable values (row(1,2)::comptype); 242insert into dcomptable values (row(2,1)::comptype); -- fail 243ERROR: value for domain dcomptype violates check constraint "c1" 244insert into dcomptable (d1.r) values(99); 245insert into dcomptable (d1.r, d1.i) values(99, 100); 246insert into dcomptable (d1.r, d1.i) values(100, 99); -- fail 247ERROR: value for domain dcomptype violates check constraint "c1" 248update dcomptable set d1.r = (d1).r + 1 where (d1).i > 0; -- fail 249ERROR: value for domain dcomptype violates check constraint "c1" 250update dcomptable set d1.r = (d1).r - 1, d1.i = (d1).i + 1 where (d1).i > 0; 251select * from dcomptable; 252 d1 253---------- 254 (11,) 255 (99,) 256 (1,3) 257 (3,5) 258 (0,3) 259 (98,101) 260(6 rows) 261 262explain (verbose, costs off) 263 update dcomptable set d1.r = (d1).r - 1, d1.i = (d1).i + 1 where (d1).i > 0; 264 QUERY PLAN 265----------------------------------------------------------------------------------------------- 266 Update on public.dcomptable 267 -> Seq Scan on public.dcomptable 268 Output: ROW(((d1).r - '1'::double precision), ((d1).i + '1'::double precision)), ctid 269 Filter: ((dcomptable.d1).i > '0'::double precision) 270(4 rows) 271 272create rule silly as on delete to dcomptable do instead 273 update dcomptable set d1.r = (d1).r - 1, d1.i = (d1).i + 1 where (d1).i > 0; 274\d+ dcomptable 275 Table "public.dcomptable" 276 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 277--------+-----------+-----------+----------+---------+----------+--------------+------------- 278 d1 | dcomptype | | | | extended | | 279Indexes: 280 "dcomptable_d1_key" UNIQUE CONSTRAINT, btree (d1) 281Rules: 282 silly AS 283 ON DELETE TO dcomptable DO INSTEAD UPDATE dcomptable SET d1.r = (dcomptable.d1).r - 1::double precision, d1.i = (dcomptable.d1).i + 1::double precision 284 WHERE (dcomptable.d1).i > 0::double precision 285 286drop table dcomptable; 287drop type comptype cascade; 288NOTICE: drop cascades to type dcomptype 289-- check altering and dropping columns used by domain constraints 290create type comptype as (r float8, i float8); 291create domain dcomptype as comptype; 292alter domain dcomptype add constraint c1 check ((value).r > 0); 293comment on constraint c1 on domain dcomptype is 'random commentary'; 294select row(0,1)::dcomptype; -- fail 295ERROR: value for domain dcomptype violates check constraint "c1" 296alter type comptype alter attribute r type varchar; -- fail 297ERROR: operator does not exist: character varying > double precision 298HINT: No operator matches the given name and argument types. You might need to add explicit type casts. 299alter type comptype alter attribute r type bigint; 300alter type comptype drop attribute r; -- fail 301ERROR: cannot drop column r of composite type comptype because other objects depend on it 302DETAIL: constraint c1 depends on column r of composite type comptype 303HINT: Use DROP ... CASCADE to drop the dependent objects too. 304alter type comptype drop attribute i; 305select conname, obj_description(oid, 'pg_constraint') from pg_constraint 306 where contypid = 'dcomptype'::regtype; -- check comment is still there 307 conname | obj_description 308---------+------------------- 309 c1 | random commentary 310(1 row) 311 312drop type comptype cascade; 313NOTICE: drop cascades to type dcomptype 314-- Test domains over arrays of composite 315create type comptype as (r float8, i float8); 316create domain dcomptypea as comptype[]; 317create table dcomptable (d1 dcomptypea unique); 318insert into dcomptable values (array[row(1,2)]::dcomptypea); 319insert into dcomptable values (array[row(3,4), row(5,6)]::comptype[]); 320insert into dcomptable values (array[row(7,8)::comptype, row(9,10)::comptype]); 321insert into dcomptable values (array[row(1,2)]::dcomptypea); -- fail on uniqueness 322ERROR: duplicate key value violates unique constraint "dcomptable_d1_key" 323DETAIL: Key (d1)=({"(1,2)"}) already exists. 324insert into dcomptable (d1[1]) values(row(9,10)); 325insert into dcomptable (d1[1].r) values(11); 326select * from dcomptable; 327 d1 328-------------------- 329 {"(1,2)"} 330 {"(3,4)","(5,6)"} 331 {"(7,8)","(9,10)"} 332 {"(9,10)"} 333 {"(11,)"} 334(5 rows) 335 336select d1[2], d1[1].r, d1[1].i from dcomptable; 337 d1 | r | i 338--------+----+---- 339 | 1 | 2 340 (5,6) | 3 | 4 341 (9,10) | 7 | 8 342 | 9 | 10 343 | 11 | 344(5 rows) 345 346update dcomptable set d1[2] = row(d1[2].i, d1[2].r); 347select * from dcomptable; 348 d1 349-------------------- 350 {"(1,2)","(,)"} 351 {"(3,4)","(6,5)"} 352 {"(7,8)","(10,9)"} 353 {"(9,10)","(,)"} 354 {"(11,)","(,)"} 355(5 rows) 356 357update dcomptable set d1[1].r = d1[1].r + 1 where d1[1].i > 0; 358select * from dcomptable; 359 d1 360-------------------- 361 {"(11,)","(,)"} 362 {"(2,2)","(,)"} 363 {"(4,4)","(6,5)"} 364 {"(8,8)","(10,9)"} 365 {"(10,10)","(,)"} 366(5 rows) 367 368alter domain dcomptypea add constraint c1 check (value[1].r <= value[1].i); 369alter domain dcomptypea add constraint c2 check (value[1].r > value[1].i); -- fail 370ERROR: column "d1" of table "dcomptable" contains values that violate the new constraint 371select array[row(2,1)]::dcomptypea; -- fail 372ERROR: value for domain dcomptypea violates check constraint "c1" 373insert into dcomptable values (array[row(1,2)]::comptype[]); 374insert into dcomptable values (array[row(2,1)]::comptype[]); -- fail 375ERROR: value for domain dcomptypea violates check constraint "c1" 376insert into dcomptable (d1[1].r) values(99); 377insert into dcomptable (d1[1].r, d1[1].i) values(99, 100); 378insert into dcomptable (d1[1].r, d1[1].i) values(100, 99); -- fail 379ERROR: value for domain dcomptypea violates check constraint "c1" 380update dcomptable set d1[1].r = d1[1].r + 1 where d1[1].i > 0; -- fail 381ERROR: value for domain dcomptypea violates check constraint "c1" 382update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1 383 where d1[1].i > 0; 384select * from dcomptable; 385 d1 386-------------------- 387 {"(11,)","(,)"} 388 {"(99,)"} 389 {"(1,3)","(,)"} 390 {"(3,5)","(6,5)"} 391 {"(7,9)","(10,9)"} 392 {"(9,11)","(,)"} 393 {"(0,3)"} 394 {"(98,101)"} 395(8 rows) 396 397explain (verbose, costs off) 398 update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1 399 where d1[1].i > 0; 400 QUERY PLAN 401---------------------------------------------------------------------------------------------------------------- 402 Update on public.dcomptable 403 -> Seq Scan on public.dcomptable 404 Output: (d1[1].r := (d1[1].r - '1'::double precision))[1].i := (d1[1].i + '1'::double precision), ctid 405 Filter: (dcomptable.d1[1].i > '0'::double precision) 406(4 rows) 407 408create rule silly as on delete to dcomptable do instead 409 update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1 410 where d1[1].i > 0; 411\d+ dcomptable 412 Table "public.dcomptable" 413 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 414--------+------------+-----------+----------+---------+----------+--------------+------------- 415 d1 | dcomptypea | | | | extended | | 416Indexes: 417 "dcomptable_d1_key" UNIQUE CONSTRAINT, btree (d1) 418Rules: 419 silly AS 420 ON DELETE TO dcomptable DO INSTEAD UPDATE dcomptable SET d1[1].r = dcomptable.d1[1].r - 1::double precision, d1[1].i = dcomptable.d1[1].i + 1::double precision 421 WHERE dcomptable.d1[1].i > 0::double precision 422 423drop table dcomptable; 424drop type comptype cascade; 425NOTICE: drop cascades to type dcomptypea 426-- Test arrays over domains 427create domain posint as int check (value > 0); 428create table pitable (f1 posint[]); 429insert into pitable values(array[42]); 430insert into pitable values(array[-1]); -- fail 431ERROR: value for domain posint violates check constraint "posint_check" 432insert into pitable values('{0}'); -- fail 433ERROR: value for domain posint violates check constraint "posint_check" 434LINE 1: insert into pitable values('{0}'); 435 ^ 436update pitable set f1[1] = f1[1] + 1; 437update pitable set f1[1] = 0; -- fail 438ERROR: value for domain posint violates check constraint "posint_check" 439select * from pitable; 440 f1 441------ 442 {43} 443(1 row) 444 445drop table pitable; 446create domain vc4 as varchar(4); 447create table vc4table (f1 vc4[]); 448insert into vc4table values(array['too long']); -- fail 449ERROR: value too long for type character varying(4) 450insert into vc4table values(array['too long']::vc4[]); -- cast truncates 451select * from vc4table; 452 f1 453---------- 454 {"too "} 455(1 row) 456 457drop table vc4table; 458drop type vc4; 459-- You can sort of fake arrays-of-arrays by putting a domain in between 460create domain dposinta as posint[]; 461create table dposintatable (f1 dposinta[]); 462insert into dposintatable values(array[array[42]]); -- fail 463ERROR: column "f1" is of type dposinta[] but expression is of type integer[] 464LINE 1: insert into dposintatable values(array[array[42]]); 465 ^ 466HINT: You will need to rewrite or cast the expression. 467insert into dposintatable values(array[array[42]::posint[]]); -- still fail 468ERROR: column "f1" is of type dposinta[] but expression is of type posint[] 469LINE 1: insert into dposintatable values(array[array[42]::posint[]])... 470 ^ 471HINT: You will need to rewrite or cast the expression. 472insert into dposintatable values(array[array[42]::dposinta]); -- but this works 473select f1, f1[1], (f1[1])[1] from dposintatable; 474 f1 | f1 | f1 475----------+------+---- 476 {"{42}"} | {42} | 42 477(1 row) 478 479select pg_typeof(f1) from dposintatable; 480 pg_typeof 481------------ 482 dposinta[] 483(1 row) 484 485select pg_typeof(f1[1]) from dposintatable; 486 pg_typeof 487----------- 488 dposinta 489(1 row) 490 491select pg_typeof(f1[1][1]) from dposintatable; 492 pg_typeof 493----------- 494 dposinta 495(1 row) 496 497select pg_typeof((f1[1])[1]) from dposintatable; 498 pg_typeof 499----------- 500 posint 501(1 row) 502 503update dposintatable set f1[2] = array[99]; 504select f1, f1[1], (f1[2])[1] from dposintatable; 505 f1 | f1 | f1 506-----------------+------+---- 507 {"{42}","{99}"} | {42} | 99 508(1 row) 509 510-- it'd be nice if you could do something like this, but for now you can't: 511update dposintatable set f1[2][1] = array[97]; 512ERROR: wrong number of array subscripts 513-- maybe someday we can make this syntax work: 514update dposintatable set (f1[2])[1] = array[98]; 515ERROR: syntax error at or near "[" 516LINE 1: update dposintatable set (f1[2])[1] = array[98]; 517 ^ 518drop table dposintatable; 519drop domain posint cascade; 520NOTICE: drop cascades to type dposinta 521-- Test arrays over domains of composite 522create type comptype as (cf1 int, cf2 int); 523create domain dcomptype as comptype check ((value).cf1 > 0); 524create table dcomptable (f1 dcomptype[]); 525insert into dcomptable values (null); 526update dcomptable set f1[1].cf2 = 5; 527table dcomptable; 528 f1 529---------- 530 {"(,5)"} 531(1 row) 532 533update dcomptable set f1[1].cf1 = -1; -- fail 534ERROR: value for domain dcomptype violates check constraint "dcomptype_check" 535update dcomptable set f1[1].cf1 = 1; 536table dcomptable; 537 f1 538----------- 539 {"(1,5)"} 540(1 row) 541 542drop table dcomptable; 543drop type comptype cascade; 544NOTICE: drop cascades to type dcomptype 545-- Test not-null restrictions 546create domain dnotnull varchar(15) NOT NULL; 547create domain dnull varchar(15); 548create domain dcheck varchar(15) NOT NULL CHECK (VALUE = 'a' OR VALUE = 'c' OR VALUE = 'd'); 549create table nulltest 550 ( col1 dnotnull 551 , col2 dnotnull NULL -- NOT NULL in the domain cannot be overridden 552 , col3 dnull NOT NULL 553 , col4 dnull 554 , col5 dcheck CHECK (col5 IN ('c', 'd')) 555 ); 556INSERT INTO nulltest DEFAULT VALUES; 557ERROR: domain dnotnull does not allow null values 558INSERT INTO nulltest values ('a', 'b', 'c', 'd', 'c'); -- Good 559insert into nulltest values ('a', 'b', 'c', 'd', NULL); 560ERROR: domain dcheck does not allow null values 561insert into nulltest values ('a', 'b', 'c', 'd', 'a'); 562ERROR: new row for relation "nulltest" violates check constraint "nulltest_col5_check" 563DETAIL: Failing row contains (a, b, c, d, a). 564INSERT INTO nulltest values (NULL, 'b', 'c', 'd', 'd'); 565ERROR: domain dnotnull does not allow null values 566INSERT INTO nulltest values ('a', NULL, 'c', 'd', 'c'); 567ERROR: domain dnotnull does not allow null values 568INSERT INTO nulltest values ('a', 'b', NULL, 'd', 'c'); 569ERROR: null value in column "col3" violates not-null constraint 570DETAIL: Failing row contains (a, b, null, d, c). 571INSERT INTO nulltest values ('a', 'b', 'c', NULL, 'd'); -- Good 572-- Test copy 573COPY nulltest FROM stdin; --fail 574ERROR: null value in column "col3" violates not-null constraint 575DETAIL: Failing row contains (a, b, null, d, d). 576CONTEXT: COPY nulltest, line 1: "a b \N d d" 577COPY nulltest FROM stdin; --fail 578ERROR: domain dcheck does not allow null values 579CONTEXT: COPY nulltest, line 1, column col5: null input 580-- Last row is bad 581COPY nulltest FROM stdin; 582ERROR: new row for relation "nulltest" violates check constraint "nulltest_col5_check" 583DETAIL: Failing row contains (a, b, c, null, a). 584CONTEXT: COPY nulltest, line 3: "a b c \N a" 585select * from nulltest; 586 col1 | col2 | col3 | col4 | col5 587------+------+------+------+------ 588 a | b | c | d | c 589 a | b | c | | d 590(2 rows) 591 592-- Test out coerced (casted) constraints 593SELECT cast('1' as dnotnull); 594 dnotnull 595---------- 596 1 597(1 row) 598 599SELECT cast(NULL as dnotnull); -- fail 600ERROR: domain dnotnull does not allow null values 601SELECT cast(cast(NULL as dnull) as dnotnull); -- fail 602ERROR: domain dnotnull does not allow null values 603SELECT cast(col4 as dnotnull) from nulltest; -- fail 604ERROR: domain dnotnull does not allow null values 605-- cleanup 606drop table nulltest; 607drop domain dnotnull restrict; 608drop domain dnull restrict; 609drop domain dcheck restrict; 610create domain ddef1 int4 DEFAULT 3; 611create domain ddef2 oid DEFAULT '12'; 612-- Type mixing, function returns int8 613create domain ddef3 text DEFAULT 5; 614create sequence ddef4_seq; 615create domain ddef4 int4 DEFAULT nextval('ddef4_seq'); 616create domain ddef5 numeric(8,2) NOT NULL DEFAULT '12.12'; 617create table defaulttest 618 ( col1 ddef1 619 , col2 ddef2 620 , col3 ddef3 621 , col4 ddef4 PRIMARY KEY 622 , col5 ddef1 NOT NULL DEFAULT NULL 623 , col6 ddef2 DEFAULT '88' 624 , col7 ddef4 DEFAULT 8000 625 , col8 ddef5 626 ); 627insert into defaulttest(col4) values(0); -- fails, col5 defaults to null 628ERROR: null value in column "col5" violates not-null constraint 629DETAIL: Failing row contains (3, 12, 5, 0, null, 88, 8000, 12.12). 630alter table defaulttest alter column col5 drop default; 631insert into defaulttest default values; -- succeeds, inserts domain default 632-- We used to treat SET DEFAULT NULL as equivalent to DROP DEFAULT; wrong 633alter table defaulttest alter column col5 set default null; 634insert into defaulttest(col4) values(0); -- fails 635ERROR: null value in column "col5" violates not-null constraint 636DETAIL: Failing row contains (3, 12, 5, 0, null, 88, 8000, 12.12). 637alter table defaulttest alter column col5 drop default; 638insert into defaulttest default values; 639insert into defaulttest default values; 640-- Test defaults with copy 641COPY defaulttest(col5) FROM stdin; 642select * from defaulttest; 643 col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 644------+------+------+------+------+------+------+------- 645 3 | 12 | 5 | 1 | 3 | 88 | 8000 | 12.12 646 3 | 12 | 5 | 2 | 3 | 88 | 8000 | 12.12 647 3 | 12 | 5 | 3 | 3 | 88 | 8000 | 12.12 648 3 | 12 | 5 | 4 | 42 | 88 | 8000 | 12.12 649(4 rows) 650 651drop table defaulttest cascade; 652-- Test ALTER DOMAIN .. NOT NULL 653create domain dnotnulltest integer; 654create table domnotnull 655( col1 dnotnulltest 656, col2 dnotnulltest 657); 658insert into domnotnull default values; 659alter domain dnotnulltest set not null; -- fails 660ERROR: column "col1" of table "domnotnull" contains null values 661update domnotnull set col1 = 5; 662alter domain dnotnulltest set not null; -- fails 663ERROR: column "col2" of table "domnotnull" contains null values 664update domnotnull set col2 = 6; 665alter domain dnotnulltest set not null; 666update domnotnull set col1 = null; -- fails 667ERROR: domain dnotnulltest does not allow null values 668alter domain dnotnulltest drop not null; 669update domnotnull set col1 = null; 670drop domain dnotnulltest cascade; 671NOTICE: drop cascades to 2 other objects 672DETAIL: drop cascades to column col1 of table domnotnull 673drop cascades to column col2 of table domnotnull 674-- Test ALTER DOMAIN .. DEFAULT .. 675create table domdeftest (col1 ddef1); 676insert into domdeftest default values; 677select * from domdeftest; 678 col1 679------ 680 3 681(1 row) 682 683alter domain ddef1 set default '42'; 684insert into domdeftest default values; 685select * from domdeftest; 686 col1 687------ 688 3 689 42 690(2 rows) 691 692alter domain ddef1 drop default; 693insert into domdeftest default values; 694select * from domdeftest; 695 col1 696------ 697 3 698 42 699 700(3 rows) 701 702drop table domdeftest; 703-- Test ALTER DOMAIN .. CONSTRAINT .. 704create domain con as integer; 705create table domcontest (col1 con); 706insert into domcontest values (1); 707insert into domcontest values (2); 708alter domain con add constraint t check (VALUE < 1); -- fails 709ERROR: column "col1" of table "domcontest" contains values that violate the new constraint 710alter domain con add constraint t check (VALUE < 34); 711alter domain con add check (VALUE > 0); 712insert into domcontest values (-5); -- fails 713ERROR: value for domain con violates check constraint "con_check" 714insert into domcontest values (42); -- fails 715ERROR: value for domain con violates check constraint "t" 716insert into domcontest values (5); 717alter domain con drop constraint t; 718insert into domcontest values (-5); --fails 719ERROR: value for domain con violates check constraint "con_check" 720insert into domcontest values (42); 721alter domain con drop constraint nonexistent; 722ERROR: constraint "nonexistent" of domain "con" does not exist 723alter domain con drop constraint if exists nonexistent; 724NOTICE: constraint "nonexistent" of domain "con" does not exist, skipping 725-- Test ALTER DOMAIN .. CONSTRAINT .. NOT VALID 726create domain things AS INT; 727CREATE TABLE thethings (stuff things); 728INSERT INTO thethings (stuff) VALUES (55); 729ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE < 11); 730ERROR: column "stuff" of table "thethings" contains values that violate the new constraint 731ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE < 11) NOT VALID; 732ALTER DOMAIN things VALIDATE CONSTRAINT meow; 733ERROR: column "stuff" of table "thethings" contains values that violate the new constraint 734UPDATE thethings SET stuff = 10; 735ALTER DOMAIN things VALIDATE CONSTRAINT meow; 736-- Confirm ALTER DOMAIN with RULES. 737create table domtab (col1 integer); 738create domain dom as integer; 739create view domview as select cast(col1 as dom) from domtab; 740insert into domtab (col1) values (null); 741insert into domtab (col1) values (5); 742select * from domview; 743 col1 744------ 745 746 5 747(2 rows) 748 749alter domain dom set not null; 750select * from domview; -- fail 751ERROR: domain dom does not allow null values 752alter domain dom drop not null; 753select * from domview; 754 col1 755------ 756 757 5 758(2 rows) 759 760alter domain dom add constraint domchkgt6 check(value > 6); 761select * from domview; --fail 762ERROR: value for domain dom violates check constraint "domchkgt6" 763alter domain dom drop constraint domchkgt6 restrict; 764select * from domview; 765 col1 766------ 767 768 5 769(2 rows) 770 771-- cleanup 772drop domain ddef1 restrict; 773drop domain ddef2 restrict; 774drop domain ddef3 restrict; 775drop domain ddef4 restrict; 776drop domain ddef5 restrict; 777drop sequence ddef4_seq; 778-- Test domains over domains 779create domain vchar4 varchar(4); 780create domain dinter vchar4 check (substring(VALUE, 1, 1) = 'x'); 781create domain dtop dinter check (substring(VALUE, 2, 1) = '1'); 782select 'x123'::dtop; 783 dtop 784------ 785 x123 786(1 row) 787 788select 'x1234'::dtop; -- explicit coercion should truncate 789 dtop 790------ 791 x123 792(1 row) 793 794select 'y1234'::dtop; -- fail 795ERROR: value for domain dtop violates check constraint "dinter_check" 796select 'y123'::dtop; -- fail 797ERROR: value for domain dtop violates check constraint "dinter_check" 798select 'yz23'::dtop; -- fail 799ERROR: value for domain dtop violates check constraint "dinter_check" 800select 'xz23'::dtop; -- fail 801ERROR: value for domain dtop violates check constraint "dtop_check" 802create temp table dtest(f1 dtop); 803insert into dtest values('x123'); 804insert into dtest values('x1234'); -- fail, implicit coercion 805ERROR: value too long for type character varying(4) 806insert into dtest values('y1234'); -- fail, implicit coercion 807ERROR: value too long for type character varying(4) 808insert into dtest values('y123'); -- fail 809ERROR: value for domain dtop violates check constraint "dinter_check" 810insert into dtest values('yz23'); -- fail 811ERROR: value for domain dtop violates check constraint "dinter_check" 812insert into dtest values('xz23'); -- fail 813ERROR: value for domain dtop violates check constraint "dtop_check" 814drop table dtest; 815drop domain vchar4 cascade; 816NOTICE: drop cascades to 2 other objects 817DETAIL: drop cascades to type dinter 818drop cascades to type dtop 819-- Make sure that constraints of newly-added domain columns are 820-- enforced correctly, even if there's no default value for the new 821-- column. Per bug #1433 822create domain str_domain as text not null; 823create table domain_test (a int, b int); 824insert into domain_test values (1, 2); 825insert into domain_test values (1, 2); 826-- should fail 827alter table domain_test add column c str_domain; 828ERROR: domain str_domain does not allow null values 829create domain str_domain2 as text check (value <> 'foo') default 'foo'; 830-- should fail 831alter table domain_test add column d str_domain2; 832ERROR: value for domain str_domain2 violates check constraint "str_domain2_check" 833-- Check that domain constraints on prepared statement parameters of 834-- unknown type are enforced correctly. 835create domain pos_int as int4 check (value > 0) not null; 836prepare s1 as select $1::pos_int = 10 as "is_ten"; 837execute s1(10); 838 is_ten 839-------- 840 t 841(1 row) 842 843execute s1(0); -- should fail 844ERROR: value for domain pos_int violates check constraint "pos_int_check" 845execute s1(NULL); -- should fail 846ERROR: domain pos_int does not allow null values 847-- Check that domain constraints on plpgsql function parameters, results, 848-- and local variables are enforced correctly. 849create function doubledecrement(p1 pos_int) returns pos_int as $$ 850declare v pos_int; 851begin 852 return p1; 853end$$ language plpgsql; 854select doubledecrement(3); -- fail because of implicit null assignment 855ERROR: domain pos_int does not allow null values 856CONTEXT: PL/pgSQL function doubledecrement(pos_int) line 3 during statement block local variable initialization 857create or replace function doubledecrement(p1 pos_int) returns pos_int as $$ 858declare v pos_int := 0; 859begin 860 return p1; 861end$$ language plpgsql; 862select doubledecrement(3); -- fail at initialization assignment 863ERROR: value for domain pos_int violates check constraint "pos_int_check" 864CONTEXT: PL/pgSQL function doubledecrement(pos_int) line 3 during statement block local variable initialization 865create or replace function doubledecrement(p1 pos_int) returns pos_int as $$ 866declare v pos_int := 1; 867begin 868 v := p1 - 1; 869 return v - 1; 870end$$ language plpgsql; 871select doubledecrement(null); -- fail before call 872ERROR: domain pos_int does not allow null values 873select doubledecrement(0); -- fail before call 874ERROR: value for domain pos_int violates check constraint "pos_int_check" 875select doubledecrement(1); -- fail at assignment to v 876ERROR: value for domain pos_int violates check constraint "pos_int_check" 877CONTEXT: PL/pgSQL function doubledecrement(pos_int) line 4 at assignment 878select doubledecrement(2); -- fail at return 879ERROR: value for domain pos_int violates check constraint "pos_int_check" 880CONTEXT: PL/pgSQL function doubledecrement(pos_int) while casting return value to function's return type 881select doubledecrement(3); -- good 882 doubledecrement 883----------------- 884 1 885(1 row) 886 887-- Check that ALTER DOMAIN tests columns of derived types 888create domain posint as int4; 889-- Currently, this doesn't work for composite types, but verify it complains 890create type ddtest1 as (f1 posint); 891create table ddtest2(f1 ddtest1); 892insert into ddtest2 values(row(-1)); 893alter domain posint add constraint c1 check(value >= 0); 894ERROR: cannot alter type "posint" because column "ddtest2.f1" uses it 895drop table ddtest2; 896-- Likewise for domains within arrays of composite 897create table ddtest2(f1 ddtest1[]); 898insert into ddtest2 values('{(-1)}'); 899alter domain posint add constraint c1 check(value >= 0); 900ERROR: cannot alter type "posint" because column "ddtest2.f1" uses it 901drop table ddtest2; 902-- Likewise for domains within domains over composite 903create domain ddtest1d as ddtest1; 904create table ddtest2(f1 ddtest1d); 905insert into ddtest2 values('(-1)'); 906alter domain posint add constraint c1 check(value >= 0); 907ERROR: cannot alter type "posint" because column "ddtest2.f1" uses it 908drop table ddtest2; 909drop domain ddtest1d; 910-- Likewise for domains within domains over array of composite 911create domain ddtest1d as ddtest1[]; 912create table ddtest2(f1 ddtest1d); 913insert into ddtest2 values('{(-1)}'); 914alter domain posint add constraint c1 check(value >= 0); 915ERROR: cannot alter type "posint" because column "ddtest2.f1" uses it 916drop table ddtest2; 917drop domain ddtest1d; 918-- Doesn't work for ranges, either 919create type rposint as range (subtype = posint); 920create table ddtest2(f1 rposint); 921insert into ddtest2 values('(-1,3]'); 922alter domain posint add constraint c1 check(value >= 0); 923ERROR: cannot alter type "posint" because column "ddtest2.f1" uses it 924drop table ddtest2; 925drop type rposint; 926alter domain posint add constraint c1 check(value >= 0); 927create domain posint2 as posint check (value % 2 = 0); 928create table ddtest2(f1 posint2); 929insert into ddtest2 values(11); -- fail 930ERROR: value for domain posint2 violates check constraint "posint2_check" 931insert into ddtest2 values(-2); -- fail 932ERROR: value for domain posint2 violates check constraint "c1" 933insert into ddtest2 values(2); 934alter domain posint add constraint c2 check(value >= 10); -- fail 935ERROR: column "f1" of table "ddtest2" contains values that violate the new constraint 936alter domain posint add constraint c2 check(value > 0); -- OK 937drop table ddtest2; 938drop type ddtest1; 939drop domain posint cascade; 940NOTICE: drop cascades to type posint2 941-- 942-- Check enforcement of domain-related typmod in plpgsql (bug #5717) 943-- 944create or replace function array_elem_check(numeric) returns numeric as $$ 945declare 946 x numeric(4,2)[1]; 947begin 948 x[1] := $1; 949 return x[1]; 950end$$ language plpgsql; 951select array_elem_check(121.00); 952ERROR: numeric field overflow 953DETAIL: A field with precision 4, scale 2 must round to an absolute value less than 10^2. 954CONTEXT: PL/pgSQL function array_elem_check(numeric) line 5 at assignment 955select array_elem_check(1.23456); 956 array_elem_check 957------------------ 958 1.23 959(1 row) 960 961create domain mynums as numeric(4,2)[1]; 962create or replace function array_elem_check(numeric) returns numeric as $$ 963declare 964 x mynums; 965begin 966 x[1] := $1; 967 return x[1]; 968end$$ language plpgsql; 969select array_elem_check(121.00); 970ERROR: numeric field overflow 971DETAIL: A field with precision 4, scale 2 must round to an absolute value less than 10^2. 972CONTEXT: PL/pgSQL function array_elem_check(numeric) line 5 at assignment 973select array_elem_check(1.23456); 974 array_elem_check 975------------------ 976 1.23 977(1 row) 978 979create domain mynums2 as mynums; 980create or replace function array_elem_check(numeric) returns numeric as $$ 981declare 982 x mynums2; 983begin 984 x[1] := $1; 985 return x[1]; 986end$$ language plpgsql; 987select array_elem_check(121.00); 988ERROR: numeric field overflow 989DETAIL: A field with precision 4, scale 2 must round to an absolute value less than 10^2. 990CONTEXT: PL/pgSQL function array_elem_check(numeric) line 5 at assignment 991select array_elem_check(1.23456); 992 array_elem_check 993------------------ 994 1.23 995(1 row) 996 997drop function array_elem_check(numeric); 998-- 999-- Check enforcement of array-level domain constraints 1000-- 1001create domain orderedpair as int[2] check (value[1] < value[2]); 1002select array[1,2]::orderedpair; 1003 array 1004------- 1005 {1,2} 1006(1 row) 1007 1008select array[2,1]::orderedpair; -- fail 1009ERROR: value for domain orderedpair violates check constraint "orderedpair_check" 1010create temp table op (f1 orderedpair); 1011insert into op values (array[1,2]); 1012insert into op values (array[2,1]); -- fail 1013ERROR: value for domain orderedpair violates check constraint "orderedpair_check" 1014update op set f1[2] = 3; 1015update op set f1[2] = 0; -- fail 1016ERROR: value for domain orderedpair violates check constraint "orderedpair_check" 1017select * from op; 1018 f1 1019------- 1020 {1,3} 1021(1 row) 1022 1023create or replace function array_elem_check(int) returns int as $$ 1024declare 1025 x orderedpair := '{1,2}'; 1026begin 1027 x[2] := $1; 1028 return x[2]; 1029end$$ language plpgsql; 1030select array_elem_check(3); 1031 array_elem_check 1032------------------ 1033 3 1034(1 row) 1035 1036select array_elem_check(-1); 1037ERROR: value for domain orderedpair violates check constraint "orderedpair_check" 1038CONTEXT: PL/pgSQL function array_elem_check(integer) line 5 at assignment 1039drop function array_elem_check(int); 1040-- 1041-- Check enforcement of changing constraints in plpgsql 1042-- 1043create domain di as int; 1044create function dom_check(int) returns di as $$ 1045declare d di; 1046begin 1047 d := $1; 1048 return d; 1049end 1050$$ language plpgsql immutable; 1051select dom_check(0); 1052 dom_check 1053----------- 1054 0 1055(1 row) 1056 1057alter domain di add constraint pos check (value > 0); 1058select dom_check(0); -- fail 1059ERROR: value for domain di violates check constraint "pos" 1060CONTEXT: PL/pgSQL function dom_check(integer) line 4 at assignment 1061alter domain di drop constraint pos; 1062select dom_check(0); 1063 dom_check 1064----------- 1065 0 1066(1 row) 1067 1068drop function dom_check(int); 1069drop domain di; 1070-- 1071-- Check use of a (non-inline-able) SQL function in a domain constraint; 1072-- this has caused issues in the past 1073-- 1074create function sql_is_distinct_from(anyelement, anyelement) 1075returns boolean language sql 1076as 'select $1 is distinct from $2 limit 1'; 1077create domain inotnull int 1078 check (sql_is_distinct_from(value, null)); 1079select 1::inotnull; 1080 inotnull 1081---------- 1082 1 1083(1 row) 1084 1085select null::inotnull; 1086ERROR: value for domain inotnull violates check constraint "inotnull_check" 1087create table dom_table (x inotnull); 1088insert into dom_table values ('1'); 1089insert into dom_table values (1); 1090insert into dom_table values (null); 1091ERROR: value for domain inotnull violates check constraint "inotnull_check" 1092drop table dom_table; 1093drop domain inotnull; 1094drop function sql_is_distinct_from(anyelement, anyelement); 1095-- 1096-- Renaming 1097-- 1098create domain testdomain1 as int; 1099alter domain testdomain1 rename to testdomain2; 1100alter type testdomain2 rename to testdomain3; -- alter type also works 1101drop domain testdomain3; 1102-- 1103-- Renaming domain constraints 1104-- 1105create domain testdomain1 as int constraint unsigned check (value > 0); 1106alter domain testdomain1 rename constraint unsigned to unsigned_foo; 1107alter domain testdomain1 drop constraint unsigned_foo; 1108drop domain testdomain1; 1109