Is there a way to see all the AGE created tables?

For example in a PostgreSQL database, all the other tables can be seen using

\dt

Or with

SELECT * FROM pg_catalog.pg_tables;

And we know that AGE creates its own label tables, vertex table, edge table.

How can I see those tables, and how can I query for them to view them along with their all columns?

ag_catalog contains all the metadata used by Apache AGE. ag_graphs is contained in ag_catalog and it stores the information/namespaces about all the graphs present in the database. ag_label stores the data. So to fetch all the tables, you do:

SELECT * FROM ag_label;

You can use the following query:

SELECT * FROM ag_label;

to get a similar result in AGE, for example:

       name       | graph | id | kind |           relation           |        seq_name
------------------+-------+----+------+------------------------------+-------------------------
 _ag_label_vertex | 16944 |  1 | v    | "Demo"._ag_label_vertex      | _ag_label_vertex_id_seq
 _ag_label_edge   | 16944 |  2 | e    | "Demo"._ag_label_edge        | _ag_label_edge_id_seq
 Person           | 16944 |  3 | v    | "Demo"."Person"              | Person_id_seq
 REL              | 16944 |  4 | e    | "Demo"."REL"                 | REL_id_seq
 KNOWS            | 16944 |  5 | e    | "Demo"."KNOWS"               | KNOWS_id_seq

where the kind column tells you if it is a vertex v or an edge e, with their labels shown in the name column.

Here is a regress test to check the different variations of the function.

If I got you correctly , we can make use out of the concept that for each graph you create using AGE to work with , AGE creates a separate schema for this graph that has it's related tables , and this schema named exactly the same as the name of your created graph.

so you can easily select the created tables behind the scenes from the pg_catalog , information_schema tables using the graph name as our schema name.

we created a graph through age as ,

SELECT * FROM ag_catalog.create_graph('your_first_graph');

you can go and see it's related tables that created for it behind the scenes in both pg_catalog.pg_tables and information_schema.tables,

SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname = 'your_first_graph';

and ,

SELECT *
FROM information_schema.tables
WHERE table_schema = 'your_first_graph';

And if you aim to query them and observe their entries you can use <your graph name>.<desired table name> and do your queries for example ,

SELECT *
FROM your_first_graph._ag_label_edge ;

Actually if you are not interested in detailed table information provided such as tablespace_oid , oid ..etc , you can directly the ag_label to get tables name , type and graph to which they are related as,

SELECT * FROM ag_label ;

Table ag_graph stores names and namespaces of all the graphs.

SELECT * FROM ag_graph;

Table ag_label stores all the data (labels, relations, sequences etc).

SELECT * FROM ag_label;

To see all the vertices and edges:

SELECT * FROM <graph_name>._ag_label_vertex;
SELECT * FROM <graph_name>._ag_label_edge;

To see all vertices and edges with a specific label:

SELECT * FROM <graph_name>."<label_name>";

SELECT * FROM ag_label; gets this done.

However, remember to SET search_path before running the query else simply run SELECT * FROM ag_catalog.ag_label;.

You could use the following query to view the table

SELECT oid, * FROM ag_catalog.ag_label;

Table Results would be as follow:

  oid   |        name       | graph | id | kind |        relation          |
----------+-------------------+-------+----+------+--------------------------+
   16950  | _age_label_vertex | 16937 |  1 | v    | database.ag_label_vertex |
   16963  | _age_label_edge   | 16937 |  2 | e    | database.ag_label_edge   |
   16975  | Country           | 16937 |  3 | v    | database."Country"       |
   16987  | City              | 16937 |  4 | v    | database."City"          |
   16999  | has_city          | 16937 |  5 | e    | database.has_city    |      

where:

• oid : An unique Postgres identifier per label ;
• name: The name of the label ;
• graph: Oid from ag_graph ;
• id : The id for the label, it is unique for each graph ;
• kind : Shows if it is a vertex “v” or an edge “e” ;
• relation : Schema qualified name of table name for label.

Reference:
https://matheusfarias03.github.io/AGE-quick-guide/

You can refer to this guide Guide

For creating a graph, you can use the create_graph function, located in the ag_catalog namespace:

SELECT * FROM ag_catalog.create_graph('graph_name');

The data for a label is stored in the ag_label and use can use this query to view the available labels:

SELECT oid, * FROM ag_catalog.ag_label;

where, oid : An unique Postgres identifier per label.

Once you've loaded AGE and set your search path to ag_catalog, you can use this query to display all the labels.
SELECT * FROM ag_label; and this will display all the nodes and vertices in the ag_catalog.
You could also use SELECT * FROM ag_graph; to display all the graph names.

In PostgreSQL, the tables created by the AGE extension are stored in ag_catalog schema.

for example:

to see all the tables in the ag_catalog schema use the following query:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'ag_catalog';

to view the columns of a particular table in ag_catalog you have to replace your table with the actual name of the table you want to view:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'ag_catalog' AND table_name = 'your_table_name';

if you want to view all the tables and their columns together, you can use the following query:

SELECT t.table_name, c.column_name, c.data_type
FROM information_schema.tables t
JOIN information_schema.columns c ON t.table_name = c.table_name AND t.table_schema = c.table_schema
WHERE t.table_schema = 'ag_catalog';

ag_graph stores the list of all the graphs, and ag_label stores the list of all the additional tables created by Apache AGE.

SELECT * FROM ag_graph;

This is the sample output of the above code.

demodb=# SELECT * FROM ag_graph;
 graphid |        name         |      namespace
---------+---------------------+---------------------
   66676 | graph               | graph
  137025 | bitnine_global_inic | bitnine_global_inic
  137077 | test_graph          | test_graph
   75557 | age_graph           | age_graph
(4 rows)

.

SELECT * FROM ag_label;

This is the sample output of the above code.

demodb=# SELECT * FROM ag_label;
       name       | graph  | id | kind |               relation               |        seq_name
------------------+--------+----+------+--------------------------------------+-------------------------
 _ag_label_vertex |  66676 |  1 | v    | graph._ag_label_vertex               | _ag_label_vertex_id_seq
 _ag_label_edge   |  66676 |  2 | e    | graph._ag_label_edge                 | _ag_label_edge_id_seq
 student          |  66676 |  8 | v    | graph.student                        | student_id_seq
 _ag_label_vertex |  75557 |  1 | v    | age_graph._ag_label_vertex           | _ag_label_vertex_id_seq
 _ag_label_edge   |  75557 |  2 | e    | age_graph._ag_label_edge             | _ag_label_edge_id_seq
 _ag_label_vertex | 137025 |  1 | v    | bitnine_global_inic._ag_label_vertex | _ag_label_vertex_id_seq
 _ag_label_edge   | 137025 |  2 | e    | bitnine_global_inic._ag_label_edge   | _ag_label_edge_id_seq
 Country          | 137025 |  3 | v    | bitnine_global_inic."Country"        | Country_id_seq
 City             | 137025 |  4 | v    | bitnine_global_inic."City"           | City_id_seq
 has_city         | 137025 |  5 | e    | bitnine_global_inic.has_city         | has_city_id_seq
 _ag_label_vertex | 137077 |  1 | v    | test_graph._ag_label_vertex          | _ag_label_vertex_id_seq
 _ag_label_edge   | 137077 |  2 | e    | test_graph._ag_label_edge            | _ag_label_edge_id_seq
(12 rows)

In the above output, rows with names starting without an underscore are user-generated labels.

You can query from the specific labels using the relation name given in relation column.

SELECT * FROM graph._ag_label_vertex;
SELECT * FROM bitnine_global_inic."City";

To see the tables and to query them in the column mode yo can follow the following steps:

1. List the Specific tables in and store them as graph data.
2. Use the '\d' in sql to view columns of a specific AGE table.
3. View all columns is the related tables in the current schema.

AGE is compatible with PostgreSQL. So, you can also run dt command to check the table created by AGE.
Here you can see i have created a demodb and a graph demo in it. And after running the command \dt here is the output.

Now to check the table created by AGE i.e., vertex and edge table you can run the commands.

SELECT * FROM demo._ag_label_vertex;

And same for _ag_label_edge.

Here is the result of the above commands.

Hope it helped!

To be able to see all the AGE tables created,You can us the following command:

SELECT * FROM ag_catalog.ag_label;

here is a sample output result to show you all the tables created:

<pre> name | graph | id | kind | relation | seq_name
------------------+-------+----+------+-----------------------------+-------------------------
_ag_label_vertex | 41520 | 1 | v | test_graph._ag_label_vertex | _ag_label_vertex_id_seq
_ag_label_edge | 41520 | 2 | e | test_graph._ag_label_edge | _ag_label_edge_id_seq
Person | 41520 | 3 | v | test_graph.&quot;Person&quot; | Person_id_seq
coworkers | 41520 | 4 | e | test_graph.coworkers | coworkers_id_seq
managers | 41520 | 5 | e | test_graph.managers | managers_id_seq
engineers | 41520 | 6 | e | test_graph.engineers | engineers_id_seq
reports | 41520 | 7 | e | test_graph.reports | reports_id_seq
(7 rows)
</pre>