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.klaver@aklaver.com