postgresql 创建允许所有数据类型的postgres列

6l7fqoea  于 2022-12-12  发布在  PostgreSQL
关注(0)|答案(1)|浏览(126)

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.

smdncfj3

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.

相关问题