ClickHouse: PostgreSQL non-default schemas are unsupported

Describe the unexpected behaviour I try to access PostgreSQL data with a query like

SELECT * FROM postgresql('some_host:5432', 'some_database', 'topology.flat_struct', 'some_user', 'some_password');

and get the error below

Received exception from server (version 21.2.2):
Code: 1001. DB::Exception: Received from ***.com:9000. DB::Exception: pqxx::undefined_table: ERROR:  relation "topology.flat_struct" does not exist
LINE 1: ...t_id", "port_name", "queue_id", "queue_name" FROM "topology....

As I can see, column names were extracted. Also, I see quotes around, presumably, fully qualified (topology.flat_struct) relation name. PostgreSQL syntax is select * from "topology"."flat_struct" not select * from "topology.flat_struct"

I ran the query

created view public.topology_flat_struct
as
select * from topology.flat_struct;

on PostgreSQL side and after that the query

SELECT * FROM postgresql('some_host:5432', 'some_database', 'topology_flat_struct', 'some_user', 'some_password');

on ClickHouse side works just fine.

The query

select * from "topology.flat_struct";

on PostgreSQL side leads to the exception

ERROR:  relation "topology.flat_struct" does not exist
LINE 1: select * from "topology.flat_struct";
                      ^
SQL state: 42P01

How to reproduce

  • Which ClickHouse server version to use: 21.2.2

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Reactions: 1
  • Comments: 17 (7 by maintainers)

Most upvoted comments

@nshah14285, till the end of this day will be in master.