PostgreSQL has a nice encryption (and hashing) module called pgcrypto which is easy to use. Using a provided key, you can quickly encrypt a column which contains sensitive information.
While the setup is fast and the usage is simple, there could be some disadvantages in some contexts:
- be careful how you send the encryption key to the database server (if public, use SSL for transport, else keep it in a private network at least)
- data is encrypted/decrypted in the database, so the transport is in plain (watch out for memory dump attacks)
- some queries are very slow, as the decrypt operation is performed on the entire table if you want to sort or filter by encrypted columns
Continue reading Encrypt column in PostgreSQL
Today I’ve found out a flavor of PostgreSQL when checking if a composite type column is not null. There are different ways of checking for not null columns:
- IS NOT NULL
- NOT ISNULL
For primitive values any of the above works, while for the composite types there is a particularity.
Given a table of users with a
deleted column of a composite type
action, and some users marked as deleted, I need all users that are deleted. Continue reading PostgreSQL NOTNULL vs IS NOT NULL vs NOT ISNULL
Consider the following case: When creating a user (database insert) with their profile (another insert), other users must be updated (database update) with a new score value. Score is just a float for which a dummy formula will be used. And then an action record is needed (insert), which marks the fact that a user was created.
The tech context is PostgreSQL in Go with pgx as database driver and Echo framework for the HTTP server. The database setup is straight forward using Docker; it also includes a database management interface which will be available at http://localhost:54321. If you clone the sample repository, and start the setup with Docker Compose (docker compose up -d), when the PostgreSQL Docker container is built, a database is created with the schema used in this post.
CREATE TABLE "users" (
"id" serial NOT NULL,
"username" CHARACTER VARYING (100) NOT NULL,
"score" DECIMAL NOT NULL DEFAULT 0,
"created" TIMESTAMP(0) WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updated" TIMESTAMP(0) WITH TIME ZONE
CREATE TABLE "user_profile" (
"user_id" INTEGER NOT NULL,
"firstname" CHARACTER VARYING (100) NOT NULL,
"lastname" CHARACTER VARYING (100) NOT NULL
CREATE TABLE "actions" (
"id" serial NOT NULL,
"description" text NOT NULL,
"created" TIMESTAMP(0) WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
Data integrity is of interest, so all the queries will be sent on a database transaction. And because there are multiple user update queries, they will be sent all at the same time in a batch of operations. Continue reading PostgreSQL batch operations in Go