npgsql data type unknown when using group by

Question

I have 2 tables:

CREATE TABLE "book" 
(
  "id" serial PRIMARY KEY, 
  "ean_number" TEXT NULL, 
  "title" TEXT NULL 
); 

CREATE TABLE "e_book" 
(
  "id" serial PRIMARY KEY, 
  "ean" TEXT NULL, 
  "title" TEXT NULL, 
  "format" VARCHAR(255) NOT NULL, 
  "physical_book_ean" TEXT NULL 
);

There is a one to many or none relationship from book to e_book.

I want to run a query like this in my code:

var q = "select b.*, array_agg(e) ebooks from book b " +
         "left join e_book e on e.physical_book_ean = b.ean_number " +
         "group by b.id";

using (var cmd = new NpgsqlCommand(q, conn))
using (var reader = cmd.ExecuteReader())
    while (reader.Read())
    {
        //read data
    }

The array_agg column ebook comes up as content type <unknown>

How do I define the content type so I can read it?

Hi this issue about array types might be useful https://github.com/npgsql/npgsql/issues/1980 — jspcal, Jun 28 '19 at 20:26
I saw that issue. It was marked a bug. It has an associated commit a year ago and it is closed so that issue appears to be resolved. It should support arrays — Guerrilla, Jun 28 '19 at 20:28

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

This was opened as a github issue: https://github.com/npgsql/npgsql/issues/2510

The answer, as given there:

First, if you create the e_book table and query it in the same process, you need to tell Npgsql to reload database type definitions. This is because when Npgsql first connects to a database, it loads the list of types and caches it - but at that point the e_book type doesn't exist yet. If you run your application again with the tables already there you should no longer have this issue, or you can call Npgsql.ReloadTypes().

Second, you will need to pass the LoadTableComposites=true flag on the connection string, telling Npgsql to load all composite types - including those which correspond to tables. Npgsql doesn't do that by default since the number of tables can be massive and affect startup performance in some cases.

npgsql data type unknown when using group by

1 Answers1

Linked