Use case-insensitive comparison for primary key detection in pgsql driver

Co-authored-by: gqcn <26347176+gqcn@users.noreply.github.com>

contrib/drivers/README.MD

@@ -57,7 +57,7 @@ import _ "github.com/gogf/gf/contrib/drivers/sqlite/v2"
 
 #### cgo version
 
-When the target is a `32-bit` Windows system, the `cgo` version needs to be used.
+When the target is a 32-bit Windows system, the cgo version needs to be used.
 
 ```go
 import _ "github.com/gogf/gf/contrib/drivers/sqlitecgo/v2"
@@ -77,10 +77,8 @@ import _ "github.com/gogf/gf/contrib/drivers/mssql/v2"
 
 Note:
 
-- `InsertIgnore` returns error if there is no primary key or unique index submitted with record.
 - It supports server version >= `SQL Server2005`
-- It ONLY supports `datetime2` and `datetimeoffset` types for auto handling created_at/updated_at/deleted_at columns, 
-  because datetime type does not support microseconds precision when column value is passed as string.
+- It ONLY supports datetime2 and datetimeoffset types for auto handling created_at/updated_at/deleted_at columns, because datetime type does not support microseconds precision when column value is passed as string.
 
 ### Oracle
 
@@ -90,8 +88,8 @@ import _ "github.com/gogf/gf/contrib/drivers/oracle/v2"
 
 Note:
 
+- It does not support `Replace` features.
 - It does not support `LastInsertId`.
-- `InsertIgnore` returns error if there is no primary key or unique index submitted with record.
 
 ### ClickHouse
 
@@ -101,7 +99,7 @@ import _ "github.com/gogf/gf/contrib/drivers/clickhouse/v2"
 
 Note:
 
-- It does not support `InsertIgnore/InsertAndGetId` features.
+- It does not support `InsertIgnore/InsertGetId` features.
 - It does not support `Save/Replace` features.
 - It does not support `Transaction` feature.
 - It does not support `RowsAffected` feature.
@@ -112,10 +110,6 @@ Note:
 import _ "github.com/gogf/gf/contrib/drivers/dm/v2"
 ```
 
-Note:
-
-- `InsertIgnore` returns error if there is no primary key or unique index submitted with record.
-
 ## Custom Drivers
 
 It's quick and easy, please refer to current driver source.

contrib/drivers/dm/dm_z_unit_basic_test.go

@@ -602,38 +602,31 @@ func Test_Model_InsertIgnore(t *testing.T) {
 	// db.SetDebug(true)
 
 	gtest.C(t, func(t *gtest.T) {
-		data := g.Map{
-			"id":           1,
-			"account_name": fmt.Sprintf(`name_%d`, 777),
-			"pwd_reset":    0,
-			"attr_index":   777,
-			"created_time": gtime.Now(),
+		data := User{
+			ID:          int64(666),
+			AccountName: fmt.Sprintf(`name_%d`, 666),
+			PwdReset:    0,
+			AttrIndex:   99,
+			CreatedTime: time.Now(),
+			UpdatedTime: time.Now(),
 		}
-		_, err := db.Model(table).Data(data).InsertIgnore()
+		_, err := db.Model(table).Data(data).Insert()
 		t.AssertNil(err)
-
-		one, err := db.Model(table).WherePri(1).One()
-		t.AssertNil(err)
-		t.Assert(one["ACCOUNT_NAME"].String(), "name_1")
-
-		count, err := db.Model(table).Count()
-		t.AssertNil(err)
-		t.Assert(count, TableSize)
 	})
-
 	gtest.C(t, func(t *gtest.T) {
-		data := g.Map{
-			// "id":           1,
-			"account_name": fmt.Sprintf(`name_%d`, 777),
-			"pwd_reset":    0,
-			"attr_index":   777,
-			"created_time": gtime.Now(),
+		data := User{
+			ID:          int64(666),
+			AccountName: fmt.Sprintf(`name_%d`, 777),
+			PwdReset:    0,
+			AttrIndex:   99,
+			CreatedTime: time.Now(),
+			UpdatedTime: time.Now(),
 		}
 		_, err := db.Model(table).Data(data).InsertIgnore()
-		t.AssertNE(err, nil)
-
-		count, err := db.Model(table).Count()
 		t.AssertNil(err)
-		t.Assert(count, TableSize)
+
+		one, err := db.Model(table).Where("id", 666).One()
+		t.AssertNil(err)
+		t.Assert(one["ACCOUNT_NAME"].String(), "name_666")
 	})
 }

contrib/drivers/mssql/mssql_do_insert.go

@@ -25,89 +25,67 @@ func (d *Driver) DoInsert(
 	ctx context.Context, link gdb.Link, table string, list gdb.List, option gdb.DoInsertOption,
 ) (result sql.Result, err error) {
 	switch option.InsertOption {
-	case gdb.InsertOptionSave:
+	case
+		gdb.InsertOptionSave,
+		gdb.InsertOptionReplace:
+		// MSSQL does not support REPLACE INTO syntax.
+		// Convert Replace to Save operation, using MERGE statement.
+		// Auto-detect primary keys if OnConflict is not specified.
+		if len(option.OnConflict) == 0 {
+			primaryKeys, err := d.Core.GetPrimaryKeys(ctx, table)
+			if err != nil {
+				return nil, gerror.WrapCode(
+					gcode.CodeInternalError,
+					err,
+					`failed to get primary keys for Save/Replace operation`,
+				)
+			}
+			foundPrimaryKey := false
+			for _, primaryKey := range primaryKeys {
+				for dataKey := range list[0] {
+					if strings.EqualFold(dataKey, primaryKey) {
+						foundPrimaryKey = true
+						break
+					}
+				}
+				if foundPrimaryKey {
+					break
+				}
+			}
+			if !foundPrimaryKey {
+				return nil, gerror.NewCodef(
+					gcode.CodeMissingParameter,
+					`Save/Replace operation requires conflict detection: `+
+						`either specify OnConflict() columns or ensure table '%s' has a primary key in the data`,
+					table,
+				)
+			}
+			option.OnConflict = primaryKeys
+		}
+		// Convert to Save operation
 		return d.doSave(ctx, link, table, list, option)
 
-	case gdb.InsertOptionReplace:
-		// MSSQL does not support REPLACE INTO syntax, use SAVE instead.
-		return d.doSave(ctx, link, table, list, option)
-
-	case gdb.InsertOptionIgnore:
-		// MSSQL does not support INSERT IGNORE syntax, use MERGE instead.
-		return d.doInsertIgnore(ctx, link, table, list, option)
-
 	default:
 		return d.Core.DoInsert(ctx, link, table, list, option)
 	}
 }
 
-// doSave support upsert for MSSQL
+// doSave support upsert for SQL server
 func (d *Driver) doSave(ctx context.Context,
 	link gdb.Link, table string, list gdb.List, option gdb.DoInsertOption,
 ) (result sql.Result, err error) {
-	return d.doMergeInsert(ctx, link, table, list, option, true)
-}
-
-// doInsertIgnore implements INSERT IGNORE operation using MERGE statement for MSSQL database.
-// It only inserts records when there's no conflict on primary/unique keys.
-func (d *Driver) doInsertIgnore(ctx context.Context,
-	link gdb.Link, table string, list gdb.List, option gdb.DoInsertOption,
-) (result sql.Result, err error) {
-	return d.doMergeInsert(ctx, link, table, list, option, false)
-}
-
-// doMergeInsert implements MERGE-based insert operations for MSSQL database.
-// When withUpdate is true, it performs upsert (insert or update).
-// When withUpdate is false, it performs insert ignore (insert only when no conflict).
-func (d *Driver) doMergeInsert(
-	ctx context.Context,
-	link gdb.Link, table string, list gdb.List, option gdb.DoInsertOption, withUpdate bool,
-) (result sql.Result, err error) {
-	// If OnConflict is not specified, automatically get the primary key of the table
-	conflictKeys := option.OnConflict
-	if len(conflictKeys) == 0 {
-		primaryKeys, err := d.Core.GetPrimaryKeys(ctx, table)
-		if err != nil {
-			return nil, gerror.WrapCode(
-				gcode.CodeInternalError,
-				err,
-				`failed to get primary keys for table`,
-			)
-		}
-		foundPrimaryKey := false
-		for _, primaryKey := range primaryKeys {
-			for dataKey := range list[0] {
-				if strings.EqualFold(dataKey, primaryKey) {
-					foundPrimaryKey = true
-					break
-				}
-			}
-			if foundPrimaryKey {
-				break
-			}
-		}
-		if !foundPrimaryKey {
-			return nil, gerror.NewCodef(
-				gcode.CodeMissingParameter,
-				`Replace/Save/InsertIgnore operation requires conflict detection: `+
-					`either specify OnConflict() columns or ensure table '%s' has a primary key in the data`,
-				table,
-			)
-		}
-		conflictKeys = primaryKeys
-	}
-
 	var (
 		one            = list[0]
 		oneLen         = len(one)
 		charL, charR   = d.GetChars()
+		conflictKeys   = option.OnConflict
 		conflictKeySet = gset.New(false)
 
-		// queryHolders:	Handle data with Holder that need to be merged
-		// queryValues:		Handle data that need to be merged
+		// queryHolders:	Handle data with Holder that need to be upsert
+		// queryValues:		Handle data that need to be upsert
 		// insertKeys:		Handle valid keys that need to be inserted
 		// insertValues:	Handle values that need to be inserted
-		// updateValues:	Handle values that need to be updated (only when withUpdate=true)
+		// updateValues:	Handle values that need to be updated
 		queryHolders = make([]string, oneLen)
 		queryValues  = make([]any, oneLen)
 		insertKeys   = make([]string, oneLen)
@@ -127,9 +105,9 @@ func (d *Driver) doMergeInsert(
 		insertKeys[index] = charL + key + charR
 		insertValues[index] = "T2." + charL + key + charR
 
-		// Build updateValues only when withUpdate is true
-		// Filter conflict keys and soft created fields from updateValues
-		if withUpdate && !(conflictKeySet.Contains(key) || d.Core.IsSoftCreatedFieldName(key)) {
+		// filter conflict keys in updateValues.
+		// And the key is not a soft created field.
+		if !(conflictKeySet.Contains(key) || d.Core.IsSoftCreatedFieldName(key)) {
 			updateValues = append(
 				updateValues,
 				fmt.Sprintf(`T1.%s = T2.%s`, charL+key+charR, charL+key+charR),
@@ -138,10 +116,8 @@ func (d *Driver) doMergeInsert(
 		index++
 	}
 
-	var (
-		batchResult = new(gdb.SqlResult)
-		sqlStr      = parseSqlForMerge(table, queryHolders, insertKeys, insertValues, updateValues, conflictKeys)
-	)
+	batchResult := new(gdb.SqlResult)
+	sqlStr := parseSqlForUpsert(table, queryHolders, insertKeys, insertValues, updateValues, conflictKeys)
 	r, err := d.DoExec(ctx, link, sqlStr, queryValues...)
 	if err != nil {
 		return r, err
@@ -155,48 +131,44 @@ func (d *Driver) doMergeInsert(
 	return batchResult, nil
 }
 
-// parseSqlForMerge generates MERGE statement for MSSQL database.
-// When updateValues is empty, it only inserts (INSERT IGNORE behavior).
-// When updateValues is provided, it performs upsert (INSERT or UPDATE).
-// Examples:
-// - INSERT IGNORE: MERGE INTO table T1 USING (...) T2 ON (...) WHEN NOT MATCHED THEN INSERT(...) VALUES (...)
-// - UPSERT: MERGE INTO table T1 USING (...) T2 ON (...) WHEN NOT MATCHED THEN INSERT(...) VALUES (...) WHEN MATCHED THEN UPDATE SET ...
-func parseSqlForMerge(table string,
+// parseSqlForUpsert
+// MERGE INTO {{table}} T1
+// USING ( VALUES( {{queryHolders}}) T2 ({{insertKeyStr}})
+// ON (T1.{{duplicateKey}} = T2.{{duplicateKey}} AND ...)
+// WHEN NOT MATCHED THEN
+// INSERT {{insertKeys}} VALUES {{insertValues}}
+// WHEN MATCHED THEN
+// UPDATE SET {{updateValues}}
+func parseSqlForUpsert(table string,
 	queryHolders, insertKeys, insertValues, updateValues, duplicateKey []string,
 ) (sqlStr string) {
 	var (
 		queryHolderStr  = strings.Join(queryHolders, ",")
 		insertKeyStr    = strings.Join(insertKeys, ",")
 		insertValueStr  = strings.Join(insertValues, ",")
+		updateValueStr  = strings.Join(updateValues, ",")
 		duplicateKeyStr string
+		pattern         = gstr.Trim(
+			`MERGE INTO %s T1 USING (VALUES(%s)) T2 (%s) ON (%s) WHEN NOT MATCHED ` +
+				`THEN INSERT(%s) VALUES (%s) WHEN MATCHED THEN UPDATE SET %s;`,
+		)
 	)
 
-	// Build ON condition
 	for index, keys := range duplicateKey {
 		if index != 0 {
 			duplicateKeyStr += " AND "
 		}
-		duplicateKeyStr += fmt.Sprintf("T1.%s = T2.%s", keys, keys)
+		duplicateTmp := fmt.Sprintf("T1.%s = T2.%s", keys, keys)
+		duplicateKeyStr += duplicateTmp
 	}
 
-	// Build SQL based on whether UPDATE is needed
-	pattern := gstr.Trim(
-		`MERGE INTO %s T1 USING (VALUES(%s)) T2 (%s) ON (%s) WHEN NOT MATCHED THEN INSERT(%s) VALUES (%s)`,
+	return fmt.Sprintf(pattern,
+		table,
+		queryHolderStr,
+		insertKeyStr,
+		duplicateKeyStr,
+		insertKeyStr,
+		insertValueStr,
+		updateValueStr,
 	)
-	if len(updateValues) > 0 {
-		// Upsert: INSERT or UPDATE
-		pattern += gstr.Trim(` WHEN MATCHED THEN UPDATE SET %s`)
-		return fmt.Sprintf(
-			pattern+";",
-			table,
-			queryHolderStr,
-			insertKeyStr,
-			duplicateKeyStr,
-			insertKeyStr,
-			insertValueStr,
-			strings.Join(updateValues, ","),
-		)
-	}
-	// Insert Ignore: INSERT only
-	return fmt.Sprintf(pattern+";", table, queryHolderStr, insertKeyStr, duplicateKeyStr, insertKeyStr, insertValueStr)
 }

contrib/drivers/mssql/mssql_z_unit_model_test.go

@@ -117,48 +117,6 @@ func Test_Model_Insert(t *testing.T) {
 	})
 }
 
-func Test_Model_InsertIgnore(t *testing.T) {
-	table := createInitTable()
-	defer dropTable(table)
-
-	// db.SetDebug(true)
-
-	gtest.C(t, func(t *gtest.T) {
-		data := g.Map{
-			"id":          1,
-			"passport":    fmt.Sprintf(`t%d`, 777),
-			"password":    fmt.Sprintf(`p%d`, 777),
-			"nickname":    fmt.Sprintf(`T%d`, 777),
-			"create_time": gtime.Now(),
-		}
-		_, err := db.Model(table).Data(data).InsertIgnore()
-		t.AssertNil(err)
-
-		one, err := db.Model(table).WherePri(1).One()
-		t.AssertNil(err)
-		t.Assert(one["PASSPORT"].String(), "user_1")
-
-		count, err := db.Model(table).Count()
-		t.AssertNil(err)
-		t.Assert(count, TableSize)
-	})
-
-	gtest.C(t, func(t *gtest.T) {
-		data := g.Map{
-			"passport":    fmt.Sprintf(`t%d`, 777),
-			"password":    fmt.Sprintf(`p%d`, 777),
-			"nickname":    fmt.Sprintf(`T%d`, 777),
-			"create_time": gtime.Now(),
-		}
-		_, err := db.Model(table).Data(data).InsertIgnore()
-		t.AssertNE(err, nil)
-
-		count, err := db.Model(table).Count()
-		t.AssertNil(err)
-		t.Assert(count, TableSize)
-	})
-}
-
 func Test_Model_Insert_KeyFieldNameMapping(t *testing.T) {
 	table := createTable()
 	defer dropTable(table)

contrib/drivers/oracle/oracle_do_insert.go

@@ -30,13 +30,10 @@ func (d *Driver) DoInsert(
 		return d.doSave(ctx, link, table, list, option)
 
 	case gdb.InsertOptionReplace:
-		// Oracle does not support REPLACE INTO syntax, use SAVE instead.
-		return d.doSave(ctx, link, table, list, option)
-
-	case gdb.InsertOptionIgnore:
-		// Oracle does not support INSERT IGNORE syntax, use MERGE instead.
-		return d.doInsertIgnore(ctx, link, table, list, option)
-
+		return nil, gerror.NewCode(
+			gcode.CodeNotSupported,
+			`Replace operation is not supported by oracle driver`,
+		)
 	default:
 	}
 	var (
@@ -98,66 +95,21 @@ func (d *Driver) DoInsert(
 	return batchResult, nil
 }
 
-// doSave support upsert for Oracle
+// doSave support upsert for Oracle.
 func (d *Driver) doSave(ctx context.Context,
 	link gdb.Link, table string, list gdb.List, option gdb.DoInsertOption,
 ) (result sql.Result, err error) {
-	return d.doMergeInsert(ctx, link, table, list, option, true)
-}
-
-// doInsertIgnore implements INSERT IGNORE operation using MERGE statement for Oracle database.
-// It only inserts records when there's no conflict on primary/unique keys.
-func (d *Driver) doInsertIgnore(ctx context.Context,
-	link gdb.Link, table string, list gdb.List, option gdb.DoInsertOption,
-) (result sql.Result, err error) {
-	return d.doMergeInsert(ctx, link, table, list, option, false)
-}
-
-// doMergeInsert implements MERGE-based insert operations for Oracle database.
-// When withUpdate is true, it performs upsert (insert or update).
-// When withUpdate is false, it performs insert ignore (insert only when no conflict).
-func (d *Driver) doMergeInsert(
-	ctx context.Context,
-	link gdb.Link, table string, list gdb.List, option gdb.DoInsertOption, withUpdate bool,
-) (result sql.Result, err error) {
-	// If OnConflict is not specified, automatically get the primary key of the table
-	conflictKeys := option.OnConflict
-	if len(conflictKeys) == 0 {
-		primaryKeys, err := d.Core.GetPrimaryKeys(ctx, table)
-		if err != nil {
-			return nil, gerror.WrapCode(
-				gcode.CodeInternalError,
-				err,
-				`failed to get primary keys for table`,
-			)
-		}
-		foundPrimaryKey := false
-		for _, primaryKey := range primaryKeys {
-			for dataKey := range list[0] {
-				if strings.EqualFold(dataKey, primaryKey) {
-					foundPrimaryKey = true
-					break
-				}
-			}
-			if foundPrimaryKey {
-				break
-			}
-		}
-		if !foundPrimaryKey {
-			return nil, gerror.NewCodef(
-				gcode.CodeMissingParameter,
-				`Replace/Save/InsertIgnore operation requires conflict detection: `+
-					`either specify OnConflict() columns or ensure table '%s' has a primary key in the data`,
-				table,
-			)
-		}
-		conflictKeys = primaryKeys
+	if len(option.OnConflict) == 0 {
+		return nil, gerror.NewCode(
+			gcode.CodeMissingParameter, `Please specify conflict columns`,
+		)
 	}
 
 	var (
 		one            = list[0]
 		oneLen         = len(one)
 		charL, charR   = d.GetChars()
+		conflictKeys   = option.OnConflict
 		conflictKeySet = gset.New(false)
 
 		// queryHolders:	Handle data with Holder that need to be upsert
@@ -185,9 +137,9 @@ func (d *Driver) doMergeInsert(
 		insertKeys[index] = keyWithChar
 		insertValues[index] = fmt.Sprintf("T2.%s", keyWithChar)
 
-		// Build updateValues only when withUpdate is true
-		// Filter conflict keys and soft created fields from updateValues
-		if withUpdate && !(conflictKeySet.Contains(key) || d.Core.IsSoftCreatedFieldName(key)) {
+		// filter conflict keys in updateValues.
+		// And the key is not a soft created field.
+		if !(conflictKeySet.Contains(key) || d.Core.IsSoftCreatedFieldName(key)) {
 			updateValues = append(
 				updateValues,
 				fmt.Sprintf(`T1.%s = T2.%s`, keyWithChar, keyWithChar),
@@ -196,10 +148,8 @@ func (d *Driver) doMergeInsert(
 		index++
 	}
 
-	var (
-		batchResult = new(gdb.SqlResult)
-		sqlStr      = parseSqlForMerge(table, queryHolders, insertKeys, insertValues, updateValues, conflictKeys)
-	)
+	batchResult := new(gdb.SqlResult)
+	sqlStr := parseSqlForUpsert(table, queryHolders, insertKeys, insertValues, updateValues, conflictKeys)
 	r, err := d.DoExec(ctx, link, sqlStr, queryValues...)
 	if err != nil {
 		return r, err
@@ -213,40 +163,40 @@ func (d *Driver) doMergeInsert(
 	return batchResult, nil
 }
 
-// parseSqlForMerge generates MERGE statement for Oracle database.
-// When updateValues is empty, it only inserts (INSERT IGNORE behavior).
-// When updateValues is provided, it performs upsert (INSERT or UPDATE).
-// Examples:
-// - INSERT IGNORE: MERGE INTO table T1 USING (...) T2 ON (...) WHEN NOT MATCHED THEN INSERT(...) VALUES (...)
-// - UPSERT: MERGE INTO table T1 USING (...) T2 ON (...) WHEN NOT MATCHED THEN INSERT(...) VALUES (...) WHEN MATCHED THEN UPDATE SET ...
-func parseSqlForMerge(table string,
+// parseSqlForUpsert
+// MERGE INTO {{table}} T1
+// USING ( SELECT {{queryHolders}} FROM DUAL T2
+// ON (T1.{{duplicateKey}} = T2.{{duplicateKey}} AND ...)
+// WHEN NOT MATCHED THEN
+// INSERT {{insertKeys}} VALUES {{insertValues}}
+// WHEN MATCHED THEN
+// UPDATE SET {{updateValues}}
+func parseSqlForUpsert(table string,
 	queryHolders, insertKeys, insertValues, updateValues, duplicateKey []string,
 ) (sqlStr string) {
 	var (
 		queryHolderStr  = strings.Join(queryHolders, ",")
 		insertKeyStr    = strings.Join(insertKeys, ",")
 		insertValueStr  = strings.Join(insertValues, ",")
+		updateValueStr  = strings.Join(updateValues, ",")
 		duplicateKeyStr string
+		pattern         = gstr.Trim(`MERGE INTO %s T1 USING (SELECT %s FROM DUAL) T2 ON (%s) WHEN NOT MATCHED THEN INSERT(%s) VALUES (%s) WHEN MATCHED THEN UPDATE SET %s`)
 	)
 
-	// Build ON condition
 	for index, keys := range duplicateKey {
 		if index != 0 {
 			duplicateKeyStr += " AND "
 		}
-		duplicateKeyStr += fmt.Sprintf("T1.%s = T2.%s", keys, keys)
+		duplicateTmp := fmt.Sprintf("T1.%s = T2.%s", keys, keys)
+		duplicateKeyStr += duplicateTmp
 	}
 
-	// Build SQL based on whether UPDATE is needed
-	pattern := gstr.Trim(`MERGE INTO %s T1 USING (SELECT %s FROM DUAL) T2 ON (%s) WHEN NOT MATCHED THEN INSERT(%s) VALUES (%s)`)
-	if len(updateValues) > 0 {
-		// Upsert: INSERT or UPDATE
-		pattern += gstr.Trim(` WHEN MATCHED THEN UPDATE SET %s`)
-		return fmt.Sprintf(
-			pattern, table, queryHolderStr, duplicateKeyStr, insertKeyStr, insertValueStr,
-			strings.Join(updateValues, ","),
-		)
-	}
-	// Insert Ignore: INSERT only
-	return fmt.Sprintf(pattern, table, queryHolderStr, duplicateKeyStr, insertKeyStr, insertValueStr)
+	return fmt.Sprintf(pattern,
+		table,
+		queryHolderStr,
+		duplicateKeyStr,
+		insertKeyStr,
+		insertValueStr,
+		updateValueStr,
+	)
 }

contrib/drivers/oracle/oracle_table_fields.go

@@ -18,23 +18,13 @@ import (
 var (
 	tableFieldsSqlTmp = `
 SELECT 
-    c.COLUMN_NAME AS FIELD, 
+    COLUMN_NAME AS FIELD, 
     CASE   
-    WHEN (c.DATA_TYPE='NUMBER' AND NVL(c.DATA_SCALE,0)=0) THEN 'INT'||'('||c.DATA_PRECISION||','||c.DATA_SCALE||')'
-    WHEN (c.DATA_TYPE='NUMBER' AND NVL(c.DATA_SCALE,0)>0) THEN 'FLOAT'||'('||c.DATA_PRECISION||','||c.DATA_SCALE||')'
-    WHEN c.DATA_TYPE='FLOAT' THEN c.DATA_TYPE||'('||c.DATA_PRECISION||','||c.DATA_SCALE||')' 
-    ELSE c.DATA_TYPE||'('||c.DATA_LENGTH||')' END AS TYPE,
-    c.NULLABLE,
-    CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 'PRI' ELSE '' END AS KEY
-FROM USER_TAB_COLUMNS c
-LEFT JOIN (
-    SELECT cols.COLUMN_NAME 
-    FROM USER_CONSTRAINTS cons 
-    JOIN USER_CONS_COLUMNS cols ON cons.CONSTRAINT_NAME = cols.CONSTRAINT_NAME 
-    WHERE cons.TABLE_NAME = '%s' AND cons.CONSTRAINT_TYPE = 'P'
-) pk ON c.COLUMN_NAME = pk.COLUMN_NAME
-WHERE c.TABLE_NAME = '%s' 
-ORDER BY c.COLUMN_ID
+    WHEN (DATA_TYPE='NUMBER' AND NVL(DATA_SCALE,0)=0) THEN 'INT'||'('||DATA_PRECISION||','||DATA_SCALE||')'
+    WHEN (DATA_TYPE='NUMBER' AND NVL(DATA_SCALE,0)>0) THEN 'FLOAT'||'('||DATA_PRECISION||','||DATA_SCALE||')'
+    WHEN DATA_TYPE='FLOAT' THEN DATA_TYPE||'('||DATA_PRECISION||','||DATA_SCALE||')' 
+    ELSE DATA_TYPE||'('||DATA_LENGTH||')' END AS TYPE,NULLABLE
+FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '%s' ORDER BY COLUMN_ID
 `
 )
 
@@ -54,8 +44,7 @@ func (d *Driver) TableFields(ctx context.Context, table string, schema ...string
 		result       gdb.Result
 		link         gdb.Link
 		usedSchema   = gutil.GetOrDefaultStr(d.GetSchema(), schema...)
-		upperTable   = strings.ToUpper(table)
-		structureSql = fmt.Sprintf(tableFieldsSqlTmp, upperTable, upperTable)
+		structureSql = fmt.Sprintf(tableFieldsSqlTmp, strings.ToUpper(table))
 	)
 	if link, err = d.SlaveLink(usedSchema); err != nil {
 		return nil, err
@@ -64,7 +53,6 @@ func (d *Driver) TableFields(ctx context.Context, table string, schema ...string
 	if err != nil {
 		return nil, err
 	}
-
 	fields = make(map[string]*gdb.TableField)
 	for i, m := range result {
 		isNull := false
@@ -77,7 +65,6 @@ func (d *Driver) TableFields(ctx context.Context, table string, schema ...string
 			Name:  m["FIELD"].String(),
 			Type:  m["TYPE"].String(),
 			Null:  isNull,
-			Key:   m["KEY"].String(),
 		}
 	}
 	return fields, nil

contrib/drivers/oracle/oracle_z_unit_basic_test.go

@@ -139,10 +139,10 @@ func Test_Do_Insert(t *testing.T) {
 			"CREATE_TIME": gtime.Now().String(),
 		}
 		_, err := db.Save(ctx, "t_user", data, 10)
-		gtest.AssertNil(err)
+		gtest.AssertNE(err, nil)
 
 		_, err = db.Replace(ctx, "t_user", data, 10)
-		gtest.AssertNil(err)
+		gtest.AssertNE(err, nil)
 	})
 }
 

contrib/drivers/oracle/oracle_z_unit_model_test.go

@@ -233,48 +233,6 @@ func Test_Model_Insert(t *testing.T) {
 	})
 }
 
-func Test_Model_InsertIgnore(t *testing.T) {
-	table := createInitTable()
-	defer dropTable(table)
-
-	// db.SetDebug(true)
-
-	gtest.C(t, func(t *gtest.T) {
-		data := g.Map{
-			"id":          1,
-			"passport":    fmt.Sprintf(`t%d`, 777),
-			"password":    fmt.Sprintf(`p%d`, 777),
-			"nickname":    fmt.Sprintf(`T%d`, 777),
-			"create_time": gtime.Now(),
-		}
-		_, err := db.Model(table).Data(data).InsertIgnore()
-		t.AssertNil(err)
-
-		one, err := db.Model(table).WherePri(1).One()
-		t.AssertNil(err)
-		t.Assert(one["PASSPORT"].String(), "user_1")
-
-		count, err := db.Model(table).Count()
-		t.AssertNil(err)
-		t.Assert(count, TableSize)
-	})
-
-	gtest.C(t, func(t *gtest.T) {
-		data := g.Map{
-			"passport":    fmt.Sprintf(`t%d`, 777),
-			"password":    fmt.Sprintf(`p%d`, 777),
-			"nickname":    fmt.Sprintf(`T%d`, 777),
-			"create_time": gtime.Now(),
-		}
-		_, err := db.Model(table).Data(data).InsertIgnore()
-		t.AssertNE(err, nil)
-
-		count, err := db.Model(table).Count()
-		t.AssertNil(err)
-		t.Assert(count, TableSize)
-	})
-}
-
 // https://github.com/gogf/gf/issues/3286
 func Test_Model_Insert_Raw(t *testing.T) {
 	table := createTable()
@@ -1221,73 +1179,14 @@ func Test_Model_Replace(t *testing.T) {
 	defer dropTable(table)
 
 	gtest.C(t, func(t *gtest.T) {
-		// Insert initial record
-		result, err := db.Model(table).Data(g.Map{
-			"id":          1,
-			"passport":    "t1",
-			"password":    "pass1",
-			"nickname":    "T1",
-			"create_time": "2018-10-24 10:00:00",
-		}).Insert()
-		t.AssertNil(err)
-		n, _ := result.RowsAffected()
-		t.Assert(n, 1)
-
-		// Replace with new data (should update existing record using MERGE)
-		result, err = db.Model(table).Data(g.Map{
+		_, err := db.Model(table).Data(g.Map{
 			"id":          1,
 			"passport":    "t11",
 			"password":    "25d55ad283aa400af464c76d713c07ad",
 			"nickname":    "T11",
 			"create_time": "2018-10-24 10:00:00",
-		}).OnConflict("id").Replace()
-		t.AssertNil(err)
-		n, _ = result.RowsAffected()
-		t.Assert(n, 1)
-
-		// Verify the data was replaced
-		one, err := db.Model(table).WherePri(1).One()
-		t.AssertNil(err)
-		t.Assert(one["PASSPORT"].String(), "t11")
-		t.Assert(one["PASSWORD"].String(), "25d55ad283aa400af464c76d713c07ad")
-		t.Assert(one["NICKNAME"].String(), "T11")
-
-		// Replace with new ID (insert new record)
-		result, err = db.Model(table).Data(g.Map{
-			"id":          2,
-			"passport":    "t222",
-			"password":    "pass2",
-			"nickname":    "T222",
-			"create_time": "2018-10-24 11:00:00",
-		}).OnConflict("id").Replace()
-		t.AssertNil(err)
-		n, _ = result.RowsAffected()
-		t.Assert(n, 1)
-
-		// Verify new record was inserted
-		one, err = db.Model(table).Where("id", 2).One()
-		t.AssertNil(err)
-		t.Assert(one["PASSPORT"].String(), "t222")
-		t.Assert(one["NICKNAME"].String(), "T222")
-
-		// Replace without OnConflict should fail (no primary key detection yet)
-		_, err = db.Model(table).Data(g.Map{
-			"id":          3,
-			"passport":    "t3",
-			"password":    "pass3",
-			"nickname":    "T3",
-			"create_time": "2018-10-24 12:00:00",
 		}).Replace()
-		t.AssertNil(err)
-
-		_, err = db.Model(table).Data(g.Map{
-			// "id":          3,
-			"passport":    "t3",
-			"password":    "pass3",
-			"nickname":    "T3",
-			"create_time": "2018-10-24 12:00:00",
-		}).Replace()
-		t.AssertNE(err, nil)
+		t.Assert(err, "Replace operation is not supported by oracle driver")
 	})
 }
 

contrib/drivers/pgsql/pgsql_do_insert.go

@@ -14,6 +14,7 @@ import (
 	"github.com/gogf/gf/v2/database/gdb"
 	"github.com/gogf/gf/v2/errors/gcode"
 	"github.com/gogf/gf/v2/errors/gerror"
+	"github.com/gogf/gf/v2/text/gstr"
 )
 
 // DoInsert inserts or updates data for given table.
@@ -38,9 +39,9 @@ func (d *Driver) DoInsert(
 				)
 			}
 			foundPrimaryKey := false
-			for _, primaryKey := range primaryKeys {
+			for _, conflictKey := range primaryKeys {
 				for dataKey := range list[0] {
-					if strings.EqualFold(dataKey, primaryKey) {
+					if strings.EqualFold(dataKey, conflictKey) {
 						foundPrimaryKey = true
 						break
 					}
@@ -62,14 +63,11 @@ func (d *Driver) DoInsert(
 		// Treat Replace as Save operation
 		option.InsertOption = gdb.InsertOptionSave
 
-	// pgsql support InsertIgnore natively, so no need to set primary key in context.
-	case gdb.InsertOptionIgnore, gdb.InsertOptionDefault:
-		// Get table fields to retrieve the primary key TableField object (not just the name)
-		// because DoExec needs the `TableField.Type` to determine if LastInsertId is supported.
+	case gdb.InsertOptionDefault:
 		tableFields, err := d.GetCore().GetDB().TableFields(ctx, table)
 		if err == nil {
 			for _, field := range tableFields {
-				if strings.EqualFold(field.Key, "pri") {
+				if gstr.Equal(field.Key, "pri") {
 					pkField := *field
 					ctx = context.WithValue(ctx, internalPrimaryKeyInCtx, pkField)
 					break

contrib/drivers/pgsql/pgsql_z_unit_model_test.go

@@ -841,24 +841,5 @@ func Test_Model_InsertIgnore(t *testing.T) {
 		value, err := db.Model(table).Fields("passport").WherePri(1).Value()
 		t.AssertNil(err)
 		t.Assert(value.String(), "t1")
-
-		count, err := db.Model(table).Count()
-		t.AssertNil(err)
-		t.Assert(count, 1)
-
-		// pgsql support ignore without primary key
-		result, err = db.Model(table).Data(g.Map{
-			// "id":          1,
-			"uid":         1,
-			"passport":    "t2",
-			"password":    "25d55ad283aa400af464c76d713c07ad",
-			"nickname":    "name_2",
-			"create_time": gtime.Now().String(),
-		}).InsertIgnore()
-		t.AssertNil(err)
-
-		count, err = db.Model(table).Count()
-		t.AssertNil(err)
-		t.Assert(count, 1)
 	})
 }