Postgresql

PostgreSQL에서 서버 프로그래밍의 일환으로 PL/pgSQL, PL/Tcl, PL/Perl, PL/Python 등 여러 프로그래밍 언어를 사용하여 만들 수 있다. 함수(FUNCION)는 다양한 작업을 자동화하거나 간단한 데이터 처리를 수행할 수 있으며 그중 기본 설치되어 있는 PL/pgSQL 으로 간단한 예시를 정리해본다.

예전에 DBeaver 로 postgresql 에서 DB 쿼리를 실행하는 툴로 포스팅한적이 있었는데, pgAdmin 이란 툴도 있어서 설치 및 접속까지 해보기로 했다. 설치해보니 pgAdmin을 통해 PostgreSQL을 보다 쉽게 다룰 수 있으며, 데이터베이스를 직관적이고 효율적으로 관리할 수 있어서 당분간 DBeaver 대신에 사용해볼 예정이다.

Docker를 사용하여 PostgreSQL을 설치하고 관리하는 것은 매우 간편하며, 개발 환경에서 유용하게 활용될 수 있다. 이번 포스팅에서는 Docker에서 PostgreSQL을 셋팅하는 법까지는 아니고 실행하는 명령어만 대략 정리하고, DBeaver를 사용하여 데이터베이스에 연결하는 방법을 단계별로 설명해본다.

PostgreSQL을 이용해서 웹페이지를 만들고 있는데, 계속 소스 볼륨이 커지다보니 git branch 를 추가하면서 개발을 하고 있다. git 브랜치 생성 및 병합방법 관련 포스팅은 여기를 클릭하여 참고한다. 소스는 잘 나누어 개발하고 있는데, DB는 실제 운영중인 DB에다 계속 테스트거래를 해볼 수는 없어서, 하나의 테스트DB 환경을 하나 더 만들어 보고자 했다.

DB 에서 거래를 등록/변경할때, 어느 시점에 등록했는지를 기록하기 위해서 보통 timestamp 를 사용한다. postgresql 에서 timestamp 컬럼을 추가해본적이 없어서 시행착오를 겪긴했다. 그 내용을 남겨보도록 한다.

페이징 처리는 웹서비스를 만들다보면 직면하는 가장 흔한 개발요건이다. 웹화면에서는 한 페이지에 보여줄 데이터는 한정적이고, 페이지를 나누어 효율적인 방법으로 최적의 조회성능을 내줘야 한다. 오늘 볼 예제는 대량의 데이터는 아니며, 간단히 10건 미만의 데이터를 기준으로 limit 를 이용한 페이징처리 쿼리정도만 정리해본다.

SQL 실행툴로서 DBeaver를 무료로 아주 잘 사용하고 있다. 무료버전은 데이터 내보내기나 가져오기를 할 때, csv 파일만 가능하다보니, 엑셀파일 확장자를 가지고 바로 데이터 적재할 수 없는 아쉬움은 있다. 그래도 웹서비스에 필요한 간단한 데이터베이스관리나 sql 실행 정도라면 이만한 무료 sql 툴이 있을까 싶다.

PostgreSQL DB에 데이터를 만들어 넣을 때, RPostgres 패키지를 사용하면 된다. 데이터프레임을 가지고 DB에 select, insert, update, delete 하는 모든 기능을 R프로그램을 이용해서 다 처리할 수 있다. 그 중에, 데이터베이스이름 dbname 과 스키마이름 schema 을 제대로 설정해서 원하는 스키마에 테이블을 만들어보는 것을 정리해본다.

윈도우 함수는 테이블에서 row 집합을 대상으로 계산하는 함수이다. 보통 min, max, count 함수들도 그 대상에 포함된다. 더 많은 함수가 있으니 클릭해서 참고하자. 보통 이런 함수들은 group by 절과 함께 사용해서 특정 값을 기준으로 그룹핑되어 요약된 행으로 산출된다. 하지만 윈도우 함수는 특정 행을 기준잡아서, 산출을 해주되 행수는 줄이지 않고 다 보여줄 수 있다. 예시를 보는게 더 이해가 빠를 듯 하다.

PostgreSQL DB 를 사용하면서 날짜 혹은 숫자를 여러행으로 리턴하는 함수가 필요해서 찾아보았다. generate_series 함수를 이용하면 별도 테이블을 create 하지 않아도, 손쉽게 연속하는 숫자 또는 날짜 리스트를 만들 수 있다.