summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/domain.out
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--src/test/regress/expected/domain.out1209
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;
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-13 16:52:38 +0000