Question on Table creation

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.

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: