A peculiar error occurred today when was manipulating data in Postgres using database/sql and driver github.com/lib/pq. I have the following SQL schema created in Postgres:
CREATE TABLE IF NOT EXISTS bench_bytea (
id INT PRIMARY KEY,
name VARCHAR,
data BYTEA
);
A very basic table containing a data blob of type BYTEA.
I then tried to execute simple INSERT statement using the Exec() function provided by database/sql. Here it is:
psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
"password=%s dbname=%s sslmode=disable",
host, port, user, password, dbname)
db, err := sql.Open("postgres", psqlInfo)
if err != nil {
panic(err)
}
defer db.Close()
stmt := `INSERT INTO bench_bytea (id, name, data) VALUES ($1, $2, $3) ON CONFLICT (id) DO NOTHING`
data := `{"title": "Sleeping Beauties", "genres": ["Fiction", "Thriller", "Horror"], "published": false}`
i := 0
_, err = db.Exec(stmt, i, "testing "+string(i), []byte(data))
if err != nil {
panic(err)
}
The key highlight happens on the db.Exec() line where I execute a SQL INSERT statement (in practicality i is an index of an array where I stored different testing data. I didn't want to include the other pieces of data here since it's really long and irrelevant). The error I received is:
pq: invalid byte sequence for encoding "UTF8": 0x00
Now if I change "testing "+string(i) into "testing", the error is gone. That is, if I didn't insert a concatenating strings into the name column, there's no error. What is going on here?
strconv.Itoato convert an integer to string.