PostgreSQL 에는 MySQL 의 SHOW CREATE TABLE 에 해당하는 문장이 없다. 세 가지 방법을 상황에 맞게 쓴다.
psql 에서 구조만 빠르게 볼 때는 메타 명령이 가장 빠르다.
\d app.users
\d+ app.users
\d+ 는 저장 방식·통계 목표·설명까지 보여 준다. 실행되는 SQL 을 보고 싶으면 psql -E 로 띄운다.
실제 CREATE TABLE 문이 필요하면 pg_dump 로 뽑는다. 인덱스·제약·기본값까지 완전한 형태가 나온다.
pg_dump -h dbhost -U appuser -d appdb -t app.users --schema-only --no-owner --no-privileges
여러 테이블을 한 번에 뽑으려면 -t 를 반복하거나 패턴을 쓴다. -t 'app.*' 처럼 따옴표로 감싸야 셸이 확장하지 않는다.
SQL 로 컬럼 정보만 보려면 카탈로그를 조회한다.
SELECT column_name, data_type, character_maximum_length, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'app' AND table_name = 'users'
ORDER BY ordinal_position;
정의를 문자열로 돌려주는 내장 함수도 유용하다.
SELECT indexdef FROM pg_indexes WHERE schemaname = 'app' AND tablename = 'users';
SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'app.users'::regclass;
SELECT pg_get_viewdef('app.v_users'::regclass, true);
ALTER TABLE app.users RENAME COLUMN name TO user_name;
이름 변경은 메타데이터만 바꾸므로 테이블을 다시 쓰지 않는다. 다만 그 컬럼을 참조하는 뷰·함수·애플리케이션 쿼리는 함께 고쳐야 한다. 뷰는 내부적으로 컬럼 번호를 참조하므로 자동으로 따라오지만, 함수 본문의 문자열 SQL 은 따라오지 않는다.
ALTER TABLE app.users ALTER COLUMN age TYPE bigint;
자동 변환이 안 되는 조합은 USING 으로 변환식을 준다.
ALTER TABLE app.orders
ALTER COLUMN price TYPE numeric(12,2) USING price::numeric;
ALTER TABLE app.logs
ALTER COLUMN created_at TYPE timestamptz USING created_at AT TIME ZONE 'Asia/Seoul';
한 문장에 여러 변경을 넣을 수 있다. 이러면 테이블을 한 번만 다시 쓴다.
ALTER TABLE app.users
ALTER COLUMN age TYPE bigint,
ALTER COLUMN age SET NOT NULL,
ALTER COLUMN email SET DEFAULT '';
RENAME COLUMN 은 다른 변경과 한 문장에 섞을 수 없다. 별도 문장으로 실행한다.
타입 변경은 대부분 테이블 전체를 다시 쓰고 그동안 ACCESS EXCLUSIVE 잠금을 잡는다. 읽기조차 막히므로 운영 중에는 위험하다. 다시 쓰지 않는 예외도 있다.
varchar(50) → varchar(100) 처럼 길이만 늘리는 경우varchar(n) → textnumeric 의 정밀도를 늘리는 경우이런 경우가 아니면 다음 순서가 안전하다. 새 컬럼을 추가하고, 배치로 값을 채우고, 트리거나 애플리케이션에서 양쪽을 함께 쓰다가, 전환이 끝나면 옛 컬럼을 지운다.
NOT NULL 을 붙이는 작업도 전체 스캔이 필요하다. PostgreSQL 12 이상에서는 같은 조건의 CHECK 제약을 NOT VALID 로 먼저 붙이고 VALIDATE 한 뒤 SET NOT NULL 을 하면 잠금 시간을 줄일 수 있다.
ALTER TABLE app.users OWNER TO appowner;
ALTER TABLE app.users ADD CONSTRAINT uq_users_email UNIQUE (email);
스키마의 모든 테이블 소유자를 한 번에 바꾸려면 문장을 만들어 실행한다.
SELECT format('ALTER TABLE %I.%I OWNER TO appowner;', schemaname, tablename)
FROM pg_tables WHERE schemaname = 'app';
큰 테이블에 UNIQUE 를 붙일 때는 인덱스를 먼저 동시 생성한 뒤 제약으로 승격하면 잠금이 짧다.
CREATE UNIQUE INDEX CONCURRENTLY uq_users_email ON app.users (email);
ALTER TABLE app.users ADD CONSTRAINT uq_users_email UNIQUE USING INDEX uq_users_email;