Creating a postgres column which allows all datatypes

December 5, 2022

I want to create a logging table which tracks changes in a certain table, like so:

CREATE TABLE logging.zaak_history (
  event_id                  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tstamp                    timestamp       DEFAULT NOW(),
  schemaname                text,
  tabname                   text,
  columnname                text,
  operation                 text,
  who                       text            DEFAULT current_user,
  new_val                   <any_type>,
  old_val                   <any_type>
);

However, the column that I want to track can take different datatypes, such as text, boolean and numeric. Is there a datatype that support the functionality?

Currently I am thinking about storing is as jsonb, as this will deal with the datatype in the json formatting, but I was wondering if there is a better way.

>Solution :

There is no postgres data type that isn’t strongly typed, because the "any" data type that is available as a pseudo type cannot be used as a column (it can be used in functions, etc.)

You could store the binary representation of your data, because every type does have a binary representation.

Your approach of using JSON seems more flexible, as you can also store meta data (such as type information).

However, I recommend looking at how other people have solved the same issue for alternative ideas. For example, most wikis store a copy of the entire record for history, which is easy to reconstruct, can be referenced independently, and has no typing issues.