Got it, let's tackle this problem step by step. You've got this string format stored in your PostgreSQL database: schedulestart | event_labels (example: 2018-04-04 | 9=TTR&11=DNV&14=SWW&26=DNV&2=QQQ&43=FTW) and need to split it into structured data for reporting—no external tools allowed, just pure PostgreSQL. Here's how to do it reliably:

Step 1: Split the Main String into Schedulestart and Raw Labels #

First, we'll split the original string at the | (space-pipe-space) separator. To handle edge cases where spacing might be inconsistent (like | without spaces or extra spaces), use a regex-based split instead of a simple split_part:

WITH split_initial AS (
  SELECT
    -- Extract and trim the schedulestart date
    trim((regexp_split_to_array(your_column, '\s*\|\s*'))[1]) AS schedulestart,
    -- Extract labels, replace HTML entity & with actual &, then trim
    replace(trim((regexp_split_to_array(your_column, '\s*\|\s*'))[2]), '&', '&') AS cleaned_event_labels
  FROM your_table
)
SELECT * FROM split_initial;

The regex \s*\|\s* matches any number of whitespace characters around the pipe, so it works even if the separator is messy. We also replace & (the HTML entity for &) to get the actual separator between label pairs.

Step 2: Split Labels into Individual Key-Value Pairs #

Next, we'll take the cleaned label string and split it into rows of key=value pairs, then split each pair into separate label_key and label_value columns:

WITH split_initial AS (
  SELECT
    trim((regexp_split_to_array(your_column, '\s*\|\s*'))[1]) AS schedulestart,
    replace(trim((regexp_split_to_array(your_column, '\s*\|\s*'))[2]), '&', '&') AS cleaned_event_labels
  FROM your_table
),
split_labels AS (
  SELECT
    schedulestart,
    -- Split each key=value pair into key and value
    split_part(label_pair, '=', 1) AS label_key,
    split_part(label_pair, '=', 2) AS label_value
  FROM split_initial,
       -- Split cleaned labels into rows using & as separator
       regexp_split_to_table(cleaned_event_labels, '&') AS label_pair
  -- Filter out any empty rows from trailing/leading &
  WHERE label_pair != ''
)
SELECT * FROM split_labels;

This will give you a row for each label tied to its schedulestart date—perfect for detailed analysis.

Step 3: Pivot Labels into Columns (For Report-Friendly Format) #

If you need to turn those label rows into columns (so each label is a separate column in your report), use PostgreSQL's crosstab function from the tablefunc extension. First, make sure the extension is installed:

CREATE EXTENSION IF NOT EXISTS tablefunc;

Then use this query to pivot the data:

WITH split_initial AS (
  SELECT
    trim((regexp_split_to_array(your_column, '\s*\|\s*'))[1]) AS schedulestart,
    replace(trim((regexp_split_to_array(your_column, '\s*\|\s*'))[2]), '&', '&') AS cleaned_event_labels
  FROM your_table
),
split_labels AS (
  SELECT
    schedulestart,
    split_part(label_pair, '=', 1) AS label_key,
    split_part(label_pair, '=', 2) AS label_value
  FROM split_initial,
       regexp_split_to_table(cleaned_event_labels, '&') AS label_pair
  WHERE label_pair != ''
)
SELECT *
FROM crosstab(
  -- First query: returns rows to pivot
  'SELECT schedulestart, label_key, label_value FROM split_labels ORDER BY 1,2',
  -- Second query: defines the columns (distinct label keys)
  'SELECT DISTINCT label_key FROM split_labels ORDER BY 1'
) AS ct(
  schedulestart date,
  "2" text,
  "9" text,
  "11" text,
  "14" text,
  "26" text,
  "43" text
);

Adjust the column list in the ct definition to match all distinct label keys in your data. This gives you a flat, report-ready table where each schedulestart has its labels as separate columns.

All these steps use native PostgreSQL functions, so you don't need to export data to another language to parse it.

内容的提问来源于stack exchange，提问作者Kwahn