Adrian come on 🤠This is a reduced example.
The real usecase involves many tables with the bitemporal record However I solved using a record type ... Il ven 7 lug 2023, 01:20 Adrian Klaver <adrian.kla...@aklaver.com> ha scritto: > On 7/6/23 14:52, Lorusso Domenico wrote: > > Hello guys, > > In my db (version 15) I've defined a composite type with some domains > > > > CREATE DOMAIN my_feat.audit_record_jsonb_domain > > AS jsonb > > NOT NULL; > > > > ALTER DOMAIN my_feat.audit_record_jsonb_domain OWNER TO postgres; > > > > CREATE DOMAIN my_feat.boolean_true_domain > > AS boolean > > DEFAULT true > > NOT NULL; > > > > ALTER DOMAIN my_feat.boolean_true_domain OWNER TO postgres; > > CREATE TYPE my_feat.bitemporal_record AS > > ( > > user_ts_range tstzrange, > > db_ts_range tstzrange, > > has_future_record timestamp with time zone, > > audit_record my_feat.audit_record_jsonb_domain, > > is_valid my_feat.boolean_true_domain > > ); > > > > ALTER TYPE my_feat.bitemporal_record > > OWNER TO postgres; > > So I've a table like that: > > CREATE TABLE IF NOT EXISTS my_feat.try_bt_info > > ( > > id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 > > START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ), > > bt_info my_feat.bitemporal_record, > > CONSTRAINT try_bt_info_pk PRIMARY KEY (id) > > ) > > Seems a long way around to arrive at: > > CREATE TABLE IF NOT EXISTS my_feat.try_bt_info > ( > id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START > 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ) PRIMARY KEY, > user_ts_range tstzrange, > db_ts_range tstzrange, > has_future_record timestamp with time zone, > is_valid boolean NOT NULL 't' > ); > > > > -- > > Domenico L. > > > > per stupire mezz'ora basta un libro di storia, > > io cercai di imparare la Treccani a memoria... [F.d.A.] > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
