Re: ERROR: failed to find conversion function from key_vals_nn to record[]

> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>
>> bryn(at)yugabyte(dot)com wrote:
>>
>> I’ve copied a self-contained testcase below. Is the error that the "as intended" test causes due to a known limitation—or even a semantic dilemma that I'm failing to spot? Or might it be due to a bug?
>
> I read the note in create domain as basically “don’t do this” (the not null part) but the issue you are pointing out seems unrelated to that.
>
>> /*
>> This one cases the error, thus:
>>
>> ERROR: failed to find conversion function from key_vals_nn to record[]
>> CONTEXT: SQL expression "(kv1_nn = any(kvs_nn))"
>> */;
>> select f('as intended');
>
> The fact that a domain over an array isn’t being seen as an array here seems like a bug. POLA violation at least, and I don’t recall any notes regarding this dynamic in the docs.
>
> However, a more trivial case does work, at least in HEAD:
>
> create domain mytext as text[] not null;
> select '1' = any(array['1','2']::mytext);
> ?column?
> ----------
> t
>
> However, as you show:
>
> create type kv AS ( key text, val text );
> create domain kvarr as kv[];
> select ('1','one')::kv = any (array[('1','one')::kv]);
> ?column?
> ----------
> t
> (1 row)
>
> select ('1','one')::kv = any ((array[('1','one')::kv])::kvarr);
> ERROR: failed to find conversion function from kvarr to record[]
>
> So the interaction of a composite type and the domain over array seems to be the scope of the issue - which makes me thing bug even more.

Thanks for the quick response, David. I'll use my workaround for now. And I'll look out for news about a possible fix.

About this from the "create domain" doc:

«
Best practice therefore is to design a domain's constraints so that a null value is allowed, and then to apply column NOT NULL constraints to columns of the domain type as needed, rather than directly to the domain type.
»

What an enormous disappointment. It defeats a large part of what I wanted to adopt as a practice. As I understand that “don’t do this” caution, and the discussion that surrounds it, the advice applies only to the case that a domain with a not null constraint is used as the data type of a column in a table. I tried this variant on what the doc has:

create domain text_nn as text not null;
create table t1(k int primary key, v text not null);
insert into t1(k, v) values(1, 'x');

-- Causes:
-- null value in column "v" of relation "t1" violates not-null constraint
insert into t1(k, v) values(2, (select 'y'::text_nn where false));

Right, the subquery evaluates to "null". Then failure comes, as is intended, when the attempt is made to assign "null" to "t.v" in the to-be-inserted row.

Then I repeated the test like this:

create table t2(k int primary key, v text_nn);
insert into t2(k, v) values(1, 'x');
insert into t2(k, v) values(2, (select 'y'::text_nn where false));

\pset null '<null>'
select
k,
v,
pg_typeof(v) as "pg_typeof(v)"
from t2;

No error—and this result:

k | v | pg_typeof(v)
---+--------+--------------
1 | x | text_nn
2 | <null> | text_nn

This is what the doc promises. But how can you see it as anything but a bug? The subquery evaluates to "null", and only then is the attempt made to create a new row which self-evidently violates the domain's constraint. How is it any different from this:

insert into t2(k, v) values(1, null);

This obligingly causes "domain text_nn does not allow null values".