Generated code for insert query isn't using pog.nullable() for nullable parameters

[Rentfunk]

Steps to reproduce:

Table:

create table books (id uuid primary key, name varchar(100));

Query:

insert into books values ($1, $2);

This is how the generated code looks like:

pub fn create_book(db, arg_1, arg_2) {
  let decoder = decode.map(decode.dynamic, fn(_) { Nil })

  "insert into books values ($1, $2);
"
  |> pog.query
  |> pog.parameter(pog.text(uuid.to_string(arg_1)))
  |> pog.parameter(pog.text(arg_2))
  |> pog.returning(decoder)
  |> pog.execute(db)
}

With the name column being nullable i would expect the parameter for arg_2 to look like this:

  |> pog.parameter(pog.nullable(pog.text, arg_2))

From looking through the code, it seems that the check if column is nullable happens only if the query returns something, which doesn't happen for insert queries. So when they are parsed we get only the types of columns.

[giacomocavalieri]

Thank you!

[giacomocavalieri]

I've looked into this and there's not much I can do about it I'm afraid: Squirrel gets the types from the Postgres server by asking it to prepare each query; what the Postgres server does is - once it has prepared a query - it sends back to squirrel the OID (identifiers) for the types of each argument in the query. However, this ID is only telling us what type the argument is, but nothing about how it's used or whether it's nullable or not. So would have to be supported by Postgres first.

In the meantime you could write two different queries: one that inserts all values and one that inserts just one when the other is null:

-- insert_book.sql
insert into book(title, author) values ($1, $2);

-- insert_book_with_no_author.sql
insert into book(title, author) values ($1, null);

And in your Gleam code you'd pick which one to call depending on wether the author is there or not:

case author {
  Some(name) -> sql.insert_book(db, title, author)
  None -> sql.insert_book_with_no_author(db, title)
}

Luckily Squirrel makes it easy to write and use multiple queries, so as long as you don't have an explosion of combinations of not-null/null values it should be easily manageable :)

[BlackEdder]

Maybe a silly question, but how does squirrel know to return options when selecting from nullable columns? And couldn't the same method be used to detect nullable columns when inserting?

[giacomocavalieri]

Not silly at all! At a very high level, the way squirrel works is it sends the query to the Postgres database running. In turn the database will parse the query and reply with loads of useful info about the query itself: things like the return type of the query, or the type of its parameters. At that point Squirrel uses that information to generate the appropriate Gleam code.

The problem is Postgres itself will not tell me if query parameters can be null (but is able to do so for the returned values of a query). So there's not much we can do besides waiting for a Postgres fix! I've asked about this in the Postgres mailing list and it seems like it would be quite the challenging feature to implement, it has to do with the way Postgres propagates constraints during the query analysis

[ToddG]

I'm ingesting data into a postgres db where there are 7 nullable fields. And this is just the first data feed I've started working on.. I'll be ingesting from ~6 more feeds, all of which have similar issues with nullable fields. Couldn't we advise squirrel of the fields that might be null at compile time? Obviously the generated code knows that the values are None at runtime...

[giacomocavalieri]

Hello! I'm doing something similar in a real world codebase where I have to ingest data from a csv into a table that can have a lot of nullable columns. The solution I came up with, which is working quite well in that case looks like this:

insert into retailers (
  distributor_id,
  chain_type, -- nullable
  premise_type,  -- nullable
  -- ... many other nullable string columns
)
values (
  $1,
  empty_string_to_null($2),
  empty_string_to_null($3),
  -- ...
);

Basically you pass to the SQL a placeholder value that you know stands for null, in my case an empty string will do, so the query can tell when to add a null value or the real deal.
The empty_string_to_null function is this little helper I've defined:

create or replace function empty_string_to_null(string text)
returns text returns null on null input
language sql immutable as $$ select nullif(trim($1), '') $$;

[ToddG]

Thx! I'll try that and report back :-)

[ToddG]

Your work-around works great, thx! I'm also using these converters, as negative values are not valid in my use case:

CREATE OR REPLACE FUNCTION negative_int_to_null(p_val int)
RETURNS int
LANGUAGE plpgsql
AS $$
BEGIN
    -- If the argument is -1, return NULL
    IF p_val = -1 THEN
        RETURN NULL;
    END IF;

    -- Otherwise return the original value
    RETURN p_val;
END;
$$;

CREATE OR REPLACE FUNCTION negative_float_to_null(p_val float)
RETURNS float
LANGUAGE plpgsql
AS $$
BEGIN
    -- If the argument is -1.0, return NULL
    IF p_val = -1.0 THEN
        RETURN NULL;
END IF;

    -- Otherwise return the original value
RETURN p_val;
END;
$$;

[dcrockwell]

@giacomocavalieri

How do you feel about a -- @nullable: column_name, column_name_two (comment at the top of the file) convention that squirrel can use to correlate the columns to nullable? It would require no SQL parsing, and would be a simple workaround while waiting for postgres to provide a more effective solution?

If no issues with this approach, I could submit a PR.