Querying nullable row

[hayleyjames]

I'm trying to select from two tables using a subquery that returns a nullable row.

This is my query: SELECT id, display_name, (SELECT ROW(id, line_one) FROM address WHERE address.id = address_id) as address FROM account;

I've tried using pgtype.CompositeFields on its own and in a custom type, but I get a cannot scan error.

Is my use case supported by pgx?

[jackc]

pgtype.CompositeFields cannot be used for a NULL.

// CompositeFields represents the values of a composite value. It can be used as an encoding source or as a scan target.
// It cannot scan a NULL, but the composite fields can be NULL.
type CompositeFields []any

You can make a custom type. However, your link is to the pgtype documentation for v4. To make a type that can scan a NULL composite type you would want to implement pgtype.CompositeIndexScanner.

Or you could use pgx's support for handling NULL via pointer-to-pointer. e.g.

var p *MyThing
QueryRow(...).Scan(&p)

If a NULL is scanned p will be set to nil. Otherwise a MyThing will be allocated and the value scanned into that.

[hayleyjames]

Thank you for your reply! I misunderstood how the CompositeFields type worked. Implementing the index scanner resolved my problem.