데이터베이스/PostgreSQL7 [PostgreSQL] group by 결과를 받아볼 수 있는 함수 SQL로 데이터 분석을 하다보면, 은근히 Group by 집계 쿼리문과 Having 후 필터링 조건문을 사용할 일이 많단 말이죠. 독자분들이 저와 같은 귀차니즘이라면 한 번 사용해보세요. 특히 pk가 논리적 erd로만 표현되어 있고, 물리적으로 생성되지 않아, 중복 데이터 유무를 조회해야 할 때 유용하게 사용 중입니다. ※ 지원 가능한 버전(Supported Versions): Current (17) / 16 / 15 / 14 / 13 1. Create Function함수를 생성합니다.물론 group by문으로 조회할 테이블과 같은 데이터베이스에 생성해야 합니다.group by 컬럼이 1개 이상이므로 jsonb 타입으로 집계처리 합니다.create or replace function public.. 2025. 1. 23. [PostgreSQL] 통계 정보 수집 (ANALYZE, VACCUM) 통계 정보 수집 방법1. 전체 데이터베이스에 대해 통계 정보 수집데이터베이스 내 모든 테이블의 통계 정보를 갱신하려면 다음 명령을 실행합니다:ANAYZE; 이 명령은 데이터베이스에 있는 모든 테이블과 열의 통계를 수집합니다. 2. 특정 테이블에 대해 통계 정보 수집특정 테이블만 통계 정보를 갱신하려면 테이블 이름을 명시합니다:ANALYZE your_table_name;해당 테이블의 모든 열에 대해 통계를 수집합니다.3. 특정 열에 대해 통계 정보 수집테이블의 특정 열에 대해서만 통계를 갱신하려면 열 이름을 명시합니다:ANALYZE your_table_name (your_column_name);이 방법은 열에 대한 통계 정보만 수집하므로 빠르게 실행됩니다.4. VACUUM과 함께 통계 정보 갱신VACUU.. 2025. 1. 3. [PostgreSQL] count 함수를 쓰지 않고, 테이블 건수 조회하는 방법 개요다들 데이터 업무를 하다보면 대용량 테이블의 건수를 대략적으로 빠르게 알고 싶을 때가 생길텐데요,이때 정확한 건수는 필요없고, 대략 이정도 건수라는 것만 알고 싶은 분들은 통계 정보를 활용하시면 됩니다. 통계 정보 활용-- 통계정보 갱신ANALYZE table_name;--통계정보로 미리 수집된 테이블 건수 조회SELECT reltuples::BIGINT AS approximate_row_countFROM pg_classWHERE relname = 'table_name';건수 조회 속도 테스트1. 테이블 구조- 단순한 date, code, value 테이블이여도 건수가 많아지면 단순 count 호출 속도가 느려집니다. 2. 건수별 조회 속도 비교통계 정보 조회하는 것이 압도적으로 빠름건수count 함.. 2025. 1. 3. [PostgreSQL] 비정형 데이터를 PostgreSQL에 저장하고 정형 테이블로 변환하기 개요API 호출 결과와 같이 비정형 데이터가 반환될 때, 데이터의 필드가 가변적일 경우 PostgreSQL에 비정형 데이터를 먼저 저장한 후, 필요한 필드를 정형 데이터로 변환하는 과정을 구현해 보겠습니다.1. 문제 정의1.1 API 호출 결과API 호출 결과는 JSON 배열로 반환되며, 각 객체는 필드 구성이 다를 수 있습니다.[{"code": "a", "name": "b", "city": "seoul"},{"code": "ab", "name": "bb", "amount": "1"}]1.2 요구 사항비정형 JSON 데이터를 PostgreSQL에 그대로 저장합니다.이후 필요한 필드만 추출하여 정형 테이블에 삽입합니다.2. 구현 과정2.1 API 호출API 호출은 Python의 requests 라이브러리를.. 2024. 11. 9. [PostgreSQL] 모든 테이블 read only User / role 권한 부여 개요모든 테이블에 대해 접근, 조회 권한만 부여된 유저를 전달하기 위함PostgreSQL 14 version 부터 default roles(Predefined Roles) 중에 read_only role이 내장되어 있어서 별도 생성하지 않아도 됨유저 생성, 권한 부여 예제8.1 version 부터 User, Group 이 모두 Role로 통합되었으므로, create role로 유저 객체를 생성하는 걸 권장CREATE ROLE "read_only_user" WITH PASSWORD 'abcde#fgh';grant pg_read_all_data to read_only_user;postgresql Predefined Roles 참고- 표 - 출처: https://www.postgresql.org/docs/cu.. 2024. 8. 16. [PostgreSQL]다른 db 조회할 수 있는 Extension - 'postgres_fdw' 머릿말PostgreSQL에서 쿼리를 날리다가 다른 DBMS처럼 외부 DB 테이블과 조인을 해야할 때, 아래와 같은 오류를 보신적 있으신가요?SQL Error [0A000]: ERROR: cross-database references are not implemented: "테이블명"snowflake에서 PostgreSQL로 마이그레이션 작업을 하던 도중, 같은 서버 DBMS 내 다른 DB를 조회할 때 제약이 걸려 있다는 것을 확인했습니다.타 DB접근하기 위해 설정 방법을 글로 정리해야겠다고 생각했습니다.PostgreSQL 다른 DB 조회 정책PostgreSQL 서버에 한 클라이언트가 접속할 때, 반드시 연결할 DB를 지정해야 합니다.하나의 연결(한 클라이언트가 접속할 때)로 여러 DB를 접속할 수 없습니다.. 2023. 11. 9. 이전 1 2 다음 반응형
