Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q6G7X-0006q7-LV for pgsql-general@arkaria.postgresql.org; Mon, 05 Jun 2023 19:44:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q6G7W-0007py-EE for pgsql-general@arkaria.postgresql.org; Mon, 05 Jun 2023 19:44:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q6G7W-0007pm-27 for pgsql-general@lists.postgresql.org; Mon, 05 Jun 2023 19:44:02 +0000 Received: from mail-yb1-xb2a.google.com ([2607:f8b0:4864:20::b2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q6G7T-000R93-Fw for pgsql-general@lists.postgresql.org; Mon, 05 Jun 2023 19:44:01 +0000 Received: by mail-yb1-xb2a.google.com with SMTP id 3f1490d57ef6-bad0c4f6f50so7808251276.1 for ; Mon, 05 Jun 2023 12:43:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1685994238; x=1688586238; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=hnipxfb4oVLtggaUJfN+DyhwaeetV4sixdOEf+dSA1o=; b=rwf4bnR0jpjtm1m3BP0jsqYyOrHZ3T9YAhHWzqYx0hiPD/7wY5vL4b+swS30fWS8ed /CDEGsYi+4gUN5gKsxzbHiYm8QBueZzD19GTI2fbtFmnAtct1VkY8q1qJdoLbFtKjr7Z kP4K9aE8uUEiHrMAjmQLZQQujG0QjCn/xIH48xsMVL1ZF7qXqw0MY+WltP6vu/+yl4yT HnI5eSrU2+tOTbvDzRljjC0O1yOP9JSp6ZtXi++3Wzxiho68bYPQwLJ8i4eaUgYSlzf9 lXKxNTsSSvqA8I52RR062VQtsC7twdFuHfJ6lBjnkXXz8SRYpZ+NNiNhnoyBjCc2sNoU 35lQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1685994238; x=1688586238; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=hnipxfb4oVLtggaUJfN+DyhwaeetV4sixdOEf+dSA1o=; b=KIwQgIePTLW33rlyYSp3e7cd/ir1X0X5HbtLxjERJAUAGtTxH0/mMZa5WsHBch6L9I X2N+F5OroWJdOqhUhTB6t6+R88UguIOTQAKFuZ08CmX+jxyxD8sHIbtddDir99Ub+m9R VtflbclPJzw3mdbxLW1rjkPzMctL1F8O9HBx/9a0bounhJy/ClNejFvDFUyOplp8CUpD HBggxzbFyN3SguYMArOzXq/0YMOcnZfnA0fF1E9fZdCmD/PCdWZBs7PIjTSYr+JZvvwj +y6PXLZRlf0kk3Wbmy6jDtkvxw20yadkotBaZrJ/uTHHsX91oazgpuD/IE58AcWbOqCh 4Mvg== X-Gm-Message-State: AC+VfDye/hQHgifI4u6beb4Zrdj19UXed0rGYQNUC+9IrCUI0vhAKksv CoS7/JZJScpqfSpsmMwxhvvokM82zR8= X-Google-Smtp-Source: ACHHUZ7FQZr2z6+ICIQSOox0kqr42EZW4RiY4z2p1sybFGT/CgLk0FIij33+D3SQ6IPNkTOTl5aK2w== X-Received: by 2002:a25:9bc3:0:b0:ba7:4c49:95e with SMTP id w3-20020a259bc3000000b00ba74c49095emr78431ybo.7.1685994237932; Mon, 05 Jun 2023 12:43:57 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:98a1:5902:8a60:7f77? ([2600:1700:bd2c:b810:98a1:5902:8a60:7f77]) by smtp.googlemail.com with ESMTPSA id k187-20020a253dc4000000b00bad08dd4a13sm2794520yba.59.2023.06.05.12.43.57 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 05 Jun 2023 12:43:57 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------hifzp2PI4n1jafDfYApxE8Sx" Message-ID: <9de67b4a-6667-143b-05d4-448e93985345@gmail.com> Date: Mon, 5 Jun 2023 14:43:57 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.10.0 Subject: Re: Composite type: Primary Key and validation Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------hifzp2PI4n1jafDfYApxE8Sx Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Consider applying "database normalization" to the schema so that the columns are only in one table, and then pass around synthetic keys. On 6/5/23 10:06, Lorusso Domenico wrote: > Thank's, you are right, I've the same doubts. > A composite type is useful because I've to add all these information on > many tables and because it more easy to pass all these informations to > functions that have to ensure the right format and evaluation. > > Talking about first point I could use the "table inheritance", but I've to > reset all constraints for each table :-( > > Il giorno lun 5 giu 2023 alle ore 16:02 Laurenz Albe > ha scritto: > > On Mon, 2023-06-05 at 11:49 +0200, Lorusso Domenico wrote: > > I've a couple of questions about composite type. > > Suppose this composite type: > > CREATE TYPE my_type AS ( > >     user_ts_start My_start_timestamp, > >     user_ts_end My_end_timestamp, > >     db_ts_start My_start_timestamp, > >     db_ts_end My_end_timestamp, > >     audit_record jsonb > > ); > > My_start_timestamp is a domain of timestamp with default as now(). > > My_end_timestamp is a domain of timestamp with default as infinite > > > >    1. May I use user_ts_start and/or db_ts_start has part of Primary > Key of a table that contains a field of my_type? > >    2. to add an overall check constraint on the entire composite > type, could be a valid > >       approach to create a domain based on my_type and add a custom > function to validate it? (check_my_type(VALUE)). > >       In this way I've a dominan of composite type that contain > others domain... what do you think? > > Avoid using composite types as data types for a table column. > It adds complexity for no clear gain. > > Yours, > Laurenz Albe > > > > -- > Domenico L. > > per stupire mezz'ora basta un libro di storia, > io cercai di imparare la Treccani a memoria... [F.d.A.] -- Born in Arizona, moved to Babylonia. --------------hifzp2PI4n1jafDfYApxE8Sx Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Consider applying "database normalization" to the schema so that the columns are only in one table, and then pass around synthetic keys.

On 6/5/23 10:06, Lorusso Domenico wrote:
Thank's, you are right, I've the same doubts.
A composite type is useful because I've to add all these information on many tables and because it more easy to pass all these informations to functions that have to ensure the right format and evaluation.

Talking about first point I could use the "table inheritance", but I've to reset all constraints for each table :-(

Il giorno lun 5 giu 2023 alle ore 16:02 Laurenz Albe <laurenz.albe@cybertec.at> ha scritto:
On Mon, 2023-06-05 at 11:49 +0200, Lorusso Domenico wrote:
> I've a couple of questions about composite type.
> Suppose this composite type:
> CREATE TYPE my_type AS (
>     user_ts_start My_start_timestamp,
>     user_ts_end My_end_timestamp,
>     db_ts_start My_start_timestamp,
>     db_ts_end My_end_timestamp,
>     audit_record jsonb
> );
> My_start_timestamp is a domain of timestamp with default as now().
> My_end_timestamp is a domain of timestamp with default as infinite
>
>    1. May I use user_ts_start and/or db_ts_start has part of Primary Key of a table that contains a field of my_type?
>    2. to add an overall check constraint on the entire composite type, could be a valid
>       approach to create a domain based on my_type and add a custom function to validate it? (check_my_type(VALUE)).
>       In this way I've a dominan of composite type that contain others domain... what do you think?

Avoid using composite types as data types for a table column.
It adds complexity for no clear gain.

Yours,
Laurenz Albe


--
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

--
Born in Arizona, moved to Babylonia.
--------------hifzp2PI4n1jafDfYApxE8Sx--