Incluir coluna de DDD da cidade about city-codes

[ppKrauss]

Cuidado, incluir ordenada,

COPY (
  SELECT c.name, c.state, c.wdid as "wdId", c.idibge as "idIBGE", 
       c.lexlabel as "lexLabel", c.creation, c.extinction, 
       c.postalcode_ranges as "postalCode_ranges", 
       ddd.ddd, c.notes
  FROM dataset.vw8_anatel_ddd as ddd RIGHT JOIN dataset.vw2_br_city_codes c
     ON c.state=ddd.uf AND c.lexlabel=ddd.namelex  -- or synonym!
  ORDER BY std_collate(name), name, state,3
) TO '/tmp/test.csv' CSV HEADER;

E fica ainda pendente o tratamento de sinônimos

[ppKrauss]

Concluído com sinônimos:

-- SOLUCAO correta fazendo inclusão da resolução por sinônimo:
COPY (
  WITH dd AS (
    SELECT DISTINCT c.name, c.state, ddd.ddd
    FROM dataset.vw8_anatel_ddd as ddd 
      INNER JOIN dataset.vw2_br_city_codes_tojoin_synonyms c
      ON c.state=ddd.uf AND c.lexlabel_join=ddd.namelex AND NOT(c.is_original)
  )
  SELECT name, state, wdid as "wdId", idibge as "idIBGE",
       lexlabel as "lexLabel", creation, extinction,
       postalcode_ranges as "postalCode_ranges",
       CASE WHEN ddd IS NULL -- tenta resolver por sinônimo
            THEN (SELECT ddd FROM dd WHERE dd.name=t.name AND dd.state=t.state)
            ELSE ddd
       END,
       notes
  FROM (
     SELECT c.name, c.state, c.wdid, c.idibge, c.lexlabel, c.creation, c.extinction,
       c.postalcode_ranges, ddd.ddd, c.notes
     FROM dataset.vw8_anatel_ddd as ddd RIGHT JOIN dataset.vw2_br_city_codes c
       ON c.state=ddd.uf AND c.lexlabel=ddd.namelex
  ) t ORDER BY std_collate(name), name, state, 3
) TO '/tmp/test_good.csv' CSV HEADER;