# PostgreSQL database

## Telosys database configuration

Below are examples of typical configurations for a **PostgreSQL** database.

### Since Telosys 4.3

```yaml
  - id: pgcars
    name: PostgreSQL 'cars' schema on 'localhost'
    type: POSTGRESQL 
    # JDBC configuration
    url: jdbc:postgresql://myhost:5432/mydatabase
    user: john_doe
    password: not_to_reveal
    # Metadata parameters
    schema: cars
```

### Before Telosys 4.3

```yaml
  - id: pgcars
    name: PostgreSQL 'cars' schema on 'localhost'
    type: POSTGRESQL 
    # JDBC configuration
    driver: org.postgresql.Driver 
    url: jdbc:postgresql://myhost:5432/mydatabase
    user: john_doe
    password: not_to_reveal
    # Metadata parameters
    catalog: '!'
    schema: cars
    tableNamePattern: '%'
    tableTypes: TABLE
```

### JDBC driver

* Download:
  * Web site:  <https://jdbc.postgresql.org/>&#x20;
  * MVN Repository: <https://mvnrepository.com/search?q=postgresql> \
    "PostgreSQL JDBC Driver"
* JAR file example :  `postgresql-42.7.7.jar`    &#x20;
* Driver class name : **org.postgresql.Driver**&#x20;

## Technical information about PostgreSQL

### Structure

Server (cluster) → **Database** → **Schema** → Tables/Objects&#x20;

<div align="left"><figure><img src="https://434201135-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MZMZz3NJC5MgGbWIG7J%2Fuploads%2FUs1WkIumPdoD19GrpDIK%2Fimage.png?alt=media&#x26;token=6108aa15-6835-40b2-822f-445ba15d8ee8" alt="" width="287"><figcaption></figcaption></figure></div>

* **Database**: A physical database (separate catalog). Connections are always made to a specific database. Databases are isolated — you can’t query across them without special tools (like `dblink` or FDWs).
* **Schema**: A logical namespace *inside a database*. A database can have multiple schemas (e.g., `public`, `sales`, `hr`). Objects (tables, views, etc.) live inside schemas. You can query across schemas in the same database (`sales.orders`, `hr.employees`).

### PostgreSQL case conversion rules

* **Unquoted identifiers**
  * Always **converted to LOWERCASE**
  * Applies to both **table names** and **column names**.
* **Quoted identifiers**
  * Case is **preserved exactly as written**
  * They are **case-sensitive** in SQL requests

### Schema management&#x20;

```textile
-- Create schema
CREATE SCHEMA [IF NOT EXISTS] foo;

-- Get current schema
SELECT CURRENT_SCHEMA();

-- Set current schema
SET SEARCH_PATH = foo ;
```