Allows SELECT and INSERT queries to data stored on a remote PostgreSQL server.
Syntax

postgresql('host:port', 'database', 'table', 'user', 'password'[, `schema`])

Param
host:port — PostgreSQL server address.
database — Remote Database name.
table — Remote Table name.
user — PostgreSQL User.
password — User Password.
Schema—non-Default table structure. Optional.
Return value
A table object with the same number of columns as the original PostgreSQL table.
!!! info "Note"
In the INSERT query, in order to distinguish between the table function postgresql(..) and the table name and the list of column names of the table, you must use the keyword FUNCTION or TABLE FUNCTION. See the examples below.

Implementation Details #

SELECT queries on PostgreSQL COPY (SELECT ...) TO STDOUT are Read only PostgreSQL transactions that SELECT Search after each SELECT query.
Simple WHERE clauses, such as =, !. =, >, >=, <, <= IN are IN Run on the PostgreSQL server. IN [ 数组 ] Condition Condition LIMIT sampling Constraint only LIMIT Condition Condition LIMIT sampling Constraint only LIMIT. INSERT queries on PostgreSQL COPY "table_name" (field1, field2, ... fieldN) FROM STDIN Method / way Method / way in PostgreSQL transactions, INSERT INSERT INSERT INSERT.
PostgreSQL array Data type will be converted to ClickHouse arrays.
!!! info "Note"
Be careful that in PostgreSQL, array Data type Data type Data type Data type.
Data type Data type Integer[] Data type. The larger the Number in the Map, the lower the Priority. 0 represents the Highest Priority.
Example
Table in 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)

Retrieving data from ClickHouse:

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

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

Inserting data:

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

table structure:

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