CREATE TABLE region (
    region_id SERIAL PRIMARY KEY,
    region_name TEXT NOT NULL,
    region_type TEXT NOT NULL,
    parent_region_id INTEGER REFERENCES region(region_id),
    CHECK (
        CASE WHEN region_type = 'Country' THEN 1 ELSE 0 END +
        CASE WHEN parent_region_id IS NULL THEN 0 ELSE 1 END = 1
    )
);

CREATE UNIQUE INDEX country_top_idx
    ON region(region_name) WHERE region_type = 'Country';

CREATE UNIQUE INDEX must_have_parents_idx ON 
    region(region_name, region_type, parent_region_id)
    WHERE parent_region_id IS NOT NULL;

INSERT INTO region (region_name, region_type)
VALUES
    ('Brasil', 'Country'),
    ('United States', 'Country');

INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
    region r
CROSS JOIN
    (
        VALUES 
        ('Centro-Oeste','Região'),
        ('Nordeste','Região'),
        ('Norte','Região'),
        ('Sudeste','Região'),
        ('Sul','Região')
    ) AS v
WHERE (r.region_name, r.region_type) = ('Brasil','Country');


INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
    region r
CROSS JOIN
    (
        VALUES
        ('Goiás','Estado'),
        ('Mato Grosso','Estado'),
        ('Mato Grosso do Sul','Estado'),
        ('Distrito Federal','Distrito Federal')
    ) AS v
WHERE (r.region_name, r.region_type) = ('Centro-Oeste','Região');

INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
    region r
CROSS JOIN
    (
        VALUES
        ('Maranhão','Estado'),
        ('Piauí','Estado'),
        ('Ceará','Estado'),
        ('Rio Grande do Norte','Estado'),
        ('Paraíba','Estado'),
        ('Pernambuco','Estado'),
        ('Alagoas','Estado'),
        ('Sergipe','Estado'),
        ('Bahia','Estado')
    ) AS v
WHERE (r.region_name, r.region_type) = ('Nordeste','Região');

INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
    region r
CROSS JOIN
    (
        VALUES
        ('Acre','Estado'),
        ('Amazonas','Estado'),
        ('Roraima','Estado'),
        ('Rondônia','Estado'),
        ('Pará','Estado'),
        ('Amapá','Estado'),
        ('Tocantins','Estado')
    ) AS v
WHERE (r.region_name, r.region_type) = ('Norte','Região');

INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
    region r
CROSS JOIN
    (
        VALUES
        ('Minas Gerais','Estado'),
        ('Espírito Santo','Estado'),
        ('Rio de Janeiro','Estado'),
        ('São Paulo','Estado')
    ) AS v
WHERE (r.region_name, r.region_type) = ('Sudeste','Região');

INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
    region r
CROSS JOIN
    (
        VALUES
        ('Paraná','Estado'),
        ('Santa Catarina','Estado'),
        ('Rio Grande do Sul','Estado')
    ) AS v
WHERE (r.region_name, r.region_type) = ('Sul','Região');


INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
    region r
CROSS JOIN
    (
        VALUES
        ('Goiânia','Cidade'),
        ('Aparecida de Goiânia','Cidade'),
        ('Anápolis','Cidade'),
        ('Luziânia','Cidade'),
        ('Águas Lindas de Goiás','Cidade'),
        ('Rio Verde','Cidade'),
        ('Valparaíso de Goiás','Cidade'),
        ('Trindade','Cidade'),
        ('Planaltina','Cidade')
    ) AS v
WHERE (r.region_name, r.region_type) = ('Goiás','Estado');

INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
    region r
CROSS JOIN
    (
        VALUES
        ('Cuiabá','Cidade'),
        ('Várzea Grande','Cidade'),
        ('Rondonópolis','Cidade'),
        ('Sinop','Cidade'),
        ('Primavera do Leste','Cidade'),
        ('Cáceres','Cidade'),
        ('Tangará da Serra','Cidade')
    ) AS v
WHERE (r.region_name, r.region_type) = ('Mato Grosso','Estado');

INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
    region r
CROSS JOIN
    (
        VALUES
        ('Porto Alegre','Cidade'),
        ('Caxias do Sul','Cidade'),
        ('Pelotas','Cidade'),
        ('Canoas','Cidade'),
        ('Santa Maria','Cidade')
    ) AS v
WHERE (r.region_name, r.region_type) = ('Rio Grande do Sul','Estado');

INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
    region r
CROSS JOIN
    (
        VALUES
        ('Rio de Janeiro','Cidade'),
        ('São Gonçalo','Cidade'),
        ('Duque de Caxias','Cidade'),
        ('Nova Iguaçu','Cidade'),
        ('Belford Roxo','Cidade'),
        ('Niterói','Cidade'),
        ('São João de Meriti','Cidade'),
        ('Campos dos Goitacases','Cidade'),
        ('Petrópolis','Cidade'),
        ('Volta Redonda','Cidade'),
        ('Magé','Cidade'),
        ('Itaboraí','Cidade'),
        ('Mesquita','Cidade'),
        ('Nova Friburgo','Cidade'),
        ('Barra Mansa','Cidade'),
        ('Macaé','Cidade')
    ) AS v
WHERE (r.region_name, r.region_type) = ('Rio de Janeiro','Estado');

INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
    region r
CROSS JOIN
    (
        VALUES
        ('São Paulo','Cidade'),
        ('Guarulhos','Cidade'),
        ('Campinas','Cidade'),
        ('São Bernardo do Campo','Cidade'),
        ('Osasco','Cidade'),
        ('Santo André','Cidade'),
        ('São José dos Campos','Cidade'),
        ('Sorocaba','Cidade'),
        ('Ribeirão Preto','Cidade'),
        ('Santos','Cidade')
    ) AS v
WHERE (r.region_name, r.region_type) = ('São Paulo','Estado');


INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
    region r
CROSS JOIN
    (
        VALUES
        ('Alabama','State'),
        ('Alaska','State'),
        ('Arizona','State'),
        ('Arkansas','State'),
        ('California','State'),
        ('Colorado','State'),
        ('Connecticut','State'),
        ('Delaware','State'),
        ('Florida','State'),
        ('Georgia','State'),
        ('Hawaii','State'),
        ('Idaho','State'),
        ('Illinois','State'),
        ('Indiana','State'),
        ('Iowa','State'),
        ('Kansas','State'),
        ('Kentucky','State'),
        ('Louisiana','State'),
        ('Maine','State'),
        ('Maryland','State'),
        ('Massachusetts','State'),
        ('Michigan','State'),
        ('Minnesota','State'),
        ('Mississippi','State'),
        ('Missouri','State'),
        ('Montana','State'),
        ('Nebraska','State'),
        ('Nevada','State'),
        ('New Hampshire','State'),
        ('New Jersey','State'),
        ('New Mexico','State'),
        ('New York','State'),
        ('North Carolina','State'),
        ('North Dakota','State'),
        ('Ohio','State'),
        ('Oklahoma','State'),
        ('Oregon','State'),
        ('Pennsylvania','State'),
        ('Rhode Island','State'),
        ('South Carolina','State'),
        ('South Dakota','State'),
        ('Tennessee','State'),
        ('Texas','State'),
        ('Utah','State'),
        ('Vermont','State'),
        ('Virginia','State'),
        ('Washington','State'),
        ('Washington','District of Columbia'),
        ('West Virginia','State'),
        ('Wisconsin','State'),
        ('Wyoming','State')
    ) AS v
WHERE (r.region_name, r.region_type) = ('United States','Country');

WITH RECURSIVE t(region_id, region_name, region_type, parent_region_id, level, path) AS (
SELECT r1.*, 0, '{}'::int[] FROM region r1 WHERE (region_name, region_type) = ('Brasil', 'Country')
UNION ALL
SELECT r2.*, level+1, path || r2.parent_region_id FROM region r2 JOIN t ON r2.parent_region_id = t.region_id
)
SELECT
    REPEAT('--', level) || region_name || '(' || region_type || ')' AS "Onde",
    path
FROM t
ORDER BY path;

/*  Result set:  Note that a bunch of cities appear to be hanging off "Distrito
    Federal" where there should be none.

                  Onde                  |   path   
----------------------------------------+----------
 Brasil(Country)                        | {}
 --Centro-Oeste(Região)                 | {1}
 --Nordeste(Região)                     | {1}
 --Norte(Região)                        | {1}
 --Sudeste(Região)                      | {1}
 --Sul(Região)                          | {1}
 ----Goiás(Estado)                      | {1,3}
 ----Mato Grosso(Estado)                | {1,3}
 ----Mato Grosso do Sul(Estado)         | {1,3}
 ----Distrito Federal(Distrito Federal) | {1,3}
 ------Planaltina(Cidade)               | {1,3,8}
 ------Trindade(Cidade)                 | {1,3,8}
 ------Valparaíso de Goiás(Cidade)      | {1,3,8}
 ------Rio Verde(Cidade)                | {1,3,8}
 ------Águas Lindas de Goiás(Cidade)    | {1,3,8}
 ------Luziânia(Cidade)                 | {1,3,8}
 ------Anápolis(Cidade)                 | {1,3,8}
 ------Aparecida de Goiânia(Cidade)     | {1,3,8}
 ------Goiânia(Cidade)                  | {1,3,8}
 ------Primavera do Leste(Cidade)       | {1,3,9}
 ------Tangará da Serra(Cidade)         | {1,3,9}
 ------Cáceres(Cidade)                  | {1,3,9}
 ------Cuiabá(Cidade)                   | {1,3,9}
 ------Várzea Grande(Cidade)            | {1,3,9}
 ------Rondonópolis(Cidade)             | {1,3,9}
 ------Sinop(Cidade)                    | {1,3,9}
 ----Alagoas(Estado)                    | {1,4}
 ----Pernambuco(Estado)                 | {1,4}
 ----Rio Grande do Norte(Estado)        | {1,4}
 ----Paraíba(Estado)                    | {1,4}
 ----Ceará(Estado)                      | {1,4}
 ----Piauí(Estado)                      | {1,4}
 ----Maranhão(Estado)                   | {1,4}
 ----Bahia(Estado)                      | {1,4}
 ----Sergipe(Estado)                    | {1,4}
 ----Tocantins(Estado)                  | {1,5}
 ----Acre(Estado)                       | {1,5}
 ----Roraima(Estado)                    | {1,5}
 ----Rondônia(Estado)                   | {1,5}
 ----Pará(Estado)                       | {1,5}
 ----Amapá(Estado)                      | {1,5}
 ----Amazonas(Estado)                   | {1,5}
 ----São Paulo(Estado)                  | {1,6}
 ----Rio de Janeiro(Estado)             | {1,6}
 ----Espírito Santo(Estado)             | {1,6}
 ----Minas Gerais(Estado)               | {1,6}
 ------São Gonçalo(Cidade)              | {1,6,30}
 ------Rio de Janeiro(Cidade)           | {1,6,30}
 ------Duque de Caxias(Cidade)          | {1,6,30}
 ------Nova Iguaçu(Cidade)              | {1,6,30}
 ------Belford Roxo(Cidade)             | {1,6,30}
 ------Niterói(Cidade)                  | {1,6,30}
 ------São João de Meriti(Cidade)       | {1,6,30}
 ------Campos dos Goitacases(Cidade)    | {1,6,30}
 ------Petrópolis(Cidade)               | {1,6,30}
 ------Volta Redonda(Cidade)            | {1,6,30}
 ------Magé(Cidade)                     | {1,6,30}
 ------Itaboraí(Cidade)                 | {1,6,30}
 ------Mesquita(Cidade)                 | {1,6,30}
 ------Nova Friburgo(Cidade)            | {1,6,30}
 ------Barra Mansa(Cidade)              | {1,6,30}
 ------Macaé(Cidade)                    | {1,6,30}
 ------Sorocaba(Cidade)                 | {1,6,31}
 ------Ribeirão Preto(Cidade)           | {1,6,31}
 ------Santos(Cidade)                   | {1,6,31}
 ------Osasco(Cidade)                   | {1,6,31}
 ------Santo André(Cidade)              | {1,6,31}
 ------São José dos Campos(Cidade)      | {1,6,31}
 ------São Paulo(Cidade)                | {1,6,31}
 ------Guarulhos(Cidade)                | {1,6,31}
 ------Campinas(Cidade)                 | {1,6,31}
 ------São Bernardo do Campo(Cidade)    | {1,6,31}
 ----Rio Grande do Sul(Estado)          | {1,7}
 ----Santa Catarina(Estado)             | {1,7}
 ----Paraná(Estado)                     | {1,7}
 ------Caxias do Sul(Cidade)            | {1,7,34}
 ------Canoas(Cidade)                   | {1,7,34}
 ------Santa Maria(Cidade)              | {1,7,34}
 ------Porto Alegre(Cidade)             | {1,7,34}
 ------Pelotas(Cidade)                  | {1,7,34}
(80 rows)

*/