Postgresql – ,로 구분된 키값으로 조인

다음 사이트들을 참고해서 해겷했다.
http://stackoverflow.com/questions/8584967/split-comma-separated-column-data-into-additional-columns
http://stackoverflow.com/questions/11730777/postgres-not-in-array
http://stackoverflow.com/questions/10738446/postgresql-select-rows-where-column-array
나머지는 진짜 참고만 했고 첫번째 링크에 솔루션이 있다.

테이블 두개…

CREATE TABLE alarm_info
(
  alarm_id bigserial PRIMARY KEY,
  alarm_name character varying(20),
  created_at timestamp without time zone DEFAULT now(),
  updated_at timestamp without time zone DEFAULT now(),
  description text,
  admin_ids character varying(255),-- 3,6,7
)

CREATE TABLE admin_info
(
  admin_id bigserial NOT NULL PRIMARY KEY,
  admin_username character varying NOT NULL,
  admin_email character varying NOT NULL,
  admin_password character varying,
  created_at timestamp with time zone NOT NULL,
)

admin_ids가 문제다. 배열이 아닌 문자열형태로 ,구분자로 저장…
m-n조인을 하는것도 좋지만… 편의를 위해서 저렇게 하는 경우가 자주 있다.

이런경우에 조인을 하려면…
설명 생략하고 테스트과정을 순차적으로 다 써놓는다.
나야 보면 생각나겠지만… 다른사람들도 순서대로 실행시켜보면 뭔지 알 수 있을거다. 아마…

SELECT	admin_ids
FROM	alarm_info

SELECT	regexp_split_to_array(admin_ids,',')
FROM	alarm_info

SELECT	cast(regexp_split_to_array(admin_ids,',') as integer[])
FROM	alarm_info

SELECT	cast(regexp_split_to_array(admin_ids,',') as integer[])
FROM	alarm_info

select (ARRAY[1, 2])

SELECT * FROM admin_info
WHERE	admin_id = ANY(3,4)


SELECT
  a.admin_id as id,
  a.admin_username as username,
  a.admin_email as email,
  a.admin_phone as phone,
  a.admin_name as name,
  n.subject as subject,
  n.message
FROM
(
SELECT
  cast(regexp_split_to_array(admin_ids,',') as bigint[]) as ids,
  alarm_name as subject,
  alarm_description as message
FROM
  alarm_info
) n, admin_info a
where
  a.admin_id = any(n.ids)

내용 변경했으니까 그냥 막 올려도 되겠지??? 아닌가…

Leave a Reply Cancel reply