[PostgreSQL] 기초 내용 정리 1

PostgreSQL을 사용하며 놓치기 쉬운 기초 내용을 정리해봅니다.

 

• 작은따옴표 자체를 데이터로 표시하기 위해서는 작은따옴표를 두 번 적는다
  • e.g., 'let''s go' : let's go

 

• 특정 컬럼만 지정해서 값 넣기
  • INSERT INTO 테이블명 (컬럼1, ..., 컬럼n) VALUES (값1, ..., 값n)

 

• 한 번에 값 여러 개 넣기
  • INSERT INTO 테이블명 VALUES (값1 ~ 값n), (값1 ~ 값n), ..., (값1 ~ 값n);

 

• 조회 시작 행 지정
  • OFFSET 1: 두 번 째 행부터 조회

 

• 다르다 비교 연산자: <>

 

• 단일 값 결과 서브쿼리는 조건문 등에 활용 가능
  • e.g., WHERE 'aa' = (SELECT v1 FROM t1 WHERE tid = 2)

 

• 특정 컬럼 수정하기
  • UPDATE 테이블명
    SET 컬럼명 = 바꿀내용
    WHERE 조건
    RETURNING *; -- 수정 내용 바로 조회

 

• 숫자형 데이터 타입 특성
  • INTEGER는 +- 21억 이내 정수
  • 정확한 소수점: NUMERIC 또는 DECIMAL
  • NUMERIC(n,m)이면 총 n자리 중 소수점 m자리. e.g., NUMERIC(4,3): 0.001 ~ 9.999
  • NUMERIC(n)이면 총 n자리 정수
  • FLOAT은 부동소수점으로 지양
  • SERIAL은 PKEY용으로 자동으로 1씩 증가하여 자동 생성
  • MONEY 데이터 타입은 정수, 부동소수점 및 '$1,000.00' 등의 다양한 형식 가능

 

• 문자형 데이터 타입
  • CHAR는 부족한 길이를 뒤쪽 공백으로
  • TEXT는 길이 무시 전체 테스트 = n을쓰지 않은 VARCHAR

 

• 날짜 및 시간 데이터 타입
  • TIMESTAMP: YYYY-MM-DD HH:MM:SS
  • TIMESTAMPTZ: YYYY-MM-DD HH:MM:SS+09
  • TIME(3): HH:MM:SS.sss
  • TIMEZONE을 사용하면 Local에 맞춰 자동 데이터 변환

 

• Boolean 데이터 타입
  • TRUE: True, yes, on, 1
  • FALSE: False, no, off, 0
  • NULL: T, F 둘 다 아님

 

• 배열 데이터 타입
  • 정의 예시: INTEGER[]
  • 입력 예시1: Array[123, 654]
  • 입력 예시2: '{123, 654}'

 

• JSON 데이터 타입
  • JSON: 사본 생성, 처리 속도가 느림
  • JSONB: Binary 형태로 압축, 처리 속도는 빠르지만, 저장속도 느림
  • JSON 안 JSON 예시: '{"A":"B", "C":{"D":"E", "F":"G"}}'

 

• DOMAIN 데이터 타입
  • 제약 조건을 부여할 수 있는 사용자 정의 데이터 타입
  • e.g., CREATE DOMAIN myint AS integer CHECK (VALUE >0);

 

•  형변환
  • CAST (값 AS 데이터타입)
  • 값::데이터타입

 

• PK가 여러 컬럼으로 정의되는 경우 맨 마지막에 묶어서 정의
  • PRIMARY KEY (컬럼1, ..., 컬럼n)

 

• 외래키(FK) 정의
  • 한 개: my_id NUMERIC(5) REFERENCES super_table(컬럼명이 다른 경우의 컬럼명)
  • 여러개: FOREIGN KEY (컬럼 여러개) REFERENCES 테이블명 (컬럼 여러개)
  • ON DELETE CASCADE: 관련 행 삭제
  • ON DELETE SET NULL: 관련 값 Null 처리
  • ON DELETE SET DEFAULT: 관련 값 Default 값으로 변경

 

• CHECK: 제약조건
  • my_num INTEGER CHECK (my_num > 0)

 

• 컬럼 추가
  • ALTER TABLE 테이블명 ADD COLUMN 컬럼명 조건;

 

• 컬럼 속성 변경
  • ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 조건;
  • ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 DROP 조건;

 

• 테이블명 변경
  • ALTER TABLE 테이블명 RENAME TO 변경테이블명;

 

• 컬럼명 변경
  • ALTER TABLE 테이블명 RENAME COLUMN 컬럼명 TO 변경컬럼명;

 

• PK, FK 추가
  • ALTER TABLE 테이블명 ADD PRIMARY KEY (컬럼명);
  • ALTER TABLE 테이블명 ADD FOREIGN KEY (컬럼명) REFERENCES 테이블명 (컬럼명);

 

• 컬럼 데이터 타입 변경
  • ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 TYPE 새로운타입;
  • ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 TYPE 새로운타입 USING 컬럼명::새로운타