Supabase 데이터 전처리 :: 텍스트 컬럼을 구분자를 기준으로 여러 컬럼으로 분리하기

주의

• prostgresSQL에 익숙하지 않은 경우 export CSV로 데이터 테이블을 필수로 백업을 하시고 진행하시기를 바람.
• 아래 사진을 보시고 글쓴이의 날짜 데이터 형식과 다르다면 본인 글에 있는 SQL 쿼리를 그대로 쓰지 마시기 바람.
  • 오전/오후로 구분하고 시간은 각각 00~12로 나가는 방식임.
  • 구분자로 마침표 (.)가 있음.
  • 중간에 공백이 많이 들어 가 있음.

상황

 

구글 스프레드시트에서 제공하는 서식을 사용하다 보니 데이터를 가공하기 어려운 형태로 되어 있어 날짜와 시간을 date 컬럼에 하나, time 컬럼에 분리하고자 한다.

개선

SQL 쿼리를 어디서 입력해야 하는지 모르겠다면 본인이 작성한 supabase 전처리 포스트 중 첫번째 포스트를 보면 사진과 함께 자세히 남겨두었다.

SELECT
    trim(split_part(날짜, ' ', 1)) AS year,
    trim(split_part(날짜, ' ', 2)) AS month,
    trim(split_part(날짜, ' ', 3)) AS day,
    trim(split_part(날짜, ' ', 4)) AS period,
    trim(split_part(날짜, ' ', 5)) AS time_part
FROM bankstatement;

 

trim으로 공백을 제거하고 발라냈더니 마침표가 포함되어 있어서 데이터 형식이 맞지 않아 에러가 났던 것이다.

 

마침표를 제거하면 잘 되는지도 아래 명령어로 미리 확인해볼 수 있다.

SELECT
    replace(trim(split_part(날짜, ' ', 1)), '.', '') AS year,
    replace(trim(split_part(날짜, ' ', 2)), '.', '') AS month,
    replace(trim(split_part(날짜, ' ', 3)), '.', '') AS day,
    trim(split_part(날짜, ' ', 4)) AS period,
    replace(trim(split_part(날짜, ' ', 5)), '.', '') AS time_part
FROM bankstatement;

 

왜 자꾸 안 되지 싶어서 직접 검증을 해봤더니 이런 미꾸라지 같은 녀석 때문이었다.

 

본인은 이 컬럼에 null이 있으면 안 되는 컬럼이라 원본 데이터 테이블에 가서 다시 알맞게 수정해주면 해결이 되겠다.

 

 

또 안 된다

본인의 시간은 오전/오후으로 구분하고 각각 00~12으로 입력되고 있는데 이를 24시간으로 변환하는 로직으로,

오전은 그대로 두고, 오후일 경우 +12시간을 해서 24시간 형태로 만드는 로직이었는데 정오(오후 12시)가 문제가 되었다.

오후 12:01분에 +12시간을 하면 24:01분이라는 존재할 수 없는 시간이 되기 때문이다.

따라서 오후 12시는 그대로 두고 나머지만 처리하는 로직이 필요하다.

최종

최종 명령어

UPDATE bankstatement
SET 
    datetime = to_timestamp(
        replace(trim(split_part(날짜, ' ', 1)), '.', '') || '-' || 
        lpad(replace(trim(split_part(날짜, ' ', 2)), '.', ''), 2, '0') || '-' || 
        lpad(replace(trim(split_part(날짜, ' ', 3)), '.', ''), 2, '0') || ' ' || 
        CASE 
            WHEN trim(split_part(날짜, ' ', 4)) = '오전' AND substring(trim(split_part(날짜, ' ', 5)), 1, 2) = '12' THEN '00' || substr(trim(split_part(날짜, ' ', 5)), 3)
            WHEN trim(split_part(날짜, ' ', 4)) = '오전' THEN trim(split_part(날짜, ' ', 5))
            WHEN trim(split_part(날짜, ' ', 4)) = '오후' AND substring(trim(split_part(날짜, ' ', 5)), 1, 2) = '12' THEN trim(split_part(날짜, ' ', 5))
            WHEN trim(split_part(날짜, ' ', 4)) = '오후' THEN lpad(((substring(trim(split_part(날짜, ' ', 5)), 1, 2)::int + 12) % 24)::text, 2, '0') || substr(trim(split_part(날짜, ' ', 5)), 3)
        END,
        'YYYY-MM-DD HH24:MI'
    );