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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
