Cross schema view

[Anandh Nagarajan]

Hi Citus team,

Is it possible to create a view which queries data from tables in multiple schemas. Current set up has one schema per customer and each schema has multiple tables. Would like to create a view which combines data from similar table across the schemas.Please note that each schema is in their own colocation group.

Example :

Customer_1_Schema.Order table

Customer_2_Schema.Order table

CREATE VIEW COMMON_ORDER AS

SELECT * FROM Customer_1_Schema.Order table

UNION

SELECT * FROM Customer_1_Schema.Order table;

Thanks for the help.

[Anandh Nagarajan]

Typo in the example corrected.

Example :

Customer_1_Schema.Order table

Customer_2_Schema.Order table

CREATE VIEW COMMON_ORDER AS

SELECT * FROM Customer_1_Schema.Order table

UNION

SELECT * FROM Customer_2_Schema.Order table;

[Abdullah Hanefi Önaldı]

Yes.

You can create views that query distributed tables that are in different colocation groups. From Citus' point of view it does not matter if the distributed tables are in different schemas. However using different colocation groups mean that some join operations may end up repartitioning your tables on-the-fly. You may run into issues when you do complex joins.

You should be able to use any supported SELECT query in a VIEW definition.

Best,

Hanefi

23 Aralık 2021 Perşembe tarihinde saat 23:44:51 UTC+3 itibarıyla Anandh Nagarajan şunları yazdı: