테이블 수정

테이블 수정 (Modifying Tables)

테이블을 만들고 나서 "아, 컬럼을 잘못 넣었네" 혹은 "요구사항이 바뀌어서 구조를 좀 바꿔야겠다" 싶을 때, 데이터가 아직 없으면 그냥 떼버리고 다시 만들 수도 있어요. 하지만 테이블에 데이터가 이미 차 있거나 다른 객체(예: 외래 키 제약)가 그 테이블을 참조하고 있다면, 함부로 지우고 다시 만들기 어렵죠. 이럴 때 쓰는 게 바로 ALTER TABLE이에요. 테이블의 정의(구조) 를 바꾸는 명령이에요 — 테이블에 담긴 데이터를 바꾸는 것과는 개념적으로 다르다는 점, 기억해두면 좋아요.

출처: 공식문서

ALTER TABLE로 할 수 있는 일은 생각보다 많아요.

컬럼 추가 / 제거
제약 추가 / 제거
기본값 변경
컬럼 데이터 타입 변경
컬럼 이름 변경
테이블 이름 변경

이 절에서 소개하는 내용보다 더 상세한 내용은 ALTER TABLE 레퍼런스 문서에 있어요.

컬럼 추가하기

컬럼을 추가하려면 이렇게 해요.

ALTER TABLE products ADD COLUMN description text;

새 컬럼은 지정한 기본값으로 초기화돼요. DEFAULT 절을 안 지정하면 NULL로 채워지죠.

팁: 상수(constant) 기본값으로 컬럼을 추가할 때는, ALTER TABLE 실행 시 테이블의 모든 행을 갱신하지 않아요. 대신 그 기본값은 다음에 행에 접근할 때 반환되고, 테이블이 다시 쓰일 때 적용돼요. 그래서 큰 테이블에서도 ALTER TABLE이 아주 빨라요.

기본값이 변할 수 있는 값(예: clock_timestamp())이라면, ALTER TABLE이 실행된 시점에 계산된 값으로 각 행이 갱신돼야 해요. 길어질 수 있는 갱신을 피하고 싶다면, 특히 어차피 대부분 비기본값으로 채울 생각이라면, 기본값 없이 컬럼을 추가하고 UPDATE로 올바른 값을 넣은 뒤에 원하는 기본값을 따로 붙이는 게 더 나아요.

컬럼을 추가하면서 동시에 제약도 정의할 수 있어요.

ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

사실 CREATE TABLE의 컬럼 정의에 쓸 수 있는 모든 옵션을 여기서도 쓸 수 있어요. 다만 기본값이 주어진 제약을 만족해야 ADD가 성공한다는 점만 유의하세요. 아니면 나중에 컬럼을 올바르게 채운 뒤 제약을 추가해도 돼요.

컬럼 제거하기

컬럼을 제거하려면 이렇게 해요.

ALTER TABLE products DROP COLUMN description;

컬럼에 담겨 있던 데이터는 사라지고, 그 컬럼을 포함한 테이블 제약도 함께 제거돼요. 그런데 그 컬럼을 다른 테이블의 외래 키 제약이 참조하고 있다면, PostgreSQL은 그 제약을 조용히 지워버리지 않아요. 컬럼에 의존하는 모든 것을 지우는 걸 허용하려면 CASCADE를 붙이면 됩니다.

ALTER TABLE products DROP COLUMN description CASCADE;

이 배후의 일반적인 메커니즘은 의존성 추적 문서(Section 5.15)에서 다뤄요.

제약 추가하기

제약을 추가할 때는 테이블 제약 문법을 써요.

ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

보통 테이블 제약으로 쓰지 않는 NOT NULL 제약은, 이 특별한 문법으로 추가해요.

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

이 명령은 컬럼에 이미 NOT NULL 제약이 있으면 조용히 아무 일도 하지 않아요.

제약은 추가되자마자 검사되므로, 추가하려면 테이블 데이터가 먼저 그 제약을 만족해야 해요.

제약 제거하기

제약을 제거하려면 그 이름을 알아야 해요. 직접 이름을 붙였다면 쉽지만, 시스템이 생성한 이름이라면 찾아봐야 해요. psql의 \d tablename 명령이 도움이 돼요. 그다음에 이렇게 지우면 되죠.

ALTER TABLE products DROP CONSTRAINT some_name;

컬럼 제거와 마찬가지로, 다른 무언가가 의존하는 제약을 지우려면 CASCADE를 붙여야 해요. 예를 들어 외래 키 제약은 참조되는 컬럼의 unique나 primary key 제약에 의존하죠.

NOT NULL 제약을 제거하는 간단한 문법도 있어요.

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

이건 NOT NULL 제약을 추가하는 SET NOT NULL 문법과 짝을 이루죠. 컬럼에 NOT NULL 제약이 없으면 조용히 아무 일도 하지 않아요. (컬럼이 가질 수 있는 NOT NULL 제약은 최대 하나이므로, 이 명령이 어떤 제약을 다루는지 모호할 일은 없어요.)

기본값 변경하기

컬럼에 새 기본값을 설정하려면 이렇게 해요.

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

이건 테이블의 기존 행에는 영향을 주지 않고, 앞으로의 INSERT 명령에 대한 기본값만 바꿔요.

기본값을 제거하려면 이렇게 해요.

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

사실상 기본값을 NULL로 설정하는 것과 같아요. 따라서 기본값이 정의된 적 없어도 지우는 건 오류가 아니에요 — 기본값은 암시적으로 NULL이기 때문이죠.

컬럼 데이터 타입 변경하기

컬럼을 다른 데이터 타입으로 바꾸려면 이렇게 해요.

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

이 명령은 컬럼의 각 기존 값이 암시적 캐스팅(implicit cast) 으로 새 타입으로 변환될 수 있을 때만 성공해요. 더 복잡한 변환이 필요하면, 새 값을 어떻게 계산할지 지정하는 USING 절을 붙일 수 있어요.

PostgreSQL은 컬럼의 기본값(있으면)이나 그 컬럼을 포함하는 제약도 새 타입으로 변환하려 시도해요. 하지만 이런 변환이 실패하거나 뜻밖의 결과를 낼 수도 있어요. 타입을 바꾸기 전에 컬럼의 제약을 먼저 지우고, 이후에 적절히 수정된 제약을 다시 추가하는 편이 좋을 때가 많아요.

컬럼 이름 바꾸기

컬럼 이름을 바꾸려면:

ALTER TABLE products RENAME COLUMN product_no TO product_number;

테이블 이름 바꾸기

테이블 이름을 바꾸려면:

ALTER TABLE products RENAME TO items;

테이블 수정