postgresql

Permet d’exécuter des requêtes SELECT et INSERT sur des données stockées sur un serveur PostgreSQL distant.

Permet d’exécuter des requêtes SELECT et INSERT sur des données stockées sur un serveur PostgreSQL distant.

​

Syntaxe

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

​

Arguments

Argument	Description
host:port	Adresse du serveur PostgreSQL.
database	Nom de la base de données distante.
table	Nom de la table distante.
user	Utilisateur PostgreSQL.
password	Mot de passe de l’utilisateur.
schema	Schéma de table autre que celui par défaut. Facultatif.
on_conflict	Stratégie de résolution des conflits. Exemple : ON CONFLICT DO NOTHING. Facultatif.

Les arguments peuvent également être transmis à l’aide de collections nommées. Dans ce cas, host et port doivent être indiqués séparément. Cette approche est recommandée en production.

​

Valeur renvoyée

Un objet de type table avec les mêmes colonnes que la table PostgreSQL d’origine.

Dans la requête INSERT, pour distinguer la fonction de table postgresql(...) d’un nom de table accompagné d’une liste de noms de colonnes, vous devez utiliser les mots-clés FUNCTION ou TABLE FUNCTION. Voir les exemples ci-dessous.

​

Détails d’implémentation

Les requêtes SELECT côté PostgreSQL s’exécutent sous la forme de COPY (SELECT ...) TO STDOUT à l’intérieur d’une transaction PostgreSQL en lecture seule, avec un commit après chaque requête SELECT.

Les clauses WHERE simples, telles que =, !=, >, >=, <, <= et IN, sont exécutées sur le serveur PostgreSQL.

Toutes les jointures, agrégations, opérations de tri, conditions IN [ array ] et la contrainte d’échantillonnage LIMIT sont exécutées dans ClickHouse, et uniquement une fois la requête vers PostgreSQL terminée.

Les requêtes INSERT côté PostgreSQL s’exécutent sous la forme de COPY "table_name" (field1, field2, ... fieldN) FROM STDIN à l’intérieur d’une transaction PostgreSQL avec commit automatique après chaque instruction INSERT.

Les types Array de PostgreSQL sont convertis en tableaux ClickHouse.

Attention : dans PostgreSQL, une colonne de type tableau comme Integer[] peut contenir des tableaux de dimensions différentes selon les lignes, mais dans ClickHouse, seuls des tableaux multidimensionnels de même dimension sont autorisés dans toutes les lignes.

Prend en charge plusieurs répliques, qui doivent être séparées par |. Par exemple :

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

or

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

Prend en charge la priorité des répliques pour la source de dictionnaire PostgreSQL. Plus le nombre dans la map est élevé, plus la priorité est faible. La priorité la plus élevée est 0.

​

Exemples

Table dans 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)

Sélection de données dans ClickHouse à l’aide d’arguments simples :

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

Ou en utilisant des collections nommées :

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 │           ᴺᵁᴸᴸ │
└────────┴──────────────┴───────┴──────┴────────────────┘

Insertion :

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 │      │           ᴺᵁᴸᴸ │
└────────┴──────────────┴───────┴──────┴────────────────┘

Utiliser un schéma non par défaut :

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');

​

Voir aussi

• Le moteur de table PostgreSQL
• Utiliser PostgreSQL comme source pour un dictionnaire

​

Répliquer ou migrer des données Postgres avec PeerDB

En plus des fonctions de table, vous pouvez également utiliser PeerDB de ClickHouse pour mettre en place un pipeline de données continu de Postgres vers ClickHouse. PeerDB est un outil spécialement conçu pour répliquer des données de Postgres vers ClickHouse à l’aide de la capture de données modifiées (CDC).