Question on Table creation

[sud <suds1434@xxxxxxxxx>]

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 "Schema_Owner" 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  "Schema_Owner"  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?

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.

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.

CREATE TABLE "Schema_Name"."PARENT"
(
"PARENT_IDENTIFIER" varchar(36)  NOT NULL ,
"PARENT_CREATE_TIMESTAMP" date  NOT NULL ,
CONSTRAINT "PARENT_PK" PRIMARY KEY ("PARENT_IDENTIFIER","PARENT_CREATE_TIMESTAMP")
);

ALTER TABLE "Schema_Name"."PARENT" OWNER TO "Scheme_Owner";
COMMENT ON TABLE "Schema_Name"."PARENT" IS 'table comment';
COMMENT ON COLUMN "Schema_Name"."PARENT"."PARENT_IDENTIFIER" IS 'Column comment';

***********

CREATE TABLE "Schema_Name"."CHILD"
(
"CHILD_IDENTIFIER" varchar(36)  NOT NULL ,
"CHILD_STATUS_CODE" varchar(9)  NOT NULL ,
"CHILD_EVENT_STATUS_TIMESTAMP" date  NOT NULL ,
"CHILD_CREATE_TIMESTAMP" date  NOT NULL
CONSTRAINT "XPKCHILD_STATUS" PRIMARY KEY ("CHILD_IDENTIFIER","CHILD_EVENT_STATUS_TIMESTAMP","CHILD_CREATE_TIMESTAMP"),
CONSTRAINT "R_12" FOREIGN KEY ("CHILD_IDENTIFIER","CHILD_CREATE_TIMESTAMP") REFERENCES "Schema_Name"."PARENT"("PARENT_IDENTIFIER","PARENT_CREATE_TIMESTAMP")
ON UPDATE RESTRICT
ON DELETE RESTRICT
);

CREATE INDEX "XIF1CHILD_STATUS" ON "Schema_Name"."CHILD_STATUS"
(
"CHILD_IDENTIFIER",
"CHILD_CREATE_TIMESTAMP"
);

ALTER TABLE "Schema_Name"."CHILD_STATUS" OWNER TO "Scheme_Owner";
COMMENT ON TABLE "Schema_Name"."CHILD_STATUS" IS 'table comment';
COMMENT ON COLUMN "Schema_Name"."CHILD_STATUS"."CHILD_IDENTIFIER" IS 'column comment';

Regards

Sud