Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qcaO8-00Fqh6-WE for pgsql-general@arkaria.postgresql.org; Sat, 02 Sep 2023 23:50:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qcaO7-00CqYs-HV for pgsql-general@arkaria.postgresql.org; Sat, 02 Sep 2023 23:50:47 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qcaO7-00CqYf-2o for pgsql-general@lists.postgresql.org; Sat, 02 Sep 2023 23:50:46 +0000 Received: from mout-u-107.mailbox.org ([2001:67c:2050:101:465::107]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qcaNz-002Pl5-59 for pgsql-general@lists.postgresql.org; Sat, 02 Sep 2023 23:50:45 +0000 Received: from smtp2.mailbox.org (smtp2.mailbox.org [10.196.197.2]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-107.mailbox.org (Postfix) with ESMTPS id 4RdWqX0Cvkz9sGV; Sun, 3 Sep 2023 01:50:32 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ewie.name; s=MBO0001; t=1693698632; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=2sa4kIn9hdksoEGgwjTpWRvmZmBKMnENXTgX5Oq6DJk=; b=Bqa3/65bi8ragEWnboGIYQp06ARCQdSEU0tvgUfp0/vJH0PS7q7B9FgkFF6RCifuquRG6B Uk++5uNFiQnjN6myf78wOAe6ULoxuzPq5dOZe1rur2Q2Gaaodn5SlCyGNV6TGxkR6n+NV5 80B/CipteQxJ+gPozYUl7J4356GpJgAClP+nX76QR0yqBiqRZq7+ArX1QiD3Y6/Wewnsjh PNQB1Ayx7uj+JPFiMD09CJiI3ZmlyOHu757V8z8ZZu3HXa8scUWsZ+oKjXHDtnYM6yIlX2 6MDnQXuD7PWZg18G+vXp/RPN9bLriHmj98EKGjMbwyoDKtV3hBw8J87uGbNzdg== Date: Sun, 3 Sep 2023 01:50:31 +0200 (CEST) From: Erik Wienhold To: veem v , pgsql-general Message-ID: <430765976.88455.1693698631044@office.mailbox.org> In-Reply-To: References: Subject: Re: Question on Partition key MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 03/09/2023 00:35 CEST veem v wrote: > We are trying to create a monthly range partition table , partitioned on > column PART_DATE. This will hold Orders and part_date is nothing but invo= ice > date. Some Team mates are asking to use the "PART_DATE" column as data ty= pe > "INTEGER" with "YYYYMM"=C2=A0format [...] Why do your team mates favor integer over date? > Want to know experts' views on this. If the data type of the partition ke= y > matters here or not? Both integer and date are stored as 4 bytes. There should be no difference regarding index size. I don't know if the data type makes a difference in partition pruning performance in this case, but I'd be surprised if it were the case. > Or if there is any downside of each approach in future? The downside of integer is that it allows invalid dates (e.g. 202313) unles= s you also add check constraints. But then just use date if you want to stor= e dates. You get input validation and can use the date operators and functio= ns that Postgres offers. -- Erik