CODEWITHSUNDEEP
c#postgresql.net-coresql-insertnpgsql
Nmaster88
Nmaster88

Reputation: 1585

How to convert a json string into jsonb type using postgres in c#

I'm working with .NET core 3.1

I have a table on my postgres database with 2 columns

+---------+--------+
| id      | bigint |
+---------+--------+
| content | jsonb  |
+---------+--------+

And i am trying to place on the column content json strings like this:

"{\"Id\":247764684382208,\"Created\":\"2020-06-08T00:00:00Z\",\"Content\":\"Ad in consequat duis qui sit eu nulla magna consectetur id anim\",\"ReceivedTimestamp\":\"0001-01-01T00:00:00\",\"MessageEventId\":0}"

In my code i do the following:

the variable result is an IColletion with a (key=long,value=string) dictionary, which will have values like the one i posted above

string connString = "Host=localhost;Port=5432;Username=postgres;Password=postgres;Database=testdb";

NpgsqlConnection conn = new NpgsqlConnection(connString);

conn.Open();

var valuesTableSql = string.Join(",", Enumerable.Range(0, result.Count).Select(i => $"(@p1{i}, @p2{i})"));

using (var cmd = new NpgsqlCommand($"INSERT INTO \"table\" (\"id\", \"content\") VALUES {valuesTableSql};", conn)) 
{
 for (int i = 0; i < result.Messages.Count; ++i)
  {
   cmd.Parameters.AddWithValue($"p1{i}", result.ElementAt(i).Key);
   cmd.Parameters.AddWithValue($"p2{i}", result.Messages.ElementAt(i).Value);
 }
}

When i run the code i will get an exception like this:

Unhandled exception. Npgsql.PostgresException (0x80004005): 42804: column "content" is of type jsonb but expression is of type text

I'm new to postgres, and in my requirements i can't use Entity framework. What do i need to do there to convert the Json string into that jsonb type of postgres?

Upvotes: 0

Views: 6282

Answers (2)

Shay Rojansky
Shay Rojansky

Reputation: 16692

As @GMB wrote, PostgreSQL won't implicitly convert a string to a jsonb. However, rather than applying a cast inside SQL, you can also instruct Npgsql to send the parameter typed as jsonb:

cmd.Parameters.AddWithValue("p1", NpgsqlDbType.Jsonb, "{....}");

Upvotes: 2

GMB
GMB

Reputation: 222462

Postgres generally avoids implicit conversion: from its perspective, you are attempting to insert a string in a jsonb column.

I think that you that you need an explicit cast in your query. Changing the following line of code should be good enough: 

var valuesTableSql = string.Join(",", Enumerable.Range(0, result.Count).Select(i => $"(@p1{i}, @p2{i})::jsonb"));

Upvotes: 2

Related Questions