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