domain.out 4.97 KB
Newer Older
1 2 3 4 5
-- Test Comment / Drop
create domain domaindroptest int4;
comment on domain domaindroptest is 'About to drop this..';
create domain basetypetest domaindroptest;
ERROR:  DefineDomain: domaindroptest is not a basetype
Tom Lane's avatar
Tom Lane committed
6 7
drop domain domaindroptest cascade;
ERROR:  DROP DOMAIN does not support the CASCADE keyword
8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
drop domain domaindroptest;
-- TEST Domains.
create domain domainvarchar varchar(5);
create domain domainnumeric numeric(8,2);
create domain domainint4 int4;
create domain domaintext text;
-- Test tables using domains
create table basictest
           ( testint4 domainint4
           , testtext domaintext
           , testvarchar domainvarchar
           , testnumeric domainnumeric
           );
INSERT INTO basictest values ('88', 'haha', 'short', '123.12');      -- Good
INSERT INTO basictest values ('88', 'haha', 'short text', '123.12'); -- Bad varchar
ERROR:  value too long for type character varying(5)
INSERT INTO basictest values ('88', 'haha', 'short', '123.1212');    -- Truncate numeric
select * from basictest;
 testint4 | testtext | testvarchar | testnumeric 
----------+----------+-------------+-------------
 88       | haha     | short       | 123.12
 88       | haha     | short       | 123.12
(2 rows)

Tom Lane's avatar
Tom Lane committed
32 33 34 35 36 37 38 39 40 41
-- check that domains inherit operations from base types
-- XXX shouldn't have to quote the constant here
select testtext || testvarchar as concat, testnumeric + '42' as sum
from basictest;
  concat   |  sum   
-----------+--------
 hahashort | 165.12
 hahashort | 165.12
(2 rows)

42 43 44 45
drop table basictest;
drop domain domainvarchar restrict;
drop domain domainnumeric restrict;
drop domain domainint4 restrict;
Tom Lane's avatar
Tom Lane committed
46
drop domain domaintext;
47 48 49 50 51 52 53 54 55 56 57 58
-- Array Test
create 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"}}');
Tom Lane's avatar
Tom Lane committed
59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78
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)

79 80 81 82 83 84 85 86 87 88 89 90
drop table domarrtest;
drop domain domainint4arr restrict;
drop domain domaintextarr restrict;
create domain dnotnull varchar(15) NOT NULL;
create domain dnull    varchar(15) NULL;
create table nulltest
           ( col1 dnotnull
           , col2 dnotnull NULL  -- NOT NULL in the domain cannot be overridden
           , col3 dnull    NOT NULL
           , col4 dnull
           );
INSERT INTO nulltest DEFAULT VALUES;
91
ERROR:  ExecAppend: Fail to add null value in not null attribute col1
92 93
INSERT INTO nulltest values ('a', 'b', 'c', 'd');  -- Good
INSERT INTO nulltest values (NULL, 'b', 'c', 'd');
94
ERROR:  ExecAppend: Fail to add null value in not null attribute col1
95
INSERT INTO nulltest values ('a', NULL, 'c', 'd');
96
ERROR:  ExecAppend: Fail to add null value in not null attribute col2
97
INSERT INTO nulltest values ('a', 'b', NULL, 'd');
98
ERROR:  ExecAppend: Fail to add null value in not null attribute col3
99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144
INSERT INTO nulltest values ('a', 'b', 'c', NULL); -- Good
select * from nulltest;
 col1 | col2 | col3 | col4 
------+------+------+------
 a    | b    | c    | d
 a    | b    | c    | 
(2 rows)

drop table nulltest;
drop domain dnotnull restrict;
drop domain dnull restrict;
create domain ddef1 int4 DEFAULT 3;
create domain ddef2 oid DEFAULT '12';
-- Type mixing, function returns int8
create 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
            , col5 ddef1 NOT NULL DEFAULT NULL
            , col6 ddef2 DEFAULT '88'
            , col7 ddef4 DEFAULT 8000
		, col8 ddef5
            );
insert into defaulttest default values;
insert into defaulttest default values;
insert into defaulttest default values;
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 rows)

drop sequence ddef4_seq;
drop table defaulttest;
drop domain ddef1 restrict;
drop domain ddef2 restrict;
drop domain ddef3 restrict;
drop domain ddef4 restrict;
drop domain ddef5 restrict;