Change schemas' structure in a PostgreSQL database

Question

I have a Postgres database with some schemas (all have the same structure), I want to know if there is the possibility to change the structure (Table names, new columns etc) for all the schemas in the same database. Is it possible or what's the purpose of the schemas in a database?

Thanks.

Sorry - hit enter by mistake. The schema describes the overall organization of the database and how all the tables inside the database relate (or don't relate) to each other. Tables can be altered, but it's hard to give a comprehensive answer without more specific information. This video might help explain how schemas and tables work. youtu.be/ymb9gsl_x1U If you want to go more in depth, Stanford University offers a database class online for free. class.stanford.edu/courses/Engineering/db/2014_1/about — Taylor714
– Taylor714, Commented Oct 16, 2014 at 0:32

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

I'm going to focus on the second half of your question, because I think it'll answer the first half (and I'm not sure I understand the first half).

what's the purpose of the schemas in a database?

This confused me when I first switched from MySQL to PostgreSQL. A Postgres schema is essentially the same as a MySQL database. In fact, according to the MySQL Reference Manual:

In MySQL, physically, a schema is synonymous with a database.

That begs the question of what is a PostgreSQL database, then? From the PostgreSQL Documentation:

More accurately, a database is a collection of schemas and the schemas contain the tables, functions, etc. So the full hierarchy is: server, database, schema, table (or some other kind of object, such as a function).

So a PostgreSQL database is essentially a collection of schemas? Seems kind of pointless, why do we need that step in the hierarchy? Let's take a look at the docs for a PostgreSQL schema:

A PostgreSQL database cluster contains one or more named databases. Users and groups of users are shared across the entire cluster, but no other data is shared across databases. Any given client connection to the server can access only the data in a single database, the one specified in the connection request.

A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database he is connected to, if he has privileges to do so.

So, in PostgreSQL, a schema contains tables, functions, etc. And a database manages user/group connectivity and access/roles to specific clusters of schemas. Typically, I work under one database and have information broken into schemas to segment information.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 16, 2014 at 0:37

Sam

20.5k2 gold badges48 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

The SQL standard defines two levels of "namespaces": catalogs and schemas. A "catalog" can contain multiple "schemas". A Postgres "database" maps (to a certain extent) to the concept of a catalog in the SQL standard. It's essentially the same way as SQL Server manages databases and schemas. Other DBMS (e.g. H2, HSQLDB) have similar two-level namespaces.

Sam Over a year ago

Thanks, @a_horse_with_no_name, didn't know that and it is very useful to my answer :)

Collectives™ on Stack Overflow

Change schemas' structure in a PostgreSQL database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related