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@xxxxxxxxxxx
