PetaPoco does not support postgresql json types

[pleb]

As reported by @edwardqinhong

I have a question about using PetaPoco. I have a postgresql database table, which has three columns: id, tester, and data (jsonb type). Here is the table class generated by T4 template: [TableName("public.jsonb1")]

[PrimaryKey("id", AutoIncrement=false)]

[ExplicitColumns] public partial class jsonb1 : SktestDB.Record
{ [Column] public int id { get; set; } [Column] public string tester { get; set; } [Column] public string data { get; set; } } My program (attached below) got the following error message:

A first chance exception of type 'Npgsql.PostgresException' occurred in Npgsql.dll Npgsql.PostgresException (0x80004005): 42804: column "data" is of type jsonb but expression is of type text at Npgsql.NpgsqlConnector.DoReadMessage(DataRowLoadingMode dataRowLoadingMode, Boolean isPrependedMessage) at Npgsql.NpgsqlConnector.ReadMessageWithPrepended(DataRowLoadingMode dataRowLoadingMode) at Npgsql.NpgsqlConnector.ReadMessage(DataRowLoadingMode dataRowLoadingMode) at Npgsql.NpgsqlConnector.ReadExpectingT at Npgsql.NpgsqlDataReader.NextResultInternal() at Npgsql.NpgsqlDataReader.NextResult() at Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior) at Npgsql.NpgsqlCommand.ExecuteNonQueryInternal() at Npgsql.NpgsqlCommand.ExecuteNonQuery() at PetaPoco.Database.ExecuteInsert(String tableName, String primaryKeyName, Boolean autoIncrement, Object poco) in c:\Users\skprod_user\Documents\Visual Studio 2013\Projects\Jsonb1\Jsonb1\Models\PetaPoco.cs:line 1333 INSERT INTO public.jsonb1 ("id","tester","data") VALUES (@0,@1,@2) -> @0 [Int32] = "14" -> @1 [String] = "Edward" -> @2 [String] = "[{"ExtRatio":9.0,"Crossing":49.0},{"ExtRatio":7.5,"Crossing":48.2}]" A first chance exception of type 'Npgsql.PostgresException' occurred in Jsonb1.exe An unhandled exception of type 'Npgsql.PostgresException' occurred in Jsonb1.exe Additional information: External component has thrown an exception.

The program '[9988] Jsonb1.vshost.exe' has exited with code -1 (0xffffffff).

I couldn't find a solution online. Your help will be very much appreciated!

Thanks, Edward

P.S. C# test code:

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 Npgsql; using System.Configuration; using Newtonsoft.Json;

namespace Jsonb1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); }

public class Measurements
{
    public double ExtRatio { get; set; }
    public double Crossing { get; set; }
}

List<Measurements> measurementsList = new List<Measurements>();
private void buttonWrite_Click(object sender, EventArgs e)
{
    ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["Sktest"];
    string connstring = settings.ToString();
    NpgsqlConnection conn = new NpgsqlConnection(connstring);
    conn.Open();
    PetaPoco.Database db = new PetaPoco.Database(conn);



    Measurements[] meas = new Measurements[2];
    meas[0] = new Measurements();
    meas[0].ExtRatio = 9.0;
    meas[0].Crossing = 49.0;
    measurementsList.Add(meas[0]);

    meas[1] = new Measurements();
    meas[1].ExtRatio = 7.5;
    meas[1].Crossing = 48.2;
    measurementsList.Add(meas[1]);

    string jsonString = JsonConvert.SerializeObject(measurementsList);

    var r = new jsonb1();
    r.id = 14;
    r.tester = "Edward";
    r.data = jsonString;

    db.Insert(r);

    conn.Close();
}

[pleb]

Added in latest beta

[edwardqinhong]

I tested 5.1.223 and got the same error. What am I missing?

[pleb]

Version 5.1.223 is not a beta.

Also make sure you review the linked commit for this issue to understand how it works and how you will need to map your json columns