Query produces "function unnest(unknown) is not unique error", but is executed fine by the application

Question

I was playing around with composite types in Postgres and Npgsql, and noticed interesting behaviour.

I have a UDT in the db created as follows:

CREATE TYPE ids_test AS (first int, second int);

In addition, there is a table 'test', which mirrors UDT: two int columns, nothing else.

I also have a corresponding C# class 'test_ids' and map it to this UDT. The query is pretty simple:

select *
from test 
join (select * from unnest(@idpairs)) as pairs
on pairs.first = test.firstid and pairs.second = test.secondid

The parameter @idpairs is passed using dynamic params:

    var dynamicParameters = new DynamicParameters();
    dynamicParameters.Add("@idpairs", ids.Select(_ => new test_ids { first = _.f, second = _.s }).ToList());

    var npgsqlConnection1 = new NpgsqlConnection(conStr);
    npgsqlConnection1.Open();
    npgsqlConnection1.TypeMapper.MapComposite<test_ids>("ids_test");

    using (npgsqlConnection1)
    {
        var res = (await npgsqlConnection1.QueryAsync(query, dynamicParameters)).AsList();
    }

Now, here's the part I don't understand. When I grab the query from csv log, I have the query and its params:

execute : select * from test join (select * from unnest('{"(1,1)","(2,2)","(3,3)"}')) as pairs on pairs.first = test.firstid and pairs.second = test.secondid

parameters: $1 = '{"(1,1)","(2,2)","(3,3)"}'

However, when I inline the param and try to execute the query, I get the 'function unnest(unknown) is not unique' error. Inline query:

select *
from test
join (select * from unnest('{"(1,1)","(2,2)","(3,3)"}')) as pairs
on pairs.first = test.firstid and pairs.second = test.secondid

As far as I understand, the inline query fails because Postgres cannot determine how to interpret the string and requires a cast. However, I don't get why the query from the app succeeds.

Furthermore, even if I add an explicit cast, the result of unnest does not contain column names. The question is, what does the app do to make this query work correctly?

Answer 1

You have an array of composite types. You need to cast the string to an array of that type.

select *
from test
join unnest('{"(1,1)","(2,2)","(3,3)"}'::ids_test[]) as pairs
on pairs.first = test.firstid and pairs.second = test.secondid

db<>fiddle

The log is not a completely accurate representation of what's going on. Parameters are bound as placeholders, they are not injected directly into the query. So the log is just an approximation of that.