Npgsql.PostgresException: 42804:

[ricoisme]

hi 我使用NET8.0 , sqlSugarCore 5.1.4.152 Postgresql table 如下 CREATE TABLE "salesorder" ( "id" VARCHAR(20) NOT NULL, "tobeemailed" BOOLEAN NULL DEFAULT NULL, "tobefaxed" BOOLEAN NULL DEFAULT NULL, "tobeprinted" BOOLEAN NULL DEFAULT NULL, "total" NUMERIC NULL DEFAULT NULL, "totalcostestimate" NUMERIC NULL DEFAULT NULL, "orderstatus" VARCHAR(30) NULL DEFAULT 'NULL::character varying', "trandate" TIMESTAMPTZ NULL DEFAULT NULL, "tranid" VARCHAR(30) NULL DEFAULT 'NULL::character varying', "webstore" VARCHAR(2) NULL DEFAULT 'NULL::character varying', "exchangerate" NUMERIC NULL DEFAULT NULL, "discounttotal" NUMERIC NULL DEFAULT NULL, "currency" VARCHAR(5) NULL DEFAULT 'NULL::character varying', "billaddress" VARCHAR(200) NULL DEFAULT 'NULL::character varying', "prevdate" TIMESTAMPTZ NULL DEFAULT NULL, "nextbill" TIMESTAMPTZ NULL DEFAULT NULL, "shipaddress" VARCHAR(200) NULL DEFAULT 'NULL::character varying', "subtotal" NUMERIC(13,3) NULL DEFAULT 'NULL::numeric', "shipcomplete" BOOLEAN NULL DEFAULT NULL, "shipdate" TIMESTAMPTZ NULL DEFAULT NULL, "shipisresidential" BOOLEAN NULL DEFAULT NULL, "shipoverride" BOOLEAN NULL DEFAULT NULL, "saleseffectivedate" TIMESTAMPTZ NULL DEFAULT NULL, "createddate" TIMESTAMPTZ NULL DEFAULT NULL, "lastmodifieddate" TIMESTAMPTZ NULL DEFAULT NULL, PRIMARY KEY ("id") ) ; 我使用dictionary操作insert 會發生資料型別錯誤，程式碼如下 DataRow dr = dt.NewRow(); dr["Id"] = "3356"; dr["ToBeEmailed"] = false; dr["ToBeFaxed"] = false; dr["ToBePrinted"] = false; dr["Total"] = 105.0; dr["TotalCostEstimate"] = 0.0; dr["OrderStatus"] = "B"; dr["TranDate"] = "2023-07-25 00:00:00"; dr["TranId"] = "SO00000001"; dr["WebStore"] = "F"; dr["exchangerate"] = 1.0; dr["DiscountTotal"] = 0.0; dr["Currency"] = "TWD"; dr["BillAddress"] = @"test address"; dr["PrevDate"] = "2023-07-25 00:00:00"; dr["NextBill"] = "2023-07-25 00:00:00"; dr["ShipAddress"] = @"test addressn"; dr["Subtotal"] = 100.0; dr["ShipComplete"] = false; dr["ShipDate"] = "2023-07-25 00:00:00"; ; dr["ShipIsResidential"] = false; dr["ShipOverride"] = false; dr["SalesEffectiveDate"] = "2023-07-25 00:00:00"; dr["CreatedDate"] = "2023-07-25 10:20:00"; dr["LastModifiedDate"] = "2023-07-25 10:20:00"; dt.Rows.Add(dr); var dcs = db.Context.Utilities.DataTableToDictionaryList(dt);

var total = db.Context.Insertable(dcs).AS("salesorder") .ExecuteCommand(); 請問我有遺漏什麼嗎?還是寫法有錯?

謝謝

[DotNetNext]

我执行成功了 建表语句报错了我将 "subtotal" NUMERIC(13,3) NULL DEFAULT 'NULL::numeric', 改成了 "subtotal" NUMERIC(13,3) NULL ',

[DotNetNext]

下面是代码

var dt = db.Ado.GetDataTable("select * from salesorder where 1=2");//从数据库获取dt结构
DataRow dr = dt.NewRow();
dr["Id"] = "3356";
dr["ToBeEmailed"] = false;
dr["ToBeFaxed"] = false;
dr["ToBePrinted"] = false;
dr["Total"] = 105.0;
dr["TotalCostEstimate"] = 0.0;
dr["OrderStatus"] = "B";
dr["TranDate"] = "2023-07-25 00:00:00";
dr["TranId"] = "SO00000001";
dr["WebStore"] = "F";
dr["exchangerate"] = 1.0;
dr["DiscountTotal"] = 0.0;
dr["Currency"] = "TWD";
dr["BillAddress"] = @"test address";
dr["PrevDate"] = "2023-07-25 00:00:00";
dr["NextBill"] = "2023-07-25 00:00:00";
dr["ShipAddress"] = @"test addressn";
dr["Subtotal"] = 100.0;
dr["ShipComplete"] = false;
dr["ShipDate"] = "2023-07-25 00:00:00"; ;
dr["ShipIsResidential"] = false;
dr["ShipOverride"] = false;
dr["SalesEffectiveDate"] = "2023-07-25 00:00:00";
dr["CreatedDate"] = "2023-07-25 10:20:00";
dr["LastModifiedDate"] = "2023-07-25 10:20:00";
dt.Rows.Add(dr);
var dcs = db.Context.Utilities.DataTableToDictionaryList(dt);

var total = db.Context.Insertable(dcs).AS("salesorder")
.ExecuteCommand();

[ricoisme]

請問有需要設定如下兩行嗎? AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true); AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true);

請問你使用postgresql資料庫是container嗎? 我在確認看看 感謝回答

[DotNetNext]

源码中默认会执行上面的2行代码

            if (StaticConfig.AppContext_ConvertInfinityDateTime == false)
            {
                AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
                AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true);
            }

[DotNetNext]

设置为true可以禁用 StaticConfig.AppContext_ConvertInfinityDateTime=true

[DotNetNext]

demo.zip 这个是我的DEMO

[DotNetNext]

DEMO是可以跑的

[DotNetNext]

我是装在docker里面的PgSQL

[ricoisme]

再請教一下 var config = new ConnectionConfig { ConfigId = configID, IsAutoCloseConnection = true, DbType = DbType.PostgreSQL, ConnectionString = connectionString, LanguageType = LanguageType.English, }; LanguageType這屬性，會影響資料庫存取嗎? 謝謝

[DotNetNext]

不会，只是错误的提示

[ricoisme]

感謝解答，錯誤主要是個人手動建立datatable造成，我後來改用_sqlSugarClient.Ado.GetDataTable取資料表結構，就正常了，後面我也沒深入手動建立datatable為什麼會錯，再次感謝你