Postgres 13

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?.

-- btree_gist is installed!
SELECT * FROM pg_extension WHERE extname = 'btree_gist';


oid
extname
extowner
extnamespace
extrelocatable
extversion
extconfig
extcondition


16385
btree_gist
10
16384
t
1.5
null
null



SELECT 1


CREATE TABLE hoo (
   hoo_id  serial PRIMARY KEY
 , shop_id int NOT NULL -- REFERENCES shop(shop_id)     -- reference to shop
 , hours   tsrange NOT NULL
 , CONSTRAINT hoo_no_overlap EXCLUDE USING gist (shop_id with =, hours WITH &&)
 , CONSTRAINT hoo_bounds_inclusive CHECK (lower_inc(hours) AND upper_inc(hours))
 , CONSTRAINT hoo_standard_week CHECK (hours <@ tsrange '[1996-01-01 0:0, 1996-01-08 0:0]')
);


-- function to normalize timestamps
CREATE OR REPLACE FUNCTION f_hoo_time(timestamptz)
  RETURNS timestamp
  LANGUAGE sql IMMUTABLE AS
$func$
SELECT timestamp '1996-01-01' + ($1 AT TIME ZONE 'UTC' - date_trunc('week', $1 AT TIME ZONE 'UTC'))
$func$;


-- function to normalize (and possibly split) ranges
CREATE OR REPLACE FUNCTION f_hoo_hours(_from timestamptz, _to timestamptz)
  RETURNS TABLE (hoo_hours tsrange)
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE COST 500 ROWS 1 AS
$func$
DECLARE
   ts_from timestamp := f_hoo_time(_from);
   ts_to   timestamp := f_hoo_time(_to);
BEGIN
   -- sanity checks (optional)
   IF _to <= _from THEN
      RAISE EXCEPTION '%', '_to must be later than _from!';
   ELSIF _to > _from + interval '1 week' THEN
      RAISE EXCEPTION '%', 'Interval cannot span more than a week!';
   END IF;

   IF ts_from > ts_to THEN  -- split range at Mon 00:00
      RETURN QUERY
      VALUES (tsrange('1996-01-01', ts_to  , '[]'))
           , (tsrange(ts_from, '1996-01-08', '[]'));
   ELSE                     -- simple case: range in standard week
      hoo_hours := tsrange(ts_from, ts_to, '[]');
      RETURN NEXT;
   END IF;

   RETURN;
END
$func$;

CREATE TABLE



CREATE FUNCTION



CREATE FUNCTION


-- INSERT with normalized and split time ranges (where necessary)
INSERT INTO hoo(shop_id, hours)
VALUES
  (1, '[1996-01-01 12:00, 1996-01-01 17:00]')
, (1, '[1996-01-03 18:30, 1996-01-04 05:00]')  -- cross midnight UTC
, (1, '[1996-01-04 22:33, 1996-01-06 13:44]')  -- multiple days
, (1, '[1996-01-07 22:00, 1996-01-08 00:00]')  -- cross week: split - 1. ending sunday 24:00 ...
, (1, '[1996-01-01 00:00, 1996-01-01 10:00]')  -- .. 2. starting mon 00:00 ..
;

-- INSERT with *any* timestamps, processed with f_hoo_hours()
-- You can really use *any* timestamptz, they are normalized automatically
INSERT INTO hoo(shop_id, hours)
SELECT id, f_hoo_hours(f, t)
FROM  (
   VALUES
      (2, timestamptz '1996-01-01 12:00+00', timestamptz '1996-01-01 17:00+00')
    , (2, '1996-01-07 22:00+00', '1996-01-08 10:00+00')   -- cross week: split in 2 rows !!!
    , (3, '2016-01-11 00:00+04', '2016-01-11 08:00+04')
    , (4, '2016-01-11 00:00-04', '2016-01-11 08:00-04')
    , (5, '2016-01-11 02:00+04', '2016-01-11 08:00+04')
   ) t(id, f, t);

-- Even works with timestamp without time zone: time zone of current session is assumed
INSERT INTO hoo(shop_id, hours)
SELECT id, f_hoo_hours(f, t)
FROM  (
   VALUES
      (6, timestamp '2016-01-11 00:00', timestamp '2016-01-11 08:00')
    , (7, '2016-01-11 00:00', '2016-01-11 08:00')
   ) t(id, f, t);

INSERT 0 5



INSERT 0 8



INSERT 0 2


TABLE hoo;


hoo_id
shop_id
hours


1
1
["1996-01-01 12:00:00","1996-01-01 17:00:00"]

2
1
["1996-01-03 18:30:00","1996-01-04 05:00:00"]

3
1
["1996-01-04 22:33:00","1996-01-06 13:44:00"]

4
1
["1996-01-07 22:00:00","1996-01-08 00:00:00"]

5
1
["1996-01-01 00:00:00","1996-01-01 10:00:00"]

6
2
["1996-01-01 12:00:00","1996-01-01 17:00:00"]

7
2
["1996-01-01 00:00:00","1996-01-01 10:00:00"]

8
2
["1996-01-07 22:00:00","1996-01-08 00:00:00"]

9
3
["1996-01-01 00:00:00","1996-01-01 04:00:00"]

10
3
["1996-01-07 20:00:00","1996-01-08 00:00:00"]

11
4
["1996-01-01 04:00:00","1996-01-01 12:00:00"]

12
5
["1996-01-01 00:00:00","1996-01-01 04:00:00"]

13
5
["1996-01-07 22:00:00","1996-01-08 00:00:00"]

14
6
["1996-01-01 00:00:00","1996-01-01 08:00:00"]

15
7
["1996-01-01 00:00:00","1996-01-01 08:00:00"]



SELECT 15


-- QUERY
SELECT *
FROM   hoo
WHERE  hours @> f_hoo_time('2014-03-01 08:27+01');


hoo_id
shop_id
hours


3
1
["1996-01-04 22:33:00","1996-01-06 13:44:00"]



SELECT 1

hoo_id	shop_id	hours
1	1	["1996-01-01 12:00:00","1996-01-01 17:00:00"]
2	1	["1996-01-03 18:30:00","1996-01-04 05:00:00"]
3	1	["1996-01-04 22:33:00","1996-01-06 13:44:00"]
4	1	["1996-01-07 22:00:00","1996-01-08 00:00:00"]
5	1	["1996-01-01 00:00:00","1996-01-01 10:00:00"]
6	2	["1996-01-01 12:00:00","1996-01-01 17:00:00"]
7	2	["1996-01-01 00:00:00","1996-01-01 10:00:00"]
8	2	["1996-01-07 22:00:00","1996-01-08 00:00:00"]
9	3	["1996-01-01 00:00:00","1996-01-01 04:00:00"]
10	3	["1996-01-07 20:00:00","1996-01-08 00:00:00"]
11	4	["1996-01-01 04:00:00","1996-01-01 12:00:00"]
12	5	["1996-01-01 00:00:00","1996-01-01 04:00:00"]
13	5	["1996-01-07 22:00:00","1996-01-08 00:00:00"]
14	6	["1996-01-01 00:00:00","1996-01-01 08:00:00"]
15	7	["1996-01-01 00:00:00","1996-01-01 08:00:00"]