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);


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?

asked on Stack Overflow Jun 8, 2020 by Nmaster88 • edited Jun 8, 2020 by GMB

2 Answers


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"));
answered on Stack Overflow Jun 8, 2020 by GMB

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, "{....}");
answered on Stack Overflow Jun 9, 2020 by Shay Rojansky

User contributions licensed under CC BY-SA 3.0