add batch
remove batch
split batch
comment selection
show hidden batches
hide batch
highlight batch
db<>fiddle
Db2
Firebird
MariaDB
MySQL
Node.js
Oracle
Postgres
SQLite
SQL Server
TimescaleDB
YugabyteDB
Developer-C 11.1
3.0
4.0
10.2
10.3
10.4
10.5
10.6
10.7
10.8
10.9
5.5
5.6
5.7
8.0
18
11g Release 2
18c
21c
23c
8.4
9.3
9.4
9.5
9.6
10
11
12
13
14
15
16
3.8
3.16
3.27
3.39
2012
2014
2016
2017
2017 (Linux)
2019
2019 (Linux)
2022
2.11
2.14
2.6
2.8
2.18
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
Sakila
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
AdventureWorks
no sample DB
no sample DB
AdventureWorks
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
run
markdown
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?
.
-- Table: l_billing_account -- DROP TABLE l_billing_account; CREATE TABLE l_billing_account ( billing_account_guid character varying(40) COLLATE pg_catalog."default" NOT NULL, ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer, load_dttm timestamp(6) without time zone NOT NULL, log_position_number character varying(40) COLLATE pg_catalog."default" NOT NULL, operation_code character varying(40) COLLATE pg_catalog."default" NOT NULL, commit_dttm timestamp(6) without time zone NOT NULL, oracle_scn character varying(40) COLLATE pg_catalog."default", ban integer NOT NULL, bl_cur_bill_seq_no smallint, bl_last_prod_date timestamp(6) without time zone, bill_method character varying(2) COLLATE pg_catalog."default", bill_method_eff_date timestamp(6) without time zone, rstr_fee_qid character varying(15) COLLATE pg_catalog."default", prev_bill_format character varying(2) COLLATE pg_catalog."default", CONSTRAINT billing_account_pkey PRIMARY KEY (billing_account_guid, ban) );
-- FUNCTION: func_l_billing_account_insert_trigger() CREATE FUNCTION func_l_billing_account_insert_trigger() RETURNS trigger LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOF AS $BODY$ BEGIN IF ( NEW.load_dttm >= '2020-02-01 00:00:00-07' AND NEW.load_dttm < '2020-02-16 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m02begin VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-02-16 00:00:00-07' AND NEW.load_dttm < '2020-03-01 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m02end VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-03-01 00:00:00-07' AND NEW.load_dttm < '2020-03-16 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m03begin VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-03-16 00:00:00-07' AND NEW.load_dttm < '2020-04-01 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m03end VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-04-01 00:00:00-07' AND NEW.load_dttm < '2020-04-16 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m04begin VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-04-16 00:00:00-07' AND NEW.load_dttm < '2020-05-01 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m04end VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-05-01 00:00:00-07' AND NEW.load_dttm < '2020-05-16 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m05begin VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-05-16 00:00:00-07' AND NEW.load_dttm < '2020-06-01 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m05end VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-06-01 00:00:00-07' AND NEW.load_dttm < '2020-06-16 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m06begin VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-06-16 00:00:00-07' AND NEW.load_dttm < '2020-07-01 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m06end VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-07-01 00:00:00-07' AND NEW.load_dttm < '2020-07-16 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m07begin VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-07-16 00:00:00-07' AND NEW.load_dttm < '2020-08-01 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m07end VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-08-01 00:00:00-07' AND NEW.load_dttm < '2020-08-16 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m08begin VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-08-16 00:00:00-07' AND NEW.load_dttm < '2020-09-01 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m08end VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-09-01 00:00:00-07' AND NEW.load_dttm < '2020-09-16 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m09begin VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-09-16 00:00:00-07' AND NEW.load_dttm < '2020-10-01 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m09end VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-10-01 00:00:00-07' AND NEW.load_dttm < '2020-10-16 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m10begin VALUES (NEW.*); ELSIF ( NEW.load_dttm >= '2020-10-16 00:00:00-07' AND NEW.load_dttm < '2020-11-01 00:00:00-07' ) THEN INSERT INTO l_billing_account_y2020m10end VALUES (NEW.*); ELSE RAISE EXCEPTION ' out of range exception. Fix the func_l_billing_account_insert_trigger() function. HINT: Might need to be create new partion table, load_dttm is out of range in existing child_tables Action: Please contact DBA' ; END IF; RETURN NULL; END; $BODY$;
-- Trigger: l_billing_account_partition_trigger CREATE TRIGGER l_billing_account_partition_trigger BEFORE INSERT OR UPDATE ON l_billing_account FOR EACH ROW EXECUTE PROCEDURE func_l_billing_account_insert_trigger();
-- Table: l_billing_account_y2020m09end -- DROP TABLE l_billing_account_y2020m09end; CREATE TABLE l_billing_account_y2020m09end ( CONSTRAINT l_billing_account_y2020m09end_load_dttm_check CHECK (load_dttm >= '2020-09-16 00:00:00'::timestamp without time zone AND load_dttm < '2020-10-01 00:00:00'::timestamp without time zone) ) INHERITS (l_billing_account);