0

From https://stackoverflow.com/a/17943883/156458

in both Postgres and the SQL Standard we have this containment hierarchy:

  • A computer may have one cluster or multiple.
  • A database server is a cluster.
  • A cluster has [catalogs][8]. ( Catalog = Database )
  • Catalogs have [schemas][9]. (Schema = [namespace][10] of tables, and security boundary)
  • Schemas have [tables][11].
  • Tables have [rows][12].
  • Rows have values, defined by [columns][13].

In postgresql, I have a database called students and there is a table called student under it:

postgres=# \c students
You are now connected to database "students" as user "postgres".
students=# \dt;
 public | student | table | postgres

I was wondering if database students is also a catalog?

Where is the schema between students and student?

In general, how can I list

  • all the catalogs and
  • all the schemas under a catalog, and
  • all the tables under a schema?

How can I show

  • the current catalog, and
  • the current schema?

Thanks.

Tim
  • 1
  • 141
  • 372
  • 590

1 Answers1

11

In shortly, I usually explain to my junior teammate when they starting to research about PostgreSQL.

In PostgreSQL:

  • A Database Server is like an Industrial Zone, there will have many Databases-Cluster (Building)
  • Each Database-Cluster (Building) have many Databases. Those are like the floors in Building. The Databases is here as you call the Catalogs. Those Databases-Catalogs (floors) are quite independent with each other, you can not using others materials directly, you must use somethings just like stair, electric wire ... (in database is DBLINK).
  • Okay, next, each Database-Catalogs have many Schemas, which are like many Rooms on your floors. Those Schemas can use the material from each others.
  • Then, each Schemas have many cell elements such as Table, View, Function, Sequence .... All schemas have the same structure.

Now, back to you example:

  • students: is Database (which you call Catalogs)
  • public: is schema.
  • student: is table.

public | student | table | postgres is corresponding with schema | table | kind of table | owner of table

You can list:

  • catalogs (Database) by the command \l in psql or query select * from pg_database;
  • Schemas under a catalog by the command \dn in psql or query select * from information_schema.schemata;
  • Tables under a schemas by the query select * from pg_tables WHERE schemaname = 'Your schema';

You can show:

  • Current Database (Catalogs) by the query select current_database();
  • Current Schema by the query select current_schema;

Please pay attention that PostgreSQL have two system schema call information_schema and pg_catalog, this maybe make you confuse.

The pg_catalog is a system schema. More information.

The system catalogs are the place where a relational database management system stores schema metadata, such as information about tables and columns, and internal bookkeeping information. PostgreSQL's system catalogs are regular tables. You can drop and recreate the tables, add columns, insert and update values, and severely mess up your system that way. Normally, one should not change the system catalogs by hand, there are always SQL commands to do that. (For example, CREATE DATABASE inserts a row into the pg_database catalog — and actually creates the database on disk.) There are some exceptions for particularly esoteric operations, such as adding index access methods.

The information_schema is a system schema. More information.

The information schema consists of a set of views that contain information about the objects defined in the current database. The information schema is defined in the SQL standard and can therefore be expected to be portable and remain stable — unlike the system catalogs, which are specific to PostgreSQL and are modeled after implementation concerns. The information schema views do not, however, contain information about PostgreSQL-specific features; to inquire about those you need to query the system catalogs or other PostgreSQL-specific views.

I hope these information will help you clearly.

Mabu Kloesen
  • 1,248
  • 7
  • 8
  • Thanks. Is `pg_catalog` a system schema or a system catalog? What is it used for? – Tim Jan 18 '18 at 03:55
  • @Tim: both of them are system schema which will be exists on each database. I've updated my answer so let check it again. – Mabu Kloesen Jan 18 '18 at 04:19
  • If `pg_catalog` is a schema, why is it named `catalog`? – Tim Jan 18 '18 at 04:55
  • @Tim: I think you are confusing here. The name `catalog` is the thing you called an unit of database in PostgreSQL. In PostgreSQL we don't have catalog. We call `database`. Maybe somewhere, other person call `catalog` for user from other database system easier to understand. We don't have any rule that `database` must be `catalog`. – Mabu Kloesen Jan 18 '18 at 06:15
  • what can a database be? – Tim Jan 18 '18 at 13:18
  • Sorry, I don't understand your question. – Mabu Kloesen Jan 18 '18 at 13:40
  • "We don't have any rule that database must be catalog". So I was wondering what a database can be? – Tim Jan 18 '18 at 17:39
  • @Tim: I wonder why you must call `catalog`? What is it? I don't have any definition about the thing that you call `catalog` – Mabu Kloesen Jan 19 '18 at 02:57