Inserting a NULL value into a SQLStrings query to replace nothing

New to Julia
1

I’m using SQLStrings and some of the values I am inserting into the database might be NULL. However, when I insert a variable that is nothing, my SQL database actually sees the string value “nothing” and not NULL. I hacked this by replacing values that are nothing with sql'NULL' as then the interpolation engine will use them literally and will not escape the NULL.

Is there a cleaner solution?

ticker = "ABC"
cusip = nothing
julia>q = sql`INSERT INTO security (ticker, cusip)
     VALUES ($ticker, $cusip);`

q = sql`INSERT INTO table1 (ticker, cusip) VALUES ($ticker, $cusip);`
INSERT INTO table1 (ticker, cusip) VALUES ($1, $2);
  $1 = "ABC"
  $2 = nothing

julia> cusip = sql`NULL`
NULL

julia> q = sql`INSERT INTO table1 (ticker, cusip) VALUES ($ticker, $cusip);`
INSERT INTO table1 (ticker, cusip) VALUES ($1, NULL);
  $1 = "ABC"
2

Which database library are you using? Could you show us the code you use to do the insertion?

3

I’m using PostgreSQL / LibPQ. The insertion code is just runquery(conn,q)

4

Are you doing it the way the SQLStrings README recommends, i.e. writing your own (small) runquery function and calling LibPQ.execute(conn, query, args) within it?

5

oh yes, exactly.

function runquery(conn, sql::SQLStrings.Sql)
    query, args = SQLStrings.prepare(sql)
    LibPQ.execute(conn, query, args)
end
6

Ah, in that case it’s LibPQ that’s failing to do the conversion. Note that the SQLStrings readme says that it:

allows the Julia types of interpolated parameters to be preserved and passed to the database driver library which can then marshal them correctly into types it understands.

i.e. it intentionally preserves the Julia types as they are, so that the DB driver can convert it as appropriate to the backend DB. But in this case, LibPQ apparently doesn’t do any Julia-to-PostgreSQL conversions:

Currently all types are printed to strings and given to LibPQ as such, with no special treatment. Expect this to change in a future release. For now, you can convert the data to strings yourself before passing to execute.

So you do have to replace nothings with NULLs yourself. You can do that for individual parameters as you’ve done, or you can change the second line in runquery to

    LibPQ.execute(conn, query, replace(args, nothing => "NULL"))