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 1q6B24-0002Js-0h for pgsql-general@arkaria.postgresql.org; Mon, 05 Jun 2023 14:18:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q6B21-0001uU-Tm for pgsql-general@arkaria.postgresql.org; Mon, 05 Jun 2023 14:18:01 +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 1q6B21-0001uL-Fh for pgsql-general@lists.postgresql.org; Mon, 05 Jun 2023 14:18:01 +0000 Received: from mail-yb1-xb2f.google.com ([2607:f8b0:4864:20::b2f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q6B1u-000O8f-0W for pgsql-general@lists.postgresql.org; Mon, 05 Jun 2023 14:18:00 +0000 Received: by mail-yb1-xb2f.google.com with SMTP id 3f1490d57ef6-bb131cd7c4aso4209162276.1 for ; Mon, 05 Jun 2023 07:17:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1685974672; x=1688566672; h=content-transfer-encoding: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=lvA1/aQ13BeFlR0VS1XvcTJzU++paSfGmNIPZ3U/CqI=; b=eZkw1Bv05hZZ6jeu1JFnkR99dU+0uUz1MTHED7BZ5M0VatXo6in8F8DFCWmxtAVK+k b6BlwpftcjCA0k/d3AN5jWUH8QmMm6JLDqexMRJeQ99IKw8ciSXLvFa8R+KTfeoDS1r4 2ul4uLUUqVdCNgYvjDkuWQpOTMVT7TcIFm8pzPQMfIUiBtXZaFAq4ecfHoUgSVdG54mV 3Oj4OWproc+ISaxgGPlGjQeJTiNJe+tb4M/KgDZFvtGoO87QvKAPF3au5gY+9b0tR56k fEmxytGPmzBAGXuhT5420sW26czqCStd/ZWuRh+DMm84arN0wxsF+k8mRDYP+x4ouM7P MVHQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1685974672; x=1688566672; h=content-transfer-encoding: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=lvA1/aQ13BeFlR0VS1XvcTJzU++paSfGmNIPZ3U/CqI=; b=gbNyRKKEltF1MkENSjpYdC8jh2rPL8OmRXj/3wlJxIlTpUV4kHm80XQattVxstwhoP x/XCIfMECQQb8ztHQQGWHxJlvLMyn2lcetlsKNvP5ZqkhjKNDXCwpqiFkKzLvrpYbHgD +8C4TqSfh++FzxALW3EE+hBtourTKd2VDmPCyoSBgvPozDRA2/ODOeb5a1EGk73lDvbS LYpqnPQYSg6gVoj28KGuu03gUJmId385+xP1sFWutT+tjhjH9GWd2uJ7aM/Xyjz6f/4h W9Kcw5Czox07XXDkUC3QnrLpDyFFKcja98MiIZbKYE8SsOPOkYhq/LPnUkyoLqZUTxkS xEoQ== X-Gm-Message-State: AC+VfDwKn81ALvxKVN1Skkln+/ptywHZwQRJR+Yh4eUfzgqD2i6Qxrms 6+GfYLygV05D5AsUsCs0nqoLVjANSqo= X-Google-Smtp-Source: ACHHUZ7emBRqGW2YKSZncsWg6Q5Y9PWlLfmZ1y6yLr3NdskwcFilysADmEsnvq8vsYEOdIUMpgn/Ig== X-Received: by 2002:a25:ae15:0:b0:ba6:cf6f:960b with SMTP id a21-20020a25ae15000000b00ba6cf6f960bmr11046332ybj.27.1685974672097; Mon, 05 Jun 2023 07:17:52 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:1cf:a1ef:84f7:c68a? ([2600:1700:bd2c:b810:1cf:a1ef:84f7:c68a]) by smtp.googlemail.com with ESMTPSA id y8-20020a25ad08000000b00bb1445a4069sm2631961ybi.24.2023.06.05.07.17.51 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 05 Jun 2023 07:17:51 -0700 (PDT) Message-ID: <1ccdbfca-5c5d-2f20-bb53-9073ee9a0b21@gmail.com> Date: Mon, 5 Jun 2023 09:17:49 -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: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 6/5/23 09:02, Laurenz Albe wrote: > 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. Isn't now() also a bad idea, since it's the "now" at the start of the transaction? Better to use clock_timestamp(). -- Born in Arizona, moved to Babylonia.