# Inserting a NULL value into a SQLStrings query to replace nothing

**URL:** <https://discourse.julialang.org/t/inserting-a-null-value-into-a-sqlstrings-query-to-replace-nothing/87056>\
**Category:** New to Julia\
**Created:** [September 11, 2022, 2:46am UTC](https://discourse.julialang.org/t/inserting-a-null-value-into-a-sqlstrings-query-to-replace-nothing/87056 "2022-09-11T02:46:11Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![ivoytov](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ivoytov/32/42480_2.png) [@ivoytov](https://discourse.julialang.org/u/ivoytov)\
**Post date:** [September 11, 2022, 2:46am UTC](https://discourse.julialang.org/t/inserting-a-null-value-into-a-sqlstrings-query-to-replace-nothing/87056/1 "2022-09-11T02:46:11Z")

</div>

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?

```julia
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"

```

---

<div class="post-metadata">

**Author:** ![digital\_carver](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/digital_carver/32/33818_2.png) [@digital\_carver](https://discourse.julialang.org/u/digital_carver)\
**Post date:** [September 11, 2022, 4:47am UTC](https://discourse.julialang.org/t/inserting-a-null-value-into-a-sqlstrings-query-to-replace-nothing/87056/2 "2022-09-11T04:47:50Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ivoytov](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ivoytov/32/42480_2.png) [@ivoytov](https://discourse.julialang.org/u/ivoytov)\
**Post date:** [September 11, 2022, 7:09am UTC](https://discourse.julialang.org/t/inserting-a-null-value-into-a-sqlstrings-query-to-replace-nothing/87056/3 "2022-09-11T07:09:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![digital\_carver](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/digital_carver/32/33818_2.png) [@digital\_carver](https://discourse.julialang.org/u/digital_carver)\
**Post date:** [September 11, 2022, 7:12am UTC](https://discourse.julialang.org/t/inserting-a-null-value-into-a-sqlstrings-query-to-replace-nothing/87056/4 "2022-09-11T07:12:46Z")

</div>

Are you doing it the way the SQLStrings [README recommends](https://github.com/JuliaComputing/SQLStrings.jl#simple-usage), i.e. writing your own (small) `runquery` function and calling ` LibPQ.execute(conn, query, args)` within it?

---

<div class="post-metadata">

**Author:** ![ivoytov](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ivoytov/32/42480_2.png) [@ivoytov](https://discourse.julialang.org/u/ivoytov)\
**Post date:** [September 11, 2022, 7:14am UTC](https://discourse.julialang.org/t/inserting-a-null-value-into-a-sqlstrings-query-to-replace-nothing/87056/5 "2022-09-11T07:14:02Z")

</div>

oh yes, exactly.

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

```

---

<div class="post-metadata">

**Author:** ![digital\_carver](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/digital_carver/32/33818_2.png) [@digital\_carver](https://discourse.julialang.org/u/digital_carver)\
**Post date:** [September 11, 2022, 1:07pm UTC](https://discourse.julialang.org/t/inserting-a-null-value-into-a-sqlstrings-query-to-replace-nothing/87056/6 "2022-09-11T13:07:01Z")

</div>

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](https://invenia.github.io/LibPQ.jl/stable/pages/type-conversions/#From-Julia-to-PostgreSQL):

> 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`](https://invenia.github.io/LibPQ.jl/dev/pages/api/#LibPQ.execute).

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

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

```