본문 바로가기

Database

 (20)
[Postgresql] Upsert 구현 (insert ~ on conflict) DB 구현시 많이 쓰는 패턴중에 하나가 해당 row가 있으면 update, 없으면 insert를 하는 패턴일 것이다. Oracle의 경우는 merge ~ into 구문을 통해 한번의 명령으로 해당 로직을 구현할 수 있다. Postgres에서도 동일한 명령이 있지 않을까? 아래와 같이 insert ~ on conflict 구문을 활용하면 구현할 수 있다. 문법은 다음과 같다. INSERT INTO [TABLE] (COLUMN1, COLUMN2, ...) VALUES (VALUE1, VALUE2, ...) ON CONFLICT ([column_name / ON CONSTRAINT constraint_name/ WHERE predicate]) [DO NOTHING] [DO UPDATE SET column1 =..
[Postgresql] auto increment, sequence 처리방법 DB를 개발할때 초반에 고민하는 것중에 하나가 행 입력시 증가하는 일련번호, 시퀀스 번호를 어떻게 동작시킬것인가이다. Postgresql도 여러 방법으로 시퀀스 번호를 동작시키는 메커니즘을 가지고 있다. SEQUENCE 객체 활용 전통적인 방법으로 따로 SEQUENCE 객체를 생성하고 테이블에 붙여주는 방법이다. postgresql 기본적인 시퀀스 생성 CREATE SEQUENCE seq_user_id INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1; 시퀀스를 생성한 후에는 ALTER명령으로 속성을 변경할 수도 있다. 아래 링크에서 기능 설명이 되어 있다. ALTER SEQUENCE [ IF EXISTS ] 이름 [ AS 자료형 ]..
[Oracle] 오라클 기본 명령어 #1 데이터 베이스 내 모든 테이블 DELETE 명령어 추출 SELECT 'DELETE FROM "' || TABLE_NAME || '";' FROM user_tables; 데이터 베이스 내 모든 테이블 DROP 명령어 추출 SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables; 전체 테이블 DROP 후 시퀀스 삭제 purge recyclebin;
[Oracle] 특정 테이블에 데이터가 없을 시 기본값 출력하기 DB쿼리를 짤때 값이 없을 경우에는 디폴트 값을 보여줘야하는 시나리오가 종종 나오게 된다. 그럴경우에 한번에 초기 테이블을 만들때 다음과 같은 방법으로 해볼 수 있다. 아래의 테이블 내용은 STR_ID 별로 물품 카테고리와 물품들 리스트를 보관하는 테이블의 데이터 모습이다. 여기서 특정 STD_ID에 해당하는 카테고리/물품 내용이 저장되어 있지 않을 경우 디폴트 STR_ID = 0인 값들로 무언가 해보고자 한다면 과연 어떻게 초기 테이블을 가져갈 수 있을까? UNION ALL을 활용해 값이 없을때의 STR_ID = 0 인 조회결과와 실제 원하는 값이 있을때의 테이블 결과를 UNION ALL 하면 위 시나리오 대로 실행을 해볼 수 있다. 아래와 같이 조회를 원하고자 하는 STR_ID = 3에 해당하는 값이..
[Oracle] first_value 함수 사용하기 오라클로 DB개발시 특히 집계 결과를 생성시에 최소값 또는 최대값을 기준으로 편차를 계산하는 등의 작업이 필요할 때가 있다. 오라클의 first_value 함수를 사용하면 특정 집단내의 기준값을 찾고 그 기준으로 결과를 뽑아내기가 쉬워진다. 정의는 다음과 같다. FIRST_VALUE (expr [ IGNORE NULLS ]) OVER (analytic_clause) 다음과 같이 간단한 데이터 테이블이 있다고 가정하자.... 카테고리별로 물품이 있고 각 물품은 가격을 가지고 있다. 각 물건의 가격을 전체 집단의 최저가격 대비 얼마만큼 차이가 있는지 쉽게 알수 있는 방법은 뭘까? 예제1. 각 카테고리별로 최저값을 구하고 각 카테고리별로 모든 물품의 최저값과의 차이를 구해라 SELECT CATEGORY, PR..
[Postgresql] Postgres db dump, restore 데이터 베이스를 개발, 또는 운영 중에 백업은 중요한 작업중에 하나이다. 본 절에서는 postgres 데이터베이스를 통으로 바이너리 레벨로 dump하고 이를 그대로 restore하는 명령을 살펴본다. pg_dump 명령어 옵션 PS C:\Program Files\PostgreSQL\13\bin> .\pg_dump.exe --help pg_dump 프로그램은 데이터베이스를 텍스트 파일 또는 기타 다른 형태의 파일로 덤프합니다. 사용법: pg_dump [옵션]... [DB이름] 일반 옵션들: -f, --file=파일이름 출력 파일 또는 디렉터리 이름 -F, --format=c|d|t|p 출력 파일 형식(사용자 지정, 디렉터리, tar, 일반 텍스트(초기값)) -j, --jobs=개수 덤프 작업을 병렬 처리 ..
[Oracle] 파티션 별 순번 매기기 ROW_NUMBER 테이블 쿼리를 한 후 순번을 매기는 경우가 있다. 예를 들어 판매량별로 우선순위를 매기고 이를 순번으로 기록해 두면 추후에 특정 등수 이상 또는 이하의 레코드를 추출할 떄 해당 컬럼을 활용하여 쉽게 추출할 수 있다. ROW_NUMBER 문법 스펙은 다음과 같다. ROW_NUMBER( ) OVER ([ query_partition_clause ] order_by_clause) 예제1) 아래 쿼리는 employees 테이블에서 department_id 별로 salary 액수기준 내림차순으로 정렬한 후 rn(row_number)가 3 이하인 레코드를 추출한다. SELECT department_id, first_name, last_name, salary FROM ( SELECT department_id, fir..
[Oracle] Insert, Update 판단을 한번에... Merge into 구문 살펴보기 서버 개발을 하면 주기적으로 갱신되거나 신규 유입되는 데이터의 경우에 DB에 해당 Key를 가진 레코드를 확인해보고 있으면 Update, 없으면 Insert로 분기시키는 로직을 많이 작성하게 된다. 간단한 개념이지만 은근히 귀찮은 작업이기도 하다. 하지만 오라클 DB에서는 이를 한번에 판단해서 처리해주는 구문이 있다. 바로 Merge into 구문이다. MERGE [ hint ] INTO [ schema. ] { table | view } [ t_alias ] USING { [ schema. ] { table | view } | ( subquery ) } [ t_alias ] ON ( condition ) [ merge_update_clause ] [ merge_insert_clause ] [ error..

티스토리툴바