오래된 개발자!!!

 mdate (속성 timestamp) 컬럼을 '2020-11-01' 에서 '2020-11-06' 일 까지 검색

# 잘못 됨
WHERE to_char(mdate,'YYYY-MM-DD') BETWEEN '2020-11-01' AND '2020-11-06'

# 권장하지 않음
WHERE mdate::date BETWEEN '2020-11-01'::date AND '2020-11-06'::date

데이터가 많은 테이블에서 timestamp 를 to_char 로 변한하면 안 됨.  Index 안 탐.

개선

# mdate 컬럼 속성을 변경하지 않아야 함
WHERE mdate >= '2020-11-01'::timestamp AND mdate < '2020-11-06'::timestamp + interval '1 day'

--> 앞으로 고민 해 봐야 할것

between 을 사용하고 싶은데... + interval '+1day -1second' 이렇게 해도 되긴되는데 

* 주의!!! 성능문제가 발생 한다고 하네요. 이런 방법도 있구나 하고 읽어 주세요 ^^

OFFSET 시작번호 limit 페이지보여줄갯수

OFFSET 0 limit 20;

select 
    user_id
   , user_name
from tbl_user
order by user_id
OFFSET 0 limit 20;

ORACLE 문법 도 된다.

OFFSET 시작번호 ROWS FETCH NEXT 페이지보여줄갯수 ROWS ONLY

OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

select 
    user_id
   , user_name
from tbl_user
order by user_id
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

되는구나! 

psql 실행 후 원하는 테이블을 조회하면서 csv 파일로 복사한다.

copy (select * from 테이블명) to '/원하는경로/result.csv' with csv 

끝이다.

=# copy (SELECT * FROM table_demo) to '/home/demo/table_demo_data.20190731.csv' with csv

* CentOS 에서 기본으로 postgresql 가 설치되어있는지 확인한다.

~]# rpm -qa | grep postgres
postgresql-docs-9.2.24-1.el7_5.x86_64
postgresql-libs-9.2.24-1.el7_5.x86_64
postgresql-9.2.24-1.el7_5.x86_64
postgresql-server-9.2.24-1.el7_5.x86_64

* 설치된 것이 있다면 삭제 한다.

~]# yum remove postgresql*

* PostgreSQL 11 버전을 설치 하기 위해서는 Yum repository 를 업데이트 해야 한다.

~]# rpm -Uvh https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm 

* PostgreSQL 11 을 설치 한다.

~]# sudo yum install -y postgresql11-server postgresql11-contrib 

postgresql11-server : PostgreSQL 11 Database Server
postgresql11-contrib : 추가 지원 모듈

주의! 설치 후 최초 Database 를 생성 해야 한다 !

~]# /usr/pgsql-11/bin/postgresql-11-setup initdb 

* 서비스 등록 및 실행 

~]# systemctl start postgresql-11 
~]# systemctl enable postgresql-11 

* 서비스 상태 확인

~]# systemctl status postgresql-11 

* 관리자 passsword 변경

~]# su - postgres -c 'psql' 
=# ALTER USER postgres PASSWORD '변경할비밀번호'; 

-- postgres 사용자 권한으로 psql 명령어 실행 

* 외부 접속 허용

~]# vi /var/lib/pgsql/11/data/postgresql.conf  

-- 중략 --
listen_addresses = '*' 

-- 중략 --

~]# vi /var/lib/pgsql/11/data/pg_hba.conf 

-- 중략 --

host      all             all         0.0.0.0/0      md5 

* 외부 접속 허용 후 서비스를 Restart 해 줘야 한다.

~]# systemctl restart postgresql-11 

* 방화벽 허용

~]# firewall-cmd --permanent --zone=public --add-port=5432/tcp 
~]# firewall-cmd --reload

PostgreSQL 프로시져 를 구현 하고자 할때 Function 을 만들어 사용한다.

(최신버전 11 부터는 procedure 를 지원한다.)

결과가 없는 Function을 실행 할 때에는 select 대신 PERFORM 을 사용하자.

PERFORM FUNCTION_NAME();

결과가 있는 Function 이라면 당연히 select 를 사용한다.

SELECT FUNCTION_NAME();

터미널 에서 postgres 사용자 전환 후 psql 을 실행 한다.

~]# su - postgres

~]$ psql

적용할 데이터베이스 로 이동 후 pgcrypto 를 설치 한다.

# \c demo_db

# CREATE EXTENSION pgcrypto;

먼저 postgres 로 사용자 변경을 한다.

 ~]# su - postgres

만약 db name 이 test_db 라면 백업 해 보자

 ~]$ pg_dump test_db > test_db.dump

좀더 자세히 알고 싶다면 아래 내용을 확인해 보자.

pd_dump 매뉴얼 에 설명된 내용이다.

EXAMPLES
    To dump a database called mydb into a SQL-script file:

       $ pg_dump mydb > db.sql

    To reload such a script into a (freshly created) database named newdb:

       $ psql -d newdb -f db.sql

    To dump a database into a custom-format archive file:

       $ pg_dump -Fc mydb > db.dump

    To dump a database into a directory-format archive:

       $ pg_dump -Fd mydb -f dumpdir

    To reload an archive file into a (freshly created) database named newdb:

       $ pg_restore -d newdb db.dump

    To dump a single table named mytab:

       $ pg_dump -t mytab mydb > db.sql

    To dump all tables whose names start with emp in the detroit schema, except for the table named 

    employee_log:

       $ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql

    To dump all schemas whose names start with east or west and end in gsm, excluding any schemas 
    whose names contain the word test:

        $ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql

    The same, using regular expression notation to consolidate the  switches:

       $ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql

    To dump all database objects except for tables whose names begin with ts_:

       $ pg_dump -T 'ts_*' mydb > db.sql

PostgreSQL 에서 Function 만들기 예제

코드를 받아서 텍스트로 변환하여 리턴해 주는 예제

1 -> A

2 -> B

3 -> C

CREATE or REPLACE FUNCTION fn_test(char)

RETURNS varchar AS $$

declare

  stat_txt varchar(20);

begin

case $1 when '1' then 

             stat_txt = 'A';

  when '2' then 

      stat_txt = 'B';

  when '3' then 

      stat_txt = 'C';

  else stat_txt = '';

 end case;

return stat_txt;



end; $$ 

language 'plpgsql';

PostgreSQL 공식 매뉴얼 링크

https://www.postgresql.org/docs/9.2/static/sql-createfunction.html