Schema

Schema Nedir?
Açıklaması şöyle.

In Postgres, the schema is also referred to as a namespace. The namespace can be associated with a family name. It is used to identify and differentiate certain objects in the database (tables, views, columns, etc.). It’s not allowed to create two tables with the same name in one schema, but you can do it in two different schemas. For example, we can have two tables both named table1 present in public and in postgres schemas.

Schema Neden Lazım?
Açıklaması şöyle.

Schemas are very useful to organize database objects into logical groups and avoid name collision. Besides this, schemas are often used to allow different users to work with the database without interfering with each other. A common example is when each database user works on its own schema, without interfering with other users and avoiding conflicts. 

Schema İsimleri

Açıklaması şöyle

public — is used by default for the user objects;

pg_catalog — is used for the system catalog tables;

information_schema — is a view of the system catalog defined by SQL standards;

pg_toast — is used for the TOAST objects;

pg_temp — stores temporal tables (note: every user has its own schema for temp tables: pg_temp_N, but pg_temp is used as an alias for every of those);

public İsimli Schema

public Schema yazısına taşıdım

SEARCH_PATH Değişkeni
Açıklaması şöyle

When executing a command with an unqualified name, Postgres follows a search path to determine what schemas to use. By default, the search path is set to the public schema.

Açıklaması şöyle

If a schema name is not specified explicitly, the first matching schema is chosen from the searching path. The searching path is built from the value stored in the search_path parameter, pg_catalog, and pg_temp schemas. It allows a user to store objects with the same names but in different schemas.

Örnek
Şöyle yaparız.

SHOW search_path;

Örnek
Bir başka schema'ya geçmek için şöyle yaparız

SET search_path TO schema_1;

SEARCH_PATH Değişkenini JDBC Connection String İle Atama

Maven'a şu satırı ekleriz

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.2.8</version>
</dependency>

Örnek
Bağlantı sırasında schema belirtmek için şöyle yaparız.

jdbc:postgresql://127.0.0.1:5432/DBNAME?currentSchema=app

Aslında bu parametrenin ismi açıklaması şöyle. Yani aslında tüm search_path'i tanımlar

The parameter name currentSchema is a bit misleading. It takes the whole search_path, not just the "current schema".

Bu yüzden birden fazla schema'yı görebilsin diye şöyle yaparız

jdbc:postgresql://localhost:5432/tst?currentSchema=app1,public