cr.sql

application/sql

Download

CREATE TABLE public.countries (
    country character(2) NOT NULL,
    country_name text NOT NULL,
    "numeric" smallint NOT NULL
);

CREATE TABLE public.regions (
    region smallint NOT NULL,
    region_name text NOT NULL
);

CREATE VIEW public._country_or_region AS
 SELECT countries."numeric" AS country_or_region_code,
    countries.country_name AS country_or_region_name,
    true AS is_country
   FROM public.countries
UNION ALL
 SELECT regions.region AS country_or_region_code,
    regions.region_name AS country_or_region_name,
    false AS is_country
   FROM public.regions;

CREATE TABLE public.country_or_region (
    country_or_region_code smallint NOT NULL,
    country_or_region_name text NOT NULL,
    is_country boolean NOT NULL
);

COPY public.countries (country, country_name, "numeric") FROM stdin;
AD	Andorra	20
\.

COPY public.country_or_region (country_or_region_code, country_or_region_name, is_country) FROM stdin;
20	Andorra	t
1	World	f
\.

COPY public.regions (region, region_name) FROM stdin;
1	World
\.

MERGE INTO country_or_region AS qq_tgt USING _country_or_region AS qq_src ON qq_tgt."country_or_region_code"=qq_src."country_or_region_code" WHEN MATCHED AND qq_src IS DISTINCT FROM qq_tgt THEN UPDATE SET "country_or_region_name"=qq_src."country_or_region_name","is_country"=qq_src."is_country" WHEN NOT MATCHED BY TARGET THEN INSERT ("country_or_region_code","country_or_region_name","is_country") VALUES(qq_src."country_or_region_code",qq_src."country_or_region_name",qq_src."is_country") WHEN NOT MATCHED BY SOURCE THEN DELETE;