Question on Table creation

From: sud <suds1434(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Question on Table creation
Date: 2024-02-20 21:19:20
Message-ID: CAD=mzVVsTF1VF2UxDehtaTrvgA8tWfaPfGaOmOiGsSQFkEGvxg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2024-02-20 21:53:01 Re: Question on Table creation
Previous Message Justin 2024-02-20 17:59:07 Re: Partitioning options