-- Test Comment / Dropcreate domain domaindroptest int4;comment on domain domaindroptest is 'About to drop this..';-- currently this will be disallowedcreate domain basetypetest domaindroptest;ERROR:  "domaindroptest" is not a valid base type for a domaindrop domain domaindroptest;-- this should fail because already gonedrop domain domaindroptest cascade;ERROR:  type "domaindroptest" does not exist-- TEST Domains.create domain domainvarchar varchar(5);create domain domainnumeric numeric(8,2);create domain domainint4 int4;create domain domaintext text;-- Test explicit coercions --- these should succeed (and truncate)SELECT cast('123456' as domainvarchar); domainvarchar --------------- 12345(1 row)SELECT cast('12345' as domainvarchar); domainvarchar --------------- 12345(1 row)-- Test tables using domainscreate table basictest           ( testint4 domainint4           , testtext domaintext           , testvarchar domainvarchar           , testnumeric domainnumeric           );INSERT INTO basictest values ('88', 'haha', 'short', '123.12');      -- GoodINSERT INTO basictest values ('88', 'haha', 'short text', '123.12'); -- Bad varcharERROR:  value too long for type character varying(5)INSERT INTO basictest values ('88', 'haha', 'short', '123.1212');    -- Truncate numeric-- Test copyCOPY basictest (testvarchar) FROM stdin; -- failERROR:  value too long for type character varying(5)CONTEXT:  COPY basictest, line 1: "notsoshorttext"COPY basictest (testvarchar) FROM stdin;select * from basictest; testint4 | testtext | testvarchar | testnumeric ----------+----------+-------------+-------------       88 | haha     | short       |      123.12       88 | haha     | short       |      123.12          |          | short       |            (3 rows)-- check that domains inherit operations from base typesselect testtext || testvarchar as concat, testnumeric + 42 as sumfrom basictest;  concat   |  sum   -----------+-------- hahashort | 165.12 hahashort | 165.12           |       (3 rows)drop table basictest;drop domain domainvarchar restrict;drop domain domainnumeric restrict;drop domain domainint4 restrict;drop domain domaintext;-- Array Testcreate domain domainint4arr int4[1];create domain domaintextarr text[2][3];create table domarrtest           ( testint4arr domainint4arr           , testtextarr domaintextarr            );INSERT INTO domarrtest values ('{2,2}', '{{"a","b"}{"c","d"}}');INSERT INTO domarrtest values ('{{2,2}{2,2}}', '{{"a","b"}}');INSERT INTO domarrtest values ('{2,2}', '{{"a","b"}{"c","d"}{"e"}}');INSERT INTO domarrtest values ('{2,2}', '{{"a"}{"c"}}');INSERT INTO domarrtest values (NULL, '{{"a","b"}{"c","d","e"}}');select * from domarrtest;  testint4arr  |     testtextarr     ---------------+--------------------- {2,2}         | {{a,c},{"",d}} {{2,2},{0,2}} | {{a,b}} {2,2}         | {{a},{c},{e}} {2,2}         | {{c},{""}}               | {{a,c,""},{"",d,e}}(5 rows)select testint4arr[1], testtextarr[2:2] from domarrtest; testint4arr | testtextarr -------------+-------------           2 | {{"",d}}             |            2 | {{c}}           2 | {{""}}             | {{"",d,e}}(5 rows)drop table domarrtest;drop domain domainint4arr restrict;drop domain domaintextarr restrict;create domain dnotnull varchar(15) NOT NULL;create domain dnull    varchar(15);create domain dcheck   varchar(15) NOT NULL CHECK (VALUE = 'a' OR VALUE = 'c' OR VALUE = 'd');create table nulltest           ( col1 dnotnull           , col2 dnotnull NULL  -- NOT NULL in the domain cannot be overridden           , col3 dnull    NOT NULL           , col4 dnull           , col5 dcheck CHECK (col5 IN ('c', 'd'))           );INSERT INTO nulltest DEFAULT VALUES;ERROR:  domain dnotnull does not allow null valuesINSERT INTO nulltest values ('a', 'b', 'c', 'd', 'c');  -- Goodinsert into nulltest values ('a', 'b', 'c', 'd', NULL);ERROR:  domain dcheck does not allow null valuesinsert into nulltest values ('a', 'b', 'c', 'd', 'a');ERROR:  new row for relation "nulltest" violates check constraint "nulltest_col5"INSERT INTO nulltest values (NULL, 'b', 'c', 'd', 'd');ERROR:  domain dnotnull does not allow null valuesINSERT INTO nulltest values ('a', NULL, 'c', 'd', 'c');ERROR:  domain dnotnull does not allow null valuesINSERT INTO nulltest values ('a', 'b', NULL, 'd', 'c');ERROR:  null value in column "col3" violates not-null constraintINSERT INTO nulltest values ('a', 'b', 'c', NULL, 'd'); -- Good-- Test copyCOPY nulltest FROM stdin; --failERROR:  domain dcheck does not allow null valuesCONTEXT:  COPY nulltest, line 1: "a	b	\N	d	\N"-- Last row is badCOPY nulltest FROM stdin;ERROR:  new row for relation "nulltest" violates check constraint "nulltest_col5"CONTEXT:  COPY nulltest, line 3: "a	b	c	\N	a"select * from nulltest; col1 | col2 | col3 | col4 | col5 ------+------+------+------+------ a    | b    | c    | d    | c a    | b    | c    |      | d(2 rows)-- Test out coerced (casted) constraintsSELECT cast('1' as dnotnull); dnotnull ---------- 1(1 row)SELECT cast(NULL as dnotnull); -- failERROR:  domain dnotnull does not allow null valuesSELECT cast(cast(NULL as dnull) as dnotnull); -- failERROR:  domain dnotnull does not allow null valuesSELECT cast(col4 as dnotnull) from nulltest; -- failERROR:  domain dnotnull does not allow null values-- cleanupdrop table nulltest;drop domain dnotnull restrict;drop domain dnull restrict;drop domain dcheck restrict;create domain ddef1 int4 DEFAULT 3;create domain ddef2 oid DEFAULT '12';-- Type mixing, function returns int8create domain ddef3 text DEFAULT 5;create sequence ddef4_seq;create domain ddef4 int4 DEFAULT nextval(cast('ddef4_seq' as text));create domain ddef5 numeric(8,2) NOT NULL DEFAULT '12.12';create table defaulttest            ( col1 ddef1            , col2 ddef2            , col3 ddef3            , col4 ddef4 PRIMARY KEY            , col5 ddef1 NOT NULL DEFAULT NULL            , col6 ddef2 DEFAULT '88'            , col7 ddef4 DEFAULT 8000            , col8 ddef5            );NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "defaulttest_pkey" for table "defaulttest"insert into defaulttest default values;insert into defaulttest default values;insert into defaulttest default values;-- Test defaults with copyCOPY defaulttest(col5) FROM stdin;select * from defaulttest; col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8  ------+------+------+------+------+------+------+-------    3 |   12 | 5    |    1 |    3 |   88 | 8000 | 12.12    3 |   12 | 5    |    2 |    3 |   88 | 8000 | 12.12    3 |   12 | 5    |    3 |    3 |   88 | 8000 | 12.12    3 |   12 | 5    |    4 |   42 |   88 | 8000 | 12.12(4 rows)drop sequence ddef4_seq;drop table defaulttest cascade;-- Test ALTER DOMAIN .. NOT NULLcreate domain dnotnulltest integer;create table domnotnull( col1 dnotnulltest, col2 dnotnulltest);insert into domnotnull default values;alter domain dnotnulltest set not null; -- failsERROR:  column "col1" of table "domnotnull" contains null valuesupdate domnotnull set col1 = 5;alter domain dnotnulltest set not null; -- failsERROR:  column "col2" of table "domnotnull" contains null valuesupdate domnotnull set col2 = 6;alter domain dnotnulltest set not null;update domnotnull set col1 = null; -- failsERROR:  domain dnotnulltest does not allow null valuesalter domain dnotnulltest drop not null;update domnotnull set col1 = null;drop domain dnotnulltest cascade;NOTICE:  drop cascades to table domnotnull column col2NOTICE:  drop cascades to table domnotnull column col1-- Test ALTER DOMAIN .. DEFAULT ..create table domdeftest (col1 ddef1);insert into domdeftest default values;select * from domdeftest; col1 ------    3(1 row)alter domain ddef1 set default '42';insert into domdeftest default values;select * from domdeftest; col1 ------    3   42(2 rows)alter domain ddef1 drop default;insert into domdeftest default values;select * from domdeftest; col1 ------    3   42     (3 rows)drop table domdeftest;-- Test ALTER DOMAIN .. CONSTRAINT ..create domain con as integer;create table domcontest (col1 con);insert into domcontest values (1);insert into domcontest values (2);alter domain con add constraint t check (VALUE < 1); -- failsERROR:  column "col1" of table "domcontest" contains values that violate the new constraintalter domain con add constraint t check (VALUE < 34);alter domain con add check (VALUE > 0);insert into domcontest values (-5); -- failsERROR:  value for domain con violates check constraint "$1"insert into domcontest values (42); -- failsERROR:  value for domain con violates check constraint "t"insert into domcontest values (5);alter domain con drop constraint t;insert into domcontest values (-5); --failsERROR:  value for domain con violates check constraint "$1"insert into domcontest values (42);-- Confirm ALTER DOMAIN with RULES.create table domtab (col1 integer);create domain dom as integer;create view domview as select cast(col1 as dom) from domtab;insert into domtab (col1) values (null);insert into domtab (col1) values (5);select * from domview; col1 ------         5(2 rows)alter domain dom set not null;select * from domview; -- failERROR:  domain dom does not allow null valuesalter domain dom drop not null;select * from domview; col1 ------         5(2 rows)alter domain dom add constraint domchkgt6 check(value > 6);select * from domview; --failERROR:  value for domain dom violates check constraint "domchkgt6"alter domain dom drop constraint domchkgt6 restrict;select * from domview; col1 ------         5(2 rows)-- cleanupdrop domain ddef1 restrict;drop domain ddef2 restrict;drop domain ddef3 restrict;drop domain ddef4 restrict;drop domain ddef5 restrict;