PostgreSQL :: Catalog Query

Yogi S schrieb am 18.07.2018 um 12:22:

this might look quite naive. but I see quite a difference in between other RDBMS database vs PostgreSQL.
usually the categorization of the entities are like what is stated below. I am sure mysql follows the same.

Cluster --> Catalog --> database --> Schema --> tables

but in postgreSQL the catalog <--> database relation is quite one-to-one.
Question is, is it safe to assume that postgreSQL does not have any /*catalog concept*/ as such.

As far as the SQL standard is concerned Postgres' databases do map to the term "catalog" .

The only difference (or deviation from the standard) is, that they are no real "namespaces".
In the SQL standard a fully qualified (table) name consists of three parts:

catalog.schema.table

however in Postgres the catalog (while it exists as a "thing") is not allowed to be used in a fully qualified name.

So in Postgres it's indeed:

"Cluster" (or "Instance") -> Databases -> Schemas -> Tables

MySQL only has databases (or catalogs) but no schemas - or schemas, but no catalogs as "database" is a synonym for "schema" there.
And MySQL has no "cluster" the way the term is used in Postgres. So in MySQL it's only

"Instance" -> Databases -> Tables

Note that in Oracle this is again completely different. And in DB2.

The only DBMS I know of that actually supports these two namespaces (catalogs, schemas) completely is SQL Server (a catalog is called a "database" there)

But this shouldn't be posted or discussed on the "bugs" mailing list, as it is not a bug.

Thomas Kellerer <spam_eater@gmx.net> writes:

As far as the SQL standard is concerned Postgres' databases do map to the term "catalog" .
The only difference (or deviation from the standard) is, that they are no real "namespaces".
In the SQL standard a fully qualified (table) name consists of three parts:
catalog.schema.table
however in Postgres the catalog (while it exists as a "thing") is not allowed to be used in a fully qualified name.

You can in fact use the database/catalog name within qualified names:

select * from mydb.someschema.sometable;

But it's purely for pro forma standards compliance. If you give a three-
part name and the first part isn't the current database, you'll just get
an error. In theory we might someday extend this to allow cross-database
accesses, but I wouldn't hold my breath waiting.

In any case, the fact that different DBMSes implement different subsets
of the spec isn't a bug, either ours or theirs.

regards, tom lane