본문 바로가기

SQL

(5)
[postgresql] with절로 insert,update,delete 를 활용 WITH 절은 다양한 서브쿼리를 한 곳에 정의하며 가독성도 높이고 재사용성이 좋아 사용하게 되는데 예를 들어 배치 스케쥴러 특정 시간 마다 데이터가 있으면 UPDATE , 없으면 INSERT 해주는 쿼리를 작성해야 할 때 WITH절을 사용해서 하나의 쿼리로 완성할 수 있다. WITH UPSET AS( UPDATE TEST_TABLE SET 컬럼1 = '테스트' WHERE 기본키컬럼 = '기본키데이터' RETURNING * ) INSERT INTO TEST_TABLE (기본키컬럼,컬럼1,컬럼2) SELECT '기본키데이터',NULL,NULL WHERE NOT EXISTS (SELECT * FROM UPSET ) --RETURNING * 따로 VALUES를 쓰지 않고 SELECT하면서 #{기본키컬럼VO} 하..
[spring,postgresql] bit타입의 컬럼은 spring vo에 어떻게 설정해줘야하는가? bit타입의 컬럼을 잘 쓰진 않지만 만약 bit타입을 쓰게 될 경우 Spring mvc패턴에서 vo(dto)에서 getter , setter 를 하기 위해 private 자료형 컬럼명(변수명)을 쓰는데 java안에서는 bit타입이 없다. 그래서 byte라던지 다른 타입을 쓰고 insert문은 column 컬럼명 is of type bit but expression is of type smallint(다른 타입) 오류가 나온다. 이것에 대해 해결책으로서 그냥 vo는 private String 형태로 설정하고 sql.xml에서 insert할때 cast(#{컬럼명} as bit)로 타입 변환을 시켜주자
pgadmin) ARRAY_POSITION 함수 , 이렇게도 ORDER BY가 된다? ORDER BY는 보통 컬럼을 두며 ASC 또는 DESC로 정렬을 해준다. 하지만 각 상황마다 ORDER BY 컬럼으로 말고 다르게 정렬해야 할 때가 있다. 예를 들어 설비 특정 이름별로 정렬을 하고 싶을때 -제품과 설비 목록- ------------------------------ 제품명 | 설비 | 일자 ------------------------------ A제품 가열기 2023/02/03 A제품 절단기 2023/02/01 A제품 진공기 2023/03/17 A제품 세척기 2023/01/27 ORDER BY ARRAY_POSITION(ARRAY['가열기','절단기','진공기','세척기'],EQUIP_NM::TEXT) ※EQUIP_NM은 컬럼명 이런식으로 특정 이름으로 정렬을 시킬 수 있다. 기본적으로..
pgadmin 달력 요일 계산, 날짜 함수, 두 날짜간의 차이 등 pgadmin에서 제공하는 함수들이 많다. // 현재 날짜 SELECT NOW() SELECT CURRENT_DATE // GENERATE_SERIES 함수 GENERATE_SERIES 함수는 PGADMIN9.1버전 부터 사용 가능하며 java로 치면 for문과 매우 유사한 함수이다. 아니면 파이썬 for문이였나 암튼 for문이 (초기값,조건식,증감식) 이라면 GENERATE_SERIES (초기값 , 목표값,증감값) 느낌이다. 엥 근데 이게 날짜랑 무슨 상관이냐구요? 이 함수는 편리한게 날짜데이터도 계산을 해주더라고요 ::date 부분은 text형식을 날짜로 변환시켜 주는 것이고 1 HOURS를 쓰면 1시간씩 증가가 된다. 어떤 느낌인지 감이 올 것이다. // extract 함수 extract함수는 요일..
pgadmin 쿼리 성능 분석으로는 쿼리 실행 속도를 측정할 수가 없다. 아무리 구글링을 하고 오픈채팅에 들어가서 물어봐도 쿼리 성능 분석이 쿼리 실행 예상 속도를 측정할 수가 없다. 물론 쿼리 실행 속도가 조금씩 다를 수 있고 구글링으로 얻은 성능 분석 읽는 법에선 실제 쿼리 실행 속도랑 전혀 다른 값이 나와 신뢰를 잃었다. 블로그 글을 다 뒤지고 아무리 계산을 때려보아도 정확히 4분 45초가 아니더라도 4분 언저리정도 걸린다 이런 예측이 되야하는데 처음부터 차근차근 보자 cost=0.00..66097.02 어떤 블로그 글에는 여기 부분이 해당 row를 스캔하는데 걸리는 시간 단위라고 써놨다. 하지만 이걸 통해 조회하는데 걸리는 시간을 알 수 없었다. 66097이 밀리초라면 초로 변환하기 위해 1000을 나누면 66.097초 그렇다면 4분은 커녕 1분6초밖에 걸리지 않는다...