Error while scanning NULL columns in SQLC generated code using LEFT join in query

Question content

I just modified a table in PostgreSQL to NULLABLE as follows:

CREATE TABLE a {
    a_name varchar NOT NULL
    b_id BIGINT <-- was previously NOT NULL with no problems 
}

CREATE TABLE b {
    id BIGSERIAL,
    b_name varchar NOT NULL
}

a.b_id > b.id has foreign key constraints.

I have many queries that join these tables and return b.name similar to this:

-- name: List :many
SELECT
   a_name,
   b_name
FROM a
LEFT JOIN b ON b.id = a.bid <-- produces NULL columns in results

Due to LEFT JOIN, the return type of query b_name can be NULL. Any row in a.b_id that is NULL will return NULL for b_name. observe.

Actually, the query is much more complex, sending multiple nullable parameters in the WHERE clause, but intuitively I don't think that's the problem. Surely SQLC configures its row structure from the SELECT part of the query...?

SQLC is generating a row structure similar to this:

type ListRow struct {
   AName string `json:"a_name"'
   BName string `json:"b_name"'
}

BName should be nullable (I use various gobuffalo null overrides in the config), but is not in the struct, thus causing a scan error:

"sql: Scan error on column index 1, name \"b_name\": converting NULL to string is unsupported"

I'm obviously missing something obvious from the documentation, as this must be a regular operation. So far I haven't had any problems using SQLC with fairly complex INNER JOIN table queries or with nullable column return types.

Not sure how active the SO community is about SQLC, appreciate any feedback, intuitive or vague.

Correct Answer

Suggestion - Replace b_name in the query with coalesce(b_name, '** Attention **') to see what might happen.

SELECT
   a_name,
   coalesce(b_name, '** Attention **')
FROM a LEFT JOIN b ON b.id = a.bid;

Alternately replace it with coalesce(b_name, '') if that is acceptable and makes sense.

SELECT
   a_name,
   coalesce(b_name, '')
FROM a LEFT JOIN b ON b.id = a.bid;

Or filter the results where b_name is null

SELECT a_name, b_name
FROM a LEFT JOIN b ON b.id = a.bid
where b_name is not null;

The above is the detailed content of Error while scanning NULL columns in SQLC generated code using LEFT join in query. For more information, please follow other related articles on the PHP Chinese website!