Re: Question on Table creation

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(dot)klaver(at)aklaver(dot)com