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?.
CREATE TABLE mytable (
mycolumn VARCHAR(100)
);
CREATE TABLE
INSERT INTO mytable (mycolumn) VALUES
('Brisbane.Wastewater.Fortitude.Valley.Pump.Station.ABC123'),
('Townsville.Water.Northern.Paluma.Reservoir.DEF456'),
('Cairns.Wastewater.Suburb.Asset.XYZ789');
INSERT 0 3
SELECT
SPLIT_PART(mycolumn, '.', 1) AS City,
SPLIT_PART(mycolumn, '.', 2) AS Department,
REPLACE(SUBSTRING(mycolumn, POSITION('.' IN mycolumn) + 1), '.', ', ') AS Site,
SPLIT_PART(mycolumn, '.', -1) AS Outstation
FROM mytable;
| city | department | site | outstation |
|---|---|---|---|
| Brisbane | Wastewater | Wastewater, Fortitude, Valley, Pump, Station, ABC123 | ABC123 |
| Townsville | Water | Water, Northern, Paluma, Reservoir, DEF456 | DEF456 |
| Cairns | Wastewater | Wastewater, Suburb, Asset, XYZ789 | XYZ789 |
SELECT 3
SELECT
SPLIT_PART(mycolumn, '.', 1) AS City,
SPLIT_PART(mycolumn, '.', 2) AS Department,
REGEXP_REPLACE(
SUBSTRING(mycolumn FROM POSITION('.' IN mycolumn) + 1),
CONCAT('.', SPLIT_PART(mycolumn, '.', -1)),
''
) AS Site,
SPLIT_PART(mycolumn, '.', -1) AS Outstation
FROM mytable;
| city | department | site | outstation |
|---|---|---|---|
| Brisbane | Wastewater | Wastewater.Fortitude.Valley.Pump.Station | ABC123 |
| Townsville | Water | Water.Northern.Paluma.Reservoir | DEF456 |
| Cairns | Wastewater | Wastewater.Suburb.Asset | XYZ789 |
SELECT 3
SELECT
SPLIT_PART(mycolumn, '.', 1) AS City,
SPLIT_PART(mycolumn, '.', 2) AS Department,
TRIM(BOTH '.' FROM SPLIT_PART(mycolumn, '.', 4)) AS Site,
SPLIT_PART(mycolumn, '.', 6) AS Outstation
FROM mytable;
| city | department | site | outstation |
|---|---|---|---|
| Brisbane | Wastewater | Valley | Station |
| Townsville | Water | Paluma | DEF456 |
| Cairns | Wastewater | Asset |
SELECT 3