Adding upstream version 16.2.upstream/16.2

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 1209 insertions, 0 deletions

diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out
new file mode 100644
index 0000000..6d94e84
--- /dev/null
+++ b/src/test/regress/expected/domain.out
@@ -0,0 +1,1209 @@
+--
+-- Test domains.
+--
+-- Test Comment / Drop
+create domain domaindroptest int4;
+comment on domain domaindroptest is 'About to drop this..';
+create domain dependenttypetest domaindroptest;
+-- fail because of dependent type
+drop domain domaindroptest;
+ERROR:  cannot drop type domaindroptest because other objects depend on it
+DETAIL:  type dependenttypetest depends on type domaindroptest
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+drop domain domaindroptest cascade;
+NOTICE:  drop cascades to type dependenttypetest
+-- this should fail because already gone
+drop domain domaindroptest cascade;
+ERROR:  type "domaindroptest" does not exist
+-- Test domain input.
+-- Note: the point of checking both INSERT and COPY FROM is that INSERT
+-- exercises CoerceToDomain while COPY exercises domain_in.
+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 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
+-- Test copy
+COPY basictest (testvarchar) FROM stdin; -- fail
+ERROR:  value too long for type character varying(5)
+CONTEXT:  COPY basictest, line 1, column testvarchar: "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 types
+select testtext || testvarchar as concat, testnumeric + 42 as sum
+from basictest;
+  concat   |  sum   
+-----------+--------
+ hahashort | 165.12
+ hahashort | 165.12
+           |       
+(3 rows)
+
+-- check that union/case/coalesce type resolution handles domains properly
+select pg_typeof(coalesce(4::domainint4, 7));
+ pg_typeof 
+-----------
+ integer
+(1 row)
+
+select pg_typeof(coalesce(4::domainint4, 7::domainint4));
+ pg_typeof  
+------------
+ domainint4
+(1 row)
+
+drop table basictest;
+drop domain domainvarchar restrict;
+drop domain domainnumeric restrict;
+drop domain domainint4 restrict;
+drop domain domaintext;
+-- Test non-error-throwing input
+create domain positiveint int4 check(value > 0);
+create domain weirdfloat float8 check((1 / value) < 10);
+select pg_input_is_valid('1', 'positiveint');
+ pg_input_is_valid 
+-------------------
+ t
+(1 row)
+
+select pg_input_is_valid('junk', 'positiveint');
+ pg_input_is_valid 
+-------------------
+ f
+(1 row)
+
+select pg_input_is_valid('-1', 'positiveint');
+ pg_input_is_valid 
+-------------------
+ f
+(1 row)
+
+select * from pg_input_error_info('junk', 'positiveint');
+                    message                    | detail | hint | sql_error_code 
+-----------------------------------------------+--------+------+----------------
+ invalid input syntax for type integer: "junk" |        |      | 22P02
+(1 row)
+
+select * from pg_input_error_info('-1', 'positiveint');
+                                  message                                   | detail | hint | sql_error_code 
+----------------------------------------------------------------------------+--------+------+----------------
+ value for domain positiveint violates check constraint "positiveint_check" |        |      | 23514
+(1 row)
+
+select * from pg_input_error_info('junk', 'weirdfloat');
+                        message                         | detail | hint | sql_error_code 
+--------------------------------------------------------+--------+------+----------------
+ invalid input syntax for type double precision: "junk" |        |      | 22P02
+(1 row)
+
+select * from pg_input_error_info('0.01', 'weirdfloat');
+                                 message                                  | detail | hint | sql_error_code 
+--------------------------------------------------------------------------+--------+------+----------------
+ value for domain weirdfloat violates check constraint "weirdfloat_check" |        |      | 23514
+(1 row)
+
+-- We currently can't trap errors raised in the CHECK expression itself
+select * from pg_input_error_info('0', 'weirdfloat');
+ERROR:  division by zero
+drop domain positiveint;
+drop domain weirdfloat;
+-- Test domains over array types
+create domain domainint4arr int4[1];
+create domain domainchar4arr varchar(4)[2][3];
+create table domarrtest
+           ( testint4arr domainint4arr
+           , testchar4arr domainchar4arr
+            );
+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","f"}}');
+INSERT INTO domarrtest values ('{2,2}', '{{"a"},{"c"}}');
+INSERT INTO domarrtest values (NULL, '{{"a","b","c"},{"d","e","f"}}');
+INSERT INTO domarrtest values (NULL, '{{"toolong","b","c"},{"d","e","f"}}');
+ERROR:  value too long for type character varying(4)
+INSERT INTO domarrtest (testint4arr[1], testint4arr[3]) values (11,22);
+select * from domarrtest;
+  testint4arr  |    testchar4arr     
+---------------+---------------------
+ {2,2}         | {{a,b},{c,d}}
+ {{2,2},{2,2}} | {{a,b}}
+ {2,2}         | {{a,b},{c,d},{e,f}}
+ {2,2}         | {{a},{c}}
+               | {{a,b,c},{d,e,f}}
+ {11,NULL,22}  | 
+(6 rows)
+
+select testint4arr[1], testchar4arr[2:2] from domarrtest;
+ testint4arr | testchar4arr 
+-------------+--------------
+           2 | {{c,d}}
+             | {}
+           2 | {{c,d}}
+           2 | {{c}}
+             | {{d,e,f}}
+          11 | 
+(6 rows)
+
+select array_dims(testint4arr), array_dims(testchar4arr) from domarrtest;
+ array_dims | array_dims 
+------------+------------
+ [1:2]      | [1:2][1:2]
+ [1:2][1:2] | [1:1][1:2]
+ [1:2]      | [1:3][1:2]
+ [1:2]      | [1:2][1:1]
+            | [1:2][1:3]
+ [1:3]      | 
+(6 rows)
+
+COPY domarrtest FROM stdin;
+COPY domarrtest FROM stdin;	-- fail
+ERROR:  value too long for type character varying(4)
+CONTEXT:  COPY domarrtest, line 1, column testchar4arr: "{qwerty,w,e}"
+select * from domarrtest;
+  testint4arr  |    testchar4arr     
+---------------+---------------------
+ {2,2}         | {{a,b},{c,d}}
+ {{2,2},{2,2}} | {{a,b}}
+ {2,2}         | {{a,b},{c,d},{e,f}}
+ {2,2}         | {{a},{c}}
+               | {{a,b,c},{d,e,f}}
+ {11,NULL,22}  | 
+ {3,4}         | {q,w,e}
+               | 
+(8 rows)
+
+update domarrtest set
+  testint4arr[1] = testint4arr[1] + 1,
+  testint4arr[3] = testint4arr[3] - 1
+where testchar4arr is null;
+select * from domarrtest where testchar4arr is null;
+   testint4arr    | testchar4arr 
+------------------+--------------
+ {12,NULL,21}     | 
+ {NULL,NULL,NULL} | 
+(2 rows)
+
+drop table domarrtest;
+drop domain domainint4arr restrict;
+drop domain domainchar4arr restrict;
+create domain dia as int[];
+select '{1,2,3}'::dia;
+   dia   
+---------
+ {1,2,3}
+(1 row)
+
+select array_dims('{1,2,3}'::dia);
+ array_dims 
+------------
+ [1:3]
+(1 row)
+
+select pg_typeof('{1,2,3}'::dia);
+ pg_typeof 
+-----------
+ dia
+(1 row)
+
+select pg_typeof('{1,2,3}'::dia || 42); -- should be int[] not dia
+ pg_typeof 
+-----------
+ integer[]
+(1 row)
+
+drop domain dia;
+-- Test domains over composites
+create type comptype as (r float8, i float8);
+create domain dcomptype as comptype;
+create table dcomptable (d1 dcomptype unique);
+insert into dcomptable values (row(1,2)::dcomptype);
+insert into dcomptable values (row(3,4)::comptype);
+insert into dcomptable values (row(1,2)::dcomptype);  -- fail on uniqueness
+ERROR:  duplicate key value violates unique constraint "dcomptable_d1_key"
+DETAIL:  Key (d1)=((1,2)) already exists.
+insert into dcomptable (d1.r) values(11);
+select * from dcomptable;
+  d1   
+-------
+ (1,2)
+ (3,4)
+ (11,)
+(3 rows)
+
+select (d1).r, (d1).i, (d1).* from dcomptable;
+ r  | i | r  | i 
+----+---+----+---
+  1 | 2 |  1 | 2
+  3 | 4 |  3 | 4
+ 11 |   | 11 |  
+(3 rows)
+
+update dcomptable set d1.r = (d1).r + 1 where (d1).i > 0;
+select * from dcomptable;
+  d1   
+-------
+ (11,)
+ (2,2)
+ (4,4)
+(3 rows)
+
+alter domain dcomptype add constraint c1 check ((value).r <= (value).i);
+alter domain dcomptype add constraint c2 check ((value).r > (value).i);  -- fail
+ERROR:  column "d1" of table "dcomptable" contains values that violate the new constraint
+select row(2,1)::dcomptype;  -- fail
+ERROR:  value for domain dcomptype violates check constraint "c1"
+insert into dcomptable values (row(1,2)::comptype);
+insert into dcomptable values (row(2,1)::comptype);  -- fail
+ERROR:  value for domain dcomptype violates check constraint "c1"
+insert into dcomptable (d1.r) values(99);
+insert into dcomptable (d1.r, d1.i) values(99, 100);
+insert into dcomptable (d1.r, d1.i) values(100, 99);  -- fail
+ERROR:  value for domain dcomptype violates check constraint "c1"
+update dcomptable set d1.r = (d1).r + 1 where (d1).i > 0;  -- fail
+ERROR:  value for domain dcomptype violates check constraint "c1"
+update dcomptable set d1.r = (d1).r - 1, d1.i = (d1).i + 1 where (d1).i > 0;
+select * from dcomptable;
+    d1    
+----------
+ (11,)
+ (99,)
+ (1,3)
+ (3,5)
+ (0,3)
+ (98,101)
+(6 rows)
+
+explain (verbose, costs off)
+  update dcomptable set d1.r = (d1).r - 1, d1.i = (d1).i + 1 where (d1).i > 0;
+                                          QUERY PLAN                                           
+-----------------------------------------------------------------------------------------------
+ Update on public.dcomptable
+   ->  Seq Scan on public.dcomptable
+         Output: ROW(((d1).r - '1'::double precision), ((d1).i + '1'::double precision)), ctid
+         Filter: ((dcomptable.d1).i > '0'::double precision)
+(4 rows)
+
+create rule silly as on delete to dcomptable do instead
+  update dcomptable set d1.r = (d1).r - 1, d1.i = (d1).i + 1 where (d1).i > 0;
+\d+ dcomptable
+                                  Table "public.dcomptable"
+ Column |   Type    | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+-----------+-----------+----------+---------+----------+--------------+-------------
+ d1     | dcomptype |           |          |         | extended |              | 
+Indexes:
+    "dcomptable_d1_key" UNIQUE CONSTRAINT, btree (d1)
+Rules:
+    silly AS
+    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
+  WHERE (dcomptable.d1).i > 0::double precision
+
+create function makedcomp(r float8, i float8) returns dcomptype
+as 'select row(r, i)' language sql;
+select makedcomp(1,2);
+ makedcomp 
+-----------
+ (1,2)
+(1 row)
+
+select makedcomp(2,1);  -- fail
+ERROR:  value for domain dcomptype violates check constraint "c1"
+select * from makedcomp(1,2) m;
+ r | i 
+---+---
+ 1 | 2
+(1 row)
+
+select m, m is not null from makedcomp(1,2) m;
+   m   | ?column? 
+-------+----------
+ (1,2) | t
+(1 row)
+
+drop function makedcomp(float8, float8);
+drop table dcomptable;
+drop type comptype cascade;
+NOTICE:  drop cascades to type dcomptype
+-- check altering and dropping columns used by domain constraints
+create type comptype as (r float8, i float8);
+create domain dcomptype as comptype;
+alter domain dcomptype add constraint c1 check ((value).r > 0);
+comment on constraint c1 on domain dcomptype is 'random commentary';
+select row(0,1)::dcomptype;  -- fail
+ERROR:  value for domain dcomptype violates check constraint "c1"
+alter type comptype alter attribute r type varchar;  -- fail
+ERROR:  operator does not exist: character varying > double precision
+HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
+alter type comptype alter attribute r type bigint;
+alter type comptype drop attribute r;  -- fail
+ERROR:  cannot drop column r of composite type comptype because other objects depend on it
+DETAIL:  constraint c1 depends on column r of composite type comptype
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+alter type comptype drop attribute i;
+select conname, obj_description(oid, 'pg_constraint') from pg_constraint
+  where contypid = 'dcomptype'::regtype;  -- check comment is still there
+ conname |  obj_description  
+---------+-------------------
+ c1      | random commentary
+(1 row)
+
+drop type comptype cascade;
+NOTICE:  drop cascades to type dcomptype
+-- Test domains over arrays of composite
+create type comptype as (r float8, i float8);
+create domain dcomptypea as comptype[];
+create table dcomptable (d1 dcomptypea unique);
+insert into dcomptable values (array[row(1,2)]::dcomptypea);
+insert into dcomptable values (array[row(3,4), row(5,6)]::comptype[]);
+insert into dcomptable values (array[row(7,8)::comptype, row(9,10)::comptype]);
+insert into dcomptable values (array[row(1,2)]::dcomptypea);  -- fail on uniqueness
+ERROR:  duplicate key value violates unique constraint "dcomptable_d1_key"
+DETAIL:  Key (d1)=({"(1,2)"}) already exists.
+insert into dcomptable (d1[1]) values(row(9,10));
+insert into dcomptable (d1[1].r) values(11);
+select * from dcomptable;
+         d1         
+--------------------
+ {"(1,2)"}
+ {"(3,4)","(5,6)"}
+ {"(7,8)","(9,10)"}
+ {"(9,10)"}
+ {"(11,)"}
+(5 rows)
+
+select d1[2], d1[1].r, d1[1].i from dcomptable;
+   d1   | r  | i  
+--------+----+----
+        |  1 |  2
+ (5,6)  |  3 |  4
+ (9,10) |  7 |  8
+        |  9 | 10
+        | 11 |   
+(5 rows)
+
+update dcomptable set d1[2] = row(d1[2].i, d1[2].r);
+select * from dcomptable;
+         d1         
+--------------------
+ {"(1,2)","(,)"}
+ {"(3,4)","(6,5)"}
+ {"(7,8)","(10,9)"}
+ {"(9,10)","(,)"}
+ {"(11,)","(,)"}
+(5 rows)
+
+update dcomptable set d1[1].r = d1[1].r + 1 where d1[1].i > 0;
+select * from dcomptable;
+         d1         
+--------------------
+ {"(11,)","(,)"}
+ {"(2,2)","(,)"}
+ {"(4,4)","(6,5)"}
+ {"(8,8)","(10,9)"}
+ {"(10,10)","(,)"}
+(5 rows)
+
+alter domain dcomptypea add constraint c1 check (value[1].r <= value[1].i);
+alter domain dcomptypea add constraint c2 check (value[1].r > value[1].i);  -- fail
+ERROR:  column "d1" of table "dcomptable" contains values that violate the new constraint
+select array[row(2,1)]::dcomptypea;  -- fail
+ERROR:  value for domain dcomptypea violates check constraint "c1"
+insert into dcomptable values (array[row(1,2)]::comptype[]);
+insert into dcomptable values (array[row(2,1)]::comptype[]);  -- fail
+ERROR:  value for domain dcomptypea violates check constraint "c1"
+insert into dcomptable (d1[1].r) values(99);
+insert into dcomptable (d1[1].r, d1[1].i) values(99, 100);
+insert into dcomptable (d1[1].r, d1[1].i) values(100, 99);  -- fail
+ERROR:  value for domain dcomptypea violates check constraint "c1"
+update dcomptable set d1[1].r = d1[1].r + 1 where d1[1].i > 0;  -- fail
+ERROR:  value for domain dcomptypea violates check constraint "c1"
+update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1
+  where d1[1].i > 0;
+select * from dcomptable;
+         d1         
+--------------------
+ {"(11,)","(,)"}
+ {"(99,)"}
+ {"(1,3)","(,)"}
+ {"(3,5)","(6,5)"}
+ {"(7,9)","(10,9)"}
+ {"(9,11)","(,)"}
+ {"(0,3)"}
+ {"(98,101)"}
+(8 rows)
+
+explain (verbose, costs off)
+  update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1
+    where d1[1].i > 0;
+                                                   QUERY PLAN                                                   
+----------------------------------------------------------------------------------------------------------------
+ Update on public.dcomptable
+   ->  Seq Scan on public.dcomptable
+         Output: (d1[1].r := (d1[1].r - '1'::double precision))[1].i := (d1[1].i + '1'::double precision), ctid
+         Filter: (dcomptable.d1[1].i > '0'::double precision)
+(4 rows)
+
+create rule silly as on delete to dcomptable do instead
+  update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1
+    where d1[1].i > 0;
+\d+ dcomptable
+                                  Table "public.dcomptable"
+ Column |    Type    | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+------------+-----------+----------+---------+----------+--------------+-------------
+ d1     | dcomptypea |           |          |         | extended |              | 
+Indexes:
+    "dcomptable_d1_key" UNIQUE CONSTRAINT, btree (d1)
+Rules:
+    silly AS
+    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
+  WHERE dcomptable.d1[1].i > 0::double precision
+
+drop table dcomptable;
+drop type comptype cascade;
+NOTICE:  drop cascades to type dcomptypea
+-- Test arrays over domains
+create domain posint as int check (value > 0);
+create table pitable (f1 posint[]);
+insert into pitable values(array[42]);
+insert into pitable values(array[-1]);  -- fail
+ERROR:  value for domain posint violates check constraint "posint_check"
+insert into pitable values('{0}');  -- fail
+ERROR:  value for domain posint violates check constraint "posint_check"
+LINE 1: insert into pitable values('{0}');
+                                   ^
+update pitable set f1[1] = f1[1] + 1;
+update pitable set f1[1] = 0;  -- fail
+ERROR:  value for domain posint violates check constraint "posint_check"
+select * from pitable;
+  f1  
+------
+ {43}
+(1 row)
+
+drop table pitable;
+create domain vc4 as varchar(4);
+create table vc4table (f1 vc4[]);
+insert into vc4table values(array['too long']);  -- fail
+ERROR:  value too long for type character varying(4)
+insert into vc4table values(array['too long']::vc4[]);  -- cast truncates
+select * from vc4table;
+    f1    
+----------
+ {"too "}
+(1 row)
+
+drop table vc4table;
+drop type vc4;
+-- You can sort of fake arrays-of-arrays by putting a domain in between
+create domain dposinta as posint[];
+create table dposintatable (f1 dposinta[]);
+insert into dposintatable values(array[array[42]]);  -- fail
+ERROR:  column "f1" is of type dposinta[] but expression is of type integer[]
+LINE 1: insert into dposintatable values(array[array[42]]);
+                                         ^
+HINT:  You will need to rewrite or cast the expression.
+insert into dposintatable values(array[array[42]::posint[]]); -- still fail
+ERROR:  column "f1" is of type dposinta[] but expression is of type posint[]
+LINE 1: insert into dposintatable values(array[array[42]::posint[]])...
+                                         ^
+HINT:  You will need to rewrite or cast the expression.
+insert into dposintatable values(array[array[42]::dposinta]); -- but this works
+select f1, f1[1], (f1[1])[1] from dposintatable;
+    f1    |  f1  | f1 
+----------+------+----
+ {"{42}"} | {42} | 42
+(1 row)
+
+select pg_typeof(f1) from dposintatable;
+ pg_typeof  
+------------
+ dposinta[]
+(1 row)
+
+select pg_typeof(f1[1]) from dposintatable;
+ pg_typeof 
+-----------
+ dposinta
+(1 row)
+
+select pg_typeof(f1[1][1]) from dposintatable;
+ pg_typeof 
+-----------
+ dposinta
+(1 row)
+
+select pg_typeof((f1[1])[1]) from dposintatable;
+ pg_typeof 
+-----------
+ posint
+(1 row)
+
+update dposintatable set f1[2] = array[99];
+select f1, f1[1], (f1[2])[1] from dposintatable;
+       f1        |  f1  | f1 
+-----------------+------+----
+ {"{42}","{99}"} | {42} | 99
+(1 row)
+
+-- it'd be nice if you could do something like this, but for now you can't:
+update dposintatable set f1[2][1] = array[97];
+ERROR:  wrong number of array subscripts
+-- maybe someday we can make this syntax work:
+update dposintatable set (f1[2])[1] = array[98];
+ERROR:  syntax error at or near "["
+LINE 1: update dposintatable set (f1[2])[1] = array[98];
+                                        ^
+drop table dposintatable;
+drop domain posint cascade;
+NOTICE:  drop cascades to type dposinta
+-- Test arrays over domains of composite
+create type comptype as (cf1 int, cf2 int);
+create domain dcomptype as comptype check ((value).cf1 > 0);
+create table dcomptable (f1 dcomptype[]);
+insert into dcomptable values (null);
+update dcomptable set f1[1].cf2 = 5;
+table dcomptable;
+    f1    
+----------
+ {"(,5)"}
+(1 row)
+
+update dcomptable set f1[1].cf1 = -1;  -- fail
+ERROR:  value for domain dcomptype violates check constraint "dcomptype_check"
+update dcomptable set f1[1].cf1 = 1;
+table dcomptable;
+    f1     
+-----------
+ {"(1,5)"}
+(1 row)
+
+-- if there's no constraints, a different code path is taken:
+alter domain dcomptype drop constraint dcomptype_check;
+update dcomptable set f1[1].cf1 = -1;  -- now ok
+table dcomptable;
+     f1     
+------------
+ {"(-1,5)"}
+(1 row)
+
+drop table dcomptable;
+drop type comptype cascade;
+NOTICE:  drop cascades to type dcomptype
+-- Test not-null restrictions
+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 values
+INSERT INTO nulltest values ('a', 'b', 'c', 'd', 'c');  -- Good
+insert into nulltest values ('a', 'b', 'c', 'd', NULL);
+ERROR:  domain dcheck does not allow null values
+insert into nulltest values ('a', 'b', 'c', 'd', 'a');
+ERROR:  new row for relation "nulltest" violates check constraint "nulltest_col5_check"
+DETAIL:  Failing row contains (a, b, c, d, a).
+INSERT INTO nulltest values (NULL, 'b', 'c', 'd', 'd');
+ERROR:  domain dnotnull does not allow null values
+INSERT INTO nulltest values ('a', NULL, 'c', 'd', 'c');
+ERROR:  domain dnotnull does not allow null values
+INSERT INTO nulltest values ('a', 'b', NULL, 'd', 'c');
+ERROR:  null value in column "col3" of relation "nulltest" violates not-null constraint
+DETAIL:  Failing row contains (a, b, null, d, c).
+INSERT INTO nulltest values ('a', 'b', 'c', NULL, 'd'); -- Good
+-- Test copy
+COPY nulltest FROM stdin; --fail
+ERROR:  null value in column "col3" of relation "nulltest" violates not-null constraint
+DETAIL:  Failing row contains (a, b, null, d, d).
+CONTEXT:  COPY nulltest, line 1: "a	b	\N	d	d"
+COPY nulltest FROM stdin; --fail
+ERROR:  domain dcheck does not allow null values
+CONTEXT:  COPY nulltest, line 1, column col5: null input
+-- Last row is bad
+COPY nulltest FROM stdin;
+ERROR:  new row for relation "nulltest" violates check constraint "nulltest_col5_check"
+DETAIL:  Failing row contains (a, b, c, null, a).
+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) constraints
+SELECT cast('1' as dnotnull);
+ dnotnull 
+----------
+ 1
+(1 row)
+
+SELECT cast(NULL as dnotnull); -- fail
+ERROR:  domain dnotnull does not allow null values
+SELECT cast(cast(NULL as dnull) as dnotnull); -- fail
+ERROR:  domain dnotnull does not allow null values
+SELECT cast(col4 as dnotnull) from nulltest; -- fail
+ERROR:  domain dnotnull does not allow null values
+-- cleanup
+drop 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 int8
+create domain ddef3 text DEFAULT 5;
+create sequence ddef4_seq;
+create domain ddef4 int4 DEFAULT nextval('ddef4_seq');
+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
+            );
+insert into defaulttest(col4) values(0); -- fails, col5 defaults to null
+ERROR:  null value in column "col5" of relation "defaulttest" violates not-null constraint
+DETAIL:  Failing row contains (3, 12, 5, 0, null, 88, 8000, 12.12).
+alter table defaulttest alter column col5 drop default;
+insert into defaulttest default values; -- succeeds, inserts domain default
+-- We used to treat SET DEFAULT NULL as equivalent to DROP DEFAULT; wrong
+alter table defaulttest alter column col5 set default null;
+insert into defaulttest(col4) values(0); -- fails
+ERROR:  null value in column "col5" of relation "defaulttest" violates not-null constraint
+DETAIL:  Failing row contains (3, 12, 5, 0, null, 88, 8000, 12.12).
+alter table defaulttest alter column col5 drop default;
+insert into defaulttest default values;
+insert into defaulttest default values;
+-- Test defaults with copy
+COPY 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 table defaulttest cascade;
+-- Test ALTER DOMAIN .. NOT NULL
+create domain dnotnulltest integer;
+create table domnotnull
+( col1 dnotnulltest
+, col2 dnotnulltest
+);
+insert into domnotnull default values;
+alter domain dnotnulltest set not null; -- fails
+ERROR:  column "col1" of table "domnotnull" contains null values
+update domnotnull set col1 = 5;
+alter domain dnotnulltest set not null; -- fails
+ERROR:  column "col2" of table "domnotnull" contains null values
+update domnotnull set col2 = 6;
+alter domain dnotnulltest set not null;
+update domnotnull set col1 = null; -- fails
+ERROR:  domain dnotnulltest does not allow null values
+alter domain dnotnulltest drop not null;
+update domnotnull set col1 = null;
+drop domain dnotnulltest cascade;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to column col2 of table domnotnull
+drop cascades to column col1 of table domnotnull
+-- 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); -- fails
+ERROR:  column "col1" of table "domcontest" contains values that violate the new constraint
+alter domain con add constraint t check (VALUE < 34);
+alter domain con add check (VALUE > 0);
+insert into domcontest values (-5); -- fails
+ERROR:  value for domain con violates check constraint "con_check"
+insert into domcontest values (42); -- fails
+ERROR:  value for domain con violates check constraint "t"
+insert into domcontest values (5);
+alter domain con drop constraint t;
+insert into domcontest values (-5); --fails
+ERROR:  value for domain con violates check constraint "con_check"
+insert into domcontest values (42);
+alter domain con drop constraint nonexistent;
+ERROR:  constraint "nonexistent" of domain "con" does not exist
+alter domain con drop constraint if exists nonexistent;
+NOTICE:  constraint "nonexistent" of domain "con" does not exist, skipping
+-- Test ALTER DOMAIN .. CONSTRAINT .. NOT VALID
+create domain things AS INT;
+CREATE TABLE thethings (stuff things);
+INSERT INTO thethings (stuff) VALUES (55);
+ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE < 11);
+ERROR:  column "stuff" of table "thethings" contains values that violate the new constraint
+ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE < 11) NOT VALID;
+ALTER DOMAIN things VALIDATE CONSTRAINT meow;
+ERROR:  column "stuff" of table "thethings" contains values that violate the new constraint
+UPDATE thethings SET stuff = 10;
+ALTER DOMAIN things VALIDATE CONSTRAINT meow;
+-- 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; -- fail
+ERROR:  domain dom does not allow null values
+alter domain dom drop not null;
+select * from domview;
+ col1 
+------
+     
+    5
+(2 rows)
+
+alter domain dom add constraint domchkgt6 check(value > 6);
+select * from domview; --fail
+ERROR:  value for domain dom violates check constraint "domchkgt6"
+alter domain dom drop constraint domchkgt6 restrict;
+select * from domview;
+ col1 
+------
+     
+    5
+(2 rows)
+
+-- cleanup
+drop domain ddef1 restrict;
+drop domain ddef2 restrict;
+drop domain ddef3 restrict;
+drop domain ddef4 restrict;
+drop domain ddef5 restrict;
+drop sequence ddef4_seq;
+-- Test domains over domains
+create domain vchar4 varchar(4);
+create domain dinter vchar4 check (substring(VALUE, 1, 1) = 'x');
+create domain dtop dinter check (substring(VALUE, 2, 1) = '1');
+select 'x123'::dtop;
+ dtop 
+------
+ x123
+(1 row)
+
+select 'x1234'::dtop; -- explicit coercion should truncate
+ dtop 
+------
+ x123
+(1 row)
+
+select 'y1234'::dtop; -- fail
+ERROR:  value for domain dtop violates check constraint "dinter_check"
+select 'y123'::dtop; -- fail
+ERROR:  value for domain dtop violates check constraint "dinter_check"
+select 'yz23'::dtop; -- fail
+ERROR:  value for domain dtop violates check constraint "dinter_check"
+select 'xz23'::dtop; -- fail
+ERROR:  value for domain dtop violates check constraint "dtop_check"
+create temp table dtest(f1 dtop);
+insert into dtest values('x123');
+insert into dtest values('x1234'); -- fail, implicit coercion
+ERROR:  value too long for type character varying(4)
+insert into dtest values('y1234'); -- fail, implicit coercion
+ERROR:  value too long for type character varying(4)
+insert into dtest values('y123'); -- fail
+ERROR:  value for domain dtop violates check constraint "dinter_check"
+insert into dtest values('yz23'); -- fail
+ERROR:  value for domain dtop violates check constraint "dinter_check"
+insert into dtest values('xz23'); -- fail
+ERROR:  value for domain dtop violates check constraint "dtop_check"
+drop table dtest;
+drop domain vchar4 cascade;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to type dinter
+drop cascades to type dtop
+-- Make sure that constraints of newly-added domain columns are
+-- enforced correctly, even if there's no default value for the new
+-- column. Per bug #1433
+create domain str_domain as text not null;
+create table domain_test (a int, b int);
+insert into domain_test values (1, 2);
+insert into domain_test values (1, 2);
+-- should fail
+alter table domain_test add column c str_domain;
+ERROR:  domain str_domain does not allow null values
+create domain str_domain2 as text check (value <> 'foo') default 'foo';
+-- should fail
+alter table domain_test add column d str_domain2;
+ERROR:  value for domain str_domain2 violates check constraint "str_domain2_check"
+-- Check that domain constraints on prepared statement parameters of
+-- unknown type are enforced correctly.
+create domain pos_int as int4 check (value > 0) not null;
+prepare s1 as select $1::pos_int = 10 as "is_ten";
+execute s1(10);
+ is_ten 
+--------
+ t
+(1 row)
+
+execute s1(0); -- should fail
+ERROR:  value for domain pos_int violates check constraint "pos_int_check"
+execute s1(NULL); -- should fail
+ERROR:  domain pos_int does not allow null values
+-- Check that domain constraints on plpgsql function parameters, results,
+-- and local variables are enforced correctly.
+create function doubledecrement(p1 pos_int) returns pos_int as $$
+declare v pos_int;
+begin
+    return p1;
+end$$ language plpgsql;
+select doubledecrement(3); -- fail because of implicit null assignment
+ERROR:  domain pos_int does not allow null values
+CONTEXT:  PL/pgSQL function doubledecrement(pos_int) line 2 during statement block local variable initialization
+create or replace function doubledecrement(p1 pos_int) returns pos_int as $$
+declare v pos_int := 0;
+begin
+    return p1;
+end$$ language plpgsql;
+select doubledecrement(3); -- fail at initialization assignment
+ERROR:  value for domain pos_int violates check constraint "pos_int_check"
+CONTEXT:  PL/pgSQL function doubledecrement(pos_int) line 2 during statement block local variable initialization
+create or replace function doubledecrement(p1 pos_int) returns pos_int as $$
+declare v pos_int := 1;
+begin
+    v := p1 - 1;
+    return v - 1;
+end$$ language plpgsql;
+select doubledecrement(null); -- fail before call
+ERROR:  domain pos_int does not allow null values
+select doubledecrement(0); -- fail before call
+ERROR:  value for domain pos_int violates check constraint "pos_int_check"
+select doubledecrement(1); -- fail at assignment to v
+ERROR:  value for domain pos_int violates check constraint "pos_int_check"
+CONTEXT:  PL/pgSQL function doubledecrement(pos_int) line 4 at assignment
+select doubledecrement(2); -- fail at return
+ERROR:  value for domain pos_int violates check constraint "pos_int_check"
+CONTEXT:  PL/pgSQL function doubledecrement(pos_int) while casting return value to function's return type
+select doubledecrement(3); -- good
+ doubledecrement 
+-----------------
+               1
+(1 row)
+
+-- Check that ALTER DOMAIN tests columns of derived types
+create domain posint as int4;
+-- Currently, this doesn't work for composite types, but verify it complains
+create type ddtest1 as (f1 posint);
+create table ddtest2(f1 ddtest1);
+insert into ddtest2 values(row(-1));
+alter domain posint add constraint c1 check(value >= 0);
+ERROR:  cannot alter type "posint" because column "ddtest2.f1" uses it
+drop table ddtest2;
+-- Likewise for domains within arrays of composite
+create table ddtest2(f1 ddtest1[]);
+insert into ddtest2 values('{(-1)}');
+alter domain posint add constraint c1 check(value >= 0);
+ERROR:  cannot alter type "posint" because column "ddtest2.f1" uses it
+drop table ddtest2;
+-- Likewise for domains within domains over composite
+create domain ddtest1d as ddtest1;
+create table ddtest2(f1 ddtest1d);
+insert into ddtest2 values('(-1)');
+alter domain posint add constraint c1 check(value >= 0);
+ERROR:  cannot alter type "posint" because column "ddtest2.f1" uses it
+drop table ddtest2;
+drop domain ddtest1d;
+-- Likewise for domains within domains over array of composite
+create domain ddtest1d as ddtest1[];
+create table ddtest2(f1 ddtest1d);
+insert into ddtest2 values('{(-1)}');
+alter domain posint add constraint c1 check(value >= 0);
+ERROR:  cannot alter type "posint" because column "ddtest2.f1" uses it
+drop table ddtest2;
+drop domain ddtest1d;
+-- Doesn't work for ranges, either
+create type rposint as range (subtype = posint);
+create table ddtest2(f1 rposint);
+insert into ddtest2 values('(-1,3]');
+alter domain posint add constraint c1 check(value >= 0);
+ERROR:  cannot alter type "posint" because column "ddtest2.f1" uses it
+drop table ddtest2;
+drop type rposint;
+alter domain posint add constraint c1 check(value >= 0);
+create domain posint2 as posint check (value % 2 = 0);
+create table ddtest2(f1 posint2);
+insert into ddtest2 values(11); -- fail
+ERROR:  value for domain posint2 violates check constraint "posint2_check"
+insert into ddtest2 values(-2); -- fail
+ERROR:  value for domain posint2 violates check constraint "c1"
+insert into ddtest2 values(2);
+alter domain posint add constraint c2 check(value >= 10); -- fail
+ERROR:  column "f1" of table "ddtest2" contains values that violate the new constraint
+alter domain posint add constraint c2 check(value > 0); -- OK
+drop table ddtest2;
+drop type ddtest1;
+drop domain posint cascade;
+NOTICE:  drop cascades to type posint2
+--
+-- Check enforcement of domain-related typmod in plpgsql (bug #5717)
+--
+create or replace function array_elem_check(numeric) returns numeric as $$
+declare
+  x numeric(4,2)[1];
+begin
+  x[1] := $1;
+  return x[1];
+end$$ language plpgsql;
+select array_elem_check(121.00);
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 4, scale 2 must round to an absolute value less than 10^2.
+CONTEXT:  PL/pgSQL function array_elem_check(numeric) line 5 at assignment
+select array_elem_check(1.23456);
+ array_elem_check 
+------------------
+             1.23
+(1 row)
+
+create domain mynums as numeric(4,2)[1];
+create or replace function array_elem_check(numeric) returns numeric as $$
+declare
+  x mynums;
+begin
+  x[1] := $1;
+  return x[1];
+end$$ language plpgsql;
+select array_elem_check(121.00);
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 4, scale 2 must round to an absolute value less than 10^2.
+CONTEXT:  PL/pgSQL function array_elem_check(numeric) line 5 at assignment
+select array_elem_check(1.23456);
+ array_elem_check 
+------------------
+             1.23
+(1 row)
+
+create domain mynums2 as mynums;
+create or replace function array_elem_check(numeric) returns numeric as $$
+declare
+  x mynums2;
+begin
+  x[1] := $1;
+  return x[1];
+end$$ language plpgsql;
+select array_elem_check(121.00);
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 4, scale 2 must round to an absolute value less than 10^2.
+CONTEXT:  PL/pgSQL function array_elem_check(numeric) line 5 at assignment
+select array_elem_check(1.23456);
+ array_elem_check 
+------------------
+             1.23
+(1 row)
+
+drop function array_elem_check(numeric);
+--
+-- Check enforcement of array-level domain constraints
+--
+create domain orderedpair as int[2] check (value[1] < value[2]);
+select array[1,2]::orderedpair;
+ array 
+-------
+ {1,2}
+(1 row)
+
+select array[2,1]::orderedpair;  -- fail
+ERROR:  value for domain orderedpair violates check constraint "orderedpair_check"
+create temp table op (f1 orderedpair);
+insert into op values (array[1,2]);
+insert into op values (array[2,1]);  -- fail
+ERROR:  value for domain orderedpair violates check constraint "orderedpair_check"
+update op set f1[2] = 3;
+update op set f1[2] = 0;  -- fail
+ERROR:  value for domain orderedpair violates check constraint "orderedpair_check"
+select * from op;
+  f1   
+-------
+ {1,3}
+(1 row)
+
+create or replace function array_elem_check(int) returns int as $$
+declare
+  x orderedpair := '{1,2}';
+begin
+  x[2] := $1;
+  return x[2];
+end$$ language plpgsql;
+select array_elem_check(3);
+ array_elem_check 
+------------------
+                3
+(1 row)
+
+select array_elem_check(-1);
+ERROR:  value for domain orderedpair violates check constraint "orderedpair_check"
+CONTEXT:  PL/pgSQL function array_elem_check(integer) line 5 at assignment
+drop function array_elem_check(int);
+--
+-- Check enforcement of changing constraints in plpgsql
+--
+create domain di as int;
+create function dom_check(int) returns di as $$
+declare d di;
+begin
+  d := $1::di;
+  return d;
+end
+$$ language plpgsql immutable;
+select dom_check(0);
+ dom_check 
+-----------
+         0
+(1 row)
+
+alter domain di add constraint pos check (value > 0);
+select dom_check(0); -- fail
+ERROR:  value for domain di violates check constraint "pos"
+CONTEXT:  PL/pgSQL function dom_check(integer) line 4 at assignment
+alter domain di drop constraint pos;
+select dom_check(0);
+ dom_check 
+-----------
+         0
+(1 row)
+
+-- implicit cast during assignment is a separate code path, test that too
+create or replace function dom_check(int) returns di as $$
+declare d di;
+begin
+  d := $1;
+  return d;
+end
+$$ language plpgsql immutable;
+select dom_check(0);
+ dom_check 
+-----------
+         0
+(1 row)
+
+alter domain di add constraint pos check (value > 0);
+select dom_check(0); -- fail
+ERROR:  value for domain di violates check constraint "pos"
+CONTEXT:  PL/pgSQL function dom_check(integer) line 4 at assignment
+alter domain di drop constraint pos;
+select dom_check(0);
+ dom_check 
+-----------
+         0
+(1 row)
+
+drop function dom_check(int);
+drop domain di;
+--
+-- Check use of a (non-inline-able) SQL function in a domain constraint;
+-- this has caused issues in the past
+--
+create function sql_is_distinct_from(anyelement, anyelement)
+returns boolean language sql
+as 'select $1 is distinct from $2 limit 1';
+create domain inotnull int
+  check (sql_is_distinct_from(value, null));
+select 1::inotnull;
+ inotnull 
+----------
+        1
+(1 row)
+
+select null::inotnull;
+ERROR:  value for domain inotnull violates check constraint "inotnull_check"
+create table dom_table (x inotnull);
+insert into dom_table values ('1');
+insert into dom_table values (1);
+insert into dom_table values (null);
+ERROR:  value for domain inotnull violates check constraint "inotnull_check"
+drop table dom_table;
+drop domain inotnull;
+drop function sql_is_distinct_from(anyelement, anyelement);
+--
+-- Renaming
+--
+create domain testdomain1 as int;
+alter domain testdomain1 rename to testdomain2;
+alter type testdomain2 rename to testdomain3;  -- alter type also works
+drop domain testdomain3;
+--
+-- Renaming domain constraints
+--
+create domain testdomain1 as int constraint unsigned check (value > 0);
+alter domain testdomain1 rename constraint unsigned to unsigned_foo;
+alter domain testdomain1 drop constraint unsigned_foo;
+drop domain testdomain1;