postgresql

    Syntax

    Arguments

    • host:port — PostgreSQL server address.
    • database — Remote database name.
    • table — Remote table name.
    • user — PostgreSQL user.
    • password — User password.
    • schema — Non-default table schema. Optional.

    Returned Value

    A table object with the same columns as the original PostgreSQL table.

    Note

    In the INSERT query to distinguish table function postgresql(...) from table name with column names list you must use keywords FUNCTION or TABLE FUNCTION. See examples below.

    Simple clauses such as =, !=, >, >=, <, <=, and IN are executed on the PostgreSQL server.

    All joins, aggregations, sorting, IN [ array ] conditions and the LIMIT sampling constraint are executed in ClickHouse only after the query to PostgreSQL finishes.

    INSERT queries on PostgreSQL side run as COPY "table_name" (field1, field2, ... fieldN) FROM STDIN inside PostgreSQL transaction with auto-commit after each INSERT statement.

    PostgreSQL Array types converts into ClickHouse arrays.

    Note

    Be careful, in PostgreSQL an array data type column like Integer[] may contain arrays of different dimensions in different rows, but in ClickHouse it is only allowed to have multidimensional arrays of the same dimension in all rows.

      or

      Supports replicas priority for PostgreSQL dictionary source. The bigger the number in map, the less the priority. The highest priority is 0.

      Examples

      Table in PostgreSQL:

      Selecting data from ClickHouse:

      1. SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password') WHERE str IN ('test');
      1. ┌─int_id─┬─int_nullable─┬─float─┬─str──┬─float_nullable─┐
      2. │      1 │         ᴺᵁᴸᴸ │     2 │ test │           ᴺᵁᴸᴸ │
      3. └────────┴──────────────┴───────┴──────┴────────────────┘

      Inserting:

      1. ┌─int_id─┬─int_nullable─┬─float─┬─str──┬─float_nullable─┐
      2. │      1 │         ᴺᵁᴸᴸ │     2 │ test │           ᴺᵁᴸᴸ │
      3. └────────┴──────────────┴───────┴──────┴────────────────┘
      1. postgres=# CREATE SCHEMA "nice.schema";
      3. postgres=# CREATE TABLE "nice.schema"."nice.table" (a integer);
      5. postgres=# INSERT INTO "nice.schema"."nice.table" SELECT i FROM generate_series(0, 99) as t(i)

      See Also