pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: sud <suds1434@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Question on Table creation
Date: Tue, 20 Feb 2024 15:59:27 -0800
Message-ID: <c8c8a46e-b295-4afe-a636-8af30d99b898@aklaver.com> (raw)
In-Reply-To: <CAD=mzVVsTF1VF2UxDehtaTrvgA8tWfaPfGaOmOiGsSQFkEGvxg@mail.gmail.com>
References: <CAD=mzVVsTF1VF2UxDehtaTrvgA8tWfaPfGaOmOiGsSQFkEGvxg@mail.gmail.com>

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






view thread (8+ messages)  latest in thread

Message-ID: <c8c8a46e-b295-4afe-a636-8af30d99b898@aklaver.com>
Permalink:  ../c8c8a46e-b295-4afe-a636-8af30d99b898@aklaver.com/
Also on:    postgresql.org/message-id/c8c8a46e-b295-4afe-a636-8af30d99b898@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, suds1434@gmail.com
  Subject: Re: Question on Table creation
  In-Reply-To: <c8c8a46e-b295-4afe-a636-8af30d99b898@aklaver.com>
  References: <CAD=mzVVsTF1VF2UxDehtaTrvgA8tWfaPfGaOmOiGsSQFkEGvxg@mail.gmail.com> <c8c8a46e-b295-4afe-a636-8af30d99b898@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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