Postgres 14

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 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit

SELECT 1

set session  timezone to  UTC;

SET

select '2017-05-01T16:16:35-05:00' ::timestamptz;

timestamptz

2017-05-01 21:16:35+00

SELECT 1

create table test_timestamp(
    org text,
    tsz timestamptz,
    ts timestamp,
    offsettz interval
);

CREATE TABLE

create or replace  function supporttsz()
returns trigger as $$
begin
    new.tsz := (new.org)::timestamptz at time zone 'utc';
    new.ts := (new.org)::timestamptz at time zone 'utc';
    new.ts := new.ts :: timestamp;
    if
        SUBSTRING((right(trim(new.org),5)), 1,1) = '-' then
        new.offsettz :=(right(trim(new.org),5))::interval;
    elsif
        SUBSTRING((right(trim(new.org),5)), 1,1) = '+' then
        new.offsettz := (right(trim(new.org),5))::interval;
    elsif
        SUBSTRING((right(trim(new.org),6)), 1,1) = '-' then
        new.offsettz := (right(trim(new.org),6))::interval;
    elsif
        SUBSTRING((right(trim(new.org),6)), 1,1) = '+' then
        new.offsettz :=(right(trim(new.org),6))::interval;
    else  new.offsettz := '0::00' ::interval;
    end if;
    return new;
end;
$$ language plpgsql;

CREATE FUNCTION

create trigger tg_supporttsz_test_timestamp
    before insert on test_timestamp
for each row execute procedure supporttsz();

CREATE TRIGGER

insert into test_timestamp(org) values ('1999-01-08 04:05:06-8:00');
insert into test_timestamp(org) values ('2017-05-01T16:16:35-05:00');

INSERT 0 1

INSERT 0 1

table test_timestamp;

org
tsz
ts
offsettz

1999-01-08 04:05:06-8:00
1999-01-08 12:05:06+00
1999-01-08 12:05:06
-08:00:00

2017-05-01T16:16:35-05:00
2017-05-01 21:16:35+00
2017-05-01 21:16:35
-05:00:00

SELECT 2

set session  timezone to  Egypt;

SET

show timezone;

TimeZone

Egypt

SHOW

insert into test_timestamp(org) values ('2017-05-11T16:16:35-05:00');

INSERT 0 1

table test_timestamp;

org
tsz
ts
offsettz

1999-01-08 04:05:06-8:00
1999-01-08 14:05:06+02
1999-01-08 12:05:06
-08:00:00

2017-05-01T16:16:35-05:00
2017-05-01 23:16:35+02
2017-05-01 21:16:35
-05:00:00

2017-05-11T16:16:35-05:00
2017-05-11 21:16:35+02
2017-05-11 21:16:35
-05:00:00

SELECT 3

org	tsz	ts	offsettz
1999-01-08 04:05:06-8:00	1999-01-08 12:05:06+00	1999-01-08 12:05:06	-08:00:00
2017-05-01T16:16:35-05:00	2017-05-01 21:16:35+00	2017-05-01 21:16:35	-05:00:00

org	tsz	ts	offsettz
1999-01-08 04:05:06-8:00	1999-01-08 14:05:06+02	1999-01-08 12:05:06	-08:00:00
2017-05-01T16:16:35-05:00	2017-05-01 23:16:35+02	2017-05-01 21:16:35	-05:00:00
2017-05-11T16:16:35-05:00	2017-05-11 21:16:35+02	2017-05-11 21:16:35	-05:00:00