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.
1条答案
按热度按时间smdncfj31#
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.