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 1rca1P-000sTK-BP for pgsql-general@arkaria.postgresql.org; Tue, 20 Feb 2024 23:59:36 +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 1rca1N-0022Sa-Vg for pgsql-general@arkaria.postgresql.org; Tue, 20 Feb 2024 23:59:34 +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 1rca1N-0022SQ-2h for pgsql-general@lists.postgresql.org; Tue, 20 Feb 2024 23:59:34 +0000 Received: from fout1-smtp.messagingengine.com ([103.168.172.144]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rca1J-0006VD-5C for pgsql-general@lists.postgresql.org; Tue, 20 Feb 2024 23:59:32 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailfout.nyi.internal (Postfix) with ESMTP id 100FD13800B8; Tue, 20 Feb 2024 18:59:29 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute1.internal (MEProxy); Tue, 20 Feb 2024 18:59:29 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm3; t=1708473569; x=1708559969; bh=Gawo+8IYcPyd1CoS/WLSNblukxFq5O0OYagjhAAyxmY=; b= QhiPlUdVvvsYsr6vhbL/DIHVxkCxqmARposBH7MluN0jRB3sbm3FeIKRkA+UAiv/ 0tJQZtNHWQFgHclmnxwI9FGGageGWrMG3UHh3iSKxyYitDUqLRwwwWfSXAQi4nVO eVIM+rkYY58Llrn56H9qpdQyDjXRz5uluNnn2MFzz8N5W9a7r9TN/n8diAH3+5IM 3V28n6lMFLWyltfTa8HkZhOeL2LaQ0HNnhIqx/gj+wWo4+V0lcA/z8q+Gsf8HzeK aBbPo+Z3AM8di/SXPbg7HGPEndJdExTdWT626H8hCkbLaFPzTP6BCsVnvg8OCXek g7MWPkyLCDc0kNcD1wuQgw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1708473569; x= 1708559969; bh=Gawo+8IYcPyd1CoS/WLSNblukxFq5O0OYagjhAAyxmY=; b=P WJr/f8JfCVwZuYgj4H1RPnz7IyxDNQlArqyURmY0UWnFES5s6WIi9OgHYiwlHgKC u3fSc53/s6SCWz4Y9hliEpQmDVMn+sthckOE8bIu3SuwmYsggO5pg/7opsA8kEEa TYddzAq7cLRZlauApWJrGpTuDEf1iTlBIv5+LbqgKZN15FjTp3cI+yzHiFNmBLIb c2CbwpWsdth7bH1Fj500Yy+DOE6yvXHJ526KOLUZsgyVOHY7X6W+7LCVd8jcAKEs m0KbtXdKqhLQE9Y/XeAQ7pQxhaIz6XvUPHH9+7wt7KdN439qCdjsg4cFewbuKTGG sTAJ5sEAW9Wrpuvpt8sRw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrfedugdduiecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecunecujfgurhepkfffgggfuffvfhfhjggtgfesthekre dttddvjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhl rghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpeehfeeiteeuhe fhjefgheegteeltedvueduudduveekvdeuheehheelffegudegieenucffohhmrghinhep phhoshhtghhrvghsqhhlrdhorhhgpdhgihhthhhusgdrtghomhdpphhgpghprghrthhmrg hnrdhmugenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhm pegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 20 Feb 2024 18:59:28 -0500 (EST) Message-ID: Date: Tue, 20 Feb 2024 15:59:27 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Question on Table creation To: sud , pgsql-general@lists.postgresql.org References: Content-Language: en-US From: Adrian Klaver 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 2/20/24 13:19, sud wrote: > Hi, > We are newly creating tables in postgres 15.4 version and we got the DDL > auto generated from one of the tools and they look something like below. > (note- I have modified the exact names to some dummy names.) These are > all failing because the schema which already exists in the database > having name 'schema_name' which is all lower case.So then i modified the > script to remove the double quotes from all the table/column/schema > names, as it seems postgres makes things case sensitive if they are put > in quotes. > > But then encountered the opposite, i.e. some places where it's showing > the object already created in the database as Upper case or mixed case > like schema owner which is showing as "*S*chema_*O*wner" as I see in the > information_schema.schemata data dictionary. And here the scripts > failing if removing the quotes from the schema owner. > > So to make it standardized, we have few questions around these > > 1)In this situation , do we have to drop the "*S*chema_*O*wner"  and > recreate it with all small letters? And then create the schema with > small letters again? > > 2)As it seems keeping mixed sets will be troublesome while accessing > them at a later stage, so is it advisable to not to use quotes while > creating key database objects like > schema/table/views/columns/indexes/constraints in postgres? Is there any > other standard we should follow in postgres so as to not have such > issues in future? See Tom Lanes post. I would add if you use tools like ORM's, GUI clients or libraries you might find they double quote all identifiers by default. I found it safest to use lower case at all times to insure that this how the identifier ends up even if goes through one of those tools. > > 3)"Comment" on table is not accepted in the same "create table" > statement but as a separate statement post table creation. Hope that is > how it works in postgres. That is what the docs say: https://www.postgresql.org/docs/current/sql-comment.html > > 4)Never created or maintained any partition table in postgres. Here we > want to daily range partition the both tables based on column > "PARENT_CREATE_TIMESTAMP", so is it advisable to create the individual > future partitions post table creation manually or through some automated > job or we should do it using pg_partman extension? I do see a lot of > docs around pg_partman. Would you suggest any specific doc which guides > us to do it in an easy way. The 'easy' way is the one you understand and can maintain. pg_partman does a lot of the boiler plate for you so there is that. The other side is you need to read and understand: https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md to verify it actually going to do what you want. > Regards > Sud -- Adrian Klaver adrian.klaver@aklaver.com