Re:PostgreSQL Schema Design
by combatentropy on Monday March 30, 2020 @11:15AM
(#22728650)
> In the SQL standard, the notion of objects in the same schema being owned by different users does not exist. Moreover, some implementations do not allow you to create schemas that have a different name than their owner. In fact, the concepts of schema and user are nearly equivalent in a database system that implements only the basic schema support specified in the standard.
--- https://www.postgresql.org/docs/current/ddl-schemas.html
Re:PostgreSQL Schema Design
by sbuttgereit on Monday March 30, 2020 @12:06AM
(#22725136)
PostgreSQL kinda mimics the oracle way, though it only goes so far. The default definition of PostgreSQL's search_path is:
"$user", public
So if a schema was named the same as the user, you'd automatically have objects available without qualification.
I spent about a fair amount of time working with Oracle and it forces the paradigm of a schema meaning user much more than does PostgreSQL... though with some effort you can make it work somewhat like the logical namespacing capability that PostgreSQL schemas are used for.
It makes sense that the owner of an object is like saying the object belongs to a namespace, but coupling user to that takes getting used to in systems that do that.
The ANSI SQL standard (I think part 11) describes the formal definition of schema.