Inserting Null to PostgreSQL in Golang
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,
The golang query
const query = "UPDATE orders SET paid_at = $1 WHERE order_id = $2"var order_id = 8var 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"
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.