Inserting Null to PostgreSQL in Golang

Golang gopher and PostgreSQL elephant

I have this use case where I want to insert timestamp value to the database, but the value can be null.

If you insert an empty time.Time variable to PostgreSQL, the inserted value won’t be `NULL` but `0001–01–01 00:00:00`. Let’s see an example where my “orders” table looks like this:

"order_id" auto-incremented, int,
"paid_at" timestamp, can be null,
...more columns

The golang query

const query = "UPDATE orders SET paid_at = $1 WHERE order_id = $2"
var order_id = 8
var paid_at time.Time // time is not set
_, err := sql.Exec(query, paid_at, order_id)

The value of paid_at where order_id is ‘8’ is `0001–01–01 00:00:00`. This is illogical because the order has not been paid, and I want it to stay nulled. None of our orders are paid at `0001–01–01 00:00:00` which is equivalent to January 1, 1970 12:00:00 AM, so we will treat that value as an empty time.

The easiest way to implement this is to use NULLIF. Let’s update the SQL query.

const query = "UPDATE orders SET paid_at = NULLIF($1::timestamp, '0001-01-01 00:00:00') order_id = $2"

The NULLIF function checks if the paid_at (as $1) is equal to ‘0001–01–01 00:00:00’, the assumed empty time. If it’s empty, NULLIF will return NULL. Perform that query, then we will get NULL as a value in the paid_at.