본문 바로가기

언어/SQL7

Key없이 UPSERT PK가 존재한다면, 해당 PK를 기반으로 UPSERT 쿼리를 짤 수 있다. 예를 들어 아래와 같은 테이블이 있다고 해보자.CREATE TABLE IF NOT EXISTS TMP_TABLE ( PK_SEQ SERIAL PRIMARY KEY, USER_ID VARCHAR(100) UNIQUE NOT NULL, USER_NAME VARCHAR(100));  만약 해당 테이블에 대한 UPSERT 쿼리를 짜야한다면 아래와 같이 짤 수 있을 것이다. (MariaDB 기준)INSERT INTO TMP_TABLE(PK_SEQ, USER_ID, USER_NAME)VALUES(1, '123okk2', 'ㅇㅁㅇ')-- 아래가 UPSERT 부분ON DUPLICATE KEY UPDATE USER_ID='123okk2', .. 2024. 4. 29.
[PostgreSQL] VARCHAR 칼럼 내 일부 단어만 변경 한 테이블의 VARCHAR 칼럼 내 일부 단어를 다른 단어로 변경해야 하는 상황이 발생했다. 방법은 생각 외로 간단했다. 그냥 단순하게 REGEXP_REPLACE 함수를 사용하면 된다. 이제 테스트를 한 번 해보자. 단순한 단어 변경 테스트용 데이터를 아래와 같이 생성해보았다. CREATE TABLE IF NOT EXISTS replace_test ( -- SERIAL 사용 시 postgresql은 AUTO_INCREMENT가 자동 적용됨. serial_no SERIAL PRIMARY KEY, simple_str VARCHAR(100), detail_str VARCHAR(1000) ); INSERT INTO replace_test (simple_str, detail_str) VALUES ('this', .. 2024. 2. 5.
View와 Materialized View 갑자기 궁금한 게 생겼다. 바로 "View에도 인덱스를 걸 수 있나?" 였다. 그리고 궁금점에 대한 답을 찾아보던 중 몰랐던 개념을 알아냈다. 바로 MATERIALIZED VIEW였다. 얼추 어떤 것인지는 알아냈지만, "알았다!" 하고 아무것도 안하면 분명 까먹을 것이 분명하기에 적어놓으려고 한다. VIEW 우선 VIEW는 데이터베이스의 테이블에서 가져온 결과 세트에 대한 저장된 쿼리이다. 실제 데이터를 저장하지 않고, 특정 쿼리에 대한 결과를 제공하는 "가상 테이블"로 생각하면 된다. 예를 들어서 다음과 같은 DB가 있다고 가정하자. 우선 위 세 개의 테이블에 대한 CREATE와 테스트용으로 사용할 더미 데이터를 입력한다. 참고로 MariaDB는 Materialized View를 지원하지 않아 Post.. 2023. 7. 24.
Batch Insert/Delete 사용자에게 적게는 수 개에서 많게는 수만 개의 데이터를 받아 일괄적으로 처리하는 CRUD API를 개발한 경험이 있다. 처음으로 다량의 데이터를 받아 처리해야 하는 기능을 맡게 되었었다. 그래서 경험이 없어 고민을 했던 게 있다. 그건 바로 "어떻게 하면 보다 빠르게 CRUD가 완료될까"에 대한 고민이었다. 우선 로직은 아래와 같았다. 사용자에게 데이터 리스트를 입력받는다. 데이터를 하나하나 검증해보며 양식에 맞지 않거나 잘못 들어온 데이터를 식별한다. 검증된 정상적인 데이터만 골라내 DB에 Upsert한다. 검증 실패 데이터와, 검증 성공 및 db 정상 입력 데이터를 각각의 리스트에 담아 return한다. JAVA 내부에서는 어쨌든 전달받은 데이터를 개별적으로 검증하는 작업을 해야 하므로 속도를 극대화.. 2023. 7. 22.
[PostgreSQL] 시간 쿼리 가장 최근 프로젝트에서 RDB는 PostgreSQL만 사용했다. 그러다보니 몰랐던 사실을 몇 가지 알게 되었는데, 시간 관련 쿼리가 대표적인 예시였다. 요즘들어 PostgreSQL을 사용하는 프로젝트들이 많은데, 적어놓지 않으면 까먹을 것이 분명해서 조금이라도 오래 기억하고, 추후 필요해질 경우 곧바로 활용할 수 있도록 저장을 해놓고자 한다. 우선 다음 구조의 간단한 테이블이 있다고 가정한다. 간단하게 name, reg_date로 이루어진 테이블이다. 해당 테이블에는 아래와 같은 데이터가 입력되어있다. (참고로 오늘 날짜는 2023-05-29이다.) SELECT에서 월, 연, 일, 요일 등 특정 시간 정보만 뽑아내기 가장 먼저 저장할 쿼리는 reg_date (timestamp)에서 연, 월, 일, 요일,.. 2023. 5. 29.
[PostgreSQL] 캐릭터형 AUTOINCREMENT AutoIncrement를 사용하면 1, 2, 3, 4 와 같은 시퀀스 ID가 자동으로 만들어지게된다. 하지만 때로는 위와 같은 방식이 아니라, 숫자형의 자리수를 정해서 0001, 0002 와 같은 ID를 만들어야 할 때가 있다. PostgreSql에서는 다음의 방법으로 위의 ID를 만들 수 있다. 쿼리는 다음과 같다. CREATE SEQUENCE IF NOT EXISTS START WITH 1 INCREMENT BY 1 NO MINVALUE || MINVALUE 1 NO MAXVALUE || MAXVALUE 99999 CACHE 1; CREATE TABLE IF NOT EXISTS ( column1VARCHAR(N)DEFAULT to_char(nextval('SEQUENCE_NAME'), 'FM0000.. 2021. 10. 25.
[PostgreSQL] 특정 칼럼 기준 가장 오래된 데이터 보여주기 조금은 이상하다고 느껴지는 요구사항을 만났다. 1:N 관계를 가진 테이블을 조인시켜, 대표적으로 하나의 데이터만 보여줘야 하는 요구사항이었다. 예를 들어 다음의 테이블이 있다고 가정하자. 설명하자면 하나의 상품이 있으면, 해당 상품을 판매하는 판매자는 여러 명이 될 수 있다. 하지만 사용자에게 보여줄 때는 모든 판매자를 보여주는 것이 아니라, 단 한 명의 판매자만 보여줘야 한다. 즉 1:N 관계의 테이블이나 마치 1:1 관계인 것처럼 보여줘야만 하는 것이다. 이를 테이블로 표현하면 아래와 같이 만들어진다. 물론 위처럼 조회용으로 테이블을 만들어서 별도로 만들면 편하겠지만, 오히려 코드가 복잡해질 가능성이 있고 관리해야 할 테이블이 늘어났다. 그리고 무엇보다 성격상 소위 말하는 가라를 치고 싶지 않았다. .. 2021. 10. 25.

티스토리툴바