PostgreSQL database vs. schema issue

[fluby]

I have just recently learned that PostgreSQL has a different definition of 'database' than MySQL. In PostgreSQL, you cannot get tables in different databases to talk to each other, as you can in MySQL. But, in PostgreSQL, you can organize data into schemas within a database. Tables in different schemas in the same database can talk to each other. The Retriever only allows you to select the database (used as it is in MySQL). When you specify a database in PostgreSQL, the Retriever creates a schema and places the table within that. This is not a big deal, as tables can be moved around, and it might in fact be the best organizational solution. The alternative is to provide the feature to place a table or set of tables directly into a specified schema in a specified database. I'm not sure what the answer here is, and this shouldn't be high on the priority list. Just thought I'd share.

[ethanwhite]

Thanks for the feedback Kate. Can you think of a use case where placing tables from different datasets into the same schema would be better than keeping them in different schemas?

[fluby]

It's really just a matter of organizational tastes, I think. In trying to design a database structure for my team, I have a number of schemas, with one of them being 'existing_data'. Purely in the interest of organization, I would like external data sets that have one (or up to 3) tables to be placed in that schema. Lots of schemas = more clutter, and more coding.

[ethanwhite]

That certainly seems reasonable.

[bendmorris]

Sounds good. I'm not a postgres user, so I don't really have a feel for how people like to organize their data. It would be easy to give users the option of specifying a schema and naming the tables something like "dataset_table" similar to how it's done in sqlite and access.

[bendmorris]

You can now specify a database name format (or schema format in postgres) and table name format using the connection settings or from the command line.