🐘 PostgreSQL을 위한 준비된 SQL 쿼리 모음

목차

사용자 데이터 가져오기

1. 유효성 검사 및 도메인
   1. 이메일 유효성 검사는 어떻게 하나요?
   2. 테이블에서 모든 유효하지 않은 이메일을 찾는 방법은요?
   3. 대용량 테이블에서 모든 유효하지 않은 이메일을 삭제하는 방법은요?
   4. CSS 색상 유효성 검사는 어떻게 하나요?
   5. 회사 정보(ИНН, КПП, ОГРН, ОГРНИП) 유효성 검사는 어떻게 하나요?
   6. 은행 정보(БИК, 계산 계좌, 담당 계좌) 유효성 검사는 어떻게 하나요?
   7. СНИЛС 유효성 검사는 어떻게 하나요?
   8. 전화번호 유효성 검사는 어떻게 하나요?
   9. UPDATE 쿼리에서 명시적으로 지정된 경우에만 필드 값을 유효성 검사하는 방법은요?
2. MySQL/MariaDB에서 PostgreSQL로 마이그레이션하기
   1. MySQL의 group_concat() 함수를 대체하는 방법은요?
   2. MySQL의 set 타입을 대체하는 방법은요?
   3. MySQL의 make_set() 함수를 대체하는 방법은요?
3. 파일
   1. SELECT 쿼리 결과를 CSV 파일로 내보내는 방법은요?
   2. CSV 파일에서 테이블에 데이터를 불러오는 방법은요?

PostgreSQL에서 CSV 파일 내보내기 및 가져오기 스니펫 (шпаргалка по выгрузке и загрузке CSV файлов в PostgreSQL)

1. 문자열
   1. 러시아 문자를 영어로 전사하는 방법?
   2. CSV 문자열을 테이블로 파싱하는 방법?
   3. 러시아어 이름(성, 이름, 부칭)으로 성별을 판단하는 방법?
   4. 정규 표현식에서 사용하기 위해 문자열을 이스케이프 처리하는 방법?
   5. LIKE 연산자에서 사용하기 위해 문자열을 이스케이프 처리하는 방법?
2. JSON
   1. JSON 배열의 조건에 맞는 레코드를 가져오는 방법?
   2. 2개의 JSON 객체를 비교하고 차이점을 얻는 방법?
3. 배열 (Array)
   1. 배열 연결(합치기) 집계 함수
   2. 배열의 동일한 요소 가져오기 (교집합)?
   3. 배열에서 고유 요소 가져오거나 정렬하는 방법?
   4. 두 배열의 다른 요소 가져오는 방법?
   5. 배열 요소에 외래 키를 설정하는 방법?
4. 구문 검색 (정확한 및 부정확한)
   1. 템플릿 목록과 일치하는 문자열 목록을 찾는 방법?

⚠️ [IMG:N] 형식 토큰은 이미지 placeholder 입니다. 번역하지 말고 원래 위치에 그대로 유지하세요.

[검색 구문으로 단어 시작을 고려하여 엔티티 이름 가져오기] (#как-получить-названия-сущностей-по-поисковой-фразе-с-учётом-начала-слов-поисковые-подсказки)
1. [오타가 있는 단어에 대해 가장 적합한 대체 단어 옵션 가져오기 (오타 수정)?] (#как-для-слова-с-опечаткой-ошибкой-получить-наиболее-подходящие-варианты-слов-для-замены-исправление-опечаток)
1. [트리 및 그래프] (#деревья-и-графы)
1. [각 노드 트리의 조상 및 후손 이름 목록 가져오기 (지역 예시)?] (#как-получить-список-названий-предков-и-наследников-для-каждого-узла-дерева-на-примере-регионов)
1. [그래프에서 순환 관계 가져오기?] (#как-получить-циклические-связи-в-графе?)
1. [그래프에서 순환 관계 방지하기?] (#как-защититься-от-циклических-связей-в-графе?)
1. [4단계 활동 분야의 모든 레벨 이름 가져오기?] (#как-получить-названия-всех-уровней-сферы-деятельности-4-го-уровня)
1. [쿼리 실행 최적화] (#оптимизация-выполнения-запросов)
1. [쿼리 실행 계획 (EXPLAIN)을 시각적인 그래픽 형태로 보는 방법?] (#как-посмотреть-на-план-выполнения-запроса-explain-в-наглядном-графическом-виде?)
1. [IN(...)에 수천 개의 값을 가진 SELECT 쿼리 가속화하기?] (#как-ускорить-select-запросы-c-тысячами-значений-в-in?)
1. [EXPLAIN 쿼리 출력을 다른 쿼리에서 사용하는 방법?] (#как-использовать-вывод-explain-запроса-в-другом-запросе?)
1. [PostgreSQL v10에서 v12로 마이그레이션 후 SELECT 쿼리 가속화하기?] (#как-ускорить-select-запрос-после-переезда-с-postgresql-v10-на-v12?)
1. [SELECT COUNT(*) 쿼리 가속화하기?] (#как-ускорить-select-count-запрос?)
1. [함수를 N천 번 실행하고 속도 측정하는 방법?] (#как-выполнить-функцию-n-тысяч-раз-и-измерить-скорость-выполнения?)

필드 값으로 중복 레코드 가져오기(어떻게 필드 값으로 중복 레코드를 얻을 수 있나요?)

1. 쿼리 실행 시간을 결과에서 얻는 방법은 무엇인가요?
2. 큰 테이블을 N천 개의 레코드로 분할하는 방법은 무엇인가요?
3. 이전 쿼리가 결과를 반환하지 않은 경우 다음 SQL 쿼리를 실행하는 방법은 무엇인가요?
4. 레코드를 컬럼 세트로 펼치는 방법은 무엇인가요?
5. 하나의 쿼리에서 각 레코드에 대한 총합과 백분율을 얻는 방법은 무엇인가요?
6. 생년월일로 나이를 계산하는 방법은 무엇인가요?
7. ISO-8601 형식으로 날짜 또는 날짜와 시간을 얻는 방법은 무엇인가요?
8. 지구 표면에서 두 지점 간의 거리를 킬로미터 단위로 계산하는 방법은 무엇인가요?
9. 주어진 좌표를 기준으로 가장 가까운 주거지를 찾는 방법은 무엇인가요?
10. SELECT 쿼리 결과에 대한 대략적인 데이터 볼륨을 계산하는 방법은 무엇인가요?
11. NOT IN()의 서브쿼리가 0개의 레코드를 반환하는 이유는 무엇인가요?
12. record와 NULL 비교의 특징은 무엇인가요?
13. 큰 테이블에서 레코드 개수를 매우 빠르게 얻는 방법은 무엇인가요?
14. 재귀 쿼리를 만드는 방법은 무엇인가요?

사용자 데이터 수정 (DML)

1. 단일 쿼리로 레코드 추가 또는 업데이트하기 (UPSERT)?
2. 중복된 경우 시퀀스 증가 없이 INSERT ... ON CONFLICT ... 사용하기?
3. INSERT ... VALUES ...를 통해 행 추가 속도 높이기?
4. 여러 테이블의 데이터를 수정하고 영향을 받은 레코드의 ID를 단일 쿼리로 반환하는 방법은?
5. 연관된 테이블의 데이터를 단일 쿼리로 수정하는 방법은?
6. ID를 포함하여, 그 ID 값을 동일한 INSERT 쿼리 내 다른 필드에 저장해야 하는 레코드 추가하기는 어떻게 하나요?
7. 첫 번째 쿼리에서 삽입된 ID를 사용하여 여러 후속 쿼리를 실행하는 방법은?
8. UPDATE 쿼리의 데이터가 DB의 데이터와 일치할 경우 레코드를 업데이트하지 않도록 하는 방법은?
9. 다른 사람이 이미 수행한 변경 사항을 덮어쓰지 않고 레코드를 업데이트하는 방법은?
10. 단일 쿼리로 여러 레코드를 다른 데이터로 업데이트하는 방법은?
11. 테이블의 수백만 개 레코드를 모든 레코드를 잠그거나 DB에 부하를 주지 않으면서 업데이트하거나 삭제하는 방법은?

⚠️ [IMG:N] 형식 토큰은 이미지 placeholder 입니다. 번역하지 말고 원래 위치에 그대로 유지하세요.

PostgreSQL용 SQL 패턴 라이브러리

테이블에서 수만 개의 레코드를 삭제하면서 모든 레코드를 잠그지 않고 DB 부하를 줄이는 방법

1. 하나의 엔티티에 대해 삽입되는 종속 엔티티의 개수를 제한하는 방법
2. 테이블 변경 이력을 기록(저널링)하는 방법
3. 자동 업데이트되는 updated_at 필드를 만드는 방법

데이터 스키마 수정 (DDL)

1. 기존 테이블에 컬럼을 추가하면서 잠금(blocking)을 피하는 방법
2. 아직 존재하지 않는 경우 테이블 제약 조건을 추가하는 방법
3. 테이블 잠금 없이 외래 키(Foreign Key) 제약 조건을 수정하는 방법
4. 레코드 추가 또는 업데이트 시 M개의 가능한 필드 중 N개가 채워졌는지 확인하는 방법
5. enum 타입에서 값을 삭제하는 방법
6. 전체 DB에서 특정 객체 이름의 모든 언급을 찾는 방법
7. 컬럼 타입을 int에서 bigint로 변경하는 방법
8. 시퀀스 값이 뒤처졌을 때 복구하는 방법

인덱스(Indexes)

1. 기존 테이블에 인덱스를 생성하거나 재생성하면서 잠금을 피하는 방법

한 필드 중 하나가 NULL일 수 있는 복합 고유 인덱스 생성 방법 {#как-сделать-составной-уникальный-индекс-где-одно-из-полей-может-быть-null}

1. 중복된 데이터가 포함된 손상된 고유 인덱스를 수정하는 방법?
2. 인덱스를 일시적으로 비활성화하는 방법?
3. 텍스트 필드에 압축된 고유 인덱스를 생성하는 방법?