pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion on Table creation
8+ messages / 5 participants
[nested] [flat]
* Question on Table creation
@ 2024-02-20 21:19 sud <suds1434@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: sud @ 2024-02-20 21:19 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Question on Table creation
@ 2024-02-20 21:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: sud <suds1434@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2024-02-20 21:53 UTC (permalink / raw)
To: sud <suds1434@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Question on Table creation
@ 2024-02-20 23:59 Adrian Klaver <adrian.klaver@aklaver.com>
parent: sud <suds1434@gmail.com>
1 sibling, 2 replies; 8+ messages in thread
From: Adrian Klaver @ 2024-02-20 23:59 UTC (permalink / raw)
To: sud <suds1434@gmail.com>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Question on Table creation
@ 2024-02-21 05:12 Lok P <loknath.73@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 1 reply; 8+ messages in thread
From: Lok P @ 2024-02-21 05:12 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; sud <suds1434@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
*"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;
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Question on Table creation
@ 2024-02-23 05:01 sud <suds1434@gmail.com>
parent: Lok P <loknath.73@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: sud @ 2024-02-23 05:01 UTC (permalink / raw)
To: Lok P <loknath.73@gmail.com>; Adrian Klaver <adrian.klaver@aklaver.com>; Greg Sabino Mullane <htamfids@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Thank you so much. This really helped.
Regards
Sud
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Question on Table creation
@ 2024-02-27 19:53 sud <suds1434@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 1 reply; 8+ messages in thread
From: sud @ 2024-02-27 19:53 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Question on Table creation
@ 2024-02-28 20:28 veem v <veema0000@gmail.com>
parent: sud <suds1434@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: veem v @ 2024-02-28 20:28 UTC (permalink / raw)
To: sud <suds1434@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general <pgsql-general@lists.postgresql.org>
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.
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Question on Table creation
@ 2024-02-29 20:29 sud <suds1434@gmail.com>
parent: veem v <veema0000@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: sud @ 2024-02-29 20:29 UTC (permalink / raw)
To: veem v <veema0000@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>
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.
https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md
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.
>
>>
>>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-02-29 20:29 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-20 21:19 Question on Table creation sud <suds1434@gmail.com>
2024-02-20 21:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-20 23:59 ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-02-21 05:12 ` Lok P <loknath.73@gmail.com>
2024-02-23 05:01 ` sud <suds1434@gmail.com>
2024-02-27 19:53 ` sud <suds1434@gmail.com>
2024-02-28 20:28 ` veem v <veema0000@gmail.com>
2024-02-29 20:29 ` sud <suds1434@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox