postgresql 사용시 알아야할 내용

---

[시간관련처리]

현재시간    |    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';

---

[pgAdmin4 에서 전체 테이블 create 스크립팅 처리]

데이터베이스 -> 백업 -> 형식(Plain) -> 데이터 옵션(객체종류: Only schemas)
-> 파일경로지정(*.sql) -> 백업버튼