[시간관련처리]
현재시간 | NOW() or CURRENT_TIMESTAMP | 현재시간을 추출함
형식변환(날짜) | NOW()::date | '2026-04-17' 형태만 남김
형식변환(파싱) | '2024-05-12 08:30:00'::timestamp | 문자열을 날자로변환
시간차이 | EXTRACT(EPOCH FROM (후-전))/3600 |
두시간사이 차이를 초로 반환해서 /3600을 하니까 시간단위로 바뀐것
시간더하기 | NOW() + INTERNAL '9 MINUTE' | 특정 시간의 합산처리
특정부분추출 | EXTRACT(HOUR FROM NOW()) | 시간에서 지정값을 뽑음
※ 시간(timstamp)을 문자로 변환하는것은 TO_CHAR() 하나로 끝남
TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS.MS') | 지정 스타일로 변환
[기간조회 공식은 같이 먹힘]
WHERE (sb::timestamp <= pe:timestamp) AND (se::timestamp >= pb::timestamp)
포스트그리SQL만은 기간조회가 아예 함수로도 있음
WHERE (SB, SE) OVERLAPS (@PB, @PE)
[백업 및 복원] - 명령파일 pg_dump.exe 같은걸로 실행해야함
[백업]
pg_dump -h <원격서버_IP> -p <포트번호> -U <권한있는계정> -d <db_이름> -F c -b -v -f "저장할_파일명.dump"
[클린복원]
pg_restore -h <원격서버 IP> -p <포트번호> -U <권한있는계정> -d <db_이름> -c -v "파일명.dump"
테이블만 지정할때는 -t 옵션으로 지정해서 처리해야함..
[프로그래밍 방식] - DO $$로 시작해서 BEGIN .. END $$;로 감싸는 구조임
예를 들어 '2017-05-23 14:23:32' 라는 문자를 파싱해서 timestamp로 만들고
그걸 문자열 6시간과 int 변수인 초값으로 더하고 그 결과를 다시 초로 환산해서
임시테이블로 바꿔서 select하는 예시
do $$
declare
input_str text := '2017-05-23 14:23:32';
target_date timestamp;
result_seconds integer;
add_sec integer := 30;
begin
create temp table if not exists temp_tb (
ori_time text,
cvt_time timestamp,
calc_sec integer
);
truncate temp_tb;
--
target_date := input_str::timestamp;
target_date := target_date + interval '6 hours';
target_date := target_date + (add_sec || ' minutes')::interval;
result_seconds := extract(epoch from target_date)::integer;
--
insert into temp_tb (ori_time, cvt_time, calc_sec)
values (input_str, target_date, result_seconds);
end $$;
--
select ori_time, cvt_time, calc_sec from temp_tb limit 1;
[또는 with와 as의 임시 테이블을 이용하는 방법]
WITH
var_ttb AS (SELECT '2017-05-23 14:23:32' AS input_str, 30 AS add_sec),
cal_ttb AS (SELECT input_str, add_sec, input_str::timestamp + INTERVAL '6 hours' AS base_date FROM var_ttb),
res_ttb AS (SELECT input_str, base_date + (add_sec || ' minutes')::interval AS target_date
FROM cal_ttb)
--
SELECT input_str AS ori_time, target_date AS cvt_time, EXTRACT(EPOCH FROM target_date)::integer AS calc_sec FROM res_ttb;
[접속한 db의 모든 테이블 목록보기]
SELECT * FROM information_schema.tables WHERE table_schema = 'public';
[자동증가 id]
자동증가 id의 경우 자료형을 integer로 해서 초기값과 증가값을 가지는 경우..
묻지도 따지지도 말고 serial 자료형으로 처음부터 등록해야함.
자동증감기라는 오브젝트가 아예 별도로 있어서 뒤로 당겨주는 역할을 별도로 하기때문에..
자동증가 당겨주기는
SELECT setval(pg_get_serial_sequence('mytable', 'id'), COALESCE(max(id), 0)) FROM mytable;
로 처리하면 되는데
자료형을 바꺼버리면 아이디가 새로 부여된다.. 굳이 아이디가 바껴도 상관없다면..
ALTER TABLE mytable DROP COLUMN id;
ALTER TABLE mytable ADD COLUMN id SERIAL PRIMARY KEY;
이렇게 할수도 있지만 id의 값이 바껴버림 (주의)
[pgAdmin4 에서 전체 테이블 create 스크립팅 처리]
데이터베이스 -> 백업 -> 형식(Plain) -> 데이터 옵션(객체종류: Only schemas)
-> 파일경로지정(*.sql) -> 백업버튼
[*.sql 파일처리]
*.sql에 대량의 insert문이나 혹은 내용에 select가 있어서 결과셋을 헤더가 있는 csv파일로
출력할때는 다음처럼 psql 명령을 이용한다. 원래는 콘솔창에서 접속해서 쿼리작업을 위한 용도
이지만 이렇게 처리해도됨.
psql -h 127.0.0.1 -p 5432 -U postgres -d parking_db -v ON_ERROR_STOP=1 -f "D:/input.sql"
이때 결과셋을 출력하는 내용이 input.sql 파일에 다음과 같이 있다면
\copy (SELECT user_no, user_name FROM myuser) TO 'D:/output.csv' WITH CSV HEADER ENCODING 'UTF-8';
실행결과셋이 csv로 출력된다.
[주기적인 청소]
os에 따라서 윈도우 작업 스케줄러 같은게 있음.
postgresql은 바이너리로 reindexdb가 외부실행이 가능하도록 되어 있다.
따라서 그 작업 스케쥴러에 배치파일을 만들어서 다음과 같이 일주일에 한번
예를들어 매주 수요일 새벽 2시 에 한번 다음과 같은 bat파일을 만들어놓고
실행하는것이 좋다.
reindexdb.exe -U [사용자명] -d [DB이름] --concurrently
vacuumdb.exe -U [사용자명] -d [DB이름] --analyze
댓글 없음:
댓글 쓰기