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)
@nshah14285, till the end of this day will be in master.