Use array_arg on nullable relationship

[tbo]

I've got a 1:n relationship between two tables (here: question, option). If I try to use array_agg on options where I have 1:0, then get the following error message:

assign GetSurveyQuestions row: cannot assign NULL to *queries.Option

This is the query I'm using:

SELECT sq.survey_id, sq.question_id, q.type, q.category, array_agg(o) as options
FROM survey_question sq
  LEFT JOIN question q ON q.question_id = sq.question_id
  LEFT JOIN option o ON o.question_id = q.question_id
WHERE sq.survey_id = pggen.arg('surveyId')
GROUP BY sq.survey_id, sq.question_id, q.type, q.category, sq.position
ORDER BY sq.position;

Is this case supported?

[jschaf]

Yep, we run into this occasionally. The simplest solution is:

coalesce(array_agg(o), '{}'::MyOptionType[]) as options

Though, from the error, it sound like one of the options is null, not the entire array, so you'd move the coalesce inside the array_agg:

array_agg(coalesce(o, '{}'::MyOptionType)) as options

This seems like it should work as-is though. A pointer should be okay to handle null. Can you provide a simplified schema and the pggen invocation?

[tbo]

I tried both suggestions, but I failed to make it work. I tried:

SELECT sq.survey_id, sq.question_id, q.type, q.category, coalesce(array_agg(o), '{}'::Option[]) as options
FROM survey_question sq
  LEFT JOIN question q ON q.question_id = sq.question_id
  LEFT JOIN option o ON o.question_id = q.question_id
WHERE sq.survey_id = pggen.arg('surveyId')
GROUP BY sq.survey_id, sq.question_id, q.type, q.category, sq.position
ORDER BY sq.position;

This resulted in the same original error. Then I tried your second suggestion:

SELECT sq.survey_id, sq.question_id, q.type, q.category, array_agg(coalesce(o, '{}'::Option)) as options
FROM survey_question sq
  LEFT JOIN question q ON q.question_id = sq.question_id
  LEFT JOIN option o ON o.question_id = q.question_id
WHERE sq.survey_id = pggen.arg('surveyId')
GROUP BY sq.survey_id, sq.question_id, q.type, q.category, sq.position
ORDER BY sq.position;

That one failed on gen:

infer typed named query GetSurveyQuestions: infer input types for query: exec prepare statement to infer input query types: ERROR: malformed record literal: "{}" (SQLSTATE 22P02)

This is my simplified schema:

CREATE TABLE option (
    option_id text NOT NULL,
    question_id text NOT NULL,
    text text NOT NULL,
);

CREATE TABLE question (
    question_id text NOT NULL,
    type public.question_type TEXT NOT NULL
    category public.question_category TEXT NOT NULL,
);

CREATE TABLE survey_question (
    survey_id text NOT NULL,
    question_id text NOT NULL,
    "position" integer NOT NULL
);

And this my pggen invocation:

pggen gen go --go-type "timestamp=time.Time" --schema-glob "migrations/*.up.sql" --query-glob "queries/*.sql"

[tbo]

@jschaf Your assumption that the not list but one of its items is NULL was correct. I was able to solve it with array_remove. Here the full query:

SELECT sq.survey_id, sq.question_id, q.type, q.category, array_remove(array_agg(o), NULL) as options
FROM survey_question sq
  LEFT JOIN question q ON q.question_id = sq.question_id
  LEFT JOIN option o ON o.question_id = q.question_id
WHERE sq.survey_id = pggen.arg('surveyId')
GROUP BY sq.survey_id, sq.question_id, q.type, q.category, sq.position
ORDER BY sq.position;

Thanks for the help.

[jschaf]

I was able to solve it with array_remove. Here the full query:

Glad to hear it! The coalesce probably would have worked but I needed to use the anonymous record syntax ROW ('qID', 'type', 'category') instead of the empty array syntax {}.

I'm going to re-open as a reminder to investigate why it didn't work. A pointer type should be able to handle null.

[jschaf]

I'm going to re-open as a reminder to investigate why it didn't work. A pointer type should be able to handle null.

The problem is pggen uses a non-pointer type for all composite types. When we call pgx Scan we give a pointer to the struct, but if we want to support null, we need a pointer to a pointer of the struct. If we get null, there's no value we can use to represent null with the struct. With a pointer type, we could use nil.

I'm okay with requiring a composite type within an array to be not null since there's workarounds with array_remove or coalesce. Otherwise, we'd need a way to specify if a composite type should be a pointer or not.

Here's a simple example that demonstrates the problem:

CREATE TYPE lnglat AS (
  lng float8,
  lat float8
);

-- name: LngLatArrayWithNull :one
SELECT ARRAY[(3, 4), NULL, (5, 6)]::lnglat[];