다음 사이트들을 참고해서 해겷했다.
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)
내용 변경했으니까 그냥 막 올려도 되겠지??? 아닌가…