```schema.table_name``` is not supported with Postgresql

[fiuyang]

query := repo.Db.Clauses(exclause.NewWith("cte", "SELECT * FROM mst.m_outlet")).Table("cte").Where("NOT EXISTS (SELECT 1 FROM pjp.route_outlet WHERE mst.m_outlet.outlet_code = pjp.route_outlet.outlet_code)").Find(&data)

2024/05/07 12:23:30 :44 ERROR: missing FROM-clause entry for table "m_outlet" (SQLSTATE 42P01)
[121.924ms] [rows:0] WITH "cte" AS (SELECT * FROM mst.m_outlet) SELECT * FROM "cte" WHERE NOT EXISTS (SELECT 1 FROM pjp.route_outlet WHERE mst.m_outlet.outlet_code = pjp.route_outlet.outlet_code)

Hello, i have a problem when using schema.table_name in postgresql, is there any solution ??

thank you

[WinterYukky]

Hi @fiuyang . Thank you for opening this issue and sorry later reponse. I look like your code has mistake. PostgreSQL not implement cross-database references. You can try this query

SELECT 1 FROM pjp.route_outlet WHERE mst.m_outlet.outlet_code = pjp.route_outlet.outlet_code

This is your subquery as it is. When run the query I got cross-database references are not implemented: "mst.m_outlet.outlet_code". I think this is reason.

What about using cte in the subquery as a workaround? (I am not a PosgreSQL expert.)

WITH "cte" AS (SELECT * FROM mst.m_outlet) SELECT * FROM "cte" WHERE NOT EXISTS (SELECT 1 FROM pjp.route_outlet WHERE cte.outlet_code = pjp.route_outlet.outlet_code);

Case of using gorm and this plugin.

query := repo.Db.Clauses(exclause.NewWith("cte", "SELECT * FROM mst.m_outlet")).Table("cte").Where("NOT EXISTS (SELECT 1 FROM pjp.route_outlet WHERE cte.outlet_code = pjp.route_outlet.outlet_code)").Find(&data)

[fiuyang]

Hi @WinterYukky , Thank you for your answer and that my problem has been resolved. This is because I wasn't careful, I should have changed it from

 SELECT 1 FROM pjp.route_outlet WHERE mst.m_outlet.outlet_code = pjp.route_outlet.outlet_code

To

 SELECT 1 FROM pjp.route_outlet WHERE cte.outlet_code = pjp.route_outlet.outlet_code

And this is my code that worked

query := repo.Db.Clauses(exclause.NewWith("cte", repo.Db.Table("mst.m_outlet"))).
        Select("outlet_id", "outlet_name", "outlet_code", "outlet_status", "latitude", "longitude", "address1").
        Table("cte").
        Where("NOT EXISTS (SELECT 1 FROM pjp.route_outlet WHERE cte.outlet_code= pjp.route_outlet.outlet_code)")

Thank you very much

[WinterYukky]

Good. I'm glad I could help you with the tools and answers I created.