SELECT
--  base.diff_lat,
--  base.diff_lon,
--  base.abs_lat,
--  base.abs_lon,
--  base.record_code,
--  base.country_code                                           AS place_nation,
--  base.admin1_code                                            AS place_admin1,
--  base.admin2_code                                            AS place_admin2,
  --
  -- Comment out the fields above to produce the 'alternate_names' insert table.
  -- The fields bellow are one's that are inserted into the 'altername_names' table.
  --
  base.geoname_id                                             AS geoname_id,
  'post'::text                                                AS iso_language,
  base.postal_code                                            AS alternate_name,
  null::text                                                  AS is_preferred_name,
  null::text                                                  AS is_short_name
  --
FROM (


SELECT
  (CASE WHEN (place_latitude - postal_latitude) = 0.0000
   THEN null::numeric
   ELSE place_latitude - postal_latitude END
  )                                                           AS diff_lat,
  (CASE WHEN (place_longitude - postal_longitude) = 0.0000
   THEN null::numeric
   ELSE place_longitude - postal_longitude END
  )                                                           AS diff_lon, 
  abs(place_latitude - postal_latitude)                       AS abs_lat,
  abs(place_longitude - postal_longitude)                     AS abs_lon,
  x_all.*
FROM (

/*
  SELECT * FROM (
  SELECT DISTINCT ON (gpostal.country_code, gpostal.postal_code, gpostal.place_name, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code)
    '1'::text                                                 AS record_code,
  --  gplaces.*
    gplaces.geoname_id                                        AS geoname_id,
    trim(gplaces.full_name)                                   AS full_name,
    trim(gplaces.feature_class)                               AS feature_class,
    trim(gplaces.feature_code)                                AS feature_code,
    trim(gplaces.country_code)                                AS country_check,
    trim(gplaces.admin1_code)                                 AS admin1_check,
    trim(gplaces.admin2_code)                                 AS admin2_check,
    round(gplaces.latitude, 4)                                AS place_latitude,
    round(gplaces.longitude, 4)                               AS place_longitude,
  --  gnames.*,
    'm1->'::text                                              AS m1,
    gnames.geoname_name_id                                    AS geoname_name_id,
    trim(gnames.alternate_name)                               AS altername_name,
    trim(gnames.iso_language)                                 AS language_code,
  --  gpostal.*,
    'm2->'::text                                              AS m2,
    trim(gpostal.country_code)                                AS country_code,
    trim(gpostal.postal_code)                                 AS postal_code,
    trim(gpostal.place_name)                                  AS place_name,
    trim(gpostal.admin1_code)                                 AS admin1_code,
    trim(gpostal.admin2_code)                                 AS admin2_code,
    trim(gpostal.admin1_name)                                 AS admin1_name,
    trim(gpostal.admin2_name)                                 AS admin2_name,
    trim(gpostal.admin3_name)                                 AS admin3_name,
    round(gpostal.latitude::numeric(13,8), 4)                 AS postal_latitude,
    round(gpostal.longitude::numeric(13,8), 4)                AS postal_longitude,
    gpostal.accuracy                                          AS accuracy
  ----------
  FROM      xwor.geoname_places        AS gplaces 
  LEFT JOIN xwor.geoname_places_names  AS gnames  ON (gnames.geoname_id = gplaces.geoname_id)
  LEFT JOIN xwor.geoname_places_postal AS gpostal ON (lower(gnames.alternate_name) = lower(gpostal.place_name) AND
                                                      gpostal.country_code = gplaces.country_code)
  WHERE     gpostal.place_name IS NOT NULL
  AND       gplaces.feature_class = 'P' --AND gplaces.feature_code = 'PPL'
  ORDER BY  gpostal.country_code, gpostal.postal_code, gpostal.place_name, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code
  ) AS x_1

UNION
*/

  SELECT * FROM (
  SELECT DISTINCT ON (gpostal.country_code, gpostal.postal_code, gpostal.place_name, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code)
    '2'::text                                                 AS record_code,
  --  gplaces.*
    gplaces.geoname_id                                        AS geoname_id,
    trim(gplaces.full_name)                                   AS full_name,
    trim(gplaces.feature_class)                               AS feature_class,
    trim(gplaces.feature_code)                                AS feature_code,
    trim(gplaces.country_code)                                AS country_check,
    trim(gplaces.admin1_code)                                 AS admin1_check,
    trim(gplaces.admin2_code)                                 AS admin2_check,
    round(gplaces.latitude, 4)                                AS place_latitude,
    round(gplaces.longitude, 4)                               AS place_longitude,
  --  gnames.*,
    'm1->'::text                                              AS m1,
    gnames.geoname_name_id                                    AS geoname_name_id,
    trim(gnames.alternate_name)                               AS altername_name,
    trim(gnames.iso_language)                                 AS language_code,
  --  gpostal.*,
    'm2->'::text                                              AS m2,
    trim(gpostal.country_code)                                AS country_code,
    trim(gpostal.postal_code)                                 AS postal_code,
    trim(gpostal.place_name)                                  AS place_name,
    trim(gpostal.admin1_code)                                 AS admin1_code,
    trim(gpostal.admin2_code)                                 AS admin2_code,
    trim(gpostal.admin1_name)                                 AS admin1_name,
    trim(gpostal.admin2_name)                                 AS admin2_name,
    trim(gpostal.admin3_name)                                 AS admin3_name,
    round(gpostal.latitude::numeric(13,8), 4)                 AS postal_latitude,
    round(gpostal.longitude::numeric(13,8), 4)                AS postal_longitude,
    gpostal.accuracy                                          AS accuracy
  ----------
  FROM      xwor.geoname_places_postal AS gpostal
  LEFT JOIN xwor.geoname_places        AS gplaces ON (lower(gpostal.place_name) = lower(gplaces.full_name) AND
                                                      gpostal.country_code = gplaces.country_code AND
                                                      gpostal.admin1_code  = gplaces.admin1_code AND
                                                      gpostal.admin2_code  = gplaces.admin2_code)
  LEFT JOIN xwor.geoname_places_names  AS gnames  ON (lower(gpostal.place_name) = lower(gnames.alternate_name) AND
                                                      gnames.geoname_id = gplaces.geoname_id)
  WHERE     gpostal.place_name IS NOT NULL
  AND       gplaces.feature_class = 'P' --AND gplaces.feature_code = 'PPL'
--  ORDER BY  gpostal.country_code, gpostal.postal_code, gpostal.place_name, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code
  ) AS x_2


) AS x_all
-- ORDER BY country_code, postal_code, place_name, geoname_id, record_code
) AS base
WHERE    base.record_code = '2'
AND      base.country_code = 'NL'
--
ORDER BY country_code, postal_code, place_name, geoname_id
;