postgresql Табличная Функция

Позволяет выполнять запросы SELECT и INSERT на данных, которые хранятся на удаленном сервере PostgreSQL.

Синтаксис

postgresql({host:port, database, table, user, password[, schema, [, on_conflict]] | named_collection[, option=value [,..]]})

Аргументы

Аргумент	Описание
host:port	Адрес сервера PostgreSQL.
database	Имя удаленной базы данных.
table	Имя удаленной таблицы.
user	Пользователь PostgreSQL.
password	Пароль пользователя.
schema	Неподразумеваемая схема таблицы. Необязательный аргумент.
on_conflict	Стратегия разрешения конфликтов. Например: ON CONFLICT DO NOTHING. Необязательный аргумент.

Аргументы также могут быть переданы с использованием именованных коллекций. В этом случае host и port должны быть указаны отдельно. Этот подход рекомендуется для производственной среды.

Возвращаемое значение

Объект таблицы с теми же колонками, что и в оригинальной таблице PostgreSQL.

примечание

В INSERT запросе для различения табличной функции postgresql(...) от имени таблицы с перечислением имен колонок необходимо использовать ключевые слова FUNCTION или TABLE FUNCTION. Смотрите примеры ниже.

Подробности реализации

Запросы SELECT на стороне PostgreSQL выполняются как COPY (SELECT ...) TO STDOUT внутри транзакции только для чтения с коммитом после каждого запроса SELECT.

Простые условия WHERE, такие как =, !=, >, >=, <, <= и IN, выполняются на сервере PostgreSQL.

Все соединения, агрегации, сортировка, условия IN [ array ] и ограничение выборки LIMIT выполняются в ClickHouse только после завершения запроса к PostgreSQL.

Запросы INSERT на стороне PostgreSQL выполняются как COPY "table_name" (field1, field2, ... fieldN) FROM STDIN внутри транзакции PostgreSQL с автокоммитом после каждого оператора INSERT.

Типы массивов PostgreSQL конвертируются в массивы ClickHouse.

примечание

Будьте осторожны: в PostgreSQL колонка типа массив (например, Integer[]) может содержать массивы различных размерностей в разных строках, однако в ClickHouse разрешены только многомерные массивы одинаковой размерности во всех строках.

Поддерживает несколько реплик, которые должны быть перечислены через |. Например:

SELECT name FROM postgresql(`postgres{1|2|3}:5432`, 'postgres_database', 'postgres_table', 'user', 'password');

или

SELECT name FROM postgresql(`postgres1:5431|postgres2:5432`, 'postgres_database', 'postgres_table', 'user', 'password');

Поддерживает приоритет реплик для источника словаря PostgreSQL. Чем больше число в карте, тем меньше приоритет. Наивысший приоритет — 0.

Примеры

Таблица в PostgreSQL:

postgres=# CREATE TABLE "public"."test" (
"int_id" SERIAL,
"int_nullable" INT NULL DEFAULT NULL,
"float" FLOAT NOT NULL,
"str" VARCHAR(100) NOT NULL DEFAULT '',
"float_nullable" FLOAT NULL DEFAULT NULL,
PRIMARY KEY (int_id));

CREATE TABLE

postgres=# INSERT INTO test (int_id, str, "float") VALUES (1,'test',2);
INSERT 0 1

postgresql> SELECT * FROM test;
  int_id | int_nullable | float | str  | float_nullable
 --------+--------------+-------+------+----------------
       1 |              |     2 | test |
(1 row)

Выборка данных из ClickHouse с использованием простых аргументов:

SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password') WHERE str IN ('test');

Или с использованием именованных коллекций:

CREATE NAMED COLLECTION mypg AS
        host = 'localhost',
        port = 5432,
        database = 'test',
        user = 'postgresql_user',
        password = 'password';
SELECT * FROM postgresql(mypg, table='test') WHERE str IN ('test');

┌─int_id─┬─int_nullable─┬─float─┬─str──┬─float_nullable─┐
│      1 │         ᴺᵁᴸᴸ │     2 │ test │           ᴺᵁᴸᴸ │
└────────┴──────────────┴───────┴──────┴────────────────┘

Вставка:

INSERT INTO TABLE FUNCTION postgresql('localhost:5432', 'test', 'test', 'postgrsql_user', 'password') (int_id, float) VALUES (2, 3);
SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password');

┌─int_id─┬─int_nullable─┬─float─┬─str──┬─float_nullable─┐
│      1 │         ᴺᵁᴸᴸ │     2 │ test │           ᴺᵁᴸᴸ │
│      2 │         ᴺᵁᴸᴸ │     3 │      │           ᴺᵁᴸᴸ │
└────────┴──────────────┴───────┴──────┴────────────────┘

Использование Неподразумеваемой Схемы:

postgres=# CREATE SCHEMA "nice.schema";

postgres=# CREATE TABLE "nice.schema"."nice.table" (a integer);

postgres=# INSERT INTO "nice.schema"."nice.table" SELECT i FROM generate_series(0, 99) as t(i)

CREATE TABLE pg_table_schema_with_dots (a UInt32)
        ENGINE PostgreSQL('localhost:5432', 'clickhouse', 'nice.table', 'postgrsql_user', 'password', 'nice.schema');

Связанные темы

• Движок таблиц PostgreSQL
• Использование PostgreSQL в качестве источника словаря

Репликация или миграция данных Postgres с PeerDB

В дополнение к табличным функциям вы всегда можете использовать PeerDB от ClickHouse для настройки непрерывного конвейера данных из Postgres в ClickHouse. PeerDB — это инструмент, специально предназначенный для репликации данных из Postgres в ClickHouse с использованием технологии захвата изменений данных (CDC).