nnpc setup

nnpc.sql

\set path '''' `pwd`/NNPC_fixed.csv ''''

CREATE EXTENSION IF NOT EXISTS prefix WITH SCHEMA public;

DROP TABLE IF EXISTS national_destination_codes;

CREATE TABLE national_destination_codes (
    id SERIAL,
    country character varying(255),
    iso3166_3 character varying(3),
    iso3166_2 character varying(2),
    idd character varying(255),
    cc character varying(255),
    ndc1 character varying(255),
    ndc1t character varying(255),
    ndc2 character varying(255),
    sn1 character varying(255),
    sn1t character varying(255),
    sn2 character varying(255),
    snl character varying(255),
    description character varying(255),
    number_type character varying(255),
    test_numbers character varying(255),
    start_date timestamp without time zone,
    end_date timestamp without time zone,
    last_update timestamp without time zone,
    net_name character varying(255),
    net_type character varying(255),
    mcc character varying(255),
    mnc character varying(255),
    tadig_nc character varying(255),
    handset_code character varying(255),
    net_start_date timestamp without time zone,
    net_end_date timestamp without time zone,
    operator character varying(255),
    licensed_service_area character varying(255),
    country_notes text,
    nop_notes text,
    net_notes text,
    prefix_notes text,
    row_insertion_date timestamp without time zone,
    prefix prefix_range
);

COPY national_destination_codes (country, iso3166_3, iso3166_2, idd, cc, ndc1, ndc1t, ndc2, sn1, sn1t, sn2, snl, description, number_type, test_numbers, start_date, end_date, last_update, net_name, net_type, mcc, mnc, tadig_nc, handset_code, net_start_date, net_end_date, operator, licensed_service_area, country_notes, nop_notes, net_notes, prefix_notes, row_insertion_date)
FROM :path DELIMITER '|' CSV HEADER ENCODING 'windows-1251';

CREATE INDEX idx_prefix ON national_destination_codes USING gist(prefix);

UPDATE national_destination_codes SET prefix = CONCAT(cc, ndc2, sn2);