importccl: support DROP TABLE [IF EXISTS] for PGDUMP

[otan]

A pgdump generated by default from ogr2ogr or using pg_dump -clean includes a DROP TABLE [IF EXISTS] line that after 921c60a we no longer ignore and explicitly error out. An example of a PGDUMP i expect to work using IMPORT PGDUMP:

DROP TABLE public.pgdump_test;
CREATE TABLE public.pgdump_test (
    a integer
);

DROP TABLE IF EXISTS public.pgdump_test;
CREATE TABLE public.pgdump_test (
    a integer
);

Ideally we should be able to handle these -- it makes sense, especially from the geospatial perspective -- for a user to have a dump that nukes a table and overwrites it if necessary.

Might be a good start project / first issue, fwiw.

[blathers-crl]

Hi @otan, I've guessed the C-ategory of your issue and suitably labeled it. Please re-label if inaccurate.

While you're here, please consider adding an A- label to help keep our repository tidy.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[otan]

pgdump example:

$ pg_dump --clean -t pgdump_test
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.3
-- Dumped by pg_dump version 12.3

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

DROP TABLE public.pgdump_test;
SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: pgdump_test; Type: TABLE; Schema: public; Owner: otan
--

CREATE TABLE public.pgdump_test (
    a integer
);


ALTER TABLE public.pgdump_test OWNER TO otan;

--
-- Data for Name: pgdump_test; Type: TABLE DATA; Schema: public; Owner: otan
--

COPY public.pgdump_test (a) FROM stdin;
1
\.


--
-- PostgreSQL database dump complete
--

ogr2ogr example:

$ ogr2ogr -f PGDUMP  $x.sql $f -skipfailures
...
SET standard_conforming_strings = OFF;
DROP TABLE IF EXISTS "public"."nyc_subway_stations" CASCADE;
DELETE FROM geometry_columns WHERE f_table_name = 'nyc_subway_stations' AND f_table_schema = 'public';
BEGIN;
CREATE TABLE "public"."nyc_subway_stations" ( "ogc_fid" SERIAL, CONSTRAINT "nyc_subway_stations_pk" PRIMARY KEY ("ogc_fid") );
SELECT AddGeometryColumn('public','nyc_subway_stations','wkb_geometry',26918,'POINT',2);
CREATE INDEX "nyc_subway_stations_wkb_geometry_geom_idx" ON "public"."nyc_subway_stations" USING GIST ("wkb_geometry");
ALTER TABLE "public"."nyc_subway_stations" ADD COLUMN "objectid" FLOAT8;
ALTER TABLE "public"."nyc_subway_stations" ADD COLUMN "id" NUMERIC(32,10);
ALTER TABLE "public"."nyc_subway_stations" ADD COLUMN "name" VARCHAR(31);
ALTER TABLE "public"."nyc_subway_stations" ADD COLUMN "alt_name" VARCHAR(38);
ALTER TABLE "public"."nyc_subway_stations" ADD COLUMN "cross_st" VARCHAR(27);
ALTER TABLE "public"."nyc_subway_stations" ADD COLUMN "long_name" VARCHAR(60);
ALTER TABLE "public"."nyc_subway_stations" ADD COLUMN "label" VARCHAR(50);
ALTER TABLE "public"."nyc_subway_stations" ADD COLUMN "borough" VARCHAR(15);
ALTER TABLE "public"."nyc_subway_stations" ADD COLUMN "nghbhd" VARCHAR(30);
ALTER TABLE "public"."nyc_subway_stations" ADD COLUMN "routes" VARCHAR(20);
ALTER TABLE "public"."nyc_subway_stations" ADD COLUMN "transfers" VARCHAR(25);
ALTER TABLE "public"."nyc_subway_stations" ADD COLUMN "color" VARCHAR(30);
ALTER TABLE "public"."nyc_subway_stations" ADD COLUMN "express" VARCHAR(10);
ALTER TABLE "public"."nyc_subway_stations" ADD COLUMN "closed" VARCHAR(10);
INSERT INTO "public"."nyc_subway_stations" ("wkb_geometry" , "objectid", "id", "name", "alt_name", "cross_st", "long_name", "label", "borough", "nghbhd", "routes", "transfers", "color", "express", "closed") VALUES ('010100002026690000371775B5C3CE2141CBD2347771315141', 1, 376.0000000000, 'Cortlandt St', NULL, 'Church St', 'Cortlandt St (R,W) Manhattan', 'Cortlandt St (R,W)', 'Manhattan', NULL, 'R,W', 'R,W', 'YELLOW', NULL, NULL);
COMMIT;