in a PostgreSQL database I have a table with a cell that contains working hours per day and its value is something like this:

---
Fri:
- '8'
- '9'
- '10'
- '11'
- '12'
- '13'
- '14'
- '15'
- '16'
- '17'
Mon:
- '8'
- '9'
... etc ...
Wed:
- '8'
- '9'
- '10'
- '11'
- '12'
- '13'
- '14'
- '15'
- '16'
- '17'

How can I read or put it in another table like this:

Where dow stands for day of week, for example 1 for monday, 2 for tuesday, etc..

Code Sample to create the starting table:

CREATE TABLE test
(
  wh text
);

INSERT INTO test (wh ) VALUES ('---
Fri:
- ''8''
- ''9''
- ''10''
- ''11''
- ''12''
- ''13''
- ''14''
- ''15''
- ''16''
- ''17''
Mon:
- ''8''
- ''9''
- ''10''
- ''11''
- ''12''
- ''13''
- ''14''
- ''15''
- ''16''
- ''17''
Sat:
- ''8''
- ''9''
- ''10''
- ''11''
- ''12''
- ''13''
Thu:
- ''8''
- ''9''
- ''10''
- ''11''
- ''12''
- ''13''
- ''14''
- ''15''
- ''16''
- ''17''
Tue:
- ''8''
- ''9''
- ''10''
- ''11''
- ''12''
- ''13''
- ''14''
- ''15''
- ''16''
- ''17''
Wed:
- ''8''
- ''9''
- ''10''
- ''11''
- ''12''
- ''13''
- ''14''
- ''15''
- ''16''
- ''17''');

Code sample to create the destination table:

CREATE TABLE working_hours
(
  dow integer NOT NULL,
  hour_start time ,
  hour_end time,
  CONSTRAINT pk_dow_working_hours PRIMARY KEY (dow)
);

INSERT INTO working_hours (dow, hour_start, hour_end, customer_user ) VALUES (1, '08:00:00', '18:00:00');
INSERT INTO working_hours (dow, hour_start, hour_end, customer_user ) VALUES (2, '08:00:00', '18:00:00');
INSERT INTO working_hours (dow, hour_start, hour_end, customer_user ) VALUES (3, '08:00:00', '18:00:00');
INSERT INTO working_hours (dow, hour_start, hour_end, customer_user ) VALUES (4, '08:00:00', '18:00:00');
INSERT INTO working_hours (dow, hour_start, hour_end, customer_user ) VALUES (5, '08:00:00', '18:00:00');
INSERT INTO working_hours (dow, hour_start, hour_end, customer_user ) VALUES (6, '08:00:00', '14:00:00');

CodePudding user response：

With a bit of regex magic the text can be transformed into a json with arrays.

Then it's just a matter of processing the json.

select 
  case l2.WeekDay
  when 'Mon' then 1
  when 'Tue' then 2
  when 'Wed' then 3
  when 'Thu' then 4
  when 'Fri' then 5
  when 'Sat' then 6
  when 'Sun' then 7
  end as DayOfWeek
, (l2.HourStart||':00')::time as TimeStart
, (l2.HourEnd||':00')::time as TimeEnd
from test
cross join lateral (
  select ('['||
   regexp_replace(
    regexp_replace(
     regexp_replace(wh, 
      '[^\w:]*([\w:] )[^\w:-]*','"\1",','g')
    , '"(\w ):",', '{"day":"\1","hours":[','g')
    , '"(,[{])|",$', '"]}\1','g')
    ||']')::json as whjs
) l1
cross join lateral (
  select 
    value->>'day' as WeekDay
  , value->'hours'->>0  as HourStart
  , value->'hours'->>-1  as HourEnd
  from json_array_elements(l1.whjs)
) l2

Test on db<>fiddle here