I have chain of parent child tables for eg.
Table A id name
Table B id name parent_a
Table C id name parent_b
I want a query to get all tables into a single table with the following columns, similar to adjacency list model
id parent_id name type
I tried doing the following query
SELECT a.id as id, null AS parent_id, a.name as name, 1 AS type FROM a UNION ALL SELECT b.id as id, b.parent_a AS parent_id, b.name, 2 AS type FROM b UNION ALL SELECT c.id as id, c.parent_b as parent_id, c.name, 3 AS type FROM c;
This doesn't give the desired result since the id, in each of the individual table is duplicating, I see that one way to solve the problem is to include a unique Guid column in each of the individual tables.
- Is Guid the only way to achieve the desired output?
- If so is there a way to generate a Guid uniquely across a set of tables in postgres?
- Is there any other way to get this done without Guid?