What is the proper syntax in Postgres for addressing tables?

Question

In sql-server, one can connect to the master db and write a statement in the below syntax to query any table:

SELECT * FROM [database].[dbo].[table]

I've ran some google searches for "postgres full table address" and "postgres object hierarchy" but to no avail. See this question for more on how this is done in sql-server.

Are operations like this possible in Postgres (I'm running version 9.3)?

If so, please explain how with a code example.
If not, please explain why not.

it's the same, database.schema.table. did you even try it before posting? — Matthew Plourde, Feb 03 '14 at 18:12
Yup, I tried and receive the following error: ERROR: cross-database references are not implemented: "DBname.public.TABLEname" — nicholsonjf, Feb 03 '14 at 18:48
Also, why the down vote? I know this is a rookie question, but for someone new to Postgres I think it's a valid thing to ask here. — nicholsonjf, Feb 03 '14 at 18:50
not my down vote. If you've tried something and received an error, it's best to include that in your post. — Matthew Plourde, Feb 03 '14 at 18:53

score 1 · Accepted Answer · answered Feb 03 '14 at 18:51

From Table Expressions in the manual:

A table reference can be a table name (possibly schema-qualified), or a derived table such as a subquery, a table join, or complex combinations of these

That's it. No database name is allowed.

If a session has to refer to tables in a different database than it's connected to, it should use a dblink, or a foreign table through a data wrapper. In PostgreSQL, the databases within the same instance are deeply insulated from each other. When different namespaces are needed, schemas are used as opposed to databases.

What is the proper syntax in Postgres for addressing tables?

1 Answers1