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.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");