Redshift : alter table ~ alter column/modify column ~ type ~ (varchar type column의 크기 제한 변경, column 최대 크기 제한 변경, column data type 변경)

SQL/Redshift

Redshift : alter table ~ alter column/modify column ~ type ~ (varchar type column의 크기 제한 변경, column 최대 크기 제한 변경, column data type 변경)

CosmosProject 2022. 10. 19. 19:28

728x90

alter table ~ alter column/modify column ~ type ~ 구문을 사용하면 column의 data type을 변경할 수 있습니다.

Syntax

alter table (table_name) alter column (column_name) type (data_type)

alter table (table_name) modify column (column_name) type (data_type)

alter table (table_name) modify (column_name) type (data_type)

alter table로 column의 data type을 바꾸기 위한 syntax로는 대표적으로 위 3가지가 있습니다.

위 3가지 형식은 사용하는 DB의 종류와 version에 따라 사용할 수 있는 구문이 다를 수 있으니 위 3가지를 시도해보시고 되는걸 사용하면 됩니다.

(table_name) = 변경내용을 적용할 table 이름입니다.

(column_name) = varchar type이며 최대 길이를 변경할 column의 이름입니다.

varchar(n) = 새로 varchar type의 최대 길이 제한을 변경합니다. n에는 원하는 숫자를 입력하면 됩니다.

본 글에서는 Redshift 기준으로 위 구문을 알아볼건데 Redshift는 column의 data type변경을 지원하지 않습니다.

예를들어 bigint type인 column을 varchar로 변경한다던지 등의 기능은 안된다는 것이죠.

Redshift에서 어떤 column의 data type을 바꾸려면 조금 돌아가는 방법을 사용해야합니다. (이 방법에 대해선 이 글의 맨 아래쪽에서 설명하겠습니다.)

그러면 위 구문은 언제 사용하느냐?

보통 varchar type의 최대 허용 길이 등을 수정할 때 주로 사용합니다.

즉, data type은 고정한 채로 해당 data type 자체에 영향을 주지 않는 요소를 바꾼다는 느낌입니다.

create table test_schema.test_table (
    col1       bigint,
    col2       varchar(60),
    col3       date,
    col4       timestamp
)
;

테스트를 위해 위처럼 test_schema.test_table을 생성합시다.
여기서 중점적으로 볼 것은 col2 컬럼의 data type이 varchar이며 (60)을 보면 최대 길이가 60글자로 제한된 것을 볼 수 있습니다.

이제 col2의 최대 제한 길이를 바꿔보겠습니다.

alter table test_schema.test_table
alter column col2 type varchar(300)
;

varchar type의 최대 제한 길이를 변경할 때에는 위처럼 하면 됩니다.

- alter table test_schema.test_table
test_schema.test_table 테이블에 변경을 가할 것이라는 의미입니다.

- alter column col2 type varchar(300)
col2 컬럼의 type을 varchar(300)으로 바꾸겠다는 의미입니다.

show table test_schema.test_table
;



-- Result
CREATE TABLE test_sch.test_table (
    col1 bigint ENCODE az64,
    col2 character varying(300) ENCODE lzo,
    col3 date ENCODE az64,
    col4 timestamp without time zone ENCODE az64
)
DISTSTYLE AUTO;

제대로 변경되었는지 보기 위해 show table 구문을 사용하여 테이블 정보를 출력해보면 위와 같습니다.

(show table 구문 관련 내용 = https://cosmosproject.tistory.com/665)

col2 컬럼의 정보를 보면 다음과 같습니다.

- col2 character varying(300) ENCODE lzo,

character varying 타입이며 최대 길이가 300으로 변경된 것을 볼 수 있습니다.

자 이제 처음에 말했던 Redshift에서 어떤 column의 data type을 바꾸려면 조금 돌아가는 방법을 사용해야한다는 것에 대한 내용을 살펴봅시다.

이것을 가능하게 하는 방법의 순서는 아래와 같습니다.

1. 새로운 column을 원하는 data type으로 추가한다.

2. 기존 column data를 새로운 column에 update 해준다.

3. 기존 column을 삭제한다.

4. 새로운 column을 기존 column과 동일한 이름으로 바꾼다.

따라서 기존 column의 data type을 직접적으로 바꿀 방법이 없으니, 새로운 column을 추가하여 기존 column의 data를 삽입하고 기존 column을 삭제하는 방식으로 진행한다고 이해하면 됩니다.

아마 무슨 소린지 잘 이해가 가지 않을 수도 있을겁니다. 예시를 보면서 구체적으로 이해해봅시다.

create table test_schema.test_table (
    col1       bigint,
    col2       varchar(60),
    col3       date,
    col4       timestamp
)
;

테스트용 table을 위처럼 생성합시다.

그리고 위 table에 데이터를 insert 하였다고 가정해봅시다.

아래 표는 test_schema.test_table 테이블에 담긴 data를 나타낸 것입니다.

col1	col2	col3	col4
1	a	2022-01-01	2022-01-01 13:17:46.135092
2	z	2022-01-02	2022-01-05 20:05:30.300951
3	1	2022-01-03	2022-01-08 11:53:57.467354
4	k	2022-01-04	2022-01-13 05:23:15.276076

데이터를 보면

col1의 data type은 bigint이므로 숫자가 담겨있습니다.

col2의 data type은 varchar이므로 문자가 담겨있습니다. col2에 있는 숫자 1은 숫자이지만 varchar type인 데이터입니다.

col3의 data type은 date이므로 날짜 데이터가 담겨있습니다.

col4의 data type은 timestamp이므로 날짜+시간 데이터가 담겨있습니다.

먼저 col1을 bigint type에서 varchar type으로 바꿔보려고 합니다.

이 과정을 위에서 설명했던 순서에 따라 진행해봅시다.

1. 새로운 column을 원하는 data type으로 추가한다.

alter table test_schema.test_table
add col1_temp varchar(300)
;

먼저 col1_temp라는 이름의 column을 추가합니다.

col1	col2	col3	col4	col1_temp
1	a	2022-01-01	2022-01-01 13:17:46.135092
2	z	2022-01-02	2022-01-05 20:05:30.300951
3	1	2022-01-03	2022-01-08 11:53:57.467354
4	k	2022-01-04	2022-01-13 05:23:15.276076

그러면 위처럼 test_schema.test_table에 col1_temp라는 이름의 column이 아무 데이터가 없는 채로 추가될겁니다.

여기서 col1_temp의 data type은 varchar(300)일 것이구요.

(alter table ~ add ~ 관련 글 = https://cosmosproject.tistory.com/670)

2. 기존 column data를 새로운 column에 update 해준다.

update test_schema.test_table
set col1_temp = col1
;

그리고 update ~ set ~ 구문을 이용해서 col1_temp에 col1의 데이터를 넣어줍니다.

(좀 더 정확히 설명하면 col1_temp의 값을 col1의 값으로 update해준다고 할 수 있겠네요.)

col1	col2	col3	col4	col1_temp
1	a	2022-01-01	2022-01-01 13:17:46.135092	1
2	z	2022-01-02	2022-01-05 20:05:30.300951	2
3	1	2022-01-03	2022-01-08 11:53:57.467354	3
4	k	2022-01-04	2022-01-13 05:23:15.276076	4

그러면 위처럼 col1_temp에 col1의 데이터가 그대로 삽입됩니다.

show table test_schema.test_table;



-- Result
CREATE TABLE test_sch.test_table (
    col1 bigint ENCODE az64,
    col2 character varying(60) ENCODE lzo,
    col3 date ENCODE az64,
    col4 timestamp without time zone ENCODE az64,
    col1_temp character varying(300) ENCODE lzo
)
DISTSTYLE AUTO;

그리고 show table 구문을 이용해서 보면 col1_temp의 data type은 그대로 chracter varying(300) 임을 알 수 있습니다.

즉, bigint type의 데이터였던 1, 2, 3, 4라는 col1의 값들이 varchar type인 col1_temp로 삽입될 때에는 삽입될 column인 col1_temp의 data type(varchar)으로 변환되어서 삽입된다는 것을 알 수 있습니다.

3. 기존 column을 삭제한다.

alter table test_schema.test_table
drop column col1
;

이제 필요없어진 col1을 삭제합니다.

(drop column 관련 글 = https://cosmosproject.tistory.com/671)

col2	col3	col4	col1_temp
a	2022-01-01	2022-01-01 13:17:46.135092	1
z	2022-01-02	2022-01-05 20:05:30.300951	2
1	2022-01-03	2022-01-08 11:53:57.467354	3
k	2022-01-04	2022-01-13 05:23:15.276076	4

여기까지 하면 test_schema.test_table의 형태는 위와 같아집니다.

col1이 사라졌죠.

4. 새로운 column을 기존 column과 동일한 이름으로 바꾼다.

alter table test_schema.test_table
rename column col1_temp to col1
;

alter table ~ rename column ~ 구문을 이용해서 col1_temp의 이름을 col1으로 바꿔줍니다.

(alter table ~ rename column ~ 관련 글 = https://cosmosproject.tistory.com/669)

col2	col3	col4	col1
a	2022-01-01	2022-01-01 13:17:46.135092	1
z	2022-01-02	2022-01-05 20:05:30.300951	2
1	2022-01-03	2022-01-08 11:53:57.467354	3
k	2022-01-04	2022-01-13 05:23:15.276076	4

여기까지 하면 test_schema.test_table의 형태는 위와 같아집니다.

col1, col2, col3, col4의 순서는 달라졌지만 컬럼의 순서는 크게 상관없습니다.

show table test_schema.test_table;



-- Result
CREATE TABLE test_sch.test_table (
    col2 character varying(60) ENCODE lzo,
    col3 date ENCODE az64,
    col4 timestamp without time zone ENCODE az64,
    col1 character varying(300) ENCODE lzo
)
DISTSTYLE AUTO;

다시 show table로 table정보를 보면 위와 같습니다.

col1을 보면 data type이 bigint에서 varchar로 바뀐 것을 알 수 있죠.

이제 다시 처음으로 돌아가서 다른 컬럼의 data type을 바꿔봅시다.

create table test_schema.test_table (
    col1       bigint,
    col2       varchar(60),
    col3       date,
    col4       timestamp
)
;

똑같이 테스트용 table을 위처럼 생성합시다.

그리고 위 table에 데이터를 insert 하였다고 가정해봅시다.

아래 표는 test_schema.test_table 테이블에 담긴 data를 나타낸 것입니다. 이전에 봤던 예시와 동일합니다.

col1	col2	col3	col4
1	a	2022-01-01	2022-01-01 13:17:46.135092
2	z	2022-01-02	2022-01-05 20:05:30.300951
3	1	2022-01-03	2022-01-08 11:53:57.467354
4	k	2022-01-04	2022-01-13 05:23:15.276076

이번에는 timestamp type인 col4를 varchar로 바꿔보겠습니다.

-- 1. 새로운 column을 원하는 data type으로 추가한다.
alter table test_schema.test_table
add col4_temp varchar(1000)
;

-- 2. 기존 column data를 새로운 column에 update 해준다.
update test_schema.test_table
set col4_temp = col4
;

-- 3. 기존 column을 삭제한다.
alter table test_schema.test_table
drop column col4
;

-- 4. 새로운 column을 기존 column과 동일한 이름으로 바꾼다.
alter table test_schema.test_table
rename column col4_temp to col4
;

순서는 이전에 봤던 예시와 완전히 동일합니다.

원본 table 정보

create table test_schema.test_table (
    col1       bigint,
    col2       varchar(60),
    col3       date,
    col4       timestamp
)
;

col4를 삭제 후 새로운 data type으로 변경한 후의 table 정보

CREATE TABLE test_sch.test_table (
    col1 bigint ENCODE az64,
    col2 character varying(60) ENCODE lzo,
    col3 date ENCODE az64,
    col4 character varying(1000) ENCODE lzo
)
DISTSTYLE AUTO;

show table을 이용해서 전/후의 table 정보를 비교해보면 col4의 data type이 바뀐 것 처럼 보이죠.

test_sch.test_table 의 데이터를 뽑아보면 아래와 같습니다.

col1	col2	col3	col4
1	a	2022-01-01	2022-01-01 13:17:46.135092
2	z	2022-01-02	2022-01-05 20:05:30.300951
3	1	2022-01-03	2022-01-08 11:53:57.467354
4	k	2022-01-04	2022-01-13 05:23:15.276076

col4의 데이터를 보면 기존과 아무 차이가 없어보입니다.

사실 보이는 것만 차이가 없을 뿐 show table의 결과로 봤던 것 처럼 col4의 data type은 varchar로 설정된 상태입니다.

update ~ set ~ 구문에서 col4의 timestamp data를 varchar type인 col4_temp에 넣을 때 자동으로 data type 변환이 이뤄진 것입니다.

이때 기존 timestamp로 나타내지는 문자들이 data type만 varchar로 자동으로 변경됨과 동시에 timestamp를 나타내던 문자들(e.g. 2022-01-01 13:17:46.135092 등)은 그대로 보존된 것이죠.

728x90