Skip to content

Queries that select optional columns are not unmarshalled into sql.NullString{} #947

Closed
@honne23

Description

@honne23

For a given table such as:

CREATE TABLE IF NOT EXISTS commodity_type (
  id SERIAL PRIMARY KEY,
  label VARCHAR NOT NULL
);


CREATE TABLE IF NOT EXISTS listings (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title VARCHAR NOT NULL,
  price BIGINT NOT NULL,
  commodity_type INT,
  comments VARCHAR,
  FOREIGN KEY (commodity_type) REFERENCES commodity_type(id)
);

and a query such as the following which replaces the id of the optional column commodity_type with its VARCHAR label:

SELECT 
    title, 
    price, 
    comments,
    commodity_type.label commodity_type
FROM listings
    LEFT JOIN commodity_type ON listings.commodity_type = commodity_type.id ;

the struct representing such a query resolves to:

type GetListingsRow struct {
    ID              uuid.UUID  `json:"id"`,
    Price           int64      `json:"price"`,
    Comments        string     `json:"comments"`,
    CommodityType   string     `json:"commodity_type"`,
}

which will throw an error when trying to retrieve that row because converting NULL to string is unsupported.

The expected behaviour is that the optional columns of the query are automatically resolved to sql.NullString{} instead of string.

Edit:
On the other hand: SELECT * FROM listings; works as expected.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions