postgresql merge problem

[znyet]

create an new table, Id is primary key and identity.

var model = new People{ Id=1,Name="lina" }; conn.Merge(model); //it is ok

var model2 = new People{ Name="lili"}; conn.Insert(model2); //it is error (only postgresql error, mysql、sqlite、sqlserver is ok )

because when Merge postgresql next seq still 1 and than Insert error

other database when Merge update next seq to 2，so success.

[JonathanMagnan]

Hello @znyet ,

Could you provide the script to create your table? My developer tried it and everything was working but perhaps he did something different.

Here is the code he used:

using Dapper;
using NodaTime;
using Npgsql;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Z.Dapper.Plus;

namespace Z.Lab.PostgreSQL
{
    class Request_98
    {
        public static void Execute()
        { 
            using (var connectionMaster = new Npgsql.NpgsqlConnection(My.ConnectionMaster))
            {
                var commandText = connectionMaster.Query<string>(@"SELECT 1 FROM pg_database WHERE datname = 'dapperpluslab'").FirstOrDefault();

                if (!string.IsNullOrEmpty(commandText))
                {
                    using (var command = connectionMaster.CreateCommand())
                    {
                        connectionMaster.Open();
                        command.CommandText = @"DROP DATABASE dapperpluslab";
                        command.ExecuteNonQuery();
                        connectionMaster.Close();
                    }
                }

                using (var command = connectionMaster.CreateCommand())
                {
                    connectionMaster.Open();
                    command.CommandText = @"CREATE DATABASE dapperpluslab";
                    command.ExecuteNonQuery();
                    connectionMaster.Close();
                }

            }

            using (var connectionTable = new Npgsql.NpgsqlConnection(My.Connection))
            {
                using (var command = connectionTable.CreateCommand())
                {
                    connectionTable.Open();
                    command.CommandText =
                @"CREATE SEQUENCE entitysimples_id_seq;  ";
                    command.ExecuteNonQuery();
                    connectionTable.Close();
                }
                using (var command = connectionTable.CreateCommand())
                {
                    connectionTable.Open();
                    command.CommandText =
                @" 

CREATE TABLE public.""EntitySimples""
(
    ""ID"" integer NOT NULL DEFAULT nextval('""entitysimples_id_seq""'),
    ""ColumnInt"" integer NOT NULL,  
    CONSTRAINT ""PK_EntitySimples"" PRIMARY KEY(""ID"")
)
WITH(
    OIDS = FALSE
)
TABLESPACE pg_default;

                ALTER TABLE public.""EntitySimples""
    OWNER to postgres;";
                    command.ExecuteNonQuery();
                    connectionTable.Close();
                }
            }

            var connection = new Npgsql.NpgsqlConnection(My.Connection);
            DapperPlusManager.Entity<EntitySimple>().Table("EntitySimples"); 

            var list = new List<EntitySimple>();
            for (int i = 0; i < 3; i++)
            { 
                list.Add(new EntitySimple() { ColumnInt = i });
            }

            connection.BulkInsert(list); 

            // Query
            var list2 = connection.Query<EntitySimple>("SELECT * FROM \"EntitySimples\"");
        }

        public class EntitySimple
        {
            public int ID { get; set; }
            public int ColumnInt { get; set; } 
        }
    }
}  

Let me know what he is missing to reproduce this issue.

Best Regards,

Jon

[znyet]

CREATE TABLE "people" (
  "id" int4 NOT NULL DEFAULT nextval('people_id_seq'::regclass),
  "name" varchar(255),
  PRIMARY KEY ("id")
)

class people
{
    public int id { get; set; }

    public string name { get; set; }
}

DapperPlusManager.Entity<people>.Key("id").Table("people");

var p1 = new people{ id=1, name="lili"};
var p2 = new people{ name="lili" };

connection.UseBulkOptions(option =>
                    {
                         option.MergeKeepIdentity = true;
                    }).BulkMerge(p1); //it will merge identity id=1 record success

connection.BulkInsert(p2);  //error (only postgresql error, other databases success)

//duplicate key value violates unique constraint "p_pkey"”

[znyet]

Hello @JonathanMagnan Will this anomaly occur on your side.

[JonathanMagnan]

Thank you, we will look at it.

[znyet]

Hello @JonathanMagnan

option.MergeKeepIdentity = true; option.InsertKeepIdentity = true;

i thik postgresql must to do select setval('tablename_id_seq',(select max(id) from tablename))

mysql、sqlite、sqlserver when insert identity will auto set seq to next but postgresql serial、sequence、identity will not auto set seq to next.

DapperPlusManager.Entity<people>.Key("id").Table("people");

var p1 = new people{ id=1, name="lili"}; //when insert indentity
var p2 = new people{ name="lili" };

connection.UseBulkOptions(option =>
                    {
                         option.MergeKeepIdentity = true;
                    }).BulkMerge(p1); 

connection.Execute($"select setval('people_id_seq',(select max(id) from people))"); //add this code success

connection.BulkInsert(p2);  //pass

i think it is not your library bug,it is postgresql.