blog

PostgreSQL tip 모음

날짜: 2024-03-20

목록으로

---

주요 개념

• 스키마
  • 데이터베이스 내에서 테이블을 논리적으로 그룹화하는 방법
  • 기본 스키마 = public
  • 스키마는 데이터베이스 객체(테이블, 뷰, 인덱스, 함수 등)의 컬렉션으로, 데이터베이스 내에서 별도의 네임스페이스를 제공하여, 이름이 같은 다른 스키마의 객체와 구분할 수 있게 합니다.
  • 이를 통해 데이터베이스의 조직화 및 접근 제어를 용이하게 하며, 다중 사용자 환경에서 객체 이름 충돌을 방지할 수 있습니다.
• 데이터베이스간 정보조회
  • A DB로 접속하여 B DB 테이블 정보를 쿼리로 얻어오는 것은 불가능함.

shell client 명령어

# postgres 서버 접속
psql -h HOST -p PORT -U USER -d DB_NAME

자주 쓰는 Select 쿼리

-- COLLATE 기본설정값 조회
SHOW lc_collate;

-- 현재유저(나)의 권한 조회
SELECT * FROM pg_roles WHERE rolname = current_user;

-- 접속가능한 데이터베이스 목록 조회
SELECT datname FROM pg_database
WHERE has_database_privilege(datname, 'CONNECT');

-- 접속한 DB 의 모든 테이블 조회
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

-- 특정 테이블의 구조(컬럼 정보) 조회
SELECT
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default
FROM
    information_schema.columns
WHERE
    table_name = 'your_table_name';

-- 스키마 까지 명시할 필요가 있다면 조건 추가
    AND table_schema = 'your_schema_name';

-- 테이블의 constraints 조회
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = '테이블명' AND table_schema = '스키마명';

실행중인 프로세스 강제종료

-- 실행 중인 쿼리 조회
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE state = 'active';

-- 실행 중인 쿼리 강제 종료 (by pid)
SELECT pg_terminate_backend(123);

-- 다중 pid 강제 종료
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid IN (123, 456);

-- 실행 시간이 1분을 초과한 쿼리 강제 종료
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE 
state = 'active' AND 
now() - query_start > '1 minutes'::interval AND
usename != 'important_user';

자주 쓰는 DDL

-- 테이블 생성
CREATE TABLE sample_contents (
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title VARCHAR(20) NOT NULL,
    description VARCHAR(50) NULL,
    event_id INTEGER NOT NULL,
    thumbnail_url VARCHAR(255) NOT NULL,
    notification_status CHAR(5) NOT NULL DEFAULT 'NS_01',
    sample1_datetime TIMESTAMP WITH TIME ZONE NULL,
    sample2_date TIMESTAMP WITH TIME ZONE NOT NULL,
    sample3_time TIMESTAMP WITH TIME ZONE NOT NULL,
    is_visible BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Comments
COMMENT ON COLUMN sample_contents.title IS '제목';
COMMENT ON COLUMN sample_contents.description IS '설명';
COMMENT ON COLUMN sample_contents.event_id IS '외래키 event.id';
COMMENT ON COLUMN sample_contents.thumbnail_url IS '대기중 이미지';
COMMENT ON COLUMN sample_contents.notification_status IS '알림 발송 상태 (NS_01:대기, NS_02:전송중, NS_03:완료, NS_04:실패)';
COMMENT ON COLUMN sample_contents.sample1_datetime IS '샘플 날짜 (시간포함)';
COMMENT ON COLUMN sample_contents.sample2_date IS '샘플 날짜 (시간제외)';
COMMENT ON COLUMN sample_contents.sample3_time IS '샘플 시간';
COMMENT ON COLUMN sample_contents.is_visible IS '노출 여부';
COMMENT ON COLUMN sample_contents.created_at IS '등록 날짜';
COMMENT ON COLUMN sample_contents.updated_at IS '수정 날짜';

-- 인덱스 생성 (일반, 유니크)
CREATE INDEX idx_sample_contents__created_at ON sample_contents (created_at);
CREATE UNIQUE INDEX uidx_sample_contents__id__event_id ON sample_contents (id, event_id);

--- 컬럼 추가
ALTER TABLE users ADD COLUMN description VARCHAR(255);
COMMENT ON COLUMN users.description IS '사용자 설명';

-- 인덱스 삭제
DROP INDEX user_name_index;

--- Foreign Key Constraint 제거
ALTER TABLE orders DROP CONSTRAINT orders_user_id_fkey;

-- table 삭제
DROP TABLE my_table;

-- DB 삭제
DROP DATABASE IF EXISTS 데이터베이스명;

기타 유용한 DDL

-- 특정 필드의 한글 정렬 적용이 필요할때 C COLLATE (ascii 기반 정렬) 지정 
ALTER TABLE products ALTER COLUMN manufacturer SET DATA TYPE character varying(100) COLLATE "C";

---

목록으로