Query for converting parent child hierarchy multiple tables to single table (adjacency list)

Question

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.

1. Is Guid the only way to achieve the desired output?
2. If so is there a way to generate a Guid uniquely across a set of tables in postgres?
3. Is there any other way to get this done without Guid?

Answer 1

Would something like the following work for you?

SELECT 'a' || a.id::text AS id,
        NULL AS parent_id,
        a.name AS name,
        1 AS type
    FROM a
UNION ALL
SELECT 'b' || b.id::text AS id,
        'a' || b.parent_a::text AS parent_id,
        b.name,
        2 AS type
    FROM b
UNION ALL
SELECT 'c' || c.id::text AS id,
        'b' || c.parent_b::text AS parent_id,
        c.name,
        3 AS type
    FROM c ;