Re: Understanding Schema's

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. Unfortunately
most tools only know how to ask for a database name, not a schema name,
and there's no way to set the search_path in (AFAIK) a JDBC URL. So in
this way, PostgreSQL schema are very _unlike_ mysql databases.

--
Craig Ringer