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?.
-- (1.1)
CREATE TABLE Product (
ProductNumber INT NOT NULL,
Etcetera CHAR(30) NOT NULL,
CONSTRAINT Product_PK PRIMARY KEY (ProductNumber)
);
CREATE TABLE Price (
ProductNumber INT NOT NULL,
StartDate DATE NOT NULL,
Amount INT NOT NULL, -- Retains the amount in cents.
CONSTRAINT Price_PK PRIMARY KEY (ProductNumber, StartDate),
CONSTRAINT Price_to_Product_FK FOREIGN KEY (ProductNumber)
REFERENCES Product (ProductNumber),
CONSTRAINT AmountIsValid_CK CHECK (Amount >= 0)
);
-- (1.2)
CREATE VIEW PriceWithEndDate AS
SELECT P.ProductNumber,
P.Etcetera AS ProductEtcetera,
PR.Amount AS PriceAmount,
PR.StartDate,
(
SELECT MIN(StartDate)
FROM Price InnerPR
WHERE P.ProductNumber = InnerPR.ProductNumber
AND InnerPR.StartDate > PR.StartDate
) AS EndDate
FROM Product P
JOIN Price PR
ON P.ProductNumber = PR.ProductNumber;
-- (2)
INSERT INTO Product
(ProductNumber, Etcetera)
VALUES
(1750, 'Price time series sample');
INSERT INTO Price
(ProductNumber, StartDate, Amount)
VALUES
(1750, '20170601', 1000),
(1750, '20170603', 2000),
(1750, '20170605', 3000),
(1750, '20170607', 4000);
1 rows affected
4 rows affected
-- (a)
SELECT ProductNumber,
ProductEtcetera,
PriceAmount,
StartDate,
EndDate
FROM PriceWithEndDate
ORDER BY StartDate DESC;
| productnumber | productetcetera | priceamount | startdate | enddate |
|---|---|---|---|---|
| 1750 | Price time series sample | 4000 | 2017-06-07 | null |
| 1750 | Price time series sample | 3000 | 2017-06-05 | 2017-06-07 |
| 1750 | Price time series sample | 2000 | 2017-06-03 | 2017-06-05 |
| 1750 | Price time series sample | 1000 | 2017-06-01 | 2017-06-03 |
-- (b)
SELECT ProductNumber,
ProductEtcetera,
PriceAmount,
StartDate,
EndDate
FROM PriceWithEndDate
WHERE ProductNumber = 1750
AND StartDate <= '20170602'
AND EndDate >= '20170602';
| productnumber | productetcetera | priceamount | startdate | enddate |
|---|---|---|---|---|
| 1750 | Price time series sample | 1000 | 2017-06-01 | 2017-06-03 |