[PostgreSQL] csv 마이그레이션

1. DBeaver 에서 csv 파일을 생성

2. 스테이징 테이블 (Linux)

CREATE TEMP TABLE stg__record
(LIKE larisopen.tb_inf_record INCLUDING DEFAULTS);

3. CSV를 스테이징에 적재 (Linux)

• Linux

\copy stg_record FROM '/usr/local/record_202512241122.csv' CSV header

• 헤더 순서가 다르다면 직접 지정

\copy stg_record (id, 칼럼B, 칼럼A, 칼럼C) FROM '/usr/local/record_202512241122.csv' WITH (FORMAT csv, HEADER true);

4. 본테이블로 upsert

• upsert 쿼리 생성

WITH cols AS (
  SELECT
    c.ordinal_position,
    c.column_name
  FROM information_schema.columns c
  WHERE c.table_schema = 'larisopen'
    AND c.table_name  = 'tb_inf_record_org'
  ORDER BY c.ordinal_position
),
col_list AS (
  SELECT string_agg(format('%I', column_name), E',\n  ' ORDER BY ordinal_position) AS s
  FROM cols
),
upd_list AS (
  SELECT string_agg(format('%I = EXCLUDED.%I', column_name, column_name), E',\n  ' ORDER BY ordinal_position) AS s
  FROM cols
  WHERE column_name <> 'tno_record_id'  -- PK/unique 키는 보통 업데이트에서 제외
)
SELECT
format(
$SQL$
INSERT INTO larisopen.tb_inf_record_org (
  %s
)
SELECT
  %s
FROM stg_tb_inf_record_org
WHERE tnr_del = 0
ON CONFLICT (tno_record_org_id) DO UPDATE
SET
  %s
;
$SQL$,
(SELECT s FROM col_list),
(SELECT s FROM col_list),
(SELECT s FROM upd_list)
) AS generated_upsert_sql;

• 생성 결과를 실행

INSERT INTO public.chats (
  id,
  message,
  user_id,
  created_at,
  chat_room_id
)
SELECT
  id,
  message,
  user_id,
  created_at,
  chat_room_id
FROM stg_chats
WHERE tnr_del = 0
ON CONFLICT (tno_record_org_id) DO UPDATE
SET
  message = EXCLUDED.message,
  user_id = EXCLUDED.user_id,
  created_at = EXCLUDED.created_at,
  chat_room_id = EXCLUDED.chat_room_id
;