Re: Understanding Schema's

On 2010-12-15, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> wrote:
> On 12/15/2010 08:08 AM, Carlos Mennens wrote:
>> I've recently switched from MySQL& have read the documentation for
>> 'schema's' however I guess I'm just not at that level or really daft
>> when it comes to database design.
>
> In terms of the way they work and their operation, PostgreSQL "schemas"
> are in many ways much more like MySQL "databases" than PostgreSQL
> "databases" are. Schema separate objects (functions, tables, views,
> types, etc) into namespaces, but can refer to each other if permissions
> allow. You can GRANT and REVOKE access to schema the same way you can
> MySQL databases. You can "change" schema using "SET search_path" in much
> the same way you'd "USE" a database in MySQL.
>
> PostgreSQL also has "databases" which are largely isolated from each
> other. They cannot refer to objects in other databases, and a backend
> connected to one database cannot switch to another one. You cannot "USE"
> or otherwise change databases on a backend; the psql "\c" command that
> appears to do this really disconnects and reconnects to a new database.

> It'd be nice if PostgreSQL offered more convenient ways to set an
> initial schema for new connections, because for some use cases it'd be
> quite handy to use a single database with many schema.

the first schema on the search path is the one that matches the name
of the database user. (although you can override this behaviour using
alter user or alter database - basically any option you can set
temporarily using "set ... " you can set permanently using "alter user
... set ..." )

eg: alter user jasen set search_path to thatschema;

> Unfortunately most tools only know how to ask for a database name

they also ask for a user name, in most cases having the different
services connect using different roles is not a bad thing.

--
⚂⚃ 100% natural