I'm trying to determine how I can sync data from a standard row relational table or view in Postgres to the table I built with Apache AGE.
I have standard tables already defined in Postgres with relational data (foreign key constraints) between 3 tables. I did build a view to join all the information and then populated the new ag_catalog vertex label "Site" with the data. I completed building other vertex and edge labels and populated and everything worked for Cypher testing.
I'm wanting to keep the data in sync between my normal site table and the AGE vertex table. I figure this will need triggers based insert, update, and delete to the main relational table I have defined, but I'm having trouble trying to determine the cypher queries to keep the vertex table defined in AGE to be in sync.
Here is sample of what my relational table looks like.
| ID | name (unique) | type | full_cost | part_cost |
|---|---|---|---|---|
| 1 | ADFEIN43 | CO | 120000 | 90000 |
| 2 | HFDEIN52 | UNK | 150000 | 85000 |
| 3 | JUHEIN23 | UNK | 145000 | 65000 |
As far as I see the AGE table is something like this.
| id | properties |
|---|---|
| 1970324836874593 | {"name": "ADFEIN43", "type": "CO", "full_cost": "120000", "part_cost": "90000"} |
Before someone asks why I'm keeping two copies of the data, I already have another application updating the relational tables. But I need to do shortest path calculations using Cypher and sum costs. We tested with Neo4j and then I found the Apache AGE extension for Postgres. This seemed like would be best to keep all data in one DB and let the DB do the work to keep the data in sync.
I have not tested any sql at this point as I'm hoping someone else has already solved.
I did find article about creation from existing table. Guessing something like this could be used for insert trigger. Is it possible to create a graph in AGE using existing table in the database?