PostgreSQL arrays can have NULL items

[kyleconroy]

Given the following schema and query:

CREATE TABLE foo (
        id text NOT NULL,
        tags text[]
);

-- name: ListFoo :many
SELECT * FROM foo;

sqlc will generate the following code:

// Code generated by sqlc. DO NOT EDIT.

package main

import (
	"context"
	"database/sql"

	"github.com/lib/pq"
)

type Foo struct {
	ID   sql.NullString
	Tags []string
}

const listFoo = `-- name: ListFoo :many
SELECT id, tags FROM foo
`

func (q *Queries) ListFoo(ctx context.Context) ([]Foo, error) {
	rows, err := q.db.QueryContext(ctx, listFoo)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []Foo
	for rows.Next() {
		var i Foo
		if err := rows.Scan(&i.ID, pq.Array(&i.Tags)); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

type DBTX interface {
	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
	PrepareContext(context.Context, string) (*sql.Stmt, error)
	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
}

func New(db DBTX) *Queries {
	return &Queries{db: db}
}

type Queries struct {
	db DBTX
}

func (q *Queries) WithTx(tx *sql.Tx) *Queries {
	return &Queries{
		db: tx,
	}
}

With the following two records inserted into the foo table, a call ListFoos succeeds.

INSERT INTO foo (id, tags) VALUES ('one', NULL);
INSERT INTO foo (id, tags) VALUES ('two', array['tag']);

However, if a NULL item exists in the tags array, an error will be returned.

INSERT INTO foo (id, tags) VALUES ('one', array[NULL]);

sql: Scan error on column index 1, name "tags": pq: parsing array element index 0: cannot convert nil to string

That's not a problem, right? We'll just mark the tags column as not null. Sadly, that just means that the column must contain an array value. The array itself can still contain NULL values. It does not appear to be easy to prevent NULL values in arrays. This was the [only solution](Declare a column of type 'not-null-string' array in postgresql - Stack Overflow) I could find, and it's pretty gross.

Note that pq.StringArray also does not support NULL array values.

[kyleconroy]

A sql.NullString slice, paired with pq.Array works correctly.

type Foo struct {
	ID   sql.NullString
	Tags []sql.NullString
}

[dimmerz92]

I don't know if this is helpful for anyone. I was also struggling with arrays containing only NULL values. If you don't need to retain the NULL in your arrays, I found the below to be a decent work around by essentially just omitting NULL values.

This would work for POSTGRES, filtering out NULLs and casting the array type.

SELECT
  e.id,
  e.name,
  e.description,
  e.datetime,
  e.status,
  e.datetime < NOW() AS elapsed,
  ARRAY_AGG(c.id) FILTER (WHERE c.id IS NOT NULL)::int[] AS category_id,
  ARRAY_AGG(c.name) FILTER (WHERE c.id IS NOT NULL)::text[] AS category_name
FROM
  events AS e
  LEFT JOIN category_links AS l ON e.id = l.event_id
  LEFT JOIN categories AS c ON l.category_id = c.id
GROUP BY
  e.id,
  e.name,
  e.description,
  e.datetime,
  e.status;