PostgreSQL database

Telosys database configuration

Below are examples of typical configurations for a PostgreSQL database.

Since Telosys 4.3

  - 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

  - 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/

  • MVN Repository: https://mvnrepository.com/search?q=postgresql "PostgreSQL JDBC Driver"

• JAR file example : postgresql-42.7.7.jar

• Driver class name : org.postgresql.Driver

Technical information about PostgreSQL

Structure

Server (cluster) → Database → Schema → Tables/Objects

• 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

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

-- Get current schema
SELECT CURRENT_SCHEMA();

-- Set current schema
SET SEARCH_PATH = foo ;