Blimbits
← StateDB development log

StateDB: The first few days, Part C - More queries

Moving beyond reading rows: learning to insert, delete, and update data in PostgreSQL from Go.

In Part B, I connected to PostgreSQL and read rows from test_items. Next was learning how to write data from Go.

These examples continue with the db connection pool and ctx from that post.

Inserting a row

The first insert looked like this:

result, err := db.Exec(
	ctx,
	`
	INSERT INTO test_items (name, status)
	VALUES ($1, $2);
	`,
	"item-six",
	"active",
)
if err != nil {
	log.Fatal(err)
}
fmt.Printf("Inserted %d row(s)\n", result.RowsAffected())

The SQL names the two columns I’m filling in: name and status. $1 and $2 are placeholders for the arguments that follow the SQL string. Here, $1 gets "item-six", and $2 gets "active".

The values are passed separately from the SQL instead of being assembled into the query text.

For this insert, I used db.Exec because I wasn’t reading a result set. It returns a command tag and an error. After checking err, result.RowsAffected() tells me how many rows the command affected. There are no rows to loop over or close in this example.

For a successful one-row insert, the output would be:

Inserted 1 row(s)

That was the next step: going from reading what’s in the table to adding something to it.

Deleting rows

Then I tried deleting by name:

deleteResult, err := db.Exec(
	ctx,
	`
	DELETE FROM test_items
	WHERE name = $1;
	`,
	"item-8",
)
if err != nil {
	log.Fatal(err)
}
fmt.Printf("Deleted %d row(s) from test_items\n", deleteResult.RowsAffected())

This uses the same Exec pattern as the insert. $1 gets "item-8", and the WHERE clause limits the deletion to rows with that name. If multiple rows share the name, they all match.

deleteResult.RowsAffected() gives me the number of rows deleted. If nothing matches, the count is zero; that isn’t an error by itself.

The insert above uses "item-six", so this delete targets a different item. It isn’t undoing that insert.

Updating rows

Updating works the same way from Go: call db.Exec, pass the values separately, check the error, and look at RowsAffected(). The SQL changes, but the surrounding pattern stays familiar.

For example, changing an item’s status could look like this:

updateResult, err := db.Exec(
	ctx,
	`
	UPDATE test_items
	SET status = $1
	WHERE name = $2;
	`,
	"inactive",
	"item-six",
)
if err != nil {
	log.Fatal(err)
}
fmt.Printf("Updated %d row(s) in test_items\n", updateResult.RowsAffected())

SET specifies what to change, and WHERE selects the rows to update. Here, $1 supplies the new status and $2 supplies the name to match. As with the delete, every row matching that name is affected, and no matches means a count of zero.

Once I understood the pattern, insert, delete, and update started to feel like variations of the same thing.

Part D continues with reorganizing the code into packages and functions.

References