Select in with uuid array not working in Postgresql

[jhuggart]

I'm running a query similar to the following:

string sql = @"select * from table where table.token in (@tokens)";

using (var connection = connection_factory.OpenDbConnection())
{
    return connection
      .Query<Dto>(sql, new { tokens = new[] { Guid.Parse("d767cfb5-ad1f-4402-8147-db884873e9fe") });
}

It produces this sql:

select * from table
where table.token in ((('{"d767cfb5-ad1f-4402-8147-db884873e9fe"}')::uuid[]))

Which throws this error:

ERROR:  operator does not exist: uuid = uuid[] at character 164
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Am I doing something explicitly wrong? Is there a workaround for in statements containing uuids?

[ceeji]

In fact, Postgresql dose not receive ARRAY type when you use IN keyword.

You can use this: (ANY statement does receive array)

table.token = ANY(@tokens)

Or you can use ToString() of Guid type, like this:

var tokens = (new[] { Guid.Parse("d767cfb5-ad1f-4402-8147-db884873e9fe") }).Select(x => x.ToString());

var tokenInString = “‘“ + tokens.Join(“‘,'”, tokens) + “'";

return connection.Query<Dto>(@"select * from table where table.token in (“ + tokenInString + “)”);

在 2015年7月14日，上午12:06，jhuggart [email protected] 写道：

I'm running a query similar to the following:

string sql = @"select * from table where table.token in (@tokens)";

using (var connection = connection_factory.OpenDbConnection()) { return connection .Query<Dto>(sql, new { tokens = new[] { Guid.Parse("d767cfb5-ad1f-4402-8147-db884873e9fe") }); } It produces this sql:

select * from table where table.token in ((('{"d767cfb5-ad1f-4402-8147-db884873e9fe"}')::uuid[])) Which throws this error:

ERROR: operator does not exist: uuid = uuid[] at character 164 HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. Am I doing something explicitly wrong? Is there a workaround for in statements containing uuids?

— Reply to this email directly or view it on GitHub https://github.com/StackExchange/dapper-dot-net/issues/315.

[NickCraver]

So I know what to test, is this using the v2 or v3 npg driver? I realize we'll need to look at syntax changes based on provider here (always fun!), but I'm assuming v3 is what we're test against going forward...and want to confirm.

[jhuggart]

@NickCraver I am using 3.0.3

[danielmahadi]

@NickCraver I can reproduce this issue in 3.1.9

[arturosevilla]

@NickCraver I'm hitting the same with Npgsql 3.1.9 and Dapper 1.50.2 in .NET Core. The only solution was to use the column = ANY(@array) syntax, which could be better in terms of query caching than generating a different query depending on the number of parameters and I guess indexes still work under these scenarios.

[tuannguyendotme]

@NickCraver I have same problem with Npgsql 4.0.6 and Dapper 1.60.6 in .NET Core.

[pouryadel]

I still have the same problem Dapper 2.0.30 .Net 6