postgresql 테이블 조인(join) 예시



1. 테이블 사이에 이름이 같은 필드가 없을 경우 


그냥 필드 이름을 나열하고 from 이후 두 개의 테이블을 나열하고 테이블 join 조건을 where에 채워주면 됨 



select name,  area, deposit_date as adate , deposit_min as aprice from table1, table2 where table1.id = table2.hid and type = '1';

postgresql 의 기본키 자동 증가





postgreSQL 사용법



기본키 자동증가 설정하기 



최근 버전


ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;



예전 버전 


1. 시퀀스를 생성한다. 



create sequence sequence_name owned by tablename.columname;



2. 기본키의 기본 값 수정



기본 키의 기본값에 nextval('sequence_name'::regclass) 를 추가한다. 


alter table test alter column id set default nextval('sequence_name');






[ postgreSQL] 다른 테이블 복사하기





PostgreSQL 사용법 


다른 테이블 구조 & 데이터 복사하기 






데이터만 복사하기 




CREATE TABLE newtable AS SELECT * FROM oldtable;





데이터 & 인덱스 &  constraint 등의 정보 다 같이 복사하기 


아래 두개의 sql 문을 차례로 실행한다. 복사 속도는 위의 방식이 더 빠르지만 인덱스 정보가 같이 복사됨.



 


create table newtable (like "oldtable" including all);

insert into newtable ( select * from "oldtable");




다른 테이블의 일부 필드만 복사하기 



insert into items_ver(item_id, item_group, name)

select * from items where item_id=2;



insert into items_ver (item_id, name, item_group)

select item_id, name, item_group from items where item_id=2;








  • 1122 2018.02.28 11:35 ADDR 수정/삭제 답글

    초대장 하나 받고싶어요 도와주세요 ramen1225@naver.com

postgresql 테이블에 칼럼 추가 + 커멘트 달기






postgresql 테이블에 칼럼 추가 + 커멘트 달기




칼럼 추가 방식 


ALTER TABLE 테이블명 ADD COLUMN 컬럼명 데이터타입 제약조건


참고) postgresql numeric type


 



예시 


- int 형의 칼럼 추가하고 코멘트 달기


ALTER TABLE 데이블이름 ADD 칼럼이름 integer;

COMMENT ON COLUMN 테이블이름.칼럼이름 IS '코멘트';



- varchar 칼럼 추가하면서 default 값 설정하기  


ALTER TABLE test_tb ADD COLUMN name varchar(50) DEFAULT 'NONAME'


- character varying 사이즈 120인 칼럼 추가하기 


ALTER TABLE test_tb ADD COLUMN name character varying(120)



default 값이 있는 칼럼을 추가할 경우 

postgresql 10 이하 버전에서는 테이블을 다시 생성하기 때문에 

시간도 오래 걸리고 그 동안에 access exclusive lock 에 걸리게 됩니다. 

access exclusive lock 은 lock level 중 제일 높은 레벨로 select 까지 막히게 됩니다. 

(실서비스 하는 데이터베이스에서 매우 곤란한 상황 ; - ; ) 


그럴 경우 한번에 칼럼을 추가하고 default 값을 설정하는 것보다는

아래의 예시와 같이 칼럼을 추가하고 기본 값을 설정하는 것이 좋습니다. 



ALTER TABLE test_tb ADD COLUMN name varchar(50);

update test_tb set name = 'NONAME';


ALTER TABLE items ADD COLUMN updated_at timestamptz;

UPDATE items SET updated_at = now();


 


참고

https://americanopeople.tistory.com/272

https://americanopeople.tistory.com/292?category=695027

https://www.postgresql.org/docs/10/datatype-numeric.html









postgresql 에서 문자열 처리 방법 ( concat, split_part)



문자열을 구분자로 나누는 경우 



postgresql에서 문자열을 구분자로 자를 경우 split_part 함수를 사용한다. 

사용법은 split_part('원래 문자열', '자를 문자', 위치) 로 사용한다. 


예를들어 1998-01-04 문자열일 경우 

split_part('1998-01-04', '-', 1)을 리턴받으면 1998이 나오게 된다. 


필드 업데이트 예시 : 

update 테이블이름 set year = split_part(date::TEXT, '-', 1)::SMALLINT, month=split_part(date::TEXT, '-', 2)::SMALLINT





문자열을 합칠 경우 




postgresql 에서 문자열 또는 필드를 붙일 경우는 concat 함수를 사용한다. 


사용법은 간단하다. 


concat()안에 붙이기 원하는 필드 또는 문자열을 구분자 , 을 사용하여 적어주면 된다. 

이렇게 생성된 새로운 필드의 이름은 뒤에 적어주면 된다. ("" 은 옵션) 


간단하게 예시를 보자. 



SELECT employee_id,first_name,last_name,

concat(first_name,'-',last_name) "Name of the Employee" 

FROM employees

WHERE department_id=100;



postgresql 문자열 타입을 date 타입으로 변환하기




postgresql 테이블을 사용하다 날짜 정보 필드가  문자열 타입으로 되어있어서 

date 타입 필드를 생성하고 to_date 함수를 사용하여 업데이트 하고 과정을 기록해 둡니다. 



우선 데이트 타입 필드를 생성합니다. 

to_date 함수는 text문자열을 받아서 지정해둔 format에 따라 date 타입을 생성해주는 함수입니다. 




to_date(text,format);


SELECT to_date('20170103','YYYYMMDD');



to_date('20170103','YYYYMMDD');



---


업데이트 예시


update 테이블이름 set 필드이름 = to_date( concat(year,'-', month, '-01'), 'YYYY-MM-DD')





참고: http://www.postgresqltutorial.com/postgresql-to_date/




postgresql 테이블을 csv로 저장하기 (with pandas)




postgresql 테이블을 csv로 저장하기 (with pandas)





flask에서 postgreSQL 테이블 내용을 csv로 저장하는 함수 작성을 하고 기록해둔다.

처음에는 psycopg2에서 제공하는 함수를 사용해볼까 했는데 

함수 자체의 설명도 부족하고 어차피 기계 실험해야 하면 pandas 사용해야 해서 

pandas 의 read_sql, to_csv의 함수를 사용했는데 간편하고 좋은 것 같다. 

 



import psycopg2 as pc

import pandas as pd



dataReceive = request.get_json()

df = get_data(dataReceive)


def get_data(req_data):

    try:

      conn = pc.connect("dbname=디비이름 user=계정 password=암호")

      curs = conn.cursor()

      sql = "Select {0}, date, region_cd, {1} From 테이블이름 where {2} > 0 order by date".format(req_data['name'], fieldstr, req_data['name'])

      df = pd.read_sql(sql, conn)

      df.to_csv("/experiment/result.csv", encoding='utf-8', header = True, doublequote = True, sep=',', index=False)

      print("CSV File has been created")

      conn.close()

    except Exception as error:

      logger.warn("ERROR in get_data function")

      logger.warn(error)

    return df




참조: https://www.mydatahack.com/how-to-bulk-load-data-into-postgresql-with-python/



mysql 데이터베이스 백업 스크립트




mysqldump를 사용하여 데이터베이스를 매일 백업하는 쉘스크립트를 작성하였다. 



mysqldump 사용법 

   mysqldump는 DB에서 원하는 DB만, table만 백업 할 수 있고, 전체 DB를 백업 할 수 있다.


  (1) 전체 백업

    mysqldump -u계정이름 -p -A > all.sql


  (2) 특정 DB 백업

    mysqldump -u계정이름 -p 특정DB명 > 특정DB명.sql


  (3) 특정 DB에 특정 테이블 백업

    mysqldump -u계정이름 -p 특정DB명 특정table명 > 특정DB명.특정table.sql


  복원법

     mysql -u [userId] -p [password] [DB명] < 특정DB(특정table).sql


- DB 백업 스크립트 작성  

# vi /root/dbbackup.sh


mysqldump를 이용하여 디비나 테이블을 백업하고 백업한 파일은 gzip을 이용하여 압축하여 백업 폴더에 저장한다. 


- db 계정정보를 스트립트 안에 명시하는 경우 

  (스크립트에 명시적으로 계정정보를 입력하는 것은 보안상 위험 있음. 사용 예시로만 참고할 것


#!/bin/bash

DB_BACKUP="/home/dbbackup/"

DB_USER="username"

DB_PASSWD="passwd"

db="dbname"

table="tablename"


# Remove backups older than 3 days

find $DB_BACKUP -ctime +3 -exec rm -f {} \;




# 데이터베이스를 모두 백업할경우 

mysqldump --user=$DB_USER --password=$DB_PASSWD -A | gzip > "$DB_BACKUP/mysqldump-$db-$(date +%Y-%m-%d).gz";




# 데이터베이스를 백업할경우 

mysqldump --user=$DB_USER --password=$DB_PASSWD $db | gzip > "$DB_BACKUP/mysqldump-$db-$(date +%Y-%m-%d).gz";



# 데이터베이스의 특정 테이블을 백업할경우 

mysqldump --user=$DB_USER --password=$DB_PASSWD $db $table | gzip > "$DB_BACKUP/mysqldump-$db-$table-$(date +%Y-%m-%d).gz";






- db 계정정보를 my.cnt에 명시하는 경우 


my.cnt 




[mysqldump]


user=사용자이름

password=암호




스크립트 


#!/bin/bash

DB_BACKUP="/home/dbbackup/"

db="dbname"

table="tablename"


# Remove backups older than 3 days

find $DB_BACKUP -ctime +3 -exec rm -f {} \;




# 데이터베이스를 모두 백업할경우 

mysqldump  -A | gzip > "$DB_BACKUP/mysqldump-$db-$(date +%Y-%m-%d).gz";




# 데이터베이스를 백업할경우 

mysqldump  $db | gzip > "$DB_BACKUP/mysqldump-$db-$(date +%Y-%m-%d).gz";



# 데이터베이스의 특정 테이블을 백업할경우 

mysqldump  $db $table | gzip > "$DB_BACKUP/mysqldump-$db-$table-$(date +%Y-%m-%d).gz";









/root/dbbackup.sh 를 실행하여 정상적으로 스크립트가 작동하는지 확인한다. 



정상적으로 스크립트가 실행이 되면 보안을 위해 root만 수정하고 실행할수 있도록 권한 설정 

 chmod 700 /root/dbbackup.sh


- 크론택에 작업 등록 

#crontab -e



00 04 * * * /root/dbbackup.sh




매일 새벽 4시마다 디비 백업을 하도록 크론택을 등록하고 작업 완료. 



postgreSQL - .pgpass 파일 설정법






.pgpass 파일 설정 방법





pgpass 파일은 postgreSQL 계정의 암호를 기록해두는 설정파일로 

해당 파일을 생성해두면 psql 이나 pg_dump 명령어를 사용할때 암호를 입력하지 않고 사용할 수 있다. 

그리고 따로 암호를 입력할 수 없는 스크립트에서도 pg_dump 등의 명령어를 사용할 수 있게 해준다. 



우선 홈디렉토리의 계정에 .pgpass 파일을 생성한다. 


포맷은 아래와 같다. 



hostname:port:database:username:password



처음 네 개의 필드는 값을 입력하거나 * (와일드카드)를 사용한다. 암호 필드는 와일드 카드를 사용할 수 없다. 

앞의 값이 다 * 라면 현재 커넥션의 파라미터들(사용자 이름, 디비 등)이 사용된다. 


아래는 pgpass 파일 설정 예시이다. 





#hostname:port:database:username:password

*:*:*:postgres:passwdhere





마지막으로 .pgpass 파일의 권한을 600으로 설정해둔다. 

해당 설정보다 약한 설정이 설정되어 있으면 postgres는 이 파일을 무시할 것이다. 




chmod 0600 /home/userdirectory/.pgpass




관련 링크 : https://www.postgresql.org/docs/current/libpq-pgpass.html

리눅스(ubuntu)에 postgreSQL 설치하기




리눅스에 postgreSQL 설치한 과정을 정리해두겠다. 


apt-get install로 아래와 같이 패키지 설치를 한다. 


sudo apt-get install postgresql postgresql-contrib








# sudo apt-get install postgresql postgresql-contrib

Reading package lists... Done

Building dependency tree

Reading state information... Done

The following packages were automatically installed and are no longer required:

  duplicity librsync1 python-lockfile

Use 'apt-get autoremove' to remove them.

The following extra packages will be installed:

  libossp-uuid16 libpq5 postgresql-9.3 postgresql-client-9.3

  postgresql-client-common postgresql-common postgresql-contrib-9.3

Suggested packages:

  uuid oidentd ident-server locales-all postgresql-doc-9.3 libdbd-pg-perl

The following NEW packages will be installed:

  libossp-uuid16 libpq5 postgresql postgresql-9.3 postgresql-client-9.3

  postgresql-client-common postgresql-common postgresql-contrib

  postgresql-contrib-9.3

0 upgraded, 9 newly installed, 0 to remove and 578 not upgraded.

Need to get 4,137 kB of archives.

After this operation, 17.7 MB of additional disk space will be used.

Do you want to continue? [Y/n]

...




이제 설치가 완료되었으니 psql로 command line 을 실행해보자. 


root로 psql을 실행하면 아래와 같이 root라는 role이 없다고 에러가 나온다.




root# psql

psql: FATAL:  role "root" does not exist



아직 root라는 role이 생성이 안되어 있으니
아래와같이 postgresql 의 슈퍼 유저인 postgres 로 실행을 해야한다. 


sudo -u postgres psql

postgres에 들어오면 아직 암호가 설정이 안되어 있으니 
마지막으로 password로 암호를 설정하고 \q로 종료한다. 



postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# \q