2 create domain domaindroptest int4;
3 comment on domain domaindroptest is 'About to drop this..';
4 -- currently this will be disallowed
5 create domain basetypetest domaindroptest;
6 ERROR: DefineDomain: domaindroptest is not a basetype
7 drop domain domaindroptest;
8 -- this should fail because already gone
9 drop domain domaindroptest cascade;
10 ERROR: Type "domaindroptest" does not exist
12 create domain domainvarchar varchar(5);
13 create domain domainnumeric numeric(8,2);
14 create domain domainint4 int4;
15 create domain domaintext text;
16 -- Test explicit coercions --- these should succeed (and truncate)
17 SELECT cast('123456' as domainvarchar);
23 SELECT cast('12345' as domainvarchar);
29 -- Test tables using domains
30 create table basictest
33 , testvarchar domainvarchar
34 , testnumeric domainnumeric
36 INSERT INTO basictest values ('88', 'haha', 'short', '123.12'); -- Good
37 INSERT INTO basictest values ('88', 'haha', 'short text', '123.12'); -- Bad varchar
38 ERROR: value too long for type character varying(5)
39 INSERT INTO basictest values ('88', 'haha', 'short', '123.1212'); -- Truncate numeric
41 COPY basictest (testvarchar) FROM stdin; -- fail
42 ERROR: copy: line 1, value too long for type character varying(5)
43 lost synchronization with server, resetting connection
44 SET autocommit TO 'on';
45 COPY basictest (testvarchar) FROM stdin;
46 select * from basictest;
47 testint4 | testtext | testvarchar | testnumeric
48 ----------+----------+-------------+-------------
49 88 | haha | short | 123.12
50 88 | haha | short | 123.12
54 -- check that domains inherit operations from base types
55 select testtext || testvarchar as concat, testnumeric + 42 as sum
65 drop domain domainvarchar restrict;
66 drop domain domainnumeric restrict;
67 drop domain domainint4 restrict;
68 drop domain domaintext;
70 create domain domainint4arr int4[1];
71 create domain domaintextarr text[2][3];
72 create table domarrtest
73 ( testint4arr domainint4arr
74 , testtextarr domaintextarr
76 INSERT INTO domarrtest values ('{2,2}', '{{"a","b"}{"c","d"}}');
77 INSERT INTO domarrtest values ('{{2,2}{2,2}}', '{{"a","b"}}');
78 INSERT INTO domarrtest values ('{2,2}', '{{"a","b"}{"c","d"}{"e"}}');
79 INSERT INTO domarrtest values ('{2,2}', '{{"a"}{"c"}}');
80 INSERT INTO domarrtest values (NULL, '{{"a","b"}{"c","d","e"}}');
81 select * from domarrtest;
82 testint4arr | testtextarr
83 ---------------+---------------------
84 {2,2} | {{a,c},{"",d}}
85 {{2,2},{0,2}} | {{a,b}}
91 select testint4arr[1], testtextarr[2:2] from domarrtest;
92 testint4arr | testtextarr
93 -------------+-------------
101 drop table domarrtest;
102 drop domain domainint4arr restrict;
103 drop domain domaintextarr restrict;
104 create domain dnotnull varchar(15) NOT NULL;
105 create domain dnull varchar(15);
106 create domain dcheck varchar(15) NOT NULL CHECK (VALUE = 'a' OR VALUE = 'c' OR VALUE = 'd');
107 create table nulltest
109 , col2 dnotnull NULL -- NOT NULL in the domain cannot be overridden
110 , col3 dnull NOT NULL
112 , col5 dcheck CHECK (col5 IN ('c', 'd'))
114 INSERT INTO nulltest DEFAULT VALUES;
115 ERROR: Domain dnotnull does not allow NULL values
116 INSERT INTO nulltest values ('a', 'b', 'c', 'd', 'c'); -- Good
117 insert into nulltest values ('a', 'b', 'c', 'd', NULL);
118 ERROR: Domain dcheck does not allow NULL values
119 insert into nulltest values ('a', 'b', 'c', 'd', 'a');
120 ERROR: ExecInsert: rejected due to CHECK constraint "nulltest_col5" on "nulltest"
121 INSERT INTO nulltest values (NULL, 'b', 'c', 'd', 'd');
122 ERROR: Domain dnotnull does not allow NULL values
123 INSERT INTO nulltest values ('a', NULL, 'c', 'd', 'c');
124 ERROR: Domain dnotnull does not allow NULL values
125 INSERT INTO nulltest values ('a', 'b', NULL, 'd', 'c');
126 ERROR: ExecInsert: Fail to add null value in not null attribute col3
127 INSERT INTO nulltest values ('a', 'b', 'c', NULL, 'd'); -- Good
129 COPY nulltest FROM stdin; --fail
130 ERROR: copy: line 1, Domain dcheck does not allow NULL values
131 lost synchronization with server, resetting connection
132 SET autocommit TO 'on';
134 COPY nulltest FROM stdin;
135 ERROR: copy: line 3, CopyFrom: rejected due to CHECK constraint "nulltest_col5" on "nulltest"
136 lost synchronization with server, resetting connection
137 select * from nulltest;
138 col1 | col2 | col3 | col4 | col5
139 ------+------+------+------+------
144 -- Test out coerced (casted) constraints
145 SELECT cast('1' as dnotnull);
151 SELECT cast(NULL as dnotnull); -- fail
152 ERROR: Domain dnotnull does not allow NULL values
153 SELECT cast(cast(NULL as dnull) as dnotnull); -- fail
154 ERROR: Domain dnotnull does not allow NULL values
155 SELECT cast(col4 as dnotnull) from nulltest; -- fail
156 ERROR: Domain dnotnull does not allow NULL values
159 drop domain dnotnull restrict;
160 drop domain dnull restrict;
161 drop domain dcheck restrict;
162 create domain ddef1 int4 DEFAULT 3;
163 create domain ddef2 oid DEFAULT '12';
164 -- Type mixing, function returns int8
165 create domain ddef3 text DEFAULT 5;
166 create sequence ddef4_seq;
167 create domain ddef4 int4 DEFAULT nextval(cast('ddef4_seq' as text));
168 create domain ddef5 numeric(8,2) NOT NULL DEFAULT '12.12';
169 create table defaulttest
173 , col4 ddef4 PRIMARY KEY
174 , col5 ddef1 NOT NULL DEFAULT NULL
175 , col6 ddef2 DEFAULT '88'
176 , col7 ddef4 DEFAULT 8000
179 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'defaulttest_pkey' for table 'defaulttest'
180 insert into defaulttest default values;
181 insert into defaulttest default values;
182 insert into defaulttest default values;
183 -- Test defaults with copy
184 COPY defaulttest(col5) FROM stdin;
185 select * from defaulttest;
186 col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8
187 ------+------+------+------+------+------+------+-------
188 3 | 12 | 5 | 1 | 3 | 88 | 8000 | 12.12
189 3 | 12 | 5 | 2 | 3 | 88 | 8000 | 12.12
190 3 | 12 | 5 | 3 | 3 | 88 | 8000 | 12.12
191 3 | 12 | 5 | 4 | 42 | 88 | 8000 | 12.12
194 drop sequence ddef4_seq;
195 drop table defaulttest cascade;
196 -- Test ALTER DOMAIN .. NOT NULL
197 create domain dnotnulltest integer;
198 create table domnotnull
202 insert into domnotnull default values;
203 alter domain dnotnulltest set not null; -- fails
204 ERROR: ALTER DOMAIN: Relation "domnotnull" attribute "col1" contains NULL values
205 update domnotnull set col1 = 5;
206 alter domain dnotnulltest set not null; -- fails
207 ERROR: ALTER DOMAIN: Relation "domnotnull" attribute "col2" contains NULL values
208 update domnotnull set col2 = 6;
209 alter domain dnotnulltest set not null;
210 alter domain dnotnulltest set not null; -- fails
211 NOTICE: AlterDomain: dnotnulltest is already set to NOT NULL
212 update domnotnull set col1 = null; -- fails
213 ERROR: Domain dnotnulltest does not allow NULL values
214 alter domain dnotnulltest drop not null;
215 alter domain dnotnulltest drop not null; -- fails
216 NOTICE: AlterDomain: dnotnulltest is already set to NULL
217 update domnotnull set col1 = null;
218 drop domain dnotnulltest cascade;
219 NOTICE: Drop cascades to table domnotnull column col2
220 NOTICE: Drop cascades to table domnotnull column col1
221 -- Test ALTER DOMAIN .. DEFAULT ..
222 create table domdeftest (col1 ddef1);
223 insert into domdeftest default values;
224 select * from domdeftest;
230 alter domain ddef1 set default '42';
231 insert into domdeftest default values;
232 select * from domdeftest;
239 alter domain ddef1 drop default;
240 insert into domdeftest default values;
241 select * from domdeftest;
249 drop table domdeftest;
250 -- Test ALTER DOMAIN .. CONSTRAINT ..
251 create domain con as integer;
252 create table domcontest (col1 con);
253 insert into domcontest values (1);
254 insert into domcontest values (2);
255 alter domain con add constraint t check (VALUE < 1); -- fails
256 ERROR: ALTER DOMAIN: Relation "domcontest" attribute "col1" contains values that fail the new constraint
257 alter domain con add constraint t check (VALUE < 34);
258 alter domain con add check (VALUE > 0);
259 insert into domcontest values (-5); -- fails
260 ERROR: ExecEvalConstraintTest: Domain con constraint $1 failed
261 insert into domcontest values (42); -- fails
262 ERROR: ExecEvalConstraintTest: Domain con constraint t failed
263 insert into domcontest values (5);
264 alter domain con drop constraint t;
265 insert into domcontest values (-5); --fails
266 ERROR: ExecEvalConstraintTest: Domain con constraint $1 failed
267 insert into domcontest values (42);
269 drop domain ddef1 restrict;
270 drop domain ddef2 restrict;
271 drop domain ddef3 restrict;
272 drop domain ddef4 restrict;
273 drop domain ddef5 restrict;