Pgsql 使用 AddOrUpdate ExecutePgCopy 生成的Sql错误

[januswx]

问题描述及重现代码：

using FreeSql.DataAnnotations;
using FreeSql.Internal;
using FreeSql.PostgreSQL;


const string connectionString = "Host=xxxx;Port=5431;Username=postgres;Password=postgres;Database=xxxx;ArrayNullabilityMode=Always;Pooling=true;Maximum Pool Size=100;Minimum Pool Size=1;";

var fsql = new FreeSql.FreeSqlBuilder()
            .UseConnectionString(FreeSql.DataType.PostgreSQL, connectionString)
            .UseAdoConnectionPool(true)
            .UseAutoSyncStructure(true)
            .UseMappingPriority(MappingPriorityType.Attribute, MappingPriorityType.FluentApi, MappingPriorityType.Aop)
            .Build();

if (fsql is IPostgreSQLProviderOptions pgsqlProviderOptions)
{
    pgsqlProviderOptions.UseMergeInto = true;
}

fsql.Aop.CommandAfter += (s, e) =>
{
    if (e.Exception != null)
    {
        //做一些日志记录的操作。以下为示例。
        Console.WriteLine($"{DateTime.Now:yyyy-MM-dd HH:mm:ss} Message:{e.Exception.Message}\r\nStackTrace:{e.Exception.StackTrace}\r\nCommandText:{e.Command.CommandText}");
    }
    else
    {
        Console.WriteLine($"{DateTime.Now:yyyy-MM-dd HH:mm:ss} CommandText:{e.Command.CommandText}");
    }
};
var list = new List<TestDemo>()
{
    new TestDemo{Id = 1,Name = "name1"},
    new TestDemo{Id = 2,Name = "name2"},
};

fsql.InsertOrUpdate<TestDemo>().SetSource(list).ExecutePgCopy();

list = new List<TestDemo>()
{
    new TestDemo{Id = 1,Name = "name3"},
    new TestDemo{Id = 2,Name = "name4"},
};

fsql.InsertOrUpdate<TestDemo>().SetSource(list).ExecutePgCopy();


[Table(Name = "demo")]
public class TestDemo
{
    [Column(Name = "id", IsPrimary = true)]
    public long Id { get; set; }

    [Column(Name = "name")]
    public string Name { get; set; }
}

数据库版本

PostgreSQL 16.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit

安装的Nuget包

.net framework/. net core？ 及具体版本

.Net8

错误信息如下：

2025-12-09 09:41:43 CommandText: select 1 from pg_tables a inner join pg_namespace b on b.nspname = a.schemaname where b.nspname || '.' || a.tablename = 'public.demo' 2025-12-09 09:41:43 CommandText: select a.attname, t.typname, case when a.atttypmod > 0 and a.atttypmod < 32767 then a.atttypmod - 4 else a.attlen end len, case when t.typelem > 0 and t.typinput::varchar = 'array_in' then t2.typname else t.typname end, case when a.attnotnull then '0' else '1' end as is_nullable, --e.adsrc, (select pg_get_expr(adbin, adrelid) from pg_attrdef where adrelid = e.adrelid limit 1) is_identity, a.attndims, d.description as comment, a.attidentity from pg_class c inner join pg_attribute a on a.attnum > 0 and a.attrelid = c.oid inner join pg_type t on t.oid = a.atttypid left join pg_type t2 on t2.oid = t.typelem left join pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum left join pg_attrdef e on e.adrelid = a.attrelid and e.adnum = a.attnum inner join pg_namespace ns on ns.oid = c.relnamespace inner join pg_namespace ns2 on ns2.oid = t.typnamespace where ns.nspname = 'public' and c.relname = 'demo' 2025-12-09 09:41:43 CommandText: select c.attname, b.relname, case when pg_index_column_has_property(b.oid, c.attnum, 'desc') = 't' then 1 else 0 end IsDesc, case when indisunique = 't' then 1 else 0 end IsUnique from pg_index a inner join pg_class b on b.oid = a.indexrelid inner join pg_attribute c on c.attnum > 0 and c.attrelid = b.oid inner join pg_namespace ns on ns.oid = b.relnamespace inner join pg_class d on d.oid = a.indrelid where ns.nspname in ('public') and d.relname in ('demo') and a.indisprimary = 'f' 2025-12-09 09:41:43 CommandText: select d.description from pg_class a inner join pg_namespace b on b.oid = a.relnamespace left join pg_description d on d.objoid = a.oid and objsubid = 0 where b.nspname not in ('pg_catalog', 'information_schema') and a.relkind in ('r') and b.nspname = 'public' and a.relname = 'demo' and b.nspname || '.' || a.relname not in ('public.geography_columns','public.geometry_columns','public.raster_columns','public.raster_overviews') 2025-12-09 09:41:43 CommandText:CREATE TEMP TABLE "temp_92cca2f1b89240c6869b33150883bc3f" ( "id" INT8, "name" VARCHAR(255) ) WITH (OIDS=FALSE); 2025-12-09 09:41:43 Message:42601: syntax error at or near "t1" StackTrace: at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|201_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage) at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken) at Npgsql.NpgsqlDataReader.NextResult() at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteNonQuery() at FreeSql.Internal.CommonProvider.AdoProvider.ExecuteNonQuery(DbConnection connection, DbTransaction transaction, CommandType cmdType, String cmdText, Int32 cmdTimeout, Action`1 cmdAfterHandler, DbParameter[] cmdParms) CommandText:MERGE INTO "demo" t1 USING (select ERGE INTO "demo" t1 USING ( from temp_92cca2f1b89240c6869b33150883bc3f ) t2 ON (t1."id" = t2."id") WHEN MATCHED THEN update set "name" = t2."name" WHEN NOT MATCHED THEN insert ("id", "name") values (t2."id", t2."name");; DROP TABLE "temp_92cca2f1b89240c6869b33150883bc3f"

看问题应该是生成的 sql 语法报错 正确的应该是： MERGE INTO "demo" t1 USING ( SELECT id, name FROM temp_a3274dadfb02460b8751bc8d90eed1a0 ) t2 ON (t1."id" = t2."id") WHEN MATCHED THEN UPDATE SET "name" = t2."name" WHEN NOT MATCHED THEN INSERT ("id", "name") VALUES (t2."id", t2."name");