add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
version
PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit
CREATE TABLE public.driver_tmp
(
number_driver character varying(250) COLLATE pg_catalog."default" NOT NULL,
cod_driver character varying(250) COLLATE pg_catalog."default" NOT NULL UNIQUE,
full_name character varying(250) COLLATE pg_catalog."default" NOT NULL,
active boolean default 'true',
CONSTRAINT driver_tmp_pkey PRIMARY KEY (number_driver)
);
CREATE TABLE public.driver
(
number_driver character varying(250) COLLATE pg_catalog."default" NOT NULL,
cod_driver character varying(250) COLLATE pg_catalog."default" NOT NULL UNIQUE,
full_name character varying(250) COLLATE pg_catalog."default" NOT NULL,
active boolean default 'true',
CONSTRAINT driver_pkey PRIMARY KEY (number_driver)
);
CREATE TABLE public.driver_false
(
number_driver character varying(250) COLLATE pg_catalog."default" NOT NULL,
cod_driver character varying(250) COLLATE pg_catalog."default" NOT NULL UNIQUE,
full_name character varying(250) COLLATE pg_catalog."default" NOT NULL,
active boolean default 'false',
CONSTRAINT conductor_false_pkey PRIMARY KEY (number_driver)
);


--- Insert
CREATE FUNCTION TR_DRIVER() RETURNS TRIGGER
AS
$$
BEGIN

IF EXISTS(SELECT number_driver, cod_driver
FROM driver AS con
WHERE EXISTS (SELECT * FROM driver_tmp AS tmp
WHERE con.number_driver<>tmp.number_driver AND con.cod_driver=tmp.cod_driver)) THEN

INSERT INTO driver_false(number_driver, cod_driver, full_name)
VALUES (new.number_driver, new.cod_driver, new.full_name);
ELSE

INSERT INTO driver(number_driver, cod_driver, full_name, active)
VALUES (new.number_driver, new.cod_driver, new.full_name, new.active)
ON CONFLICT (number_driver)
DO UPDATE SET
cod_driver=excluded.cod_driver,
full_name=excluded.full_name,
active=excluded.active;

END IF;
RETURN NEW;
END $$
LANGUAGE plpgsql;
--- create new Trigger

CREATE TRIGGER TR_DRIVER_TMP AFTER INSERT OR UPDATE ON driver_tmp
FOR EACH ROW
EXECUTE PROCEDURE TR_DRIVER() ;

INSERT INTO public.driver_tmp(number_driver, cod_driver, full_name )
VALUES ('111111', '1010', 'Juanito Perez');
INSERT INTO public.driver_tmp(number_driver, cod_driver, full_name)
VALUES ('222222', '2020', 'Ariel Humberto');
INSERT INTO public.driver_tmp(number_driver, cod_driver, full_name)
VALUES ('333333', '3030', 'Jorge Toro');
1 rows affected

1 rows affected

1 rows affected
SELECT * FROM driver_tmp;
SELECT * FROM driver;
SELECT * FROM driver_false;
number_driver cod_driver full_name active
111111 1010 Juanito Perez t
222222 2020 Ariel Humberto t
333333 3030 Jorge Toro t
number_driver cod_driver full_name active
111111 1010 Juanito Perez t
222222 2020 Ariel Humberto t
333333 3030 Jorge Toro t
DELETE FROM public.driver_tmp;
3 rows affected
--- NEW RECORD
INSERT INTO public.driver_tmp(number_driver, cod_driver, full_name)
VALUES ('111111', '1010', 'Juanito Perez Linguini');
INSERT INTO public.driver_tmp(number_driver, cod_driver, full_name)
VALUES ('222222', '2020', 'Ariel Humberto Lechumbre Agech');
INSERT INTO public.driver_tmp(number_driver, cod_driver, full_name)
VALUES ('444444', '3030', 'Monica Hermez Vanesa Rech');
1 rows affected

1 rows affected

1 rows affected
SELECT * FROM driver_tmp;
SELECT * FROM driver;
SELECT * FROM driver_false;
number_driver cod_driver full_name active
111111 1010 Juanito Perez Linguini t
222222 2020 Ariel Humberto Lechumbre Agech t
444444 3030 Monica Hermez Vanesa Rech t
number_driver cod_driver full_name active
333333 3030 Jorge Toro t
111111 1010 Juanito Perez Linguini t
222222 2020 Ariel Humberto Lechumbre Agech t
number_driver cod_driver full_name active
444444 3030 Monica Hermez Vanesa Rech f
--- Select, which should be in "driver_false"
SELECT number_driver, cod_driver, full_name, active
FROM driver AS con
WHERE EXISTS (SELECT * FROM driver_tmp AS tmp
WHERE con.number_driver<>tmp.number_driver AND con.cod_driver=tmp.cod_driver)
number_driver cod_driver full_name active
333333 3030 Jorge Toro t