Q's on pgsql

Question

Hi, I'm a newbie to pgsql. I have few questionss on it:

1) I know it is possible to access columns by <schema>.<table_name>, but when I try to access columns like <db_name>.<schema>.<table_name> it throwing error like

Cross-database references are not implemented

How do I implement it?

2) We have 10+ tables and 6 of have 2000+ rows. Is it fine to maintain all of them in one database? Or should I create dbs to maintain them?

3) From above questions tables which have over 2000+ rows, for a particular process I need a few rows of data. I have created views to get those rows. For example: a table contains details of employees, they divide into 3 types; manager, architect, and engineer. Very obvious thing this table not getting each every process... process use to read data from it... I think there are two ways to get data SELECT * FROM emp WHERE type='manager', or I can create views for manager, architect n engineer and get data SELECT * FROM view_manager

Can you suggest any better way to do this?

4) Do views also require storage space, like tables do?

Thanx in advance.

Answer 1

1: A workaround is to open a connection to the other database, and (if using psql(1)) set that as your current connection. However, this will work only if you don't try to join tables in both databases.

Answer 2

1) That means it's not a feature Postgres supports. I do not know any way to create a query that runs on more than one database.

2) That's fine for one database. Single databases can contains billions of rows.

3) Don't bother creating views, the queries are simple enough anyway.

4) Views don't require space in the database except their query definition.

Answer 3

1. Cross Database exists in PostGreSQL for years now. You must prefix the name of the database by the database name (and, of course, have the right to query on it). You'll come with something like this:

   SELECT alias_1.col1, alias_2.col3 FROM table_1 as alias_1, database_b.table_2 as alias_2 WHERE ...

   If your database is on another instance, then you'll need to use the dblink contrib.

2. This question doe not make sens. Please refine.
3. Generally, views are use to simplify the writing of other queries that reuse them. In your case, as you describe it, maybe that stored proceudre would better fits you needs.
4. No, expect the view definition.

Answer 4

Thank you very much for the answers... Ya for que 2, because within one DB I can access OR select columns, rows from one table to another table... since table size is very big I want to distribute them into different DBs... so that it is possibly easy to take backup of each individual DBs.... Anyway very much thankful to u.... (" ,)