설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


Powered by EnterpriseDB
총 게시물 84건, 최근 0 건
   

컬럼 drop 시 참고사항

글쓴이 : PostgresDBA 날짜 : 2017-11-30 (목) 09:38 조회 : 37
CREATE TABLE testtab (
   id integer PRIMARY KEY,
   dropme text NOT NULL,
   val text NOT NULL
);

ALTER TABLE testtab DROP dropme;

SELECT attname, attnum, attisdropped
FROM pg_attribute
WHERE attrelid = 'testtab'::regclass
   AND attnum > 0
ORDER BY attnum;
--------------------------------------
id 1 f
........pg.dropped.2........ 2 t
val 3 f


컬럼을 drop 해도 오라클처럼 dictionary 에서 완전히 정리되는게 아니고
흔적을 남겨둡니다. 위처럼
그래서 컬럼순서값(attnum)도 재조정되지 않습니다.

The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated.

   

postgresdba.com