1

I have problems to scan json objects which include daterange types:

Let's assume these tables:

CERATE TABLE first_table (
  id int PRIMARY KEY,
  name text
);

CERATE TABLE second_table (
  id int PRIMARY KEY,
  title text,
  period daterange NOT NULL,
  a_id int NOT NULL REFERENCES first_table (id)
);

The structs are

type A struct {
  ID int
  Name *string
  SomeBs []B
}

type B struct {
  ID int
  Title *string
  Period  pgtype.Range[pgtype.Date]
}

The query:

SELECT 
    a.id,
    a.name,
    bs.*
FROM first_table a
LEFT JOIN LATERAL (
    SELECT 
    COALESCE(json_agg(json_build_object(
        'id', id,
        'title', title,
        'period', period)), '[]')
    FROM second_table b 
    WHERE a.id = b.a_id
) AS bs ON true 

I get the error can't scan into dest[2]: json: cannot unmarshal string into Go struct field B.Period of type pgtype.Range[github.com/jackc/pgx/v5/pgtype.Date]

As writte here, scanning it without json_agg and json_build_object, it works. If for testing purpose I changed the type B.Period to string, the query works and the value is something like [2020-02-01,2023-02-03). I tried to change the type to date_range.DateRange or pgtype.Range[differentTypes], nothing worked.

How can I scan the values in date_range.DateRange or pgtype.Range[pgtype.Date]? I would prefer the latter.

Emaborsa
  • 2,360
  • 4
  • 28
  • 50

0 Answers0