0

Running the following...

SELECT
      a.allocation_date, a.trade_account, a.trade_broker, a.account,
      a.custodian, a.book, a.symbol, a.side,
      SUM(a.quantity)::integer,
      SUM(a.quantity*a.price) / SUM(a.quantity) AS price
    FROM backoffice.allocations a
    inner join symdb.products.product s
    on (symbol=products)
    WHERE a.allocation_date BETWEEN '2020-01-01' AND '2020-12-30'
    GROUP BY
      a.allocation_date, a.trade_account, a.trade_broker, a.account,
      a.custodian, a.book, a.symbol, a.side
    ORDER BY
      a.allocation_date, a.trade_account, a.trade_broker, a.account,
      a.custodian, a.book, a.symbol, a.side;

...is throwing...

SQL Error [0A000]: ERROR: cross-database references are not implemented: "symdb.products.product"

The INNER JOIN part is what I can't get to work. How do I fix it?

  • Are these tables in different databases? – Joe W Feb 19 at 19:17
  • See here, Laurenz isn't the most verbose fellow, but it's the correct answer. You need need to use FDWs or move the tables into the same DB to join – Peter Vandivier Feb 19 at 19:45
  • 1
    If you need to do that a lot, I highly recommend to use schemas, rather than database to separate tables. – a_horse_with_no_name Feb 19 at 19:47
  • 1
    If both tables are in the same db, why you are not referencing symdb also for allocations? paths seem not to be the same – Imanol Y. Feb 19 at 21:29
  • 2
    If everything is in the same database, then symdb.products.product should (probably) be products.product – a_horse_with_no_name Feb 19 at 21:35
0

If you do have dots in your schema name, this wouldn't be very good practice as postgres expects the input to be schemaname.tablename so now its getting extra arguements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.