Обсуждение: Question on Table creation
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?
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';
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
sud <suds1434@gmail.com> writes: > 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. I'd advise reading this section carefully: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS Notably, the advice to "always quote a particular name or never quote it" could be a useful rule for you here. regards, tom lane
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
"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?"
As per above question goes, I believe OP is not required to drop and recreate but has to just Rename the user something as below and that would remove the case sensitiveness making all lower case. and the same thing can be done for the schema too.
ALTER USER "Schema_Owner" RENAME TO Schema_Owner ;
ALTER schema "Schema_Name" RENAME TO Schema_Name;
Thank you so much. This really helped.
Regards
Sud
On Wed, Feb 21, 2024 at 5:29 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> 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
While testing the pg_partman extension I see it by default creates a "default partition" even if we provide the parameter p_start_partition as "current date". But if someone purposely doesn't want to have the default partitions , so as to avoid any unwanted data entering the table , which may be required from a data quality perspective. Is there any option in the create_parent function to achieve that? Or do we have to manually drop the default partition after getting this created through create_parent function?
I am not able to understand the significance of the "p_template_table" parameter. When we create the partitions without passing this parameter the template table gets created automatically in the partman schema. Checking the details of the template table, it doesn't show any indexes or constraints present in it, but still the child partitions get created with all the necessary indexes and constraints as those are there defined for the main table. So wondering , in what real life scenario do we really need the template table to be defined and how will it help if the partitions are different then from the main table structure?
Lastly , the template table is by default created in the partman schema but it also works without error, if we pass the template table to be created in the application schema. So is there any downside of having the template table reside in the application schema?
Thanks And Regards
Sud
On Wed, 28 Feb 2024 at 01:24, sud <suds1434@gmail.com> wrote:
While testing the pg_partman extension I see it by default creates a "default partition" even if we provide the parameter p_start_partition as "current date". But if someone purposely doesn't want to have the default partitions , so as to avoid any unwanted data entering the table , which may be required from a data quality perspective. Is there any option in the create_parent function to achieve that? Or do we have to manually drop the default partition after getting this created through create_parent function?I am not able to understand the significance of the "p_template_table" parameter. When we create the partitions without passing this parameter the template table gets created automatically in the partman schema. Checking the details of the template table, it doesn't show any indexes or constraints present in it, but still the child partitions get created with all the necessary indexes and constraints as those are there defined for the main table. So wondering , in what real life scenario do we really need the template table to be defined and how will it help if the partitions are different then from the main table structure?Lastly , the template table is by default created in the partman schema but it also works without error, if we pass the template table to be created in the application schema. So is there any downside of having the template table reside in the application schema?
As per my understanding , you can't control the creation of the default partition. If you somehow don't want to keep it then, you can drop it post creation of the partition for the first time i.e after the create_parent function call.
Template_table is necessary when someone needs the child partitions to have a difference in structure as compared to main table like difference in indexes or constraints. But I think the number and data types of columns should be the same in main table and template tables. I have not tried though.
I think keeping a template table in an application schema is not a good idea considering the fact that it's not a business table but a technical one, and nobody should accidentally alter this.
Thank you so much.
I tested and it worked as you mentioned i.e the template table is helping to add the extra indexes or constraints to the child table/partitions (by inheriting those from the template table if defined), those are not defined there in the main table. Also dropping the default partition does no harm to the new partition creation through the automatic maintenance job.
Though I am unable to visualize the situation in which we want the child table/partitions to be having additional indexes or constraints as compared to the main table. But I also see that the template table will be phased out quickly. So I believe it's better to just go with the default template table with current Pg version 16.
IMPORTANT NOTES:
The template table feature is only a temporary solution to help speed up declarative partitioning adoption. As things are handled better in core, the use of the template table will be phased out quickly from pg_partman. If a feature that was managed by the template is supported in core in the future, it will eventually be removed from template management in pg_partman, so please plan ahead for that during major version upgrading if it applies to you.
On Thu, Feb 29, 2024 at 1:58 AM veem v <veema0000@gmail.com> wrote:
On Wed, 28 Feb 2024 at 01:24, sud <suds1434@gmail.com> wrote:While testing the pg_partman extension I see it by default creates a "default partition" even if we provide the parameter p_start_partition as "current date". But if someone purposely doesn't want to have the default partitions , so as to avoid any unwanted data entering the table , which may be required from a data quality perspective. Is there any option in the create_parent function to achieve that? Or do we have to manually drop the default partition after getting this created through create_parent function?I am not able to understand the significance of the "p_template_table" parameter. When we create the partitions without passing this parameter the template table gets created automatically in the partman schema. Checking the details of the template table, it doesn't show any indexes or constraints present in it, but still the child partitions get created with all the necessary indexes and constraints as those are there defined for the main table. So wondering , in what real life scenario do we really need the template table to be defined and how will it help if the partitions are different then from the main table structure?Lastly , the template table is by default created in the partman schema but it also works without error, if we pass the template table to be created in the application schema. So is there any downside of having the template table reside in the application schema?As per my understanding , you can't control the creation of the default partition. If you somehow don't want to keep it then, you can drop it post creation of the partition for the first time i.e after the create_parent function call.Template_table is necessary when someone needs the child partitions to have a difference in structure as compared to main table like difference in indexes or constraints. But I think the number and data types of columns should be the same in main table and template tables. I have not tried though.I think keeping a template table in an application schema is not a good idea considering the fact that it's not a business table but a technical one, and nobody should accidentally alter this.