차근차근

PostgreSQL은 다양한 배열 함수를 지원한다. 그 중 자주 사용하는 배열 함수 몇 가지를 알아보자. 설명 위한 테이블 & 데이터 생성 create table table1 ( col1 text, col2 integer[] ); insert into table1 values ('Bill', '{10000, 10000, 10000, 10000}'); insert into table1 values ('Carol', '{20000, 25000, 25000, 25000}'); create table table2 ( col1 text, col2 text ); insert into table2 values ('A', 'A1'); insert into table2 values ('A', 'A2'); insert i..

UNION 이란? 두 개 이상의 SELECT 문 결과를 결합하며 결합 시 중복 데이터는 제거된다. 두 개의 SELECT문 간의 컬럼 개수는 동일해야하고, 데이터 유형 역시 호환되어야 한다. 데이터를 정렬하기 위해서는 맨 마지막 SELECT문에 ORDER BY 절을 사용해야 한다. UNION ALL 이란? 두 개 이상의 SELECT 문 결과를 결합하며 결합 시 중복된 데이터도 모두 출력한다. 예제 CREATE TABLE table1 ( id varchar(255) , name varchar(255) , email varchar(255) ); INSERT INTO table1 VALUES ('id1', 'a', 'a@naver.com') , ('id2', 'b', 'b@naver.com') , ('id3',..

인덱스가 생성되어 있는데도 인덱스를 타지 않는 경우가 있다. 그 이유를 정리해보자! 1. 인덱스에 변형을 준 경우 select * from table where LOWER(name) = 'world'; select * from table where seq - 1 = 5; 2. NOT 또는 IN 연산자 사용 - NOT일 경우에도 인덱스를 타긴 타지만, 일반적으로, NOT에 사용된 값이 아닌 데이터의 비율이 높은 경우가 많기 때문에 인덱스를 타지 않는 경우가 많다. - 마찬가지로 IN일 경우에도, IN에 포함된 데이터들의 비율이 매우 높다면 풀스캔을 하는 것이 낫다고 DBMS가 판단하면 인덱스를 타지 않는다. 3. 와일드 카드 LIKE문장에서 범위 전체를 지정 - 문자열로 이루어진 값을 인덱스로 잡았을 때,..

문자열 함수 btrim : 문자열의 시작과 끝 양쪽에 지정된 문자들을 제거 char_length : 문자열의 길이를 반환 character_length : 지정된 문자열의 길이를 반환 || : 2개의 문자열을 합하여 반환 initcap : 문자열 중 첫번째 문자는 대문자로 변환하고 나머지는 소문자로 변환 length : 문자열의 길이를 반환 lower : 지정된 문자열을 모두 소문자로 변환 lpad : 지정된 문자열을 원하는 길이로 맞추는데, 이때 부족한 문자를 왼쪽에 채움 ltrim : 문자열의 왼쪽에서 지정된 모든 문자를 제거 position : 문자열에서 지정된 문자열이 존재하는 인덱스값을 반환 repeat : 문자열을 원하는 만큼 반복해서 반환 replace : 기존의 문자열을 구성하는 부분 문자..

PostgreSQL에서는 함수(Function)을 사용할 수 있다. 함수 구조 CREATE [OR REPLACE] FUNCTION function_name (arguments) RETURNS return_datatype AS $$ DECLARE -- 변수 선언 BEGIN -- 함수 로직 END; $$ LANGUAGE plpgsql; 키워드 설명 CREATE [OR REPLACE] FUNCTION 함수 생성 / [ON REPLACE]는 기존 함수 업데이트 허락 여부 function_name 함수명 arguments 함수 입력 인수 RETURNS return_datatype 함수 리턴 데이터 타입 DECLARE 함수에서 사용할 변수 선언 BEGIN, END 함수 로직 $$ LANGUAGE plpgsql 함수..

PostgreSQL는 B-Tree 기반의 Index 구조를 기본으로 가지고 있기 때문에 타입 미지정 시 B-Tree 구조로 Index 생성 다만 상수가 아닌 컬럼을 사용해 like 연산자를 사용해야 하는 경우 B-Tree Index로는 index scan이 되지 않아 GIN Index를 사용 GIN Index는 Full Text 검색 속도를 높이는 데 사용 가능한 Index 예시 CREATE EXTENSION pg_trgm; CREATE INDEX ${인덱스명} ON ${테이블명} USING GIN("${컬럼명}" gin_trgm_ops); 참고 https://onbaba.tistory.com/2

테이블 소유자 변경 ALTER TABLE ${테이블명} OWNER TO ${새소유자명}; 특정 유저의 모든 객체를 다른 유저 소속으로 변경 REASSIGN OWNED BY ${기존소유자명} TO ${새소유자명}; 참고 - https://orcl.tistory.com/entry/postgresql-%ED%85%8C%EC%9D%B4%EB%B8%94%EC%9D%98-%EC%8A%A4%ED%82%A4%EB%A7%88%EC%99%80-owner-%EB%B3%80%EA%B2%BD%ED%95%98%EA%B8%B0

에러 ERROR: duplicate key value violates unique constraint 원인 nextval() 등을 통해 primary key를 생략한 채로 자동 시퀀스를 실행하다 보면 시퀀스 객체와 테이블 pk 값에 차이가 생기는 경우가 종종 발생 primary key 값은 유니크해야 하는데 이미 존재하는 seq를 insert 하려고 하는 경우에 발생 해결 -- 오류 난 테이블의 최대 키 값 조회 select max(seq) from ${테이블명}; -- nextval 함수가 있는 시퀀스 객체 값 조회 select nextval('${시퀀스명}'::regclass); -- 두 값이 불일치하는 경우 시퀀스 객체 값으로 변경 select setval('${시퀀스명}', (select max(..

VACUUM 테이블과 인덱스에서 삭제 된 자료(old version row, dead row)를 정리하고, 그 자리에 다른 자료가 저장될 수 있는 빈공간으로 표시하는 작업 물리적인 여유 공간을 확보하는 것은 아님 운영상황에서 사용 가능 -- DB 전체 간단 실행 vacuum verbose analyze; -- 특정 테이블 간단 실행 vacuum ${테이블명} vacuum analyze ${테이블명} VACUUM FULL 물리적인 여유 공간 확보 삭제나 수정으로 생긴 빈 공간을 정리해 디스크 공간 확보 작업 시간이 많이 소요되고, 다른 작업과 같이 사용 불가능 운영 상황에서는 트래픽이 없는 밤 시간대에 사용 -- DB 전체 풀 실행 vacuum full analyze; -- 특정 테이블 풀 실행 vacuu..

인메모리 데이터베이스 디스크가 아닌 주 메모리에 모든 데이터를 보유하고 있는 데이터베이스 디스크 검색보다 자료 접근이 훨씬 빠름 디스크 방식은 디스크에 저장된 데이터를 대상으로 쿼리를 수행하지만, 인메모리 방식은 메모리상에 인덱스를 넣어 필요한 모든 정보를 인덱스를 통해 빠르게 검색 단점은 매체가 휘발성이라 DB서버 전원이 갑자기 꺼지면 안의 데이터가 삭제되기 때문에 날아가도 상관 없는 임시 데이터에 주로 사용 (로그인 세션) 지속성을 보장하기 위해 입력/수정/삭제된 값은 모두 디스크에 로그로 기록하며, 디스크로부터 로그 파일을 읽어와 메모리에서 재구축 하기도 함 Redis, H2 … Redis NoSQL에 속하는 데이터 방식이며, key-value 구조 key-value 구조이기 때문에 별도 쿼리 없이..