Hi
Im having a problem with querying a table partitioned by date.
Depending on the sequence of operations on a date parameter used in a where clause, I either get the expected results,
orno results.
This suggests a bug in the handling of date parameters and partition range handling.
I’ve replicated this down to a single sequence of create table, insert data, query.
This issue occurs for me on postgresql 11.2 on a mac, installed via brew.
In this case the table is partitioned by an id and then by the date, if it is partitioned by only the date everything
worksas expected.
However, I am attempting to add partitioning to a fairly large sofware-as-a-service platform, so making changes to the
tabledefinitions or global code changes is not really practical.
The sql in question is below.
I hope there is something simple I can change in the partition definitions to work around this.
Many Thanks,
Alan Jackson
Data Architect
TVSquared
--SQL STARTS HERE
--drop table dataid;
CREATE TABLE dataid
(
id integer not null,
datadatetime timestamp without time zone NOT NULL,
CONSTRAINT dataid_pkey PRIMARY KEY (id, datadatetime)
) PARTITION BY RANGE (id, datadatetime)
;
CREATE TABLE dataid_201902 PARTITION OF dataid FOR VALUES FROM (1, '2019-02-01 00:00:00') TO (1, '2019-03-01
00:00:00');
CREATE TABLE dataid_default PARTITION OF dataid DEFAULT;
insert into dataid values (1,'2019-02-24T00:00:00');
--- returns 1 row as expected
select * from dataid where id=1 and datadatetime < (('2019-02-26T00:00:00'::timestamp::timestamp at time zone
'America/New_York') at time zone 'UTC' + '2 days'::interval);
--- returns no rows
select * from dataid where id=1 and datadatetime < (('2019-02-26T00:00:00'::timestamp::timestamp at time zone
'America/New_York'+ '2 days'::interval) at time zone 'UTC');
-- both date expressions evaluate to the same date.
select
(('2019-02-26T00:00:00'::timestamp::timestamp at time zone 'America/New_York' ) at time zone 'UTC' + '2
days'::interval)as workingdate,
(('2019-02-26T00:00:00'::timestamp::timestamp at time zone 'America/New_York' + '2 days'::interval) at time zone 'UTC')
asnotworkingdate;
--SQL ENDS HERE
--
TV Squared Limited is a company registered in Scotland. Registered number:
SC421072. Registered office: CodeBase, Argyle House, 3 Lady Lawson Street,
Edinburgh, EH3 9DR.
TV Squared Inc (File No. 5600204) is an Incorporated
company registered in Delaware. Principal office: 1412 Broadway, 22 Fl, New
York, New York, 10018
TV Squared GmbH is a company registered in Munich.
Registered number: HRB 236077. Registered office: Oskar-von-Miller-Ring 20,
c/o wework, 80333 Munchen
This message is private and confidential. If
you have received this message in error, please notify us and remove it
from your system.
